Skip to main content

Compute daily evolution of a stock investment portfolio by using only built-in functions of Google Sheets

Labels:

To effectively track a stock investment portfolio, it is necessary to know its evolution in the past. As I use Google Sheets to track my stock investment portfolio, I have researched and successfully implemented several solutions. In this post, I am happy to share in detail how to compute the daily evolution of a stock investment portfolio by simply using only the available built-in functions in Google Sheets.

The evolution chart of a stock investment portfolio in Google Sheets

Table of Contents

Concept

A stock investment portfolio consists of several different stocks. The composition of a portfolio changes over time depending on the frequency of transactions. At any time, it is necessary to answer the three questions below to determine the market value of an equity investment portfolio:

  • What stocks were present in the investment portfolio?
  • How many shares did each stock present in the investment portfolio?
  • What was the price of each stock?

The three questions can be answered with Google Sheets because:

It means that computing the historical evolution of a stock investment portfolio in Google Sheets is not an impossible mission. However, there are still several technical challenges concerning its implementation:

  • The repetitive use of the GOOGLEFINANCE function to fetch prices of stocks might decrease the performance of a spreadsheet.
  • What is the most suitable design of the sheet for storing the daily evolution of a stock investment portfolio?
  • What are the available built-in functions that can be helpful?
  • How to automatically compute the market value of a new day?
  • etc.

In the following sections, I explain in detail the solution for computing the daily evolution of a stock investment portfolio by simply using the available built-in function in Google Sheets.

Implementation

Register transactions of a stock investment portfolio

To compute the historical evolution of a stock investment portfolio, its transactions must be carefully registered from the beginning. I create the Transactions sheet to keep track of my investment portfolio's transactions. In that sheet, a row represents essential information about a transaction: the date, the type (BUY, SELL, DEPOSIT, WITHDRAW, and DIVIDEND), the symbol of the involved stock, the amount of money, and the number of shares.

I explained the further details in the post how to manage stock transactions with Google Sheets

Manage transactions of a stock investment portfolio with Google Sheets

Optimally use the GOOGLEFINANCE function to fetch historical prices of stocks

To compute the historical evolution of a stock investment portfolio, I need to use the GOOGLEFINANCE function to fetch the historical prices of my stocks in Google Sheets. However, the repetitive use of the GOOGLEFINANCE function can cause performance issues for the spreadsheet, especially when it comes to many stocks and many dates. To avoid that, I create a dedicated sheet for each stock, then call the GOOGLFINANCE function only once to get its historical price and store the results in that sheet. After that, I will query that sheet to search for the price on the date that I need.

For example:

  • I started my stock investment portfolio on 27/12/2017.
  • I own the EPA:TTE stock and I create a sheet named EPA:TTE.
  • On the cell A1 of that sheet, I put the formula =GOOGLEFINANCE("EPA:TTE", "price", DATE(2017,12,27), TODAY(), "DAILY") to get the daily historical prices of the EPA:TTE stock since 27/12/2017 to today.
  • When I need the price of the EPA:TTE stock on any given date, I will use built-in functions of Google Sheets to lookup the corresponding price in the sheet EPA:TTE instead of using the GOOGLEFINANCE function again. By doing so, I optimize the performance of my spreadsheet.

For many other useful tips for working with the GOOGLEFINANCE function in Google Sheets, check out my post GOOGLEFINANCE Best Practices

Formula to determine the (close) price of a stock on a given day

Continuing with the example for the EPA:TTE stock, concretely, to know its close price on 01/11/2022, I will use the QUERY function as follow:

=QUERY('EPA:TTE'!A:B,"select B where datediff(A, date '2022-11-01')=0 limit 1",0)

There are some reasons why I need to use the QUERY function here instead of the VLOOKUP function. I explained those reasons at the section Use QUERY function instead of VLOOKUP function for looking up by date of the post GOOGLEFINANCE Best Practices.

Formula to determine the number of shares that a stock presents in the portfolio on a given day

To know the number of shares of a stock that I own on a given day, I need to do two steps:

  • Firstly, I need to identify all the BUY and SELL transactions of that stock before or on that day. For that, I will use once again the powerful QUERY function.
  • Secondly, I need to sum up all the shares of those identified BUY or SELL transactions. For that, I will the classic SUM function.

Continuing with the example for the EPA:TTE stock, concretely, to know how many shares of the the EPA:TTE stock that I own on 01/11/2022, I will use the QUERY function and the SUM function as follow:

=SUM(QUERY(Transactions!$A:$E,"select E where C='EPA:TTE' and B != 'DIVIDEND' and A <= date '2022-11-01'"))

Design the sheet for storing the daily evolution of a stock investment portfolio

So far, I have managed to determine the close price and the number of shares for the EPA:TTE stock on 01/11/2022. Therefore, it is an easy task to know how much value the EPA:TTE stock contributes to my investment portfolio on 01/11/2022. For that, I simply multiply the number of shares by the close price.

To know the total market value of my investment portfolio on 01/11/2022, I need to repeat the process for other stocks that I held on that day.

I then need to repeat again on other days to compute my investment portfolio market value on those days.

How should I design the Evolution sheet to ease the computation?

There are 2 main variables: the stocks and the dates. Therefore, I think of the design where:

  • The header row contains the stocks.
  • The header column contains the dates.
  • Just after the header column, I create a column Portfolio Market Value
how to compute the daily evolution of a stock investment portfolio by simply using the available built-in function in Google Sheets

Getting started

  • Make a copy of the demo spreadsheet below
  • Replace your own transactions in the Transactions sheet as explained in the post how to manage stock transactions with Google Sheets
  • Create a Evolution sheet
  • Use the column A for Date and put the wanted dates to this column. The values must be of type Date
  • Use the column B for Portfolio Market Value
  • On the cell C1, put the formula =TRANSPOSE(UNIQUE(QUERY(Transactions!A:F,"select C where B='BUY' order by A desc",0))), so that all the available stocks in the investment portfolio will be populated on the first row, starting from the column C
  • On the cell C2, put the below formula then apply it by dragging from the cell C2 to other cells starting from the column C and the row 2

=IF(SUM(QUERY(Transactions!$A:$E,"select E where C='"&C$1&"' and B != 'DIVIDEND' and A <= date '"&TEXT($A2,"yyyy-MM-dd")&"'"))>0,SUM(QUERY(Transactions!$A:$E,"select E where C='"&C$1&"' and B != 'DIVIDEND' and A <= date '"&TEXT($A2,"yyyy-MM-dd")&"'"))*QUERY(INDIRECT(C$1&"!A:B"),"select B where datediff(A, date '"&TEXT($A2,"yyyy-MM-dd")&"')=0 limit 1",0),0)

  • On the cell B2, put the formula =SUM(C2:2) and then apply it to the whole column B

Demo

Demo spreadsheet: how to compute the daily evolution of a stock investment portfolio by simply using only the available built-in functions in Google Sheets

Another solution for computing the daily evolution of a stock investment portfolio is to write a scripting program with Google Apps Script. Although that solution requires programming, it can compute the evolution of a stock investment portfolio for a longer period. For more information, I have explained in detail in the post Compute daily evolutions of a stock portfolio with Google Sheets and Apps Script

Compute daily evolutions of a stock portfolio with Google Sheets and Apps Script

Conclusion

In this post, I have explained in detail how to compute the daily evolution of a stock investment portfolio by simply using the available built-in function in Google Sheets. The post covered many topics:

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. HawkWolfDecember 11, 2024 at 2:45 PM

    Thank you for your work. What if you have stocks in USD, GBP and EUR. Would you convert to local currency on the stock daily prices or in the sheet "last 52 weeks" ?

    ReplyDelete

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.
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...
How to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio with Google Sheets

How to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio with Google Sheets

As a long-term investor, I need to know how to evaluate the performance of my stock portfolio. A simple return on investment calculation is not a good indicator for long-term investment because it does not take into account the holding duration, and cash flows involved during that period. A return on investment of 80% after 20 years is not as impressive as it sounds after 1 year. In this post, I explain the idea of using Google Sheets to calculate the internal rate of return (IRR) and the net present value (NPV) of a stock portfolio.
Time value of money, Present Value (PV), Future Value (FV), Net Present Value (NPV), Internal Rate of Return (IRR)

Time value of money, Present Value (PV), Future Value (FV), Net Present Value (NPV), Internal Rate of Return (IRR)

Why do I use my current money to invest in the stock market? Because I expect to have more money in the future. Why do I need more money in the future than now? Because of many reasons, the same amount of money will have less purchasing power than today. Therefore my investment needs to generate more money than today to protect my purchasing power in the future. That is the main concept of the time value of money where one dollar today is worth more than one dollar in the future.
Demo how to use XIRR and XNPV functions of Google Sheets to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio

Demo how to use XIRR and XNPV functions of Google Sheets to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio

I have explained the idea of using Google Sheets functions to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio . The process consists mainly of three steps: Identify cash flows from transactions managed in a Google Sheets spreadsheet Choose a discount rate based on personal preferences Apply XIRR and XNPV functions of Google Sheets In this post, I demonstrate step-by-step how to apply this process to calculate internal rate of return (IRR) and net present value (NPV) for a stock portfolio at 3 levels.
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.
Compute daily evolutions of a stock portfolio with Google Sheets and Apps Script

Compute daily evolutions of a stock portfolio with Google Sheets and Apps Script

When it comes to investment, it is not only important to know the up-to-date state of portfolio but also to track its evolution day by day. We need to know on a specific day, how much money has been invested in the portfolio, the current market value of owned shares, the available cash and the current profit. Visualizing those historical data points on a time-based graph helps us to identify which transactions were good and which were bad. This post shows how to compute automatically those historical data points by using data in Transactions sheet and the built-in GOOGLEFINANCE function of Google Sheets. A sample spreadsheet can be found in this post Demo stock portfolio tracker with Google Sheets . You can take a look at the sample spreadsheet 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.
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?
Stock Portfolio Tracker Dashboard With Google Data Studio

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