≡ Menu

How to Create a Stock Watchlist with Google Spreadsheets

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.

A B
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?

If you would like to read more articles like this, you can sign up for my free newsletter service below (we will not spam you).

FrugalTrader About the author: FrugalTrader is the founder and editor of Million Dollar Journey (est. 2006). Through various financial strategies outlined on this site, he grew his net worth from $200,000 in 2006 to $1,000,000 by 2014. You can read more about him here.

{ 33 comments… add one }
  • SavingMentor August 15, 2011, 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.

  • The Investment Blogger August 15, 2011, 10:04 am

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

  • Steve August 15, 2011, 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.

    Thanks!

  • FrugalTrader FrugalTrader August 15, 2011, 10:58 am

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

  • Sustainable PF August 15, 2011, 11:34 am

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

  • NYCer August 15, 2011, 11:39 am

    I use G&M Watchlist

  • Value Indexer August 15, 2011, 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?

  • Gord August 15, 2011, 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!

  • Jordan August 15, 2011, 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!

    https://spreadsheets.google.com/spreadsheet/ccc?key=0AnU4nXDzXQaidFVhV3lUdlBucXBVdENZN3hWYk1JZXc&hl=en_US

  • FrugalTrader FrugalTrader August 15, 2011, 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.

  • SavingMentor August 15, 2011, 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.

  • Slacker August 15, 2011, 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.

  • FrugalTrader FrugalTrader August 15, 2011, 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.

  • RG August 16, 2011, 2:27 am

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

  • The Passive Income Earner August 16, 2011, 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.

  • Millionaire August 16, 2011, 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!!!

  • Millionaire August 17, 2011, 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

  • Amiel B August 17, 2011, 10:24 pm

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

  • Balk August 18, 2011, 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.

    Thanks,

    Balk

  • RG August 18, 2011, 2:00 pm

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

  • Chris October 17, 2011, 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.

  • Bokam Lay November 6, 2012, 4:09 pm

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

  • mohamed kassim March 1, 2013, 4:20 pm

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

  • Alex May 21, 2014, 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 FrugalTrader May 21, 2014, 2:59 pm

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

  • Alex May 21, 2014, 6:03 pm

    This is what they say on Google site:
    https://developers.google.com/finance/

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

  • Laura January 16, 2015, 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 July 13, 2016, 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.
      thanks!

  • Peter June 3, 2016, 10:20 pm

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

  • dimitris June 10, 2016, 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

  • rgz July 17, 2016, 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.

Leave a Comment