All About Interest

One man's journey to financial independence.

Using Google Docs for Your Portfolio

by

google-docs-logo

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

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:

=D2/(GoogleFinance(A2; “closeyest”))

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 are unfamiliar with Excel it might be worth pursuing Excel training classes in your spare time.

If you look at my Dividends page, I’ve calculated the dividend yield for APD using the formula: =C2/dg!C2.

APD_Dividend_Example

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:

APD_DGR_Example

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 divide 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. However, if all this sounds too unfamiliar, you can take up excel online free classes to make it all seem like a cakewalk.

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.

google_drive

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.

Opening_Excel_Googledocs

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/”:

excel_hash

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

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

Formulas:

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:

CCC_Portfolio_Tab

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:

CCC_Years

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:

=if(T2>=25,vlookup(A2,CCC!A$2:B$150,2,False),if(T2>=10,vlookup(A2,CCC!C$2:D$300,2
,False),vlookup(A2,CCC!E$2:F$300,2,False)))

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:

Simple Google Docs Spreadsheet

Comments

  1. 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 2014My Profile

    • Asset-Grinder,

      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.

      Take care!

    • Hi R2R,

      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.

      Thanks for stopping by!

  3. 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.

    AFFJ
    A Frugal Family’s Journey recently posted…No impulse buys!My Profile

    • Frugal Family,

      I’m glad it helped, sorry it was a little late 🙁 I know spreadsheets can get very frustrating sometimes. I’m glad you were able to get it figured out. Let me know if I can help.

      Thanks for stopping by!

  4. 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 TakeMy Profile

    • Hey Scott,

      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.

      Thanks for stopping by!

      • AAI,

        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.

        You can also play directly with templates for the three spreadsheets that I’ve made on my website (spreadsheets tab) as well as on the Google template gallery: https://docs.google.com/templates?view=public&authorId=12641558499014907742

        I still haven’t worked out a good way to account for options income yet. Let me know if you can think of anything!

        • Scott,

          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.

          Thanks again!

    • Wallet,

      That string looks very interesting. I’m going to play around with it. I like how a lot of things are condensed into one formula. Thanks for sharing.

      Scott,

      That’s awesome news on Google removing the spreadsheet limits.

    • Evan,

      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.

      Take care!

  7. Hi,

    I’m getting an error when I try the formula below. I remove the 1st & last ” and change A2 to reference my data cell. I even tried to hard-code the stock symbol instead. No luck.

    Any suggestions? Or is there a way I can test portions of the formula separately to troubleshoot it.

    Thanks.

    Cell P2 contains (without first and last “): “=REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&A2&”&ql=1″, “table”, 3), 8, 2); “[()]“; “”) ; “([^/]*) “)“

    • Hi Richard,

      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.

  8. 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…

    Thanks.

    • 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.

  10. Very nice post. Is there a way for the cells to auto-refresh (say every minute) if I leave the google spreadsheet open.

    • I’m glad it was helpful. I’ve heard the =GoogleClock() function can do this but I’ve never tried it.

      Thanks for stopping by

  11. Hi!

    Here is a simpler and cleaner way to import dividend amount. Put this line to a cell:

    =IMPORTDATA(CONCATENATE(“http://finance.yahoo.com/d/quotes.csv?s=”,A1,”&f=d”))

    This will give you the dividend of a ticker in A1.

    • Hi Paapaa,

      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.

        • Gotcha. For my purposes and when you usually see the yield, it’s the forward yield based on the most recent dividend. If you have another formula for this dividend I’d love to know.

          Thanks for stopping by

          • I don’t think it matters anything if you get 3.4% or 3.5%. So in any case this is totally irrelevant.

            And ttm dividend is actual dividend that really happened, just like you can calculate PE using ttm EPS or some forward estimate. There is no better alternative – just different.

            I agree it is odd that Yahoo uses two different ways to calculate yield.

            Forward yield works for many dividend aristocrats but not all companies are that consistent. The a ttm dividend might be better approach.

            I don’t really care which I see. The ballpark is still correct and it is enough. I never have strict decimal criteria. And in this case I like more the simpler way to retrieve the data 🙂

            It is sad that Google doesn’t provide yield and Yahoo doesn’t have ANY official documentation of anything related to this.

  13. Thought you may like this function to pull the 5Yr CAGR from the web automatically so you do not have to update your CCC data every month.

    Here is what I currently use:
    =index ( importhtml(“http://www.gurufocus.com/dividend/”&B2&””, “table”, 4), 2, 3)

  14. Hello,

    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

    • Guy,

      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.

      Cheers!

  16. Looks like the Yahoo dividend yield import broke. Any idea what happened? Any decent alternatives?

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

All About Interest will use the information you provide on this form to be in touch with you and to provide updates and marketing.