Using Google Spreadsheet As Your Watch List

Includes: ABT, JNJ, PG
by: Big Babu

I love using Google Docs. I can take them with me anywhere, and work on them from anywhere. In fact, I use it so much that I'm basing most of my trades off what my Google Spreadsheets tells me.

The easiest thing you can do with Google Spreadsheet is to keep track of stocks you want to buy, but only if certain criteria have been met. This can either be the stock price hitting a certain value, the dividend yield exceeding some value, or the P/E ratio being "just right". Google Spreadsheet allows you to query data from Google Finance, and pull into your spreadsheet the latest values.

The following are values supported by the "Google Finance" function in Google Spreadsheet:

  • 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 Google Finance function.
  • volumeavg: the average volume for this stock.
  • P/E: 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.

One key thing that is missing from the Google Finance function is dividend. This means that you cannot obtain the dividend information directly from Google Finance and pull it into your spreadsheet. I don't know if this is intentional or not, but it is definitely a desired feature. This is where I went off and wrote my own function to do this:

GoogleFinanceAddOn (symbol, "dividend")

This will automatically pull in the latest dividend per share information so I don't have to key it in manually. This allows me to determine the current dividend yield by dividing the dividend per share by the current stock price.

My "Stock Watch List" spreadsheet looks like this:

Stock Watchlist using Google Spreadsheet

In my example, I'm tracking just 3 stocks:

  • Abbott Laboratories (NYSE:ABT) - Global, diversified pharmaceutical and health care products company.
  • Johnson and Johnson (NYSE:JNJ) - Multinational, medical devices, pharmaceutical, and consumer packaged goods company.
  • Procter & Gamble (NYSE:PG) - Multinational company that manufactures verious personal goods and consumer items.

I made use of price, change, changepct, volume, high52, low52, P/E from Google Finance function, and dividend from my Google Finance Add On function. I added the "% from High" and "% from Low" column, where I express the current stock price compared to the 52 week high and low. The "% from High" is the % gain the stock will have to achieve today to reach the 52 week high. Similarly, the "% from low" is the % decline the stock will have to suffer to reach the 52 week low. This gives you a relative feel of where the stock currently stands in terms of their valuation

I've also added a "Desired Yield" and "Desired Price" column. The "Desired Price" column is simply the price the stock would have to be to generate the yield I want. Lastly, the "Status" column tells me if I should buy or wait. The formula I have in the "Status" column is simply:

=IF(N2>B2, "Buy", "Wait")

The way I would use this spreadsheet is to add high quality dividend stocks that I want to monitor. The more stocks from different sectors you add to it, the better. I would see the current yield of each stock, and increase it by 0.25 or 0.50 percentage and make it my "Desired Yield". Some income investors would not consider any yield under 4%, so that could be another requirement. For example, ABT is currently yielding only 3.27% so you could set the "Desired Yield" for ABT to be 4%.

Next, I would then check to see if the "Desired Price" that the stock has to be for my desired yield is reasonable, i.e. it's above the 52-week low. I want to have a realistic sense that the stock can indeed reach a sufficiently low value to warrant the dividend yield I desire. If it tuns out that my dividend yield requirement is too unrealistic (i.e. expecting 6% return from PG), then I will need to re-examine my holdings.

Finally, you sit and you wait for your desired price to be hit. Or, while you wait, you can sell some cash-secured puts near your desired price and collect some premium while you wait. After you buy, regardless of where the stock price goes afterward, just be glad that at least you stuck to and committed to your desired yield on cost.

Disclosure: I have no positions in any stocks mentioned, and no plans to initiate any positions within the next 72 hours.