How I Use The Google Docs Spreadsheet For Investing

I am not exactly sure how Microsoft still profits off the office suite of products considering the awesomeness that is both google docs and openoffice; although I guess they figured out the model might be currently flawed as they have recently moved to an ad based, stripped down version of the product installed on new computers called Office 365.  But I digress, I have recently discovered a way to use the free google spreadsheets to help with my investments.

Using Google Spreadsheet To Share Investment Information

I first started using google docs to share my data with readers of this blog.  Whenever I update my dividend investment portfolio I share the numbers I am using and then provide access to look (technical term: iFrame) directly into the spreadsheet I am working on:

iFrame of Google Spreadsheets

Take note of the scroll bars they allow you to see exactly what I am putting in as criteria to eliminate companies:

Dividend investment portfolio yield data

You can also share documents by using someone’s email account!  So I could let someone edit or just look at each spreadsheet I create.

Using Google Doc’s Spreadsheet to Purchase Investments

It wasn’t until recently that I discovered that I could actually use google docs not only to share how I was narrowing down my choices, but use it to actually pick the stocks I purchase.  Using the same example as above, my Dividend Investment Portfolio, I ended up with a watch list.  In the year or 18 months since I have been doing this type of investing I would just eyeball the investments or use Fidelity’s Pro Tools (which I have been booted from for a lack of trading) prior to purchasing with the intent of buying near 52 week lows.  However with the power that is google docs I can create up to the minute (think there is a 20 min delay) results as to certain stock metrics and then use those metrics in Formulas.

Watch List

Unlike the process to narrow down to a watch list which is hand inputted the final watch list is based on pure formula that is automatically updated by google.  As you can see my goal is to determine how far away a stock is from its 52 week high and 52 week low.

What Can Google Docs Import from Google Finance

How did I create the spreadsheet?  Amazingly, google docs is set up to import information straight from google finance which you can then use in formulas.  The support forums provide 20 common 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

The following types of historical data are currently available:

  • open: the opening price of the stock for the indicated day.
  • close: the closing price of the stock for the indicated day.
  • high: the highest price the stock traded to date.
  • low: the lowest price the stock traded to date.
  • volume: number of shares traded of this stock to date.
  • all: the concatenation of date/open/close/high/low/volume returned as a table for historical quotes.

 

Am I behind on the technology? Have you been using these free tools for awhile?

7 Responses to How I Use The Google Docs Spreadsheet For Investing

  1. Awesome! I keep track of my investments on Adaptu (another great free tool), but I have the stock price widget on my Google Reader too.

  2. Great use of FREE google spreadsheet. You can’t go wrong with free and I will surely be looking into using this. Stay young and thrifty :)

  3. Thanks. I never knew Google docs has such functionality. I will sure check it out. Have been skeptical of anything free…

Leave a reply


+ 5 = 6