To track investments using google sheets, you’ll need to have a Gmail account. After that, Google Sheets is a free service for you and all of your spreadsheets will be stored in your personal Google Drive.
After creating a new spreadsheet, all you will need to do to populate your personal watchlist is enter a ticker and exchange. We can leave the rest of the magic to Google.
Setup a Ticker to populate Data
The ticker will be the centerpiece of your watchlist, but it is important to note that ticker symbols are not unique. Different companies may have the same ticker symbol but are listed on different exchanges.
For example, the ticker symbol T refers to different companies depending on which exchange you are browsing: it could be AT&T or Telus. To choose the right stock for your watchlist, you’ll need to concatenate the ticker with the right stock exchange in the following notation:
Using the example above, we would use NYSE:T for AT&T or TSE:T for Telus depending on which stock we want to track. A full list of covered exchanges within GoogleFinance can be found here.
Adding the Company Name and Current Price
To populate company names and prices we will introduce the GoogleFinance API as part of our spreadsheets. As with all formulas, they’re accessed by a leading “=” followed by the formula itself.
To add the company name as well as the price in the currency it’s traded in we will insert the following formulas into two different cells:
=GoogleFinance([cell of the ticker],"name")
=GoogleFinance(([cell of the ticker],"price")
Using our example above we can now see the following information in our watchlist.
Adding a Chart
A graph in a single spreadsheet cell? Yes this is possible thanks to a formula called sparkline. This tool gives us the opportunity to add a chart into a cell by defining a date range you want to display.
The formula to draw graphs is constructed like this:
=SPARKLINE(GoogleFinance([cell of the ticker], "price", Start, End))
Thankfully Google Sheets allows us to insert formulas to calculate the date instead of adding it manually every time. This means we won’t be putting something like 01/01/19,12/31/19, but rather we can specify the number of days we want the chart to go back in time from today. We can use the two formulas below to add columns displaying the 90-day and 1-year trend for the equity:
=SPARKLINE(GoogleFinance(B2, "price", TODAY()-90, TODAY()))
=SPARKLINE(GoogleFinance(B2, "price", TODAY()-365, TODAY()))
The final result will look like this:
Adding more information
Now that we know how to use the sparkline and Google Finance API, we can add a bit more information to our watchlist. The most useful, in my opinion, are 52-week high and low as well as the PE-Ratio.
Those values will allow us to calculate the current discount from the 52-week high as well as the premium from the 52-week low. The PE-ratio allows us to use conditional formatting and filters to find potential values in our watchlist. Using our example we would add the information as follows:
Thanks for reading through this introduction to using Google Sheets and Google Finance to track stock information for your watchlist! I would love to hear if this article was helpful for you. I use the mentioned formulas and data to generate and maintain the raw data for my watchlist and portfolio pages. In the upcoming second part, I will cover using Google Sheets to track my portfolio using conditional formatting (highlighting values like low PE-ratios) as well as data validation, which allows us to add a traded currency for each symbol.
The final goal is to automatically populate the position size relative to a single currency making it easy to compare position sizes and allocations over multiple equities and sectors.
This article expresses personal opinions and observations of someone who is not licensed to provide financial advice. I am not receiving compensation for writing this analysis and have no business relationship with any company whose stock is mentioned in this article other than the long positions I own.