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?

Subscribe
Notify of

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

37 Comments
Oldest
Newest
Inline Feedbacks
View all comments

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.

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

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!

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

I use G&M Watchlist

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?

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!

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

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.

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

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

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.

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

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

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

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

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

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.

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

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

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.

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….

The API isn’t the same thing as the Google Sheets function. They used to have an API so you could easily retrieve data from google finance using your own code (ex: http://www.google.com/finance/info?infotype=infoquoteall&q=aapl). Now you have to scrape the data.

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

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!

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

=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

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.

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

thanks
Pranav

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.
Thanks,

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”)