Editor's note - You can trust the integrity of our balanced, independent financial advice. We may, however, receive compensation from the issuers of some products mentioned in this article. Opinions are the author's alone, and this content has not been provided by, reviewed, approved or endorsed by any advertiser.
There are a lot of tools out there to track your investments. One of my favorites is Personal Capital. It offers a free online financial dashboard that automatically tracks your investments and your spending. Today I have another option for you–a free spreadsheet that tracks your investments and tells you when it’s time to rebalance your portfolio.

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

Investment Tracking Spreadsheet

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:

Asset Allocation Spreadsheet

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

Article comments

93 comments
Jon says:

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?

Jon says:

Or the possibility to add individual bonds?

Rob Berger says:

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.

Grant says:

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!

Rob Berger says:

Grant, on #1, that’s a GoogleFinance issue. The only thing I know to do is enter and update it manually.

Justin says:

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.

Mihir says:

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.

Mark Church says:

Great spreadsheet Rob. I added YTD return to mine using google finance feature. Thanks for sharing.

Rob Berger says:

Mark, nice addition. I’ll add it to the spreadsheet as well.

Mike says:

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…

Mike says:

Works now. Guess it needed to reload.

Grant says:

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

Mike says:

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.

Justin says:

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.

Neo says:

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.

Justin says:

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

Brett says:

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?

Rob Berger says:

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.

Dan says:

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.

Ben says:

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.

Justin says:

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.

Mike says:

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

Justin says:

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.

Mike says:

Thanks Justin…….I thought about decomposing, but it gets fairly complex. I’ll just manually update.

Richard says:

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.

Richard says:

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.

Mike says:

Interesting. Thanks Richard, I’ll try that.

Ryan says:

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?

Hoppy says:

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

Matt says:

Can I change the categories of the assets classes? The included ones don’t really fit with my asset allocation.

Matt says:

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.

Rob Berger says:

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

Steve says:

“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!

Steve says:

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

Greg says:

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!

Evelyn says:

The spreadsheet seems to require permission to access.
I sent a request for access – thanks in advance. Appreciate the help.

Rob Berger says:

Evelyn, make a copy of the spreadsheet and then you’ll be able to edit it.

Evelyn says:

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.

Evelyn says:

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

Christian says:

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

Rob Berger says:

Christian, thanks for the suggestion. I’ll look into it.

BruceT says:

Great Spread sheet. Any way another tab for stock option trades could be added. Maybe others have a spreadsheet they use for option trades.

Chad says:

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?

Peter says:

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.

Stephanie Colestock says:

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

Peter says:

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)

Jordana says:

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!

Jordana says:

Never mind, I figured it out! Data –> Named Ranges allows you to change the values of the “Holding Value”. Fixed!

Peter says:

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?

Stephanie Colestock says:

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

Peter says:

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!

Kyla says:

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

Aji says:

Hi, once a stock in the holding is sold, is it saved somewhere or is deleted from the sheet ?

Rob Berger says:

Aji, that’s up to you. You can delete it or move it to a new page you create with stocks you’ve sold.

Cris says:

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!

Paul D says:

Stupid newbie question. How do you save your changes? I try but when I reload the spreadsheet is is as original.
Sorry!

Stephanie Colestock says:

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

Mark says:

Great spreadsheet. The prices are updating on my pc but not my phone. Am I missing something?

Rick says:

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!

Jeff says:

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!

Clay says:

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.

Alex Kistler says:

Thanks for the spreadsheet. I am not used to using these and can not figure out a way to add rows that function.

Benjamin Hutterer says:

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.

J John Lynch says:

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.

gale hamilton says:

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.

J John Lynch says:

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.

RickC says:

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

Rick says:

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

Paul Harsley says:

How do I account for sold funds/assets using this spreadsheet? Or once sold do I just delete them?

Rob Berger says:

I delete them, but it’s really up to you.

William says:

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?

Rob Berger says:

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.

A.J. says:

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

Rob Berger says:

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?

david says:

yes I too am also only seeing the second page….

Gary says:

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.

Jon says:

Thanks so much! I just found this and it’s wonderful. Yay!

KimS says:

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 ­čÖé

yiling says:

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?

DD says:

Thank you so much
What is alternative investment on the spreadsheet?

Randall Petty says:

Can you download the spreadsheet and work with it on your computer/laptop, or will the functions not work outside of Google Sheets?

Pam says:

Thank you for this. How would I enter my canadian corporate bonds, and Canadian Stocks?

john says:

POS dont try to use. They immediately force you to arrange a meeting with one of their hounds within 14 days. scam.

Leo Jacob says:

i agree back to excel… dont have to give google anything more

Pam says:

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.

MickinMD says:

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.

Richard says:

The Personal Capital links won’t open in Chrome. Just says “Not Secure”

Danniel says:

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

Angelique says:

Are there any updates for this spreadsheet? There are a lot of #N/A’s and google can’t pull money fund information.

NancyN says:

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.

Mike says:

This is totally awesome!!! I love this!! It’s so nice having the stocks automatically update to their share prices. Thanks Dan!

Pam says:

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!