just-dividends banner
post-thumbnail

Creating a watchlist in Google Sheets

General Setup

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:

Exchange:Ticker

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.

Ticker_Name_Price
 

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:

Ticker_Name_Price_Graph
 

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:

=GoogleFinance(B2,"high52")

=GoogleFinance(B2,"low52")

=GoogleFinance(B2,"pe")

Ticker_Name_Price_Graph_PE
 

Closing Words

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.

Disclaimer

Images shown in this post may include stocks from my personal watchlist or portfolio.

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.

Additional Resources

GoogleFinance Reference

Sparkline Reference

Post Comments(2)

user-avatar
LizJune 21, 2020, 9:33 a.m.

Super valuable for people who are keeping tabs on and trading individual stocks! Really clear and helpful walk-through on how to set this up. Thanks for sharing!

user-avatar
Just-DividendsJune 22, 2020, 7:45 a.m.

Thank you Liz! I'm looking to add a follow up with some data validation and currency conversion and samples to round up everything you need to track your whole portfolio.

Leave a reply