An Awesome (and Free) Investment Tracking Spreadsheet

Awesome Free Investment Tracking SpreadsheetThere 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.

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!

Published or Updated: May 26, 2015
About Rob Berger

Rob founded the Dough Roller in 2007. A litigation attorney in the securities industry, he lives in Northern Virginia with his wife, their two teenagers, and the family mascot, a shih tzu named Sophie.

Comments

  1. 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?

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

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

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

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

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

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

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

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

  9. 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?

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

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

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

  13. 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!

Speak Your Mind

*