Skip to main content

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.

Compare the stock portfolio's performance vs a market indexes

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 portfolio's performance vs a market indexes

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.

Comments

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

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. Use a pivot table to group transactions by symbols Configure the pivot table Positions Use GOOGLEFINANCE function to get real-time information for stocks Conditional formatting columns Demo Note References Disclaimer 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. Select the Transactions sheet. Select the 5 columns A:E . In the menu at the top, click Dat

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