Skip to main content

Monitor stock investment portfolio with Pivot table and GOOGLEFINANCE function in Google Sheets

Labels:

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.

How to manage stock investment positions in Google Sheets

Table of Contents

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 in Google Sheets to manage stock investment portfolio

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.
How to use pivot table in Google Sheets to count the number of shares for each stock in the investment portfolio

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.

How to use pivot table in Google Sheets to count the number of shares for each stock in the investment 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.
How to use conditional format in Google Sheets to effectively manage a stock investment portfolio
  • 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.
How to use color scale conditional format in Google Sheets to effectively manage a stock investment portfolio

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.

Feedback

If you have any feedback, question, or request please:

Support this blog

If you value my work, please support me with as little as a cup of coffee! I appreciate it. Thank you!

Support AllStacksDeveloper blog a coffee if you find the contents useful!

Share with your friends

If you read it this far, I hope you have enjoyed the content of this post. If you like it, share it with your friends!

Comments

  1. Mohd AnishDecember 27, 2020 at 7:39 AM

    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
    Replies
    1. All Stacks DeveloperJanuary 16, 2022 at 6:31 PM

      I use a small Google Apps Script program to do some complex computation, such as generating automatically the stock portfolio's daily change and comparing it with alternative investments.

      For your information, I have explained in details how to create a personal stock portfolio with Google Sheets and Google Data Studio in this post. https://www.allstacksdeveloper.com/p/lion-stock-portfolio-tracker.html

      I hope it can help you.

      Delete

Post a Comment

Popular posts

Compute cost basis of stocks with FIFO method in Google Sheets

Compute cost basis of stocks with FIFO method in Google Sheets

After selling a portion of my holdings in a stock, the cost basis for the remain shares of that stock in my portfolio is not simply the sum of all transactions. When selling, I need to decide which shares I want to sell. One of the most common accounting methods is FIFO (first in, first out), meaning that the shares I bought earliest will be the shares I sell first. As you might already know, I use Google Sheets extensively to manage my stock portfolio investment, but, at the moment of writing this post, I find that Google Sheets does not provide a built-in formula for FIFO. Luckily, with lots of effort, I succeeded in building my own FIFO solution in Google Sheets, and I want to share it on this blog. In this post, I explain how to implement FIFO method in Google Sheets to compute cost basis in stocks investing.
Use SPARKLINE to create 52-week range price indicator chart for stocks in Google Sheets

Use SPARKLINE to create 52-week range price indicator chart for stocks in Google Sheets

The 52-week range price indicator chart shows the relative position of the current price compared to the 52-week low and the 52-week high price. It visualizes whether the current price is closer to the 52-week low or the 52-week high price. In this post, I explain how to create a 52-week range price indicator chart for stocks by using the SPARKLINE function and the GOOGLEFINANCE function in Google Sheets.
How to convert column index into letters with Google Apps Script

How to convert column index into letters with Google Apps Script

Although Google Sheets does not provide a ready-to-use function that takes a column index as an input and returns corresponding letters as output, we can still do the task by leveraging other built-in functions ADDRESS , REGEXEXTRACT , INDEX , SPLIT as shown in the post . However, in form of a formula, that solution is not applicable for scripting with Google Apps Script. In this post, we look at how to write a utility function with Google Apps Script that converts column index into corresponding letters.
Create personal stock portfolio tracker with Google Sheets and Google Data Studio

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
Slice array in Google Sheets

Slice array in Google Sheets

Many functions in Google Sheets return an array as the result. However, I find that there is a lack of built-in support functions in Google Sheets when working with an array. For example, the GOOGLEFINANCE function can return the historical prices of a stock as a table of two columns and the first-row being headers Date and Close. How can I ignore the headers or remove the headers from the results?
GOOGLEFINANCE Best Practices

GOOGLEFINANCE Best Practices

Anyone using Google Sheets to manage stock portfolio investment must know how to use the GOOGLEFINANCE function to fetch historical prices of stocks. As I have used it extensively to manage my stock portfolio investment in Google Sheets , I have learned several best practices for using the GOOGLEFINANCE function that I would like to share in this post.
Stock Correlation Analysis With Google Sheets

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, I explain in details how to perform correlation analysis among stocks in Google Sheets.
Manage Stock Transactions With Google Sheets

Manage Stock Transactions With Google Sheets

The first task of building a stock portfolio tracker is to design a solution to register transactions. A transaction is an event when change happens to a stock portfolio, for instance, selling shares of a company, depositing money, or receiving dividends. Transactions are essential inputs to a stock portfolio tracker and it is important to keep track of transactions to make good decisions in investment. In this post, I will explain step by step how to keep track of stock transactions with Google Sheets.
How to copy data in Google Sheets as HTML table

How to copy data in Google Sheets as HTML table

I often need to extract some sample data in Google Sheets and present it in my blog as an HTML table. However, when copying a selected range in Google Sheets and paste it outside the Google Sheets, I only get plain text. In this post, I explain how to copy data in Google Sheets as an HTML table by writing a small Apps Script program.