# How To Make A Google Finance Portfolio Tracker For Beginners

Creating your own Google Finance portfolio tracker is a cheap and easy way to help you track your stock portfolio performance.

That is exactly why I’m going to teach you how to **create your own investment portfolio tracker on google sheets**.

In this article, we’ll show you how to grab data using google finance all the way to building an interactive dashboard.

**Free Bonus:** **Click Here To Get A Free Introduction To Financial Analytics in Spreadsheets** and learn the basics of spreadsheets by working with rows, columns, addresses, and ranges.

**Affiliate Disclaimer:** We sometimes use affiliate links in our content. This wonâ€™t cost you anything, but it helps keep our lights on and pays our writing and developer teams. We appreciate your support!

## How To Make A Google Finance Portfolio Tracker

### Step 1: Create a new google spreadsheet

First step is to create a new google spreadsheet. You can do this by signing into your google account, then click on a plus button to add a new google spreadsheet. I have named mine “Portfolio Tracker” for safe-keeping.

Once this spreadsheet has been created, it’s safely stored and auto-saved onto your google account.

### Step 2: Enter Headers in the stock portfolio tracker spreadsheet

Now, we need to create the skeleton of our spreadsheet, this will make everything much easier in the later steps.

In this step we will start entering the essential headers, these are the columns of data we want our portfolio tracker to show. In my case, I would like to see things such as the ticker symbol, price of purchase, current stock price, etc.

For the exact design, you can check out the image below.

### Step 3: **Input conditional formats in the PortfolioTracker spreadsheet**

Next, we need to start inputting conditional formats to grab information from Google finance. It is going to be a very detailed step so bear with me.

**Ticker**

The ticker symbol is going to be the first input, most of our conditional formats will use the ticker symbol to grab data. This can be simply found through a google search, an example of a ticker symbol of Facebook stock is FB, this would be inputted in our ticker symbol column. Let’s store our ticker symbol in cell **B7**.

**Stock Name**

To grab the stock name we can simply make google spreadsheet’s function **=GoogleFinance(Ticker Symbol, “name”)** . Since our ticker symbol has already been inputted in our worksheet, we can call it using **B7**, which is the location where our ticker symbol FB is stored on the worksheet.

So to grab the name of ticker symbol FB in my case would be **=GoogleFinance(B7, “name”).**

**Number Of Stocks**

In terms of the number of stocks, this will be a manual input by the user, if we own 10 shares of Facebook we would input 10 next to the cell of the ticker symbol FB.

**Purchase Price**

Similarly, the purchase price will also be a manual input by the user, for example, 5 of my stock were purchased at $240, the other 5 were purchased at $250, this will give us an average purchase price of $245.

Therefore, the purchase price of our Facebook stocks would be $245.

**Last Price**

To get the last price it’s a similar process to grabbing the name of a stock, we will be using the google sheet’s function, but this time instead of “name” for the attribute it would be **=GoogleFinance(Ticker Symbol, “price”)**.

So to get the current price of our Facebook stock it would be **=GoogleFinance(B7, “price”).**

**Amount invested**

The amount invested would be the total initial investment you have used to purchase all your stocks, we can calculate our total initial investment using the formula **“Total amount of stocks * Average purchase price”** in my case I have purchased a total of 10 stocks at an average price of 145.

Since both these values are already stored in our spreadsheet we can simply calculate the total amount invested by calling **=C7*D7**.

**Market Value**

The current market value is how much your current stocks are worth, this can be simply calculated using the formula **“Total amount of Stocks * Current stock price”**.

So in our case, the number of Facebook stock we own is stored in **C7** and the last price is stored in **E7**. Therefore our market value formula would be **=C7*E7**.

**% Change**

The percent change is how much percent has our stock investment grown, this can be either a negative or a positive value. The exact formula to calculate the percentage change is **“Market value – Amount invested / Amount invested”.**

Therefore, to calculate the % Change in our Facebook stock it would be** ****=(G7-F7)/F7**.

**Dollar Change**

Dollar change is how much our initial investment has changed since we purchased our stock, this value can be either a positive or negative value. To calculate how much our initial investment has changed we can use the formula** “Market Value – Amount invested”.**

Therefore, our dollar change for the Facebook stock can be calculated using **=H7-G7.**

**Total Market Value**

The total market value is how much our total portfolio is currently worth, this can be calculated by adding all the values inputted in our **Market Value column** for each stock. In my case, I only have one stock therefore the total market value would only be **=G7.**

**Total Initial investment**

The total initial investment is how much deposit we have used for our entire portfolio. This can be calculated by summing all the values in our **Amount invested column**. In my case, I had only invested a total of 2450$ for my 10 Facebook shares. So, for the total initial investment, it would be only just **=F7**.

**Portfolio Gain**

Portfolio gain is the return of our total initial investment, this can be either a positive or negative value. The formula to calculate our total return is **“Total Market Value – Total Initial investment”.**

To calculate the total portfolio gain we can call **=B4-B3.**

**Total % change**

The total percent change is how much percent change our portfolio investment has moved, this can be either a negative or a positive value. The exact formula to calculate the percentage change is **“Total market value – Total initial investment / Total Initial investment”**.

To calculate the total % change in the portfolio it would be **=(B4-B3)/B3.**

**Weight**

The weight attribute will show the percentage value of our individual stock investment compared to the entire portfolio, this can be calculated using **“Market value / Total market value”**, in my case it would be **=G7/B4** for Facebook.

The final result should look something like this.

### Step 4: **Input stocks into your Portfolio Tracker spreadsheet**

Finally, all our hard work will come to use.

In this step we want to input all our stocks into our spreadsheet portfolio, the only columns you need to input are the ticker symbol, a number of stocks, and purchase price, as shown in the blue highlighted cells below.

For the rest of the inputs we will our special tool called **autocomplete**, you can use this by clicking the desired cell’s bottom right corner then drag that cell all the way down, in my case, I dragged mine to cell 11.

What this essentially does is that it copies each formula you have completed previously and moves those formulas down to the desired cell. The autocomplete knows how to change its formula to fit the desired row.

### Step 5: **Insert a distribution pie chart into your Portfolio Tracker spreadsheet**

Now we need to add our distribution pie chart, which will be using only the **weight column and stock name.**

To get the pie chart, you need to click on **Insert > Chart.**

Then choose chart type to the pie chart.

Next you will have two options Label and Value. For the label option, highlight all the cells under **Stock name** similarly for the value label highlight all the cells under **Weight**.

This will automatically generate a distributed pie chart.

### Step 6: **Insert percentage change bar chart into your Portfolio Tracker spreadsheet**

In this step we are going to create a bar chart, the bar chart will be a graphic representation of our** %change**. This process is very similar to the previous step.

To get the bar chart click on **insert > chart**, for our X axis we want to highlight all the** Stock names** and for the series we want to highlight our **% change**.

### Step 7: **Insert 30 day historical return line chart into your HistoricalData spreadsheet**

Now, we want to add a little line chart to see the historical data of our stock portfolio performance. In our line chart we want the graph to indicate **current date and current price of the previous 30 days** to show the historic performance of our portfolio.

This **historicaldata sheet** will store all our data each day, so its going to store a lot of data.

That’s why we want to create a new sheet, you can create a new sheet in your current spreadsheet by clicking the plus button on the bottom left, I have named mine **HistoricalData.**

A good naming convention is not including spaces in your sheet’s name or use a “_” whenever you want to add a space, this is to make it easier to call cells from different sheets.

The first that we need is to import the ticker Symbol from our main sheet, my main spreadsheet is called **“PortfolioTracker”**.

We can do this by using **=PortfolioTracker!B8** and then dragging the cell down and automatically copying the data from our mainsheet.

Similarly, we need to import the number of stocks we have for each ticker symbol, this can be done by using **=PortfolioTracker!C8** and dragging the cells down to copy data from our **PortoflioTracker **into our **HistoricalData** sheet.

Next we want to create the dates we want to track. So to track the 30 days we need to firstly track what the current performance is today that can be easily done using the formula **=today()** which is automatically updated each day.

And for the previous 30 days, we would just use the formula **=today()-30 **which subtract the dates by 30 days.

Now we want to start using our ticker symbols, in this step, we want to create ticker headers that are two columns apart and listing their headers with our ticker symbols such as** =A2 **for Facebook and etc.

Do this for all your ticker symbols.

After we have completed the headers, lets start using google finance to import our data, we want our formula to grab the daily stock prices between today and 30 days before, we can do this by using the formula **=googlefinance(Ticker, “price”, Startdate, Enddate).**

For my first apple stock to get the 30 days daily price we can simply call **=googlefinance(A2, “price”, A9, A10).**

Now that we have all our data ready we want to calculate the value of our Facebook holding in each single day, this can be completed using the formula **“Close Price * Number of Stocks”**.

For the Facebook stock we can use the formula **=E4*B2** to calculate the value of our holdings in the first day, then use our neat little tool auto-complete for the rest of the days.

After all the stocks have been calculated, our next step will be calculating the total portfolio’s value each day.

In this step three columns** Date, Total and** I**nitial investment **have been inserted, these three columns are what we will be using to input data into our 30-day historical graph.

**Date:** We want our date to automatically update each day in synchronize with our other stocks, this can be simply completed by simply copying the date from our first Facebook date column, so start at **D4** then autocompleted to **D22**.

**Total: **The total attribute shows our total portfolio’s worth at the end of each day, to calculate the total value of the portfolio, we should sum each individual’s stock’s total value each day, for example in the Facebook stock the first date starts on **12/7/2020 **with a total value of **F4,** next stock Apple start date **12/7/2020** with a total value of **I4 **and so on.

The total value on **12/7/2020** should look something like this **=F4+I4+L4+O4+R4. **Then for the other days, we can simply use autocomplete.

**Initial investment:** For the Initial investment we would be using our **Total initial investment** from the main spreadsheet this can be completed using **=PortfolioTracker!B4**. After the first input has been added on the initial investment cell, autocomplete to fill the rest of the column.

Now that we have all our data ready, let’s go back to our main sheet and create the chart.

To insert the chart click on **Insert > Chart** then change the chart to a **line graph**.

For the** X-axis, **dates would be the input this can be simply called using the formula **HsitoricalData!T4:T23, **which** **locates to the HistoricalData sheet then the cells T4 to T23 which is where we stored our dates in the previous step.

For the **Y-axis,** we want to output the historical prices of our portfolio in the last 30 days, this can be completed by calling the formula **HistoricalData!U4:U23,** which is where our Total column was inputted in the HistoricalData sheet.

The last step is an optional one, if you would like to have your graph show your initial investment just simply click on “Add series” and add the column where the initial investment was stored, in my case, it would be** HistoricalData!V4:V22**.

### Step 8: **Insert 12 month historical return line chart into your HistoricalData spreadsheet**

Now that we have our 30-day Historical chart sorted out, let’s make our 12-month historical return chart!

For 12 months dates the process is very similar to the 30-day step, the only difference is that we will need to continuously subtract by 30-day increments till the dates reach** **a 360 loop** **for a full year.

The result should look something like the image below.

In the next step we need to create the headings for each stock, date total, and initial investment, this step should be self-explanatory, and it is completely the same process as the 30-day historical graph step.

The image below should be a reference on how to create the headings.

Now we want to import our google finance data for each stock. This step is going to be **ALOT** of work.

What we need to do in this step is import stock data for each month, so for the first month in the Facebook stock we need to import the formula **=googlefinance(A2, “price” A13)** the next month will be **=googlefinance(A2, “price” A14).**

The only reason why this step is so tasking is because autocomplete will not work in this step since google finance automatically loads a header of date and close, therefore all months for each step required to be a manual input.

After we have inputted the financial data for each month, let’s get the total value of the stock holdings of those months, you can do this by multiplying the close price and the number of shares you hold for each stock.

This process is an exact similar process as the 30-month historical chart step, except for some instances where date and close are used as headers the total column will output a null value.

Now we are going to fill the **Date**, **Total**, and **Initial investment columns, **each column values are color-coded as shown in the image below.

For the **date column**, Amazon dates were used (highlighted yellow), in your case, any date column can be used as long as they are automatically updated through google finance.

In the** total column**, we want to see our total portfolio value each month, this can be calculated by summing each row of the stocks within the months, for instance in the first month **1/5/2021** the portfolio value is calculated by summing all the stock’s total value within those dates this would be **=F28+I28+L28+O28+R28 (**highlighted red).

Now for the initial investment, which is very simple, we would just use the formula **=V21 **which is where we stored our initial investment(highlighted green), autocomplete was then used to fill in the rest of the column.

Now that we have all our data ready, let’s go back to our main sheet and create the chart.

To insert the chart click on **Insert > Chart** then change the chart to a **line graph**.

For the** X-axis, **dates would be the input this can be simply called using the formula **HsitoricalData!T28:T40, **which** **locates to the HistoricalData sheet then the cells T28 to T40 which is where we stored our dates in the previous step.

For the **Y-axis,** we want to output the historical prices of our portfolio in the last 12 months, this can be completed by calling the formula **HistoricalData!U28:U40** which is where our Total column was inputted in the HistoricalData sheet.

The last step is an optional one, if you would like to have your graph show your initial investment just simply click on “Add series” and add the column where the initial investment was stored, in my case, it would be** HistoricalData!V28:V40**.

### Step 9: **Customize your Portfolio!**

Congrats! You have successfully created your own stock portfolio tracker on Google Sheets.

Sadly, my help stops here, since all the essential tools for your portfolio tracker have been added, all you need to do next is customize the portfolio such as adding titles, axis, color scheme, etc.