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

Comments

People also enjoyed…

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

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. Concept Demo Conclusion Concept With the GOOGLEFINANCE function, it's possible to retrieve the current price, the 52-week low price, and the 52-week high price of a stock by using the below formulas: =GOOGLEFINANCE("AAPL") returns the latest price of APPLE stock =GOOGLEFINANCE("AAPL","low52") returns the 52-week low price of APPLE stock =GOOGLEFINANCE("AAPL","high52") returns the 52-week high price of APPLE stock To measure the relative position of the current price compared to the 52-week low price and 52-wee

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. With the solution in the form of a formula , we don't even need to understand how column index and letters map each other. With apps script, we need to understand the mapping to come up with an algorithm. In a spreadsheet, columns are indexed alphabetically, starting from A. Obviously, the first 26 columns correspond to 26 alphabet characters, A to Z. The next 676 columns ( 26*26 ), from 27th to 702nd, are indexed with 2 letters. [AA, AB, ... AY, AZ], [BA, BB, ... BY, BZ],

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. Concept Implementation Source Code Demo HTML table code HTML table visualization Getting Started Conclusion Concept On a spreadsheet, users select a range that they want to copy as HTML table. With the selected range, users trigger a command Copy AS HTML table . The command can be added to the toolbar, or to the contextual menu, or accessed via a keyboard shortcut. The command is executed to transform the selected range into HTML code for table. The HTML code can be added to the clipboard or can be displayed somewhere so users can copy it manually. The HTML table must consist of all displayed cells of the selected range and the widths

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 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 coefficient

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 References Manage stock transactions with Google Sheets I use a spreadsheet on Goo

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

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 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 Data and then

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. Define the structure of transactions Use Google Sheets to register transactions Demo Note References Define the structure of transactions In the example, I assume that a transaction generally has 5 main attributes: Date : It is the moment when a transaction happened. Type : It can be one of the following values: DEPOSIT : When money is added to the portfolio BUY : When money in the portfolio is used to buy shares of a company SELL : When money is added into

Demo stock portfolio tracker with Google Sheets

As explained in the post Create personal stock portfolio tracker with Google Sheets and Google Data Studio , a personal stock portfolio tracker consists of 2 main elements: a spreadsheet in Google Sheets and an interactive dashboard in Google Data Studio. You can take a look at the sample spreadsheet below 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. NOTE: An enhanced version was published at Create personal stock portfolio tracker with Google Sheets and Google Data Studio . Make a copy Note Disclaimer Make a copy Click here to make a copy Note To better understand the overall concept, please check out this post Create personal stock portfolio tracker with Google Sheets and Google Data Studio . Disclaimer The post is only for informational purposes and not for trading purposes or financial advice.