About a week ago, I spent some time messing around in Google Docs trying to get it to automagically pull in data related to stock quotes. At the time, I was interested in getting updated dividend yields, P/E values, etc., for the S&P Dividend Aristocrats.
For background, most of you know that I’m a diehard index fund investor. That being said, I love data, and the idea of dividend investing intrigues me. During a recent chat with Jim from Bargaineering, we got to talking about how there must be an easy way of compiling stock data in a spreadsheet by pulling data from the web.
I was actually vaguely aware that you can do this in Excel, but it seemed that it should be even easier with Google Docs. As it turns out, I was right… They have a “GoogleFinance” function built right in.
For full details, see this page from Google.
Here’s how it works:
To insert the current price of a stock into a spreadsheet cell, use:
Where GOOG is for Google, though you can use any ticker symbol.
For P/E ratio, use:
And so on…
The page I linked above includes a list of attributes that can be accessed in this fashion, including trading volume, 52 week high and low, change from the previous day, number of shares outstanding, market capitalization, and many more.
The beauty of this approach is that whenever you load your spreadsheet, you’ll have the latest data at your fingertips. Also note that you can use this same approach for mutual funds or ETFs.
Unfortunately, they don’t currently offer access to dividend-related data using this function. I’m not sure why, since that info is all included on the quote page. While you can still access it by using the “importhtml” function and extracting what you need, that’s a far more complex endeavor.
If you have any other useful Google Docs tricks, I’d love to hear them.