Did you know that you can build an automated stock tracker in Google Spreadsheets, using a little-known GoogleFinance() function?
- There is a function in Google Spreadsheets that lets you pull a stock's price and other stats, automatically pulling current market data into your spreadsheets.
- This =GoogleFinance() function is fast and efficient to lookup stock price, eps, market cap, etc.
- This helps turn Google spreadsheet into a more flexible & efficient way to track your holdings or diligence your stock investments.
- It's like Excel with import macros but without the pain.
Tracking Public Equities
I'm in perpetual search for new, efficient ways to track things, especially companies. I'm proud of DataFox as it's an awesome way to track private companies. I also enjoy tracking public equities and I have several favorite ways to do this, filling different needs with different solutions. For example, I use SeekingAlpha to read a lot of people's perspectives on equities. I use the new Yahoo Finance app to check prices and read the news. In my banking days, I used our paid services to save different filters and export them to Excel.
Current Tracking Solutions
Meanwhile, none of these are really great at (a) being mobile, (b) tracking my holdings in a lightweight way, and (c) allowing me to really analyze ratios and sort to identify outliers, etc. Excel is obviously the go-to analysis tool, but it doesn't auto-fill with updated market data and it isn't cloud-based. While I have tried tools offered by lots of mobile apps and big online brokers, none of them make it super easy to track ratios across large watchlists of companies (at least I haven't been able to find them). If you have good suggestions here, let me know in the comments!
Having used Google Spreadsheets more advanced formulas before, the thought occurred to me to check if there was a way to dynamically import stock data and financials into this spreadsheets product. Much to my delight, there is!
Having found a system I am excited about, I thought I would:
- Show it to you
- Teach you to build it
- Share my template with you
Below is a step-by-step walkthrough of how create your own dynamically updated stock watchlists. (approximate time: 5 minutes)
| This post includes...
How to Create a Stock Watchlist in Google Docs
- A list of ticker symbols you care about (or mutual funds)
- A Gmail account so you can use Google Docs
- A DataFox account (optional, but delicious)
- A willingness to learn a dash of programming along the way
Step 1: Gather a list of companies you want to track
Since I'm using DataFox, I just visit my watchlist and click "Export to CSV" to download it.
But, it doesn't have to be from DataFox - any list will do - as long as you have the company's public market ticker in one of the columns, as below...
Step 2: Paste this company list into a Google Spreadsheet
If you already have a list of tickers that you want to track, paste it in. Otherwise, you can type in your own tickers in a column.
Step 3: Add your first dynamic lookup
Because my ticker symbols are in column "B" and I want to fetch the stock "price" from Google, I use THIS formula for the company in row 12:
=GoogleFinance(B12,"price") Here's the Google Documentation for how the GoogleFinance() function works
Step 4: Add formulas. Make them smarter with "$"s and references
Next, I want to track more than just price. I want "Price to Earnings" and the 52 week high's and low's. Full list of the stock stats available are below. The formulas available for mutual funds are in Google's documentation.
In the example below, I now use this formula:
If you are an excel wiz, the following two points are superfluous. If not, you will note that there are two tweaks to the previous formula.
- I refer to "E$1", rather than putting the text string "high52" into the formula. This means that I can later change the cell "E1" once, and all of the formulas/rows in that column will update to pull a different stat from Google. This also means that I can drag the formula to adjacent columns and they will always look into whatever is in the header row, rather than hard-coding the word "price" and "high52" into all of the formulas.
- I put "$" next to the "B" and the "1". The one before "B" will freeze that column, so that I can copy my formula from column "C" into columns "D", "E", etc, but they will all look to column B. With the "$" before the "1", I make it so that when I copy and paste down (really, highlight all and cmd+d), they will all look to row 1 where I have "high52", etc.
Step 5: All of these stats ("attributes") are available from Google
Pick from the different "attributes" that you can ask for from Google - depending on which types of stats and ratios you want to analyze. The ones supported as of this writing (Nov 2013) are below...
- 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 at for the current day.
- low: the lowest price the stock traded at 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 market close yesterday.
- beta: the beta value of this stock.
- changepct: the percentage change in the price of this stock since market close yesterday.
- 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.
Step 6: Effortlessly track and analyze
From now on, this document will update itself automatically whenever you load it. Gone are the days of checking stock prices and doing mental math to calculate the difference between current price and when you bought it...or how the market cap compares to other companies, etc. All of the data is right there, so you can run calculations and assess on the go and on any device.
It's kind-of magical that Google makes it so easy to have your own custom version of Google Finance that you can update, add formulas and derivatives to, and track with so much of the power of a spreadsheet, but backed by current market data. While it's not real-time, the docs I read quoted a 20-min delay time, which is great for the kind of end-of-day check-ins and periodic analysis that I use it for.
Finally as a special goodie, here is the public version of my example tracking spreadsheet (from DataFox's "Dividend Stocks" example watchlist):
You can just open this template version and then copy and paste it into your own spreadsheet (and then edit from there). It is "locked", so that everyone will have access to the master copy, but you can clone it for yourself and then adjust it from there.
Hope you enjoy! Let me know if I missed anything, if you want more like this and if you have any other tricks for tracking equities that others might like.
Disclosure: I am not an investment advisor. Neither is DataFox. I am not advising that you buy these stocks or any others. You should do your own research on what to buy, if anything. I currently have an investment in two of these stocks, but I am not saying you should or should not invest in these stocks, or any stocks, in general. This list is just a sample I've used to show off a cool technique for using the internet to track data.