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.

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?