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 existen

Stock Correlation Analysis With Google Sheets

Correlation is a statistical relationship that measures how related the movement of one variable is compared to another variable. For example, stock prices fluctuate over time and are correlated accordingly or inversely to one another. Understanding stock correlation and being able to perform analysis are very helpful in managing a stock portfolio investment. In this post, we will look at how to perform stock correlation analysis with Google Sheets. Understanding correlation and its applications in stock investing Stock correlation analysis with Google Sheets Getting started User guide Conclusion Disclaimer Understanding correlation and its applications in stock investing The most familiar correlation measure is the Pearson product-moment correlation coefficient . The strength of the relationship between two variables is expressed numerically between -1 and 1. For example: Two stocks are positively correlated when their prices always go up or go down together. Their co

Create a dividend income tracker with Google Sheets by simply using pivot tables

As my investment strategy is to buy stocks that pay regular and stable dividends during a long-term period, I need to monitor my dividends income by stocks, by months, and by years, so that I can answer quickly and exactly the following questions: How much dividend did I receive on a given month and a given year? How much dividend did I receive for a given stock in a given year? Have a given stock's annual dividend per share kept increasing gradually over years? Have a given stock's annual dividend yield been stable over years? In this post, I explain how to create a dividend tracker with Google Sheets. Manage stock transactions with Google Sheets Create dividend tracker with Google Sheets Track annual dividend amount of stocks Track dividend amount by month and by year Track annual dividend per share of stocks Track annual dividend yield of stocks Demo Conclusion Buy me a coffee Feedback References Disclaimer Manage stock transactions with Go