I brought this post out of the archives due to a number of recent emails about how to track stock holdings.  I still use Google spreadsheets  today to track my dividend holdings.

With the market volatility, there is a heightened interest in dividend stocks and how to buy stocks in general. Within my article on when to buy dividend stocks, I mentioned that using a spreadsheet to create a watch list is particularly useful.

While not everyone has Excel to use their stock quote addin, most have access to Google’s free spreadsheet app. What’s neat about Google Spreadsheets is that it has a built in stock quote function that can be quite useful. The function can pull important semi real-time metrics like current stock price, price earnings, 52 week high/low, volume, eps, and market cap (complete list below).

How do you use Google Spreadsheets to create a stock watch list?

1. Syntax

Lets start with the code required to get this up and running.  Pick a cell on the spreadsheet, and use the following syntax.

=GoogleFinance(“symbol”; “attribute”);
where “symbol” represents the stock symbol of the company or mutual fund you’re looking for (like GOOG, QQQ, XXXXX, and so on), and “attribute” represents the type of market data that you want (like price, volume, and so on). If the attribute is left blank, price is returned by default.

Here is a complete list of attributes:

  • price: market price of the stock – delayed by up to 20 minutes.
  • priceopen: the opening price of the stock for the current day.
  • high: the highest price the stock traded for the current day.
  • low: the lowest price the stock traded for the current day.
  • volume: number of shares traded of this stock for the current day.
  • marketcap: the market cap of the stock.
  • tradetime: the last time the stock traded.
  • datadelay: the delay in the data presented for this stock using the googleFinance() function.
  • volumeavg: the average volume for this stock.
  • pe: the Price-to-Earnings ratio for this stock.
  • eps: the earnings-per-share for this stock.
  • high52: the 52-week high for this stock.
  • low52: the 52-week low for this stock.
  • change: the change in the price of this stock since yesterday’s market close.
  • beta: the beta value of this stock.
  • changepct: the percentage change in the price of this stock since yesterday’s close.
  • closeyest: yesterday’s closing price of this stock.
  • shares: the number of shares outstanding of this stock.
  • currency: the currency in which this stock is traded.

Here are a few examples:

To insert the current price of Royal Bank (TSX) stock:
=GoogleFinance(“RY.TO”; “price”)

To insert the current voloume of Google stock:
=GoogleFinance(“GOOG”; “volume”)

Alternatively, the stock symbol and/or the attribute values can come from spreadsheet cells. For example, the function can be:
=GoogleFinance(A2; B1)

In this case, the attribute specified as a string in cell B1 would be returned for the stock symbol in cell A2.

2. Sample Stock Watchlist

Here is a sample spreadsheet that is similar to what I use to watch my favorite dividend stocks.  I use the full table below for each position, I used Royal Bank in the example below.

1 Stock RY.TO
2 Price =GoogleFinance(B1; “price”)
3 EPS =GoogleFinance(B1; “eps”)
4 P/E =GoogleFinance(B1; “pe”)
5 Annual Dividend Manual Entry
6 Current Yield =B5/B2
7 Desired Yield Manual Entry
8 Action =if(B6>B7,”BUY”, “WATCH”)

Hopefully the table is relatively intuitive, the confusing parts may be where it says “manual entry”.  Since Google cannot pull data for the annual dividend, you’ll need to obtain that info yourself.  You can generally get this information directly from the company website, or even other stock info websites (stockhouse.com, google finance, msn, dividendinvestors.ca).

The B7 manual entry field is the dividend yield that you would find attractive enough for you to buy.  For example, if you would buy RY.TO when it has a 4% yield, then put 4% in that field.  One tip, make sure that field (B6 and B7) is designated/formatted as a “percentage”.

One final thing, B8 is a cell that has a fancy “IF” statement.  For you non-programmers out there, it basically translates, (if current yield is greater than my desired buy yield, then show “buy”, otherwise, show “watch”).

What do you use for your stock/ETF watch list?


  1. SavingMentor on August 15, 2011 at 9:11 am

    That’s really neat FrugalTrader! I didn’t know it was possible to do all of that with Google Spreadsheets and I use them a fair bit myself.

    I tend to use Google Finance for watching stocks and tracking performance but adding some of this data into a spreadsheet for tracking investments could definitely come in handy as well.

  2. The Investment Blogger on August 15, 2011 at 10:04 am

    Nice tip! Thanks for sharing! I usually just use Google Finance for watching, and a custom spreadsheet for tracking.

  3. Steve on August 15, 2011 at 10:53 am

    I also use Google Finance to keep an eye on stocks and track my own positions manually with an Excel spreadsheet. I had no idea Google Spreadsheet could do that.

    You just convinced me to switch to Google Spreadsheet for that purpose.


  4. FrugalTrader on August 15, 2011 at 10:58 am

    @Steve, Excel also has a “stock addin” that will enable you to do the same thing.

  5. Sustainable PF on August 15, 2011 at 11:34 am

    @FT – is the Excel addin quicker to update than using Google?

  6. NYCer on August 15, 2011 at 11:39 am

    I use G&M Watchlist

  7. Value Indexer on August 15, 2011 at 11:49 am

    Very interesting tricks – I track indexes, and I’ve found recently that it’s hard to get good data especially for the P/E. For example, Google Finance quotes the P/E ratio of SPY as 6.92 for some reason. That sounds like around half the real number. Other sources put it at 17-19, which may just be 6 months out of date. It seems like the only reliable source of that information is the news! Does anyone else have a good way to look it up?

  8. Gord on August 15, 2011 at 12:31 pm

    Great tip! I’ve been using a Google spreadsheet as well for a while (and Google finance to track my positions), however, like NYCer I recently switched to the Globe and Mail’s Watchlist. I find it much easier to add to. It has a “Dividend” view, but even better is the “build your own” view. It has all the metrics you mention plus things like 5yr or 1 yr dividend growth, debt to equity, 1 yr. profit growth, dividend payout ratio, etc.

    One major difference, which I don’t quite understand, is the dividend payout ratio is calculated differently. The G&M says “Calculated by dividing a stock’s dividend per share by the cash flow per share for the latest trailing 12 months.” vs. the conventional dividend/EPS I’m not sure which is a more “accurate” representation of payout ratio. As an example of the differences, TRPs payout ratio is 83% (dividend/EPS) or 33% according to the G&Ms calculation. Big difference!

  9. Jordan on August 15, 2011 at 12:40 pm

    Great post Frugal! Would you consider making your spreadsheet Public?

    This is what I’m using currently, I’ll have to compare it with what you’ve desribed here and look for improvements!


  10. FrugalTrader on August 15, 2011 at 3:58 pm

    @Sustainable_PF, the updates are about the same as the info has to load from the web. I like how the goog spreadsheet can be accessed from any computer, where the excel spreadsheet is local.

    @Gord, it really depends on how they calculate cash flow. The way that I calculate cash flow is: net earnings + amortization/depreciation – capex.

  11. SavingMentor on August 15, 2011 at 9:11 pm

    Isn’t Microsoft planning a massive move to the cloud with their next release of office? Pretty soon we probably won’t be able to say that Google docs has that advantage over office.

  12. Slacker on August 15, 2011 at 9:41 pm

    The google data (both spreadsheet and google finance) are sometimes defective. Specifically regarding dividend and yield data. They are sometimes missing or worse, incorrect.

  13. FrugalTrader on August 15, 2011 at 10:19 pm

    @SavingMentor, although Office may be offered in the cloud in the near future, I doubt that they’ll offer it for free! Office is a huge cash cow for them.

  14. RG on August 16, 2011 at 2:27 am

    Is there a function that will allow you to display the historic price on a particular date in the past?

  15. The Passive Income Earner on August 16, 2011 at 1:09 pm

    I have a Google Spreadsheet for tracking my watch list with some math to create a value ratio. The challenge I have run into is that there is a max of 1000 GoogleFinance functions and I am hitting it :( I may just move to Excel. I really like to have access to my spreadsheet everywhere. I haven’t tried Office online yet …

    Don’t forget to add .TO for Canadian stocks otherwise you may pull in the US price.

  16. Millionaire on August 16, 2011 at 6:12 pm

    I used google spreadsheet but I soon reached the 1000 formulas limit. Plus it’s slow and lacks the dividend yield, payout ratio and much more. It takes forever to update it manually when you follow 500 stocks. There’s also the fact that different websites calculate ratios different ways. I think I tried them all. Eg bmo investorline (same program as the globe investor) calculates the dividend yield by multiplying the last dividend payment by 12 / stock price, while yahoo calculates using the last 12 months dividends. Same with the payout ratios. Data is different everywhere and it drives me crazy. I have no better alternative tho. Please help!!!

  17. Millionaire on August 17, 2011 at 1:11 pm

    The Globe and Mail Watch List = WOW !!!
    It has it all !!
    Thanks NYCer and Gord !!

    Now let’s find out if the data is accurate…

    I love everything that the G&M does. Their portfolio manager is the best as well. It’s not free on the G&M site but it is free on Lesaffaires.com

  18. Amiel B on August 17, 2011 at 10:24 pm

    Awesome. Thanks. I’ve been meaning on getting a watchlist rolling. You’ve inspired me.

  19. Balk on August 18, 2011 at 1:44 pm

    Does anyone know where I can get historical P/E data? I, similarly to FT, use historical yields to signal a buying opportunity but I would like to also use historical PEs as a second measure.

    Any help would be appreciated.



  20. RG on August 18, 2011 at 2:00 pm

    Can this be used to track mutual funds, or is it stocks only?

  21. Chris on October 17, 2011 at 3:49 am

    FYI, if you try and copy and paste your very first example as I did when trying mine out for the first time, it doesn’t work because the first quotation symbol in front of GOOG is backwards for some reason.

    Great tip though and thanks for the info, I use a lot of google products.

  22. Bokam Lay on November 6, 2012 at 4:09 pm

    I can’t seem to make it work with stocks on the CNSX. Any ideas?

  23. mohamed kassim on March 1, 2013 at 4:20 pm

    Please advise me how to create my watchlist geogle spreadsheet.
    Thank you in advance.

  24. Alex on May 21, 2014 at 2:41 pm

    Google API has been discontinued, we can use Yahoo API instead. LibreOffice Calc is a free spreadsheet and there is GETQUOTE add-in that can be used for the stock data.

    • FrugalTrader on May 21, 2014 at 2:59 pm

      @Alex, the Google stock quotes are still working for me? When will they stop functioning?

  25. Alex on May 21, 2014 at 6:03 pm

    This is what they say on Google site:

    They say API is deprecated, but has no scheduled shutdown. So, I guess it may be still working….

  26. Laura on January 16, 2015 at 8:39 pm

    Found a VBA function that puts live stock prices (and other financial data) into Excel. Seems to use the Yahoo Finance API

    • qubikal on July 13, 2016 at 4:41 pm

      can you share the VBA function? or point to how to use this (i’m not a vba expert), but i prefer using Excel vs google web based spreadsheets.

  27. Peter on June 3, 2016 at 10:20 pm

    Does anyone know of APPs that can do the same thing as this goggle soreadsheet?

  28. dimitris on June 10, 2016 at 3:44 pm

    =split(ImportXML(concatenate(“http://finance.google.com/finance?q=”,B41), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)

    this can give you dividend and dividend yield on the ETF on cell B41

  29. rgz on July 17, 2016 at 7:48 pm

    Is there a way to get the all time high and low price of the stock for this? I see 52-week high and low but not all time.

  30. pranav on November 29, 2017 at 9:53 pm

    how to determine price of stock is correct or not? In other word is it right time to buy or not?


  31. TC on May 22, 2019 at 11:32 am

    I found your website by chance while surfing the web. It is a great collection of ideas and very practical. Found it quite useful.
    I was trying to build the dividend stocks spreadsheet with google sheets. It is great tool which I was not aware of. However, I think there is an error in the instructions. For TMX, the stock ticker to be used is – TSE:Stockticker. Looks like Stockticker.TO was old ticker type and no more used. Also, many more stock exchanges can be added this way.
    Hope you will find it useful.

    • FT on May 22, 2019 at 3:18 pm

      Thanks for the update ! Yes for tsx stocks you’ll need to add tse: in front of the stock symbol . Ie. Tse:ry

  32. Massimiliano on October 5, 2019 at 10:19 pm

    WOW… This is neat… Just needed a tweek to get the latest formula formats and a slight change in the symbol..

    if D40 contains TSE:RY
    =GoogleFinance(D40, “price”)
    =GoogleFinance(D40, “eps”)

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.