One man's journey to financial independence.

Using Google Docs for Your Portfolio

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

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 priceCHANGE – gives you the change in price from yesterday’s closeCLOSEYEST – gives you yesterday’s closing priceHIGH52 – gives you the value of the 52-week highLOW52 – gives you the value of the 52-week lowPE – gives you the P/E ratio (trailing)BETA – Gives you the betaMARKETCAP – Gives you the current market cap

Example:

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:

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.

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.

Step 1.

Make sure you are clicked on the folder that contains your portfolio and the Excel sheet that you want to pull information from.

Step 2

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.

Step 3.

Now look at the url in your browser. You want to copy the hash, basically the series of numbers and letter after “spreadsheets/d/”:

Step 3.

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):

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):

=vlookup(A2,CCC!G$2:H$750,2,False)

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.

Update (12/24/15): I’ve posted a new and simpler Google Docs spreadsheet for all to download. Please see the following post:

Comments

Thanks a tonne! I really want to get my portfolio on a spreadsheets but I am a spreadsheet noobie. The frmula thing I am having a hard time with. Right now I just manually type in my info like I am Don Draper in 1969 but I am much less cool.Asset-Grinder recently posted…First NET WORTH update May 17 2014

I’m glad this was helpful. Haha, I hear you. I know it can be frustrating if you’ve never used them before but they are so helpful. I’ve always been fond of spreadsheets since college and enjoy playing around with formulas. If you ever need help just shoot me a message.

I just started grabbing information from the CCC lists. I have no idea if I need to change formulas when I add a new version yet though. The good thing is that the CAGR’s only change yearly. Hopefully my explanation wasn’t too difficult.

Great reference…I sure could have used some of the information a few weeks ago. I probably spent an entire weekend trying to learn the basics so I could upload my portfolio on blog. After some trial and error, I was able to upload my portfolio but I know there is so much more still to learn. Thanks for including the functions list, I will be spending some time trying out some of them so as to further improve my portfolio page.

Very helpful information, AAI. It’s too bad that Google Docs has not yet incorporated the ability to pull in dividend yield information via the GoogleFinance function. The importhtml and importxml functions do work okay though. Hopefully Google will raise the limit on the number of times these functions can be used per spreadsheet…I’ve run into that limit when my watchlist gets too big.

Another problem I’ve found, especially using the importhtml function, is that it is very dependent on the website layout not changing. (In that regards, the importxml function may be a little more robust.)

I’ve used a similar code on the spreadsheet available on my own site to pull in the 5-year dividend growth rate from Gurufocus in order to calculate a stock’s “Chowder rule.” Fingers crossed that the layout stays the same so that the importhtml code doesn’t break.

Overall, I’ve been very impressed with Google Docs, especially with regards to its ability to automatically update stock prices and dividend yields. Back in the day I used Excel and OpenOffice but, in terms of online data lookup, don’t think those are nearly as powerful.Scott recently posted…The Illogic of Active Trading – My Take

I agree with you. Google Docs really needs a function for yield. I’ve also run into the 50 function limit so I have to use multiple sheets. Another issue I run into (and I’m not sure if it’s Google or Yahoo) is that the dividend pulls in an error so nothing gets populated. This error can last up to an hour then it’s usually gone. It happens randomly. It’s sort of a pain.

I may have to email you for the code to pull the info from Gurufocus. I want to incorporate the 10-year YOC based on a 5-year CAGR. This would be very nice to have.

I’m also impressed with the auto-updating. Since I have multiple brokerages I like looking at one spreadsheet with all my stocks together. It makes things simple and I can access it on the go. I also use Excel but as far as online functionality then I just use Google Docs.

I’ve also occasionally gotten that same error where nothing gets populated and then fixes itself. Very frustrating, though it seems less frequent now.

To return 5-yield dividend CAGR, I used this code:
=index ( importhtml(“http://www.gurufocus.com/dividend/”&B6&””, “table”, 3), 2, 3)
B6 is the cell with the stock symbol. Then just add dividend yield to that to get the Chowder rule number.

Thanks for the code you use. I have some good ideas to add to my spreadsheet now. I don’t use the Chowder rule but a 10-year YOC metric. Either way I need the 5-year CAGR to calculate it. I wonder if the data at gurufocus is in line with what is on the CCC sheets. I’ll check it out.

I’ll check out your templates, thanks for the link.

I only sell options in one account, E*Trade, currently. I also don’t sell a whole lot of them and usually put in limit orders if I want to close one out. I’ve just been tracking the earnings on my options page. So I haven’t looked into options income or pricing automatically. If I do come up with something I’ll be sure and let you know.

Scott, I love the tutorial but way above my head! For my monthly dividend research posts I literally enter in all the data manually from morningstar lol. I always think to myself there has to be an easier way lolEvan recently posted…Random Thoughts About My Trip to Disney World

You could start with some of the simpler formulas until you get used to using spreadsheets. It just takes a little bit of time but once you know what you’re doing it can save you a lot of time later on with all of the automatic updating.

I figured it out. I also was getting errors from Scott’s code above. Apparently when you paste the code into Google Docs, the ” actually turn into a different character. You need to paste the code then replace the ” with actually retyping the ” in the code. Once I did this, it worked.

On a more general note, do you all rely on the Yahoo or Google Yield & dividend data? A while back, when I was looking into a way to automatically collect yield & dividend data, I found some discrepancies in numbers between Y/G and other sites that dealt with dividends & historical payments data only…

Any comments, ideas/suggestions or workarounds? Entering everything manually is so tedious. There has to be a better way – I hope…

The discrepancy between sources is likely due to different stock prices being used. If Google and Yahoo’s listed dividend yields were truly “real time” or even 20-min delayed, then the yields should change with each page refresh.

The most accurate would be to pull the annual dividend from either source and then divide that by the current stock price to get the annual dividend yield. Then you’re not dependent on waiting for Google/Yahoo to update the reference stock price. This is the method that AAI describes above.

I just tried your formula. It’s certainly pulling in values but they don’t match up with the correct dividend. For instance, I used it for A2 which is APD. It gave me 2.9 as the output. The correct dividend is 3.08. I got different values than the dividend for each ticker I tried.

Both are correct values. The CSV version shows you the actual dividend from the last 12 months. The other version shows you an estimate: it quadruples the current quarter’s dividend. Both are correct and both give valid info.

Good grief, right after I typed that I figured it out. Dunno what you used but i just used the code
=SUM( F2:F25) for example than converted that to a percentage with the % button. Seems to work ok. 🙂Captain Dividend recently posted…Recent Buy – General Mills (GIS)

Very nice, informative and very well explained. You are Master of the subject. I seek your guidance to achieve the following in my spreadsheet which I am learning to prepare.
1. 3 family members, 3 sheets in one spreadsheet.
1. One member purchasing say particular stock ‘A’ bought many times- on different dates, at different prices, in different quantity. How to automatically consolidate them in single entry by adding the quantity and calculating average price instead of doing manually relating to that particular member.
2. All members have some common stocks and some different stocks from each other. want to prepare single consolidated sheet based on names of stocks with total quantity and average price of that stock.

While helping, please take into account that you are guiding a layman.
Regards

I don’t, and haven’t tried to do something like that. I have just been using Yahoo’s quotes. I’ve also posted a simpler portfolio and formula that I’m using now. I’ve just posted a link to it at the bottom of this page.

I just noticed this. I’m checking some google forums for answers. It seems everyone is having issues right now importing yahoo functions. The strange thing is that if I erase the cell and then restore the formula, it works. Eventually the error comes back but it is working temporarily doing this. I’ll post a fix or workaround as soon as I can find one.

Glad I found you guys chatting here. I’m also having major problems. Thought I was the only one! I’ll try the suggestion above. I hope it’s that easy.
-RBDRetire Before Dad recently posted…5 Truths About Traveling The World In Your 20s

Categories

Disclaimer

I am not a licensed financial professional. I created this site to be informative and entertaining. No purchases I make are recommendations to buy those particular equities. I'm not liable by any party for losses you might incur. All investments are subject to losing money and you should consult a financial professional before making any investment decisions.