Build a Google Sheets Stock Market Template in 5 Easy Steps

Step 1: Understanding Google Finance

In order to build a stock market portfolio in Google Sheets it makes sense to first make yourself familiar with the Google Finance functions. There are a wide range of functions that you can call directly from your Google Spreadsheet such as the current price, the daily percentage change or the volume.

The most basic function in Google Finance is to display the price of a stock. Let’s assume you want to display the price of the Twitter stock today. The syntax for your query is calling the Google Finance function, specifying the stock ticker you want to display and stating price as the attribute to display:

=GOOGLEFINANCE("stock_ticker","attribute")

Since both the stock ticker and the attribute are strings you need to enclose the parameters in parentheses. Hence for the Twitter stock your query would look like this:

=GOOGLEFINANCE("TWTR","price")

Note that the stock ticker for Twitter is TWTR. You can link the stocks to their stock ticker through any financial service such as Yahoo Finance. Also the stock ticker will typically be displayed through your trading app.

This is just the most basic query you can perform with Google Finance. It is certainly worth familiarizing yourself further with the Google Finance functions before building your template.

Step 2: Monitor Market Prices

A good starting point for your stock market template might be a market tracking section. You might not be invested in ETFs tracking the performance of popular market indices but it might still be useful for you to see their daily performance in order to understand the direction your portfolio is moving.

For this purpose you might want to track the daily performance of popular indices such as the S&P 500 and the Nasdaq. In addition you might be interested in the performance of popular funds such as the ARKK innovation fund and the most important commodities or precious metals.

Note that in order to query an index you can’t simply state the index symbol as stock ticker as it is the case for stocks. Instead you need to query the ticker provided by Google Finance. For the most common indices that is:

Nasdaq: “.ixic”

S&P 500: “.inx”

Dow Jones: “.dji”

Russell 2000: “rut”

Google Finance does not support precious metal prices. Therefore you need to parse the prices of gold and silver through an IMPORTHTML function via a third party website that provides the prices.

Note that Google Finance does support currency exchange rates but requires a different syntax for currencies. In order to display the exchange rate between the US Dollar and the Euro use the following syntax:

=GOOGLEFINANCE("CURRENCY:EURUSD")

Once you have tracked all the important market data that matters in your Google Spreadsheet it should look similar to this:

You can then start building your portfolio and market tracker dashboard in Chartmat so that you can access it from any web browser. In order to display current market data simply create a table in Chartmat and connect it to the data source in your Google Spreadsheet. The result will look similar to this:

Note that the items listed in the market overview can be fully customized according to your needs and the assets you track. In case you are more interested in the cryptocurrency market you might as well create your individual market overview for digital assets based on importing data from a third party website with the IMPORTHTML function:

Step 3: Record your Stock Market Transactions in the Google Spreadsheet

The next step for building your stock market template would be for you to record your transactions in a Google Spreadsheet. It makes sense to create a ledger in Google Sheets for recording buy and sell transactions. At Chartmat we have prepared a template for this purpose which you can copy and looks like this:

The template comes with a number of formulas that populate fields such as current price, value and performance. However, the initial purchase and sell information needs to be recorded by the investor.

The transaction details can be recorded through a browser form in Chartmat or directly in the Google Spreadsheet. The data that gets entered into the transaction view is the basis for a number of charts and dashboards that can be derived from this data.

Step 4: Visualize your Portfolio Information and Performance

Once you have prepared your transaction details in the Google Spreadsheet you can start visualizing your stock market portfolio and display it in a browser dashboard as we have done for our template.

As you record stocks in our template you categorize them based on the following categories:

Growth

Value

Emerging Markets

Based on your investment strategy and type you may add different or more categories to the template. You can then in a separate Sheet inside your Spreadsheet summarize the percentage allocation and performance for each category:

Once you have done that you can create a corresponding pie chart in your Chartmat Board visualizing the results from the Google Spreadsheet:

Additionally to the composition you might want to visualize the total performance of your portfolio by category:

For your portfolio it is crucial to visualize the performance per asset. With Chartmat you can easily visualize the daily and the total performance in one single bar chart:

Step 5: Track the Evolution of your Portfolio over Time

Eventually it is important for you to track the evolution of your portfolio over time. For that purpose you can simply write a Macro in your Google Spreadsheet that copies the daily value of your portfolio every day at a specific time to a sheet that you can name “Evolution”. The macro could look something like this:

You can then simply set a timer in Google App Script that performs this macro every day:

With this you can easily track the performance of your portfolio over time. Note that Google Finance can also retrieve historical values of assets but the formula is not always working well. From experience copy pasting daily values with a macro delivers better results than trying to retrieve historical values from Google Finance. Once you have a substantial amount of data points collected you can display the evolution of your portfolio through a line chart in your Chartmat Dashboard.

To Summarize

In this guide we have illustrated how to build a market tracker and a stock market template in Google Sheets. You can copy our Google Sheets template and the corresponding Chartmat Dashboard if you want to start building your own dashboard.