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. This same method would also work for any other metric you want to automatically add. You just need to change the column in the formulas from the one that has 5-year CAGR to the column of the metric you’d like. 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″)
I’ve added the entire instructions on my previous article under the Advanced section that you can find here:
I’m having trouble getting this to work. The problem I’m running into is the CONTINUE function. Basically what happens is that when I use your first formula, A2: =importrange(“Hash”, “Champions!B7:B150″), the entire CCC champions sheet gets imported. The A2 column is correct; the A3 cell displaying MMM has a function of =continue(A2, 2, 1). However, the B2 column displays “Banking” with a code of =continue(A2, 1, 2). If I remove that code in B2 or replace it with anything else, it gets overridden from the function in A2 and returns to say “Banking.”
Per the directions on this site, https://productforums.google.com/forum/#!topic/docs/xu-SQtQHzss, I’ve managed to stop the repeating continue functions using this:
=index(importrange(“hash”, “Champions!B7:150”); 1; 1)
That functions works correctly for a single cell but you have to manually increment the row at the end in order to have the next ticker symbol show up; the row delineator at the end will not automatically increment as the function is “dragged” down.
How did you manage to get around this problem in your spreadsheet?
I’m thinking it might be just easier to import the entire CCC spreadsheet into a hidden sheet on the spreadsheet you want to use it on. Then I think everything would work normally without having to worry about the CONTINUE function. You’d also not have to update the hash to reflect the new uploaded version of the CCC list…just import the CCC spreadsheet directly into the hidden CCC data spreadsheet.
Thanks.
Scott recently posted…May 2014 Dividends/ Option Income
Scott,
That seems very strange. Honestly, all I did was put the following into A2: =importrange(“Hash”, “Champions!B7:B150″)
Did you make sure and replace the quotations with another ” so it doesn’t create a problem if you copied and pasted from here?
That formula doesn’t do anything in my spreadsheet to Column B. It’s only pulling B7:B150 from the CCC sheet so I’m not sure why it would pull anything else into another column?
Without looking at your sheet I wouldn’t know how to fix that as I never encountered an issue.
I haven’t updated a CCC sheet yet with a new version. The hidden sheet that you mention might be a good alternative if you never have to update the hash. I’ll be looking into that soon when I import that latest CCC sheets.
Sort of figured it out. My main investing spreadsheet still uses the “old” version of Google Spreadsheets. (Don’t know why Google made it so hard to convert existing spreadsheets to the new version…current recommended method is to create a new spreadsheet and then copy over each sheet individually!)
When I switched to the new version of Google Spreadsheets the issue resolved itself. Very frustrating! Might want to mention something on your site in case other people haven’t converted their spreadsheet over yet.
I never knew about the older version. Do you know when the newer one started? I’ll mention it, thanks. I figured it had to be something pretty simple.
I think I started hearing about the new version in December. New spreadsheets that were created beginning in March 2014 were on the new platform. Spreadsheets created before that time have yet to be converted over.
Great resource for setting up a google docs file for calculating dividends. Thank you
what would be the formula for pulling ex div dates from yahoo?
I would appreciate your help.
Hi Amit,
I’m glad this helped you out. The ex-dividend date can be pulled by using the letter “q” in the formula on the latest post I made. The code in red is just a combination of the api tags to pull the information I want. You can change that part however you’d like to.