Skip to main content

Compare stock investment portfolio to market indexes

Labels:

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.

Compare the stock portfolio's performance vs a market indexes

Table of Contents

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 represents the average of the market. Many investors compare their portfolios against the index S&P500 that measures the stock performance of 500 large companies listed on stock exchanges in the United States. However, it depends on how a portfolio is constituted. For instance, if a stock portfolio consists of only stocks in the French market, the S&P500 index isn't an appropriate benchmark. Instead, that portfolio should be compared to the French market's index which is CAC40 that tracks the 40 largest French stocks based on the Euronext Paris market capitalization.

There are lots of indexes to choose from. It depends on markets where your stocks are traded or even your personal preference.

Method to compare

As we want to evaluate the stock portfolio's performance over a period of time, the first thing we need to do is to compute its evolution during that period. In the post, I have explained how to use Apps Script to automate daily that computation. As a result, we have the sheet Evolutions that contains the data for Invested Money, Available Cash, Market Value, Portfolio Value, and Gain for each day since the first transaction date.

By using the GOOGLEFINANCE function in a Google spreadsheet, we can easily fetch historical prices of a market index over a period of time. You can find the symbol corresponding to a market index at the website https://www.google.com/finance.

At this point, we can plot on the same time series chart both the Portfolio's values and the market index's prices over the same period of time. What can we conclude about the comparison below? We see clearly that the portfolio's value has increased linearly whereas the CAC40 has fluctuated over the same period. Does it mean the stock portfolio has outperformed largely the CAC40? No, the reason for the linear growth of the portfolio's value is because money has been regularly deposited in the account. You can see those transactions in the sheet Transactions of the Sample Portfolio spreadsheet in this post.

Compare the stock portfolio's value vs the market index CAC40 over the same period

Because of the cash-flow, money deposited into or withdrawn from the investment account, we should not benchmark the stock's portfolio value against a market index. Instead, we can use the stock's portfolio gain percentage. Another problem is that the gain percentage and the market index don't use the same unit. We can plot two series on a dual-axis chart as shown in the below picture. However, it is not a reliable chart to extract insights from. If we change the scale of the left y-axis or the right y-axis, the presentation will be changed significantly. On the left chart, we might have thought that the portfolio's evolution and the market index's evolution have a similar trend. On the right chart, with the same data, the two evolutions differ a lot because the scale of the right y-axis has changed from (3k, 7k) to (0, 10k).

Compare the stock portfolio's performance vs a market index on a dual-axis time series chart

To resolve that problem, we should plot the two evolutions on the same y-axis with the same unit which is percent. To do so, we need to select a reference price for the chosen market index and we compute how many percent has changed between the market index's price on a given date compared to the reference price. For instance, the reference price can be the price of the market index on the first transaction's date of the portfolio. By so, the two evolutions will both start with 0% on the first transaction's date of the portfolio.

Compare the stock portfolio's gain percentage vs the change of the market index CAC40 since the first transaction date of the portfolio

Guides

Prepare data with Google Sheets and Apps Script

In this post, we have used Apps Script to generate daily evolutions of the portfolio into the sheet Evolutions. The data we still miss are Gain Percentage, and change by percent of market indexes compared to their prices on the first transaction date of the portfolio.

  • Define in the sheet Indexes all market indexes that we want to compare our stock portfolio to. You can find the symbol corresponding to a market index at the website https://www.google.com/finance.
  • In the Apps Script editor, add the new function extractIndexes to extract market indexes defined in the sheet Indexes
  • In the Apps Script editor, update the function generateHistoricalPricesSheets to generate also historical prices sheets for all indexes in the sheet Indexes
  • In the Apps Script editor, add the new function getFirstPriceBySymbol to extract the first price in a historical prices sheet
  • In the Apps Script editor, update the function generateDailyEvolution to compute the Gain Percentage of the portfolio as well as change by percent of market indexes defined in the sheet Indexes compared to their prices on the first transaction date of the portfolio.

The latest script can be found in this github repository.

As we have already defined two time-based triggers for the two functions generateHistoricalPricesSheets and generateDailyEvolution, we will have the sheet Evolutions automatically updated every day before 8 a.m.

You can find the sheet Evolutions and Indexes in a sample spreadsheet presented in this post.

Visualize benchmark in Google Data Studio

In this post, we have already added the sheet Evolutions as a data source to the report in Google Data Studio. Since new columns have been added to the sheet, we need to update the data source to make sure those columns are correctly connected to the report.

  • Select Resource on the menu, then Manage added data sources menu item
  • Click EDIT on the data source corresponding to the sheet Evolutions
  • Click EDIT CONNECTION on the top left of the menu
  • Click RECONNECT button on the top right of the menu

A dialogue appears to ask for confirmation of applying changes. The changes consist of new fields: Gain Percentage, CAC40, and S&P 500 because they are two market indexes chosen in the sample spreadsheet.

Edit the data source Evolutions to add new fields

With the data ready, we can visualize the comparison between the evolution of portfolio and market indexes on a time series chart. We can modify the Main Dashboard to make room for a new chart or we can create a new page Benchmarks specifically for it.

  • Find the Add a new page button on the top left of the toolbar to add a new page and name it Benchmarks
How to add new page to a Google Data Studio report
  • Insert a Time series chart into the page
  • On the DATA tab
    • Select the Sample Evolutions as the data source
    • Select Date as Date Range Dimension
    • Select Gain Percentage as the first metric
    • Select CAC 40 as the second metric
    • Select S&P 500 as an optional metric
  • On the STYLE tab, select the option Linear Interpolation for the Missing Data
  • Insert a Date range control into the page to filter chart by date range
How to configure a time series chart on Google Data Studio report

As a result, the evolution of portfolio and market indexes are visualized beautifully and interactively as below. We can select a specific date range to see more closely the difference between series. From the menu of the optional metrics, we can show or hide a series. All series start from 0% on the first transaction date of the portfolio. It is clear that my sample portfolio doesn't have a chance to compare with the index S&P 500. As the portfolio consists of only stocks in the French market, its evolution is closer to the index CAC 40. Before 2020, the portfolio witnessed a very poor performance compared to the index CAC 40, but since then, it has caught up the index thanks to some good decisions in the investment strategy.

Compare the stock investment portfolio's performance vs a market indexes in Google Sheets

Demo

  • You can find the sheets Evolutions and Indexes in the sample spreadsheet presented in this post.
  • The Benchmarks dashboard can be found here.

Conclusion

By using Google Sheets, Apps Script, and Google Data Studio, we can evaluate the stock portfolio's performance vs a market index. It is important to recognize the impact of the cash-flow (when money is deposited into or withdrawn from the investment account) in evaluating the stock portfolio's performance. On a time series chart, we can identify when the portfolio loses track of the market index and hence make adjustments in investment strategy to improve the performance.

If you find this post interesting, please do share it! Thank you!

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

Post a Comment

Popular posts

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.
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.
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.
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?
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
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.
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.
How to use WEEKDAY function to get last Friday in Google Sheets

How to use WEEKDAY function to get last Friday in Google Sheets

As I manage my stock investment portfolio in Google Sheets, I need to see its evolution over time, for example, in the last year. However, the computation for daily evolution is resource-consuming and might cause performance issues for the spreadsheet. As an alternative, I compute only the weekly evolution of the investment portfolio for the last year. For each week, I compute only the portfolio's value at the end of the Friday. For that, I need a Google Sheets formula to return the last Friday for a given date. This post explains how I do that with the WEEKDAY formula in Google Sheets.