just-dividends banner

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:


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:





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.


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(8)

Liz June 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!

Just-Dividends June 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.

Duncan July 9, 2020, 4:42 p.m.

Great post, I’m gonna have to try this!

Imran Feb. 15, 2021, 9:57 a.m.

Good Post. More help on the following page. https://support.google.com/docs/answer/3093281?hl=en

Just-Dividends Feb. 15, 2021, 9:39 p.m.

Thank you Imran. The documentation from Google itself is really good when it comes to the different formula for Sheets

tecteem June 2, 2021, 9:52 a.m.

This is amazing, Google you see has lots of alternatives and works that it does. Talking about Google Finance, I guess their service is nice and wonderful. With the presentation of your article, I get to know more about what Google Finance is, thanks to you.

Just-Dividends June 2, 2021, 6:53 p.m.

Tecteem, thank you for your kind words. Unfortunately HTML tags are not allowed in the comment section. I will remove them to make your comment more readable.

Techlittleblue Feb. 21, 2022, 5:57 p.m.

This is really nice content.

Leave a reply