I have to give a BIG thank you to a reader named Dan. He built this spreadsheet and shared it with me weeks ago. With his permission, I’ve created a copy and am sharing it with you today. Thanks, Dan!
The Spreadsheet
To start, you can see the spreadsheet by clicking here. It is build in Google Sheets. When you go to the spreadsheet, you’ll see a sample portfolio built with Vanguard mutual funds and ETFs. This is not my portfolio (or Dan’s). But it will give you a clear picture on how it works.
You’ll want to make a copy of it so that you can edit it and save your own portfolio. Remember, in order to make a copy, you’ll need a Google account and you’ll need to be signed in. Once that’s taken care of, you can make a copy by selecting ‘File’ from the menu and then ‘Make A Copy.’
The workbook has just two sheets. The first sheet lists all of your holdings (click image to enlarge):
The sections in color you’ll need to fill out. Once you do, the other columns get automatically populated using the Google Finance function and built-in formulas. Note that for ETFs you’ll need to input the expense ratio yourself. For reasons unknown, the Google Finance function doesn’t provide it.
The second page provides a summary of your investments by asset class, tells you when it’s time to rebalance, and provides a graph of your investments:
The Rick Ferri and Personal Capital allocations are there just for ideas. You’ll enter your asset allocation in the column labeled “My Target.” Note that the spreadsheet comes with five asset classes listed in rows three through seven. The spreadsheet calculates how much you have in each asset class based on column D of the first page of the workbook. If you want to add or change asset classes, you can. Just remember to add them in column D of page one and then add a row for each new class on this page.
I use both this spreadsheet AND Personal Capital to track my investments. The spreadsheet helps me rebalance my investments, as you’ll see below. Personal Capital is excellent for tracking investment fees, asset allocation, and even my monthly budget. It’s by far my favorite financial tool, and it’s totally free.
Rebalancing
One of the cool features of this spreadsheet is the rebalancing function. You’ll see in the sheet above columns labeled ‘Actual’, ‘Diff’, and ‘Threshold.’ It’s critical to understand how these work.
Actual: This simply calculates the actual amount of investments you have in each asset class. This data comes from the Holdings page of the spreadsheet.
Diff: This column calculates the difference between your target allocation by class and your actual allocation. Note that some of the cells in this column are in red.
Threshold: This column allows you to set a threshold for each asset class. In other words, how much can your actual investments drift from your target allocation before you need to rebalance. Once your actual allocation drifts from your target by the threshold percentage, the difference in the Diff column turns red. It’s a visual way to see what part of your portfolio might need to be rebalanced.
Leave comments below if you have any questions on how to use the spreadsheet.
Enjoy!
Check It Out: Personal Capital Review
Podcast: Play in new window | Download | Embed
Article comments
Just wondered if for those with CD’s part of the portfolio (safer than bonds and yet yielding presently more than short/intermediate term bonds), whether the google spreadsheet could be updated for this possibility?
Or the possibility to add individual bonds?
Jon, you certainly can add individual stocks to the spreadsheet. I do that for my portfolio. I’m not so sure about individual bonds, at least form a Google Finance perspective. You can of course add anything manually.
Rob, very nice spreadsheet. I live in Canada, and it works here, too, although with 2 issues.
1. One of the ETFs I own is iShares emerging markets ETF, XEC. When I enter that symbol, Google Finance recognizes that as Cimarex Energy Co. I can manually change the price to that of iShares emerging markets ETF, but would you know how I could correct that?
2. I also own 3 US domiciled ETFs which are quoted in US $s, so I need to manually change the value to Canadian $s. I guess there is no easy way around that.
Thanks!
Grant, on #1, that’s a GoogleFinance issue. The only thing I know to do is enter and update it manually.
Regarding currency, Column G’s strings could read “=GOOGLEFINANCE($B3,”price”) * GoogleFinance(“CURRENCY:USDCAD”)”. This assumes investments are held in one’s own currency. Better yet, code the string so that it looks within a user-input cell containing the currency one prefers. That way it is more universal.
The currency exchange rate function could also be used as a field for each investment if one wants to track impact of exchange rates for foreign investments purchased in native currency.
You can try using the importhtml function in google sheets to import ducky from Morningstar or yahoo finance. I know few folks on bogleheads do so, although I’ve never tried it.
Great spreadsheet Rob. I added YTD return to mine using google finance feature. Thanks for sharing.
Mark, nice addition. I’ll add it to the spreadsheet as well.
Just curious how you got this to work……..I’m trying =GOOGLEFINANCE($B9,”returnytd”) and no success…….I just keep getting loading data error. Checked with Google Finance help and it seems to be an issue…
Works now. Guess it needed to reload.
Rob, I notice that the spreadsheet works fine when run directly off google docs, but when I try to save the file as an excel file to my desk top, some of the fields are filled with “value” with no total displayed. What am I doing wrong? Thanks again
Same problem for me. Also, I tried to add some extra rows to input additonal funds and the formulas didn’t copy. When I went back to copy formulas, data changed to n/a value, etc…..Seems to act a little strange when adding lines, copying formulas, etc.
Both are spreadsheet programs but built on different codes. It’s like trying to play a nintendo game in a playstation.
Try googling for a VBA macro to use google finance api in excel but you’re better off installing the Yahoo finance add-in.
Im having the same issue…I’d like to keep this spreadsheet out of Google docs, but does that mean it actually wont work? I dont understand the Yahoo Finance part you mentioned Justin.
Neo, that’s correct. Excel doesn’t come coded to pull ticker information from MSN/google/yahoo/etc. Google sheets on the other hand already “knows” where to pull information from when you give it the function”googlefinance”.
So if you want to use Excel instead, you need to define the function to tell Excel where to look and what data to pull. I mentioned Yahoo because people tend to have better luck pulling their data rather than google.
If you look it up, there are plenty of add-ins that will cost you because someone has done the work to script it.
You might be lucky and find someone who has generously posted their work. This link might work: http://www.financial-modelling.net/tutorials/excel/open-excel-retrieve-stock-quotes-in-a-formula
Rob,
My understanding is that the “category” column, which represents the funds asset class, is self-populated, not auto-populated. So, if you have a fund that has multiple asset classes, like many do, what would you say is the best way to manage that? Do you have to enter multiple lines for that fund and divvy out the percentage of shares for each line based on the percentages of each asset class within the fund?
Brett, that’s a interesting issue. As the spreadsheet currently works, you would have to create multiple lines for the same investment. I’ll give some thought to whether a better solution exists for that situation.
Hey Rob… This is Dan (yes that Dan). This is an issue I also brought up to you in a couple different emails now. I have a solution already done for this, I was just looking for a good source for this data.
If possible, would someone mind sharing the method for breaking down managed funds, e.g. VFIFX? I know it’s possible, wondering what’s the best way to do it without manually adjusting every time I purchase shares. Thanks.
Brett/Rob – I’ve struggled with same issue of multiple asset classes. I initially created a “blended” category, but then took the step of entering two lines, using the % bonds vs. equity from the funds profile page online. Too important to have the bond/equity allocation broken out to use the “blended” category.
Rob, I wouldn’t worry too much about comments regarding specific issues people are going to bring up. You provided a good template that anyone can modify to their liking. The syntax might differ slightly from the more popular MS Excel but documentation regarding google spreadsheet is robust.
This spreadsheet is FANTASTIC once set up. I was able to customize it in many ways and most likely will make it my “go to” spreadsheet. The only issue I’m having and I don’t know if there’s a good answer is – I have all my mutual funds listed with ticker symbols and love the “real time” updates. However, i have quite a few funds in my 401K and my my wife’s, COPERA 401K / 457 Plan which do not have ticker symbols. Thus, I need to manually update with Shares and Price. These funds are automatically updated on the Vanguard site (as Outside Investments). The Vanguard site is able to log into Fidelity and COPERA and refresh the data to current. I guess I’m asking all you Excel experts if there’s a way to do this in the Google Spreadsheet to avoid the manual entries. Rob – THANKS for a great show and the continued wonderful advice……………Mike
You can only track investments with ticker symbols. If you can decompose funds, you can track them in google finance. (e.g. PeraAdvantage SRI Fund is 60 NSRIX, 40 OGGAX). Some COPERA funds unfortunately are actively managed and thus opaque.
Thanks Justin…….I thought about decomposing, but it gets fairly complex. I’ll just manually update.
Mike,
I have the same issue (as I’m sure many do), and modified the spreadsheet to account for it:
First, determine a proxy for each fund in your 401k. For instance, I have a Northern Trust S&P 400 index (NTDPT) for which I’m using IJH as the proxy.
Then, get the closing prices for both your fund and the proxy to create a conversion (e.g. $120/$85). Add a column to the spreadsheet for this conversion factor.
Enter your proxy in the Symbol column.
Lastly, modify the Value column to multiply the existing formula by the new conversion factor column.
This should give you at least be a pretty close value for your 401k funds.
Mike,
Tried to post this earlier, but it didn’t work 😕
Find a proxy for the fund, and using closing prices determine the price ratio between them. Then add a column with that conversion ratio, and use your proxy in the ticker column. Modify the value column to include the conversion in the calculation.
Interesting. Thanks Richard, I’ll try that.
Is there any way to plug in the funds from the Thrift Savings Plan (TSP) into this spreadsheet since they don’t technically have ticker symbols?
To get share price data for TSP funds, you’ll need a workaround, because GoogleFinance can’t recognize the TSP funds. See this link for how to do it: https://www.bogleheads.org/forum/viewtopic.php?t=141547
Can I change the categories of the assets classes? The included ones don’t really fit with my asset allocation.
I found out how to change categories of asset classes by listening to the podcast on the spreadsheet.
Click on a cell in the Category column. Go to Data -> Validate then you will see the asset classes listed by commas. You can change them there.
Three quick notes in reply to the above great comments:
1. Thanks to all who have improved the spreadsheet and shared their tips here.
2. I’ll be incorporating many of the improvements into the spreadsheet soon.
3. Sorry if it takes some time to see your comments once you post them. If you are a first time commenter, I have to manually approve the comment. It’s the only way to fight spam!
Best, Rob
“If you want to add or change asset classes, you can. Just remember to add them in column D of page one and then add a row for each new class on this page.”
This is not working. I want US Bonds and International Bonds. I change the current Bonds to US Bonds and add an International Bonds row but these new asset classes aren’t available on the Holdings page in Column D. Please help!
Nevermind, just saw Matt’s comment above:
“Click on a cell in the Category column. Go to Data -> Validate then you will see the asset classes listed by commas. You can change them there.”
This looks great! I’m definitely going to try it when I get home! I have a few ideas of my own for some things to add later, but I know what I’ll be having fun with tonight!
Thanks for the share Rob!
The spreadsheet seems to require permission to access.
I sent a request for access – thanks in advance. Appreciate the help.
Evelyn, make a copy of the spreadsheet and then you’ll be able to edit it.
Rob, for some reason the spreadsheet for me is now in View Only mode when I download it. I’m not able to make a copy of it. It is now protected and requires you give permission in order to make a copy. This seems very strange. I am logged on to my google account when I download the speadsheet.
Thank you for making this available – for a spreadsheet newbie, this one looks workable for my passive index etf portfolio.
Rob, I feel like complete dunce. For the life of me no idea why on earth I didn’t just go to File – Make a Copy. Sorry for the confusion and again thanks for the resource.
Evelyn
Rob, great spreadsheet. Thank you so much for doing this. One small suggestion: If you would change the validation in the Holding sheet to use the method “List from Range” with range ‘By Asset Class’!A5:A11, it makes it easier for people to add their own asset classes
Christian, thanks for the suggestion. I’ll look into it.
Great Spread sheet. Any way another tab for stock option trades could be added. Maybe others have a spreadsheet they use for option trades.
Great spreadsheet except one issue: the Stocks tab is not incorporated into the current value formula on the By Asset Class tab. For example the value for US Stocks is =SUMIF(Holdings!$D:$D,”=” & $A5,Holdings!$H:$H) meaning the Stocks tab is not included. Can you provide the code for including the stocks tab?
How do you add lines that are formatted to make more entries? I just added rows, but then none of the formatting is done on those rows. Is it limited to just 20 entries? I’m sure not, but I don’t know how to add.
Thanks for your help.
If you’ve already added new, blank rows, it’s quite easy. You can copy an entire line (by clicking on the actual row number to highlight it all, then right-click+Copy or Ctrl+C), then click on the new row’s number to highlight it and and paste the copied info (right-click+Paste or Ctrl+V). The formatting should be included, so you’ll just need to change the values shown to match your new entry.
Let me know if that doesn’t clear it up a bit!
–Stephanie
Hi Stephanie, and thanks for your help! That worked for the most part. Columns I (Portfolio%) and K (Weighted) only work through line 23, after that they say !VALUE#. Also, the pulldown menu under Category only works thru line 25. After that I just entered it myself. But I hesitate to keep adding data if this is not formatted correctly.
I think there are still other problems too.
I added two asset classes under Category: Preferred and REIT. I use them in the appropriate cells, but I notice they do not transfer to the Asset Class page. So I added them to the bottom of that chart, but of course the formulas for adding in the figures from the Holdings page are not there, so there’s no information.
Anyway, I really appreciate your help. I’d love to make this all work if you, or anyone, knows what I’m doing wrong.
Thanks! (I am working entirely in Google drive and the price info is being drawn from Google Finance as it should)
Peter – just wondering if you ever solved your problem? I have the same problem with the I & K columns not working, but for me it’s after Line 15. I think it possibly has to do with how I copy and pasted data to create more rows but I’m honestly not sure. Please let me know if you figured out a work around. Thank you!
Never mind, I figured it out! Data –> Named Ranges allows you to change the values of the “Holding Value”. Fixed!
I started filling it out and I assumed it was being saved in Google somewhere, but now I can’t find it. Where are these things saved?
Did you “Save a copy” first? If so, try looking in your Google Drive (drive.google.com should prompt sign-in if it doesn’t pull up your files automatically).
–Stephanie
Thanks again Stephanie. This helped me find it. I still don’t know where it is on the Google menu, but if I search for Google drive it comes up. Their menu is awful, it should say where the documents are. But thanks you saved the day for me twice!
Has anyone figured out how to get the I and K columns to work? I was able to copy the formula for everything else. Maybe Dan “the man” who created the spreadsheet knows?
Thanks
Hi, once a stock in the holding is sold, is it saved somewhere or is deleted from the sheet ?
Aji, that’s up to you. You can delete it or move it to a new page you create with stocks you’ve sold.
Thank you! The spreadsheet is fantastic! I was able to make ETFs work, Canadian mutual funds and Canadian stocks. All you need to do is add the ticker symbol exactly as Google Finance has it. As a suggestion, it would be great to take currency conversion into account.
Thank you so much for publishing this!
Stupid newbie question. How do you save your changes? I try but when I reload the spreadsheet is is as original.
Sorry!
Paul,
You should be able to save it to your computer as a copy, which you can then edit with your own tracked info!
–Stephanie
Great spreadsheet. The prices are updating on my pc but not my phone. Am I missing something?
really like this spreadsheet and find it very usefulfor Mutual Funds tracking. so thanks for developing- one thing I would like to do and see that others have mentioned is to be able to show multi categories for a mutual fund, that can then be visualized in the asset class page- is there an easy way to describe how you can do this?
thanks much!
This investment tracker is just what I’ve been looking for! Thanks. However, I’m having the same problem that was addressed back in July of last year – adding rows and having #VALUE show up in column I and K. Someone back then said he figured out how to fix this problem, but unfortunately I’m not well-versed in spreadsheets. Could someone please give a detailed explanation of the steps I need to take to resolve this? Much appreciated!
Thank you for the spreadsheet. I was looking for something to help my parents view their holdings and figured it would be Excel based, but the googlefinance formula is too easy too resist.
Thanks for the spreadsheet. I am not used to using these and can not figure out a way to add rows that function.
If you are using Excel software, then here’s the simple trick: right-click on your mouse to activate a floating box. Move the mouse up to and hover it on the “Insert” portion. Next, you will see “Insert Rows Above” as well as another command. Click the first one I suggested; it is below the other one. Unfortunately, there are no short-cut keys to do so when on Excel.
Great spreadsheet! To me the real value is seeing the changes over time. If I use this going forward (updating each quarter or so), what’s the best way to preserve historical info? I copied the By Asset Class to a new sheet and then just pasted values into column H instead of formulas. Maybe then just add a column for Current Values each time I update? Next quarter Column I will be the latest and greatest amounts? Thanks for any suggestions.
Please explain how to add additional lines so that I can add more funds. Every time I add a line it will not complete the data filling function for the fund and gives errors for the other values ie Portfolio $ and weighted expense ratio.
Gale, you need to copy those actual cells with formulas. And for the asset class options if the copy/paste function doesn’t work you will need to put those in yourself. Good luck, repost if you have more questions and I will try to help.
John- thx but not sure I quite understand your answer to Gale’s question re columns for portfolio % and weighted expense ratio- can you elaborate? I copied the formulas for each new row I added ( =HoldingValue/TotalValue) and ( =$K18*PortfolioPercent) but get the #VALUE error- By adding a number of rows to the spreadsheet, even though i copied the formulas, I get these errors- what gives? thanks
can you tell me how to get the Stock page total of investments to be included in the total value of the first page “by asset class”? thank you
How do I account for sold funds/assets using this spreadsheet? Or once sold do I just delete them?
I delete them, but it’s really up to you.
Hi Rob,
Excellent, excellent sheet! I do have the same question as above; how can the Stocks sheet total investments also be shown on the By Asset Class sheet?
I categorize my individual stocks the same way I do mutual funds. So, for example, my shares of Berkshire are listed as U.S. stocks. They then show up on the asset class sheet under that category.
Hi Rob
Any suggestion for any service or software to track performance of individual stocks and ETFs ? Personal Capital only does whole portfolio performance.
By the way the sheet on google docs only contain the 2nd page – couldn’t find the 1st page with all individual Vanguard funds….
A.J., Morningstar works very well, although it does take some time to set up. As for the spreadsheet, I just checked it and it has 3 separate sheets. One shows individual Vanguard funds as an example. Is that what you are seeing?
yes I too am also only seeing the second page….
Thank you for the spreadsheet!!! I did not realize that Google have created the finance functions. Now I have a lot of searching to do. Great start of a spreadsheet.
Thanks so much! I just found this and it’s wonderful. Yay!
Have you come across a good workbook that will lay out the journal entry to reconcile the change in value during the month in the portfolio? My reconciliation template is far to general, and I can never seem to get to the fund manager’s same dollar amount for the change in value, even after I enter all the activity. Many thanks 🙂
Hello, I’m from Singapore and would like to use this spreadsheet for some stocks listed on the SGX. For argument’s sake let’s say I want to add “Singapore Exchange Limited”, an individual stock. the google finance symbol is “SGX: S68”. But when I try to change the Symbol to either “SGX: S68” or “S68” neither works. Is this spreadsheet incompatible with non-US listed stocks?
Thank you so much
What is alternative investment on the spreadsheet?
Can you download the spreadsheet and work with it on your computer/laptop, or will the functions not work outside of Google Sheets?
Thank you for this. How would I enter my canadian corporate bonds, and Canadian Stocks?
POS dont try to use. They immediately force you to arrange a meeting with one of their hounds within 14 days. scam.
i agree back to excel… dont have to give google anything more
Is it possible to get “current Value” of each individual asset on the “Holdings” tab? I’m not sure how to link the to the current market price…This would help when rebalancing and tax harvesting at the same time.
The GOOGLEFINANCE functions have been notorious off for most of 2018. Sometimes it rotates back and forth between two sets of data. It’s not simply a delay – sometimes it’s showing something like the S&P 500 being up, say, 0.26% when it’s been higher or lower for an hour.
The Personal Capital links won’t open in Chrome. Just says “Not Secure”
Isn’t it better to use a platform and not sheets? I mean sheets don’t seem to me as the best alternative. Since Google Finance portfolios were killed there appeared several companies that brought a good product without the need for google sheets. I mean you have tradigview, morningstar, marketwatch or wallmine. And most of them are free – at least partially
Are there any updates for this spreadsheet? There are a lot of #N/A’s and google can’t pull money fund information.
Same comment as from Angelique. Lots of N/As. Error message was not helpful. Please update – you are right this spreadsheet is awesome but some fields no longer working.
This is totally awesome!!! I love this!! It’s so nice having the stocks automatically update to their share prices. Thanks Dan!
How do I change this to suit Australian share (ASX)?
I also have some US, Asia & Europe exposure so I want to cover all bases.
Apologies if this has already been answered, I couldn’t see anything.
Thanks!