Tracking your DGI Portfolio

Last month, All About Interest posted a nice article about using Google Docs for your portfolio. In his article he describes several ways to import data into a Google Docs spreadsheet. The easiest and most convenient way is to use the GoogleFinance function, which fetches current and historical stock information from Google Finance.

The purpose of this post is to explain the content of the above-mentioned article for readers that are not so technically inclined. I'm not presenting anything new here, except for a sample spreadsheet containing the necessary formulas.

I've been using the GoogleFinance function to automatically update the price of stocks I own in DivGro. However, there are many other bits of information you can import with GoogleFinance, including historical data.

Look at my DivGro-Portfolio spreadsheet on my Portfolio Page. At the bottom, the spreadsheet tells the viewer when the current price was last updated. Quotes may be delayed up to 20 minutes. The cool thing about using GoogleFinance is that your portfolio updates automatically, including other formulas that depend on data that GoogleFinance provides (such as the TOTAL portfolio value seen here).

Syntax

GoogleFinance(ticker, [attribute], [start_date], [num_days | end_date], [interval])

Only ticker is required. All other arguments are optional, including attribute, which defaults to "price".  Other interesting data that can be obtained this way include:

• "high52" or "low52" – 52-week high (or low) price
• "beta" – beta value
• "pe" – price/earnings ratio
• "marketcap" – market capitalization of stock
• "volumeavg" – current day's trading volume 

All arguments must be enclosed in quotation marks or be references to cells. 

Example

I created an example Google Docs spreadsheet in a public folder to illustrate the use of the GoogleFinance function. The spreadsheet comes pre-filled with four of the most popular dividend growth stocks:

Except for the Ticker column and the last two columns, every cell contains an instance of the GoogleFinance function. Below are three examples to illustrate, assuming the cell containing CVX is B5:

Market Price formula:	= GoogleFinance(B5)
P/E formula:	= GoogleFinance(B5, "pe")
Volume Avg (M) formula:	= GoogleFinance(B5, "volumeavg")/1000000

Annual Dividend?

For dividend growth investors, dividend data are very important. Unfortunately, GoogleFinance does not provide data about a stock's annual dividend or the compound annual growth rate (CAGR). 

Thanks to All About Interest, I've learned how to pull data from financial sites like Yahoo and from on-line spreadsheets like Dave Fish's CCC spreadsheet. 

An example of the first method appears in the second-to-last column in my spreadsheet. We're using the function ImportHtml to import data from a table or a list within an HTML page, in this case the annual dividend. Here's the essential formula, where, again, we're assuming the cell containing the ticker symbol CVX is B5:

Syntax:	ImportHtml(url, query, index)
Cell formula:	= ImportHtml("http://finance.yahoo.com/q?s="&B5&"&ql=1", "table", 3)

The first argument is the URL of the page to import data from – simply visit the page and copy the relevant text, then paste it as the first argument to ImportHtml:

You'll need to surround the URL with double quotes. To generalize the formula, replace the ticker symbol with the cell address containing the ticker symbol, using the string concatenation operator (&).

The second and third arguments refer to the type of query, either "table" or "list", and an index, starting at 1, to indicate which table or list in the HTML source import. In this case, we want the third table:

The function returns the entire table, so you'll need to leave enough space in your spreadsheet for the results. Alternatively, you can use the Index function to pick out the value you want:

Syntax:

Index(reference, [row], [column])

In this case, we want the value in row 8, column 2 of Table 3: 4.28 (3.40%)

Of course, what you really want is the annual dividend part of the value and not the dividend yield part in parentheses. Here, we'll use the function REGEXextract:

Syntax:

REGEXextract(text, regular_expression)

REGEXextract extracts matching substrings based on a special search string called a regular expression. It is well beyond the scope of this post to explain the intricacies of regular expressions, but, in essence, we want to find the first occurrence of a string containing any digits and a period up to but excluding a space:

Regular Expression:

"([0-9.]*) "

Putting all this together, the entire formula looks like this:

= REGEXextract(       Index(ImportHtml("http://finance.yahoo.com/q?s="&B5&"&ql=1","table", 3),       8, 2),       "([0-9.]*) ")

Finally, the dividend yield in the last column is simply calculated using the price and annual dividend.

In a future post, I'll discuss how to import other interesting data into a Google Docs spreadsheet, including from Dave Fish's CCC spreadsheet. Again, most of this information have already been presented by All About Interest in his excellent and informative post. My objective here is to explain things a little more and to provide example spreadsheets you could download and edit, or start using for tracking your own dividend growth investment portfolio.

Thanks for reading (if you got this far!). Did you find this post useful? Also, let me know if anything is unclear and I'll try to fix it...