Skip to main content

Monitor stock portfolio with Google Sheets (Pivot table and GOOGLEFINANCE function)

As an investor, it is important to know the latest state of the stock portfolio. We need to know what stocks currently owned in the portfolio, how many shares for each one, how much dividend or gain contributed so far by each stock, etc. As we have registered stock transactions in a spreadsheet with Google Sheets, we can easily have the latest update from the stock portfolio by using pivot tables and GOOGLEFINANCE function.

The Positions sheet: Contribution of each stock in the portfolio.

Use a pivot table to group transactions by symbols

The pivot table helps to see relationships between data points. To see how each stock contributes to the portfolio, we will create a pivot table that originated from the Transactions sheet.

  1. Select the Transactions sheet.
  2. Select the 5 columns A:E.
  3. In the menu at the top, click Data and then Pivot table.
  4. Select the newly created pivot table sheet, if it’s not already open.
  5. Rename the sheet as Positions or as you prefer.

Create a pivot table from 5 columns of the Transactions sheet

Configure the pivot table Positions

The most essential information we want to know about a stock portfolio is how many shares there are for each stock. Let's configure then the pivot table Positions to answer that question based on data from the Transactions sheet:

  1. Select the Positions sheet where the pivot table is located.
  2. Select the cell A1 so that the Pivot table editor is displayed on the right-side panel.
  3. In the Rows section, click Add and select Symbol from the dropdown list. All symbols that appeared in transactions will be displayed in column A. Un-check the Show totals checkbox as it is not needed.
  4. In the Values section, click Add and select Shares from the dropdown list. To count the number of shares for each symbol, choose Sum in the Summarize by select box.
  5. As a transaction of type dividend has also a number of shares greater than 0, it is hence necessary to ignore that type of transaction to count correctly the number of shares for each symbol. In the Filters section, click Add and select Type from the dropdown list. Select then only 2 types BUY and SELL so that only transactions of those types are selected for the pivot table.

Pivot table editor - count the number of shares for each symbol that appeared in transactions

As a result, the Positions sheet is now showing the number of shares for each stock in the portfolio. If the number of shares is 0, it means that stock was sold out from the portfolio. If the number of shares is greater than 0, it means that stock is still owned in the portfolio.

The number of shares for each stock in the portfolio

Use GOOGLEFINANCE function to get real-time information for stocks

Having the number of shares for each stock in the portfolio is essential but it is not enough to manage efficiently the investment. We need to know the current price of each stock to evaluate how well each stock is performing. We might also need to know some basic financial information of companies to make a decision whether for buying or selling shares of a company.

Luckily, the built-in function GOOGLEFINANCE allows us to fetch that financial information directly into Google Sheets. For examples:

  • =GOOGLEFINANCE("NASDAQ:TSLA","price"): Get the current price for the stock of Tesla Inc
  • =GOOGLEFINANCE("NASDAQ:TSLA","pe"): Get the current price / earning ratio for the stock of Tesla Inc
  • =GOOGLEFINANCE("NASDAQ:TSLA","low52"): Get the 52-week low price for the stock of Tesla Inc
  • =GOOGLEFINANCE("NASDAQ:TSLA","high52"): Get the 52-week high price for the stock of Tesla Inc
  • =GOOGLEFINANCE("NASDAQ:TSLA", "price", DATE(2018,1,1), TODAY(), "DAILY"): Get all daily close prices from 01/01/2018 until today for the stock of Tesla Inc

So for each line in the Positions sheet, we have the symbol on column A, its number of shares on column B, its transactions in the Transactions sheet and a powerful GOOGLEFINANCE function let's see what we can derive from them:

Column Description Formula
Price the current price of the symbol (delayed by up to 20 minutes according to the official documentation) =GOOGLEFINANCE(A2)
Market Value the current market value by multiplying the current price of the symbol and its current number of shares in the portfolio =B2*E2
Change how much the value for a stock has been changed since the previous trading's close =GOOGLEFINANCE(A2,"change")*B2
Change % The percentage change in value since the previous trading day's close. =GOOGLEFINANCE(A2,"changepct")/100
Low 52 The low-52 price =GOOGLEFINANCE(A2, "low52")
High 52 The high-52 price =GOOGLEFINANCE(A2, "high52")
P/E The price/earnings ratio =GOOGLEFINANCE(A2,"pe")
Total Buy the total money spent on buying shares of stock. It is computed by using SUMIFS to sum up all BUY transactions amount for that stock. =SUMIFS(Transactions!D:D,Transactions!B:B,"BUY",Transactions!C:C,A2)
Total Sell the total money received by selling shares of stock. It is computed by using SUMIFS to sum up all SELL transactions amount for that stock. =SUMIFS(Transactions!D:D,Transactions!B:B,"SELL",Transactions!C:C,A2)
Cost the total buy minus the total sell for the stock. If the current number of shares owned for a stock is 0, the cost will be the total buy amount. =IF(B2>0,ABS(I2+J2),ABS(I2))
Unit Cost the cost for each share of stock. =IF(B2>0,K2/B2,"")
Capital Gain the un-realized gain if all shares are sold out or the realized-gain if all shares were sold out. It is the sum of the total buy, the total sell, and the current market value. =I2+J2+H2
Capital Gain % The percentage of capital gain comparing to the cost =N2/K2
Total Dividend the accumulated dividend received by owning shares of a stock. =SUMIFS(Transactions!D:D,Transactions!B:B,"DIVIDEND",Transactions!C:C,A2)
Yield % the percentage of dividend comparing to the cost. =L2/ABS(K2)

The columns are arranged in the way where:

  • The first 3 columns Symbol, Shares, and P/E are fixed while scrolling horizontally or vertically.
  • The Low 52, Price, Unit Cost, and High 52 columns are close to each other to evaluate quickly the current price compared to its historical prices and its unit cost.
  • The Market Value, Total Buy, Total Sell, and Cost columns are close to each other to evaluate quickly the distribution.
  • The Total Dividend, Yield %, Capital Gain, and Captial Gain % columns are close to each other to evaluate quickly the performance.

Conditional formatting columns

To have a good insight about the portfolio at a quick glance, we can apply conditional formatting for certain columns. As mentioned in their documentation, Google Sheets offers 2 types of conditional formatting, which are single color or color scale.

  • Single Color: The columns Capital Gain and Change will be applied to the type of conditional formatting as their values are absolute. If the cell's value is greater than 0, the cell's color is green. If the cell's value is less than 0, the cell's color is red.

Single color conditional formatting with Google Sheets

  • Color Scale: The columns P/E, Yield %, and Capital Gain % will be applied to the type of conditional formatting as their values are relative. For instance, as the P/E is often used to evaluate how expensive a stock is, it is formatted such as the smaller it is, the greener is the cell's color.

Color scale conditional formatting with Google Sheets

Demo

You can take a look at the sample spreadsheet in this post to have an idea of how the data is organized and related. It is possible to make a copy of the spreadsheet to study it thoroughly.

Let's see if the created Positions sheet can answer our questions at the beginning:

  • What are the stocks presented in the portfolio? The column Symbol gives the answer.
  • How many shares are there for each stock? The column Shares gives the answer. The ENGI stock has 0 shares meaning that it was sold out from the portfolio.
  • How much does worth each stock? The column Market Value gives the answer.
  • How much dividend has been earned so far for each stock? The column Total Dividend and Yield % give the answer.
  • How much gain has each stock made so far? The column Capital Gain and Capital Gain % give the answer.

Note

To better understand the overall concept, please check out this post Create personal stock portfolio tracker with Google Sheets and Google Data Studio.

References

Disclaimer

The post is only for informational purposes and not for trading purposes or financial advice.

Comments

  1. Awesome Work, need your help to understand Evolutions sheet. Is it manual work or you use any formula/settings to update Invested Money/Cash/Market Value/ Portfolio Value and Gain columns.

    Really appreciate for your help.

    Thanks.

    ReplyDelete

Post a Comment

Popular posts from this blog

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

I have been investing in the stock market for a while. I was looking for a software tool that could help me better manage my portfolio, but, could not find one that satisfied my needs. One day, I discovered that the Google Sheets application has a built-in function called GOOGLEFINANCE which fetches current or historical prices of stocks into spreadsheets. So I thought it is totally possible to build my own personal portfolio tracker with Google Sheets. I can register my transactions in a sheet and use the pivot table, built-in functions such as GOOGLEFINANCE, and Apps Script to automate the computation for daily evolutions of my portfolio as well as the current position for each stock in my portfolio. I then drew some sort of charts within the spreadsheet to have some visual ideas of my portfolio. However, I quickly found it inconvenient to have the charts overlapped the table and to switch back and forth among sheets in the spreadsheet. That's when I came to know the existence

Stock Portfolio Tracker Dashboard With Google Data Studio

In the series of building personal stock portfolio tracker, we have learned how to use Google Sheets to register transactions . We have then used the pivot table and GOOGLEFINANCE function to compute the latest position of the stock portfolio . We have made a step further to use Apps Scripts to compute automatically and daily the stock portfolio's evolution . However, after all, we have several tables of data as the result which do not tell any story yet. We need to present those data in graphs to understand the portfolio's performance and make improvements accordingly. We can effectively plot graphs in different aspects directly in Google Sheets as it provides many charting tools. However, in my experience, having charts and data in the same spreadsheet is not very convenient because charts and tables tend to overlap each other and of lack of interactivity. We should have a dedicated dashboard to have an overview of the stock portfolio and we can do it greatly with Google Dat

Compare stock portfolio to market indexes

As investors, we always want to see our portfolio grows over time. If a portfolio made a 1000$ of gain in one year, is it good enough? Should we gauge the portfolio's performance against an alternative investment, for instance, a market index? Should we aim to beat that index? In this post, we will see how to compare a stock portfolio to market indexes by using Google Sheets, Apps Script, and Google Data Studio. Select market indexes to compare Method to compare Guides Prepare data with Google Sheets and Apps Script Visualize benchmark in Google Data Studio Demo Conclusion Note References Disclaimer Select market indexes to compare In the context of stock investment, investors can compare the performance of their portfolios over a period of time with an alternative investment such as a market index, a portfolio of another investor, or even a single stock if they don't want any diversification, etc. In general, a market index is a good benchmark because it re

Demo stock portfolio tracker with Google Sheets

As explained in the post Create personal stock portfolio tracker with Google Sheets and Google Data Studio , a personal stock portfolio tracker consists of 2 main elements: a spreadsheet in Google Sheets and an interactive dashboard in Google Data Studio. You can take a look at the sample spreadsheet below to have an idea of how the data is organized and related. It is possible to make a copy of the spreadsheet to study it thoroughly. Make a copy Note Disclaimer Make a copy Click here to make a copy Note To better understand the overall concept, please check out this post Create personal stock portfolio tracker with Google Sheets and Google Data Studio . Disclaimer The post is only for informational purposes and not for trading purposes or financial advice.