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?
I've Completed My Million Dollar Journey. Let Me Guide You Through Yours!
Sign up below to get a copy of our free eBook: Can I Retire Yet?
Thanks for the great tips. I have a question. I am trying to build my own stock price tracker. I have an issue when I try to use “TD.TO” to track the Canadian price of TD. I get and error when I use the “.TO” any ideas why the link doesn’t work and how can I make the link to the Canadian price? any suggestion. 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”)
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,
Thanks for the update ! Yes for tsx stocks you’ll need to add tse: in front of the stock symbol . Ie. Tse:ry
how to determine price of stock is correct or not? In other word is it right time to buy or not?
thanks
Pranav
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.
Here are all the functions available: https://support.google.com/docs/answer/3093281?hl=en. In terms of highs and lows, there is an option there for 52 week, but not over all time.
=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
Does anyone know of APPs that can do the same thing as this goggle soreadsheet?
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!
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.
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.
@Alex, the Google stock quotes are still working for me? When will they stop functioning?