I’m getting a lot of questions on how I use the Google Docs spreadsheets to pull in information automatically. Some of this information is very easy to find and use. Other financial data like Dividend Yield and Dividend Growth Rate (DGR) are not so easy. I’ll go through how I’m using some of these formulas in my portfolio.
Let’s First Start with the Basics
You can find the entire list here: Google Spreadsheets Function List
Most functions are in the form: =GoogleFinance(“Cell containing ticker symbol”, “Function name”)
From this list I currently use these Function Names:
PRICE – gives you the current stock price
CHANGE – gives you the change in price from yesterday’s close
CLOSEYEST – gives you yesterday’s closing price
HIGH52 – gives you the value of the 52-week high
LOW52 – gives you the value of the 52-week low
PE – gives you the P/E ratio (trailing)
BETA – Gives you the beta
MARKETCAP – Gives you the current market cap
I wanted to calculate a percent change from yesterday’s close. There is no specific function for this but we can use two functions to get the value.
If you look at APD in my portfolio under the “% Ch.” column, I calculate that as follows:
D2 contains: =GoogleFinance(A2; “change”) [This is the change in price from yesterday's close]
A2 contains: APD [the ticker of the stock]
Basically I’m using two of these functions to calculate what I wanted. I’m calculating the change in price from yesterday’s close and dividing that number by yesterday’s close to get the percent change from yesterday.
How to Calculate Dividend Yield Automatically
Now that you have the basics down I’m going to show you how to calculate the dividend yield and any information you’d like to import from another spreadsheet, like the CCC sheets in my resource tab. This allows me to grab the dividend growth rates and any other metrics I want.
First is the dividend yield. I’ve found a couple ways to do this so far.
I currently have a spreadsheet with the first tab named “DG”. My dividend page is on a separate tab named “Dividends”.
Just like in Excel, you can call information on another tab using an “!” after the name of the tab.
If you look at my Dividends page, I’ve calculated the dividend yield for APD using the formula: =C2/dg!C2.
C2 in my dividend tab contains “DG!P2″. So I’m basically taking the information I have in cell P2 in my DG tab and dividing it by the information I have in cell C2.
Now if you aren’t needing information from multiple tabs just skip to this next part.
In my DG tab it looks like this:
Cell C2 is simple, it just contains the price. So C2 has “=GoogleFinance(A2; “price”)” where I have the ticker symbol “APD” listed in A2.
Now that we have the price, we just need to divided that by the annual dividend to get the current yield.
This is the formula I’m using to pull the annual dividend from Yahoo:
Cell P2 contains (without first and last “): “=REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&A2&”&ql=1″, “table”, 3), 8, 2); “[()]“; “”) ; “([^/]*) “)“
The only change you need to make is to change the A2 in the formula to the cell that contains your ticker of the stock you want to look up.
That’s it! You now have a way to get the dividend yield automatically and it updates automatically as the price changes.
Update: After pasting the above code, the quotation mark, ” , changes into a different character in Googe Docs. Make sure you go through and delete each one and retype a ” in it’s place. This worked for me. This is for any code you try and paste into Google Docs appraently.
How to Import Information From Another Spreadsheet – 5-year CAGR
Ok so this one isn’t so bad either if you follow these steps. Let’s assume you want to grab the 5-year CAGR from the CCC sheets. You can obviously use this method to pull information from any Excel sheet.
The CCC sheet is an Excel sheet so we first want to copy that into the same Google Docs folder that contains your portfolio spreadsheet.
Make sure you are clicked on the folder that contains your portfolio and the Excel sheet that you want to pull information from.
Now you want to open the Excel file in Google Docs. So you right click on the file and go to Open With and select Google Sheets.
Now look at the url in your browser. You want to copy the hash, basically the series of numbers and letter after “spreadsheets/d/”:
Now we can grab information from the Excel sheet in your Google Docs folder. My hash string had 44 characters total. We are going to use the “=importrange” function to get the 5-year CAGR of APD:
The formula I’m using is (where Hash is obviously the string of characters we found earlier): =importrange(“Hash”, “Champions!AP11″)
The “Champions!” part is telling Google Docs which tab to use and the “AP11″ is the location of the information we want.
The second picture at the top I posted shows the DGR that I pulled into my portfolio.
Advanced – How to automatically pull in the 5-year CAGR for your entire portfolio
Ok, so I spent an hour or so playing around with formulas to do this. What I wanted to accomplish was a way to copy the CCC sheet to my Google Docs folder and have my spreadsheet automatically populate the 5-year CAGR for all Champions & Contenders, and the 3-year CAGR for all Challengers. Below is how I accomplished this.
First, create a new tab in your spreadsheet named CCC or whatever name you’d like. For these formulas I’m using CCC since that’s what I named mine.
We are going to employ two new functions. The first is ImportRange and second is VLookup. In your CCC tab, there are 12 labels and formulas we’re going to enter in the specific cell I listed as follows (Using the Hash we found ealrier):
Labels – (These are what I used, can be changed):
A1 – Champions
B1 – 5-year CAGR
C1 – Contenders
D1 – 5-year CAGR
E1 – Challengers
F1 – 3-year CAGR
G1 – Entire CCC
H1 – Entire YRS Inc
A2: =importrange(“Hash”, “Champions!B7:B150″)
B2: =importrange(“Hash”, “Champions!AP7:AP150″)
C2: =importrange(“Hash”, “Contenders!B7:B300″)
D2: =importrange(“Hash”, “Contenders!AP7:AP300″)
E2: =importrange(“Hash”, “Challengers!B7:B300″)
F2: =importrange(“Hash”, “Challengers!AO7:AO300″)
G2: =importrange(“Hash”, “Champions!B7:B150″)
G151: =importrange(“Hash”, “Contenders!B7:B300″)
G451: =importrange(“Hash”, “Challengers!B7:B300″)
H2: =importrange(“Hash”, “Champions!D7:D150″)
H151: =importrange(“Hash”, “Contenders!D7:D300″)
H451: =importrange(“Hash”, “Challengers!D7:D300″)
Your CCC Tab should now look like this:
Now let’s go to your portfolio page. In my portfolio page, I first want a column that lists the number of consecutive years of increases. In my portfolio, this is in column T. You can use any column you want.
The formula for T2 would then be (where A2 is the cell of your ticker):
You can now highlight this cell and drag down the entire column and press “Ctrl D”.
Now you should have the number of years for each holding that is contained in the CCC sheet. If your holding is not contained in the CCC sheets (It could be a foreign company), then a #N/A will display. You should now have a column that looks like this:
Now you just need a column for the dividend growth rates. I named mine DGR and it was in column M. I’m using if statements with vlookup to check whether each holding is a Champion, Contender or Challenger so the formula knows where to pull the information. The formula I have in M2 is:
Just like before, you can copy this formula down to the last position in your portfolio.
You now have automatically pulled in all of the DGR information for your positions that are in the CCC sheets! I’ve also added the DGR columns on my Portfolio and Watchlist pages on my website.
How are you using Google Docs?
I hope this was helpful. I’d love to know if anyone else has a better way to automatically grab dividend growth rates online or any other advanced formulas they are using. If I come up with any other better ideas I’ll be sure and post them.