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!

Trending Stories

Topics: Investing

59 Responses to “An Awesome (and Free) Investment Tracking Spreadsheet”

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

    • Rob Berger

      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.

  2. Grant

    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!

    • Justin

      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

      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.

  3. Grant

    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.

      • Justin

        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.

          • 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

  4. Brett

    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

      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.

  5. Justin

    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.

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

      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.

    • Richard

      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

      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.

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

  7. Rob Berger

    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

  8. Steve

    “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

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

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

      • 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

  10. Christian

    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

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

  12. 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
      Stephanie Colestock

      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!

    • Stephanie Colestock
      Stephanie Colestock

      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!

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

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

Leave a Reply