How to Create a Stock Watchlist with Google Spreadsheets
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?
21 Comments, Comment or Ping
1. SavingMentor
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.
Aug 15th, 2011 @ 9:11 am
2. The Investment Blogger
Nice tip! Thanks for sharing! I usually just use Google Finance for watching, and a custom spreadsheet for tracking.
Aug 15th, 2011 @ 10:04 am
3. Steve
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!
Aug 15th, 2011 @ 10:53 am
4. FrugalTrader
@Steve, Excel also has a “stock addin” that will enable you to do the same thing.
Aug 15th, 2011 @ 10:58 am
5. Sustainable PF
@FT – is the Excel addin quicker to update than using Google?
Aug 15th, 2011 @ 11:34 am
6. NYCer
I use G&M Watchlist
Aug 15th, 2011 @ 11:39 am
7. Value Indexer
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?
Aug 15th, 2011 @ 11:49 am
8. Gord
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!
Aug 15th, 2011 @ 12:31 pm
9. Jordan
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
Aug 15th, 2011 @ 12:40 pm
10. FrugalTrader
@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.
Aug 15th, 2011 @ 3:58 pm
11. SavingMentor
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.
Aug 15th, 2011 @ 9:11 pm
12. Slacker
The google data (both spreadsheet and google finance) are sometimes defective. Specifically regarding dividend and yield data. They are sometimes missing or worse, incorrect.
Aug 15th, 2011 @ 9:41 pm
13. FrugalTrader
@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.
Aug 15th, 2011 @ 10:19 pm
14. RG
Is there a function that will allow you to display the historic price on a particular date in the past?
Aug 16th, 2011 @ 2:27 am
15. The Passive Income Earner
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.
Aug 16th, 2011 @ 1:09 pm
16. Millionaire
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!!!
Aug 16th, 2011 @ 6:12 pm
17. Millionaire
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
Aug 17th, 2011 @ 1:11 pm
18. Amiel B
Awesome. Thanks. I’ve been meaning on getting a watchlist rolling. You’ve inspired me.
Aug 17th, 2011 @ 10:24 pm
19. Balk
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
Aug 18th, 2011 @ 1:44 pm
20. RG
Can this be used to track mutual funds, or is it stocks only?
Aug 18th, 2011 @ 2:00 pm
21. Chris
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.
Oct 17th, 2011 @ 3:49 am
Trackbacks
Reply to “How to Create a Stock Watchlist with Google Spreadsheets”