There are plenty of companies online offering paid software to track your portfolio investments, sadly most of them are not very well made, some of them even sell your personal information.

How else would they make money? It’s definitely not the subscription price.

Creating your own 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.

All methods used are completely free.

So please, bookmark or save this for future reference, because it’s going to be a very detailed step-by-step guide to build your own investment portfolio tracker.

Want to Learn Spreadsheets?
Learn by DOING on our online interactive platform!

You also get a certificate of completion when you finish!

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!

What can the stock portfolio tracker do?

Here are the basic functionalities the portfolio tracker must have:

  • Automatically calculate return on investment
  • Automatically get stock data from google finance
  • Graph individual stock performance
  • Graph portfolio diversification
  • Graph 30-day historical performance of the portfolio
  • Graph 12-month historical performance of the portfolio

This is the final portfolio tracker that you will be making, but with a few exceptions such as the styling, which you will do yourself.

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Investment Tracker Tutorial

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.

Stock Portfolio Tracker Tutorial

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).

Stock Portfolio Tracker Tutorial

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.

Stock Portfolio Tracker Tutorial

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 Initial 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.

Stock Portfolio Tracker Tutorial

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.

Stock Portfolio dashboard tutorial

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.

Stock Portfolio dashboard tutorial

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.

Stock Portfolio dashboard tutorial

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.

Stock Portfolio dashboard tutorial

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.

Stock Portfolio dashboard tutorial

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.

Stock Portfolio dashboard tutorial

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.

Stock Portfolio dashboard tutorial

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.