A Free Google Sheet for Asset Allocation Tracking

[TrackerDan]

Hello fellow Bogleheads,
I've seen various posts asking for suggestions for Asset Allocation tracking spreadsheets.
I'm excited to share a new Google Sheets spreadsheet I've created called "Portfolio Tracker".

As a long-time lurker and beneficiary of the wealth of knowledge shared here, I wanted to contribute something that I've found personally useful.

What is "Portfolio Tracker"?
Portfolio Tracker is a free Google Sheet designed for asset allocation tracking and simplified rebalancing.
A popular option out there is one that Rob Berger (formerly of DoughRoller) distributes.
I am a big fan of Rob and his spreadsheet... I should be since I created the original version and sent it to him over 10 years ago!

This new version has been completely rebuilt from scratch and includes a lot of great visualizations.

Key Features:

• Interactive Dashboard: Visualize your asset allocation at a glance and gain valuable insights into your portfolio's composition.
• Near Real-Time Quotes: Stay up-to-date with market data (delayed up to 20 minutes).
• Organized Table Views: Pre-defined views help you quickly analyze your holdings from different perspectives.
• Collapsible Column Groups: Show or hide detailed information as needed, keeping your view clean and focused.
• Multiple Classes / Asset: Track allocation of funds/assets having more than one asset class (e.g. Target Date Funds).

How to get it:
The best way to get the latest version is to visit the Releases page and click the "Make a copy" link.
I have included links to 2.0.1 below:

• Make a copy
• Documentation

Visit my GitHub Repository to stay up-to-date.

Feedback:
I welcome any feedback, suggestions, or bug reports.

I hope you find "Portfolio Tracker" useful!



Thank you,
Dan

-----------------
Update 3/9/2025: Updated links to v2.0.1

[LadyGeek]

Welcome! TrackerDan has received permission to post about his spreadsheet. See: No Solicitation

Authors, software developers, or others looking for feedback should contact [email protected] for approval before posting.

[alpenglow]

Thanks for posting this. I've been using my own spreadsheet for 15 or so years. I'll probably stick with mine because it meets my needs, but yours is much nicer to look at. I have a thing or two to learn about Google sheets style.

[b1gdog83]

Thanks for sharing !

[Raspberry-503]

Rob Berger updated his spreadsheet and just posted on YouTube today, yours is prettier and maybe easier to set up, but to offer an alternative/"competition": about as an alternative: https://www.youtube.com/watch?v=tuIq6t6hdrU

I only took a cursory look but it looks like your spreadsheet makes the assumption all asset classes should eb balanced in all accounts (not doing Asset Location?)

[TrackerDan]

I only took a cursory look but it looks like your spreadsheet makes the assumption all asset classes should eb balanced in all accounts (not doing Asset Location?)

Thanks for taking a look! The spreadsheet does not assume that asset classes should be balanced in all accounts.
In the "Holdings" tab, there is a column group above the "Value" column that you can expand.
This will expose additional columns allowing you to set target allocations on a per account basis.

Note: This is easiest to see when you also do a "Group By Account" table view.

Once this is configured, the "After Rebalance" bar in the chart on the "Allocation" tab will show you a preview of what your new overall allocation will be if you rebalance your account holdings back to these targets.

I am a big believer in asset location as well (thanks to these forums). For example, I have no bond funds in my Roth accounts, and higher bond targets in my tax deferred accounts.

I am working to update the documentation as fast as I can, and will try to make this clearer. With a full time job, and 3 kids, and all their activities it is sometimes a challenge to find the time

I wish Rob the best. He has given me a wealth of knowledge over the years as well.

[bhtomj]

Pretty slick. I will try it out over the weekend.

[filbert]

Great!! Thanks

[renegade06]

Thanks for sharing this! I started playing with it last night. VXUS ticker symbol doesn't show up, and then I seem to get an error message. VTI works just fine.

[bynomial]

Thanks, I just watched Rob's video yesterday, so your timing is perfect.

I like the inclusion of 'sparkline' graphs in the holdings tab. Well done

[TrackerDan]

Thanks for sharing this! I started playing with it last night. VXUS ticker symbol doesn't show up, and then I seem to get an error message. VTI works just fine.

Hi renegade, I just tested VXUS and it worked for me.
I suspect you may be entering VXUS in the holding tab directly.

The assets (i.e. funds) are first entered on the "Asset Setup" tab. This allows you to set them up once.
Note: this is good for cases where you have to manually override info and have the fund in multiple accounts.

Example: I have SPAXX in several of my accounts. The Google Finance function is not able to pull this info in for this fund.
I create one entry on the "Asset Setup" tab and manually override the Name, Price and Expense Ratio.

I can then select SPAXX on the holdings tab for each of my accounts and the info is pulled in.
Hope this helps!

[Harry Livermore]

Hi Dan, thanks for sharing! Very slick and well-constructed.
I'll continue to use my own Excel sheet for a more comprehensive, personalized "snapshot" (as I call my sheet) for my spouse and I.
But yours is, for me, an ideal replacement for Vanguard's perpetually broken "Portfolio Watch".
Thanks again.
Cheers

[Gadget]

This is pretty slick. It is much prettier and setup better than mine.

The only thing it is missing that I have in mine is a net worth tracker. I have scripts that will log the total account value and net worth of each category when I hit a record button. Then track a chart.

[folkher0]

Thank you. I've been using/adapting your original spreadsheet for years. Great update.

[renegade06]

Thanks for sharing this! I started playing with it last night. VXUS ticker symbol doesn't show up, and then I seem to get an error message. VTI works just fine.

Hi renegade, I just tested VXUS and it worked for me.
I suspect you may be entering VXUS in the holding tab directly.

The assets (i.e. funds) are first entered on the "Asset Setup" tab. This allows you to set them up once.
Note: this is good for cases where you have to manually override info and have the fund in multiple accounts.

Example: I have SPAXX in several of my accounts. The Google Finance function is not able to pull this info in for this fund.
I create one entry on the "Asset Setup" tab and manually override the Name, Price and Expense Ratio.

I can then select SPAXX on the holdings tab for each of my accounts and the info is pulled in.
Hope this helps!

Thanks!

[Gadget]

I have a likely dumb question... How do I delete rows if I have way fewer rows, without killing the pretty formatting of your tables? It seems to go haywire if I delete a row.

[TrackerDan]

I have a likely dumb question... How do I delete rows if I have way fewer rows, without killing the pretty formatting of your tables? It seems to go haywire if I delete a row.

Google Sheets has all sorts of "quirks" and this is one of them .

If you right click inside a table there will be an option to "Delete table row".
This will delete the row without affecting anything around it.

Unfortunately, I think this can only be done one row at a time.

[techbud]

Great spreadsheet; thanks @trackerdan!
A question: Is there any alert or validation checking if you've mis-entered the asset class percentages and they don't add up to 100% Eg, suppose I am entering FIOFX per your example, which is made up of five asset classes. If these don't add up to 100%, will there be any indicator or warning in the sheet?

[Gadget]

I have a likely dumb question... How do I delete rows if I have way fewer rows, without killing the pretty formatting of your tables? It seems to go haywire if I delete a row.

Google Sheets has all sorts of "quirks" and this is one of them .

If you right click inside a table there will be an option to "Delete table row".
This will delete the row without affecting anything around it.

Unfortunately, I think this can only be done one row at a time.

That fixed my issue. I was trying to delete more than one at a time. Weird quirk in sheets for that.

[aj76er]

Very nice spreadsheet. Thanks for sharing.

I have my own spreadsheet that I've been maintaining for over a decade that I plan to keep using. But it is great to see how others organize things. I especially like how you made use of Table elements, which allows for programmatic access of data.

In my own spreadsheet I have a couple of nice features that you may want to implement:

• In my "Rebalance Summary", I include the absolute dollar amount that I must buy/sell to bring an asset back to policy target. With this information, it is easy to place trades; I only need to decide in which account(s) to make the trades.

• I have a category for "sweep cash" that is separate from regular cash on hand. I guess you could account for this in your "Asset Setup" table, but it's nice to have it represented as a "special" asset in the rebalancing table (e.g. it only goes one way - to buy other assets). It's useful to quickly see when the sweep cash has built up and needs to be invested.

• I have the ability to model large deposits/withdrawals for "what if" scenarios on how the rebalancing would look. For example, entering the amount for a car purchase and then seeing which assets could be sold.

• I have a "Net Worth Summary" that includes things like Real Estate, debt (Mortgage), etc. I have two primary views of Net Worth: (1) By Accounts and (2) By Assets. Sometimes it's nice to see how the money is spread across accounts (in addition to the asset pie chart).

Other ideas:

• Include expense ratios of all the assets and a total E/R in the Dashboard (for the whole portfolio).

• Include an estimate of yield (per ticker), including a total Portfolio Yield in the Dashboard.

• Estimates for tax-adjusted value of each account. For example the actual, after-tax value of a 401k will be a bit lower and will affect the asset allocation. Have options for switching this on/off, and for adjusting the Federal/State tax rates.

[Steady59]

Deleted

[TrackerDan]

In my own spreadsheet I have a couple of nice features that you may want to implement:

• In my "Rebalance Summary", I include the absolute dollar amount that I must buy/sell to bring an asset back to policy target. With this information, it is easy to place trades; I only need to decide in which account(s) to make the trades.

• I have a category for "sweep cash" that is separate from regular cash on hand. I guess you could account for this in your "Asset Setup" table, but it's nice to have it represented as a "special" asset in the rebalancing table (e.g. it only goes one way - to buy other assets). It's useful to quickly see when the sweep cash has built up and needs to be invested.

• I have the ability to model large deposits/withdrawals for "what if" scenarios on how the rebalancing would look. For example, entering the amount for a car purchase and then seeing which assets could be sold.

• I have a "Net Worth Summary" that includes things like Real Estate, debt (Mortgage), etc. I have two primary views of Net Worth: (1) By Accounts and (2) By Assets. Sometimes it's nice to see how the money is spread across accounts (in addition to the asset pie chart).

Other ideas:

• Include expense ratios of all the assets and a total E/R in the Dashboard (for the whole portfolio).

• Include an estimate of yield (per ticker), including a total Portfolio Yield in the Dashboard.

• Estimates for tax-adjusted value of each account. For example the actual, after-tax value of a 401k will be a bit lower and will affect the asset allocation. Have options for switching this on/off, and for adjusting the Federal/State tax rates.

Thanks for the suggestions @aj76er!
I am compiling a list of suggestions for v2.1, and I will be sure to include these.
(Some of these I actually already thought of... great minds!)

My goal is to add value, while keeping it as simple to understand as I can.
I will be sure to announce new versions here as I add them

[TrackerDan]

I want to thank everyone for the excellent feedback I have received so far!

One common cause of confusion was the "After Rebalance" value in the holdings tab.
I have just updated the main post with links to a v2.0.1 that I hope will minimize confusion.

The "Target Percentages" on the holding tabs, will now default to the "Actual Percentage".
What this means:

• The "After Rebalance" value on the "Target vs Actual" chart will now show no change by default.

I felt this was better than wildly-inaccurate values people were seeing as they updated their holdings.

Other Fixes:
One other issue I happened to notice was that the "After Rebalance" value was not properly handling assets with multiple asset classes.

If you have assets with multiple asset classes, I would suggest upgrading by making a new copy (See link in main post) to make sure your calculations are accurate.

[Harry Livermore]

Dan, I'm not sure if you can reproduce this. But I changed the names of some asset classes in the Asset Setup tab, and it seems to have messed with the Tree Map on the Dashboard page. It has an exclamation point message "Column Parent ID Must Be Text". Additionally, if I double-click to edit the chart, the "Parent" pulldown, set to "Parent ID", is highlighted in red and is listed as "Invalid Type".
It is still set to reference the locked "Allocation Chart Data" tab.
Of course, I had also entered lots of data prior to noticing this. So I may have messed with something else. But I'm pretty sure the only "changes" I made were in renaming "U.S. Bonds", to "U. S. Treasuries", for example.
I have good Excel knowledge but am in the dark generally on Sheets.
I guess I could start fresh and go slowly, and see when the chart breaks. Then I might have more info for you.
Cheers

[Cletus Davenport]

Wow! Very slick..... Nice job!

And thanks to this, I discovered "Smart Chips" today, so thanks for that.

Like many others, I've got my own sheet, but I think I'm going to switch over to yours. Thanks!

[bynomial]

I'm not sure if you can reproduce this. But I changed the names of some asset classes in the Asset Setup tab, and it seems to have messed with the Tree Map on the Dashboard page. It has an exclamation point message "Column Parent ID Must Be Text". Additionally, if I double-click to edit the chart, the "Parent" pulldown, set to "Parent ID", is highlighted in red and is listed as "Invalid Type".

I did the something similar and got the same message, I have not had the time to dig into it - but you are not alone...

[DoubleDraw]

Very cool and useful sheet, thanks for all the time and work put into it!

Do you have a historical view on your roadmap? One thing I track in my own personal spreadsheet (not nearly as pretty or as good for a current view!) is how my asset allocation and account values have trended over time.

Thanks again!

[Gadget]

The only thing missing from your spreadsheet that I track on mine is net worth tracking. I press a button and it records each category to a column, with the date. That way I can have a historical net worth log and graph.

Since I like to track net worth, it would also be nice if you had a way to exclude an asset from target calculations. For instance, you can't rebalance real estate. So I like to see my real estate totals on the sheet, but for Asset allocations it ignores the real estate. If there was a toggle to exclude Asset classes from target allocation calculations it'd be awesome.

[musicislife]

Very cool, thanks for sharing!
Playing with this now, couple of questions.

1. The tickers for Vanguard's Money Market Funds (VMFXX or VMRXX) don't seem to be recognized by the Google sheets formula. Any clues on why? Trying to minimize manual data entry of price and fund name in the Asset Setup tab.

2. Any tips on how to handle funky funds like "State Street Target Retirement 2035" which is what I have in my 401K plan? The ticker is marked as SSTC35 but that doesn't get picked up.

Thanks again, this looks like a worthy substitute for Empower's buggy dashboard for aggregation, which I finally got annoyed enough with to try and wean myself off of...

[jjj_22]

2. Any tips on how to handle funky funds like "State Street Target Retirement 2035" which is what I have in my 401K plan? The ticker is marked as SSTC35 but that doesn't get picked up.

If it's in your 401k plan it could be a collective investment trust (CIT) that isn't traded. But it almost certainly mirrors the publicly traded version of the fund which google says is SSCKX, so try that instead. The price might be different, in which case just normalize the price to get at the right total value and it should track.

[Harry Livermore]

Hi Dan, I had a quick minute to experiment and grabbed a fresh copy of 20.0 to test. In the Asset Setup tab, I changed "U.S. Small Cap" to "U.S. Large Cap", making no other changes, and sure enough, the Tree Map on the Dashboard tab was blank, with an error message saying couldn't find U.S. Small Cap.
Of course, you might have built the sheet with the intention that asset types would be edited elsewhere in the sheet, but that's the only place that made sense to me.
Just FYI.
Thanks again. Seems like a potentially useful tool, especially as a replacement (for me, anyway) of Vanguard's broken Portfolio Watch.
Cheers

[Hot Sauce]

I think it would be helpful to have a dashboard that starts at the most important level of AA — % equities vs % bonds vs % “cash”. You could then have a breakdown for sub-categories such as %US vs %International within equities, % TIPS vs %nominal within fixed income, and so on. But as it stands, the reader would have to do some hand calcs to determine their most basic AA from this dashboard.

[TrackerDan]

Hi Dan, I had a quick minute to experiment and grabbed a fresh copy of 20.0 to test. In the Asset Setup tab, I changed "U.S. Small Cap" to "U.S. Large Cap", making no other changes, and sure enough, the Tree Map on the Dashboard tab was blank, with an error message saying couldn't find U.S. Small Cap.
Of course, you might have built the sheet with the intention that asset types would be edited elsewhere in the sheet, but that's the only place that made sense to me.
Just FYI.
Thanks again. Seems like a potentially useful tool, especially as a replacement (for me, anyway) of Vanguard's broken Portfolio Watch.
Cheers

Good troubleshooting Harry,
The reason it is breaking is because you now have assets (FSSNX, VVSMAX) in the Assets table that are assigned a class of "U.S. Small Cap" (which is now invalid since you renamed it). This, as you noted, breaks the treemap.

All assets must have valid classes for the treemap to work.

Hope this helps!

[TrackerDan]

I think it would be helpful to have a dashboard that starts at the most important level of AA — % equities vs % bonds vs % “cash”. You could then have a breakdown for sub-categories such as %US vs %International within equities, % TIPS vs %nominal within fixed income, and so on. But as it stands, the reader would have to do some hand calcs to determine their most basic AA from this dashboard.

Hello Hot Sauce, thanks for the suggestion.

Right now the tree map has "3 levels" of categorization (Class Category, Asset Classes, and Individual Assets).
That being said, the class categories are completely customizable.

For your scenario, one option would be to just have three class categories:

• Equities
• Bonds
• Cash

I am currently compiling a rather large list of enhancements, and I will add your suggestion, but I cannot promise if or when it might make it in.
Once things settle down, I may set up the ability to vote on features to help me prioritize them.
Dan

[TrackerDan]

1. The tickers for Vanguard's Money Market Funds (VMFXX or VMRXX) don't seem to be recognized by the Google sheets formula. Any clues on why? Trying to minimize manual data entry of price and fund name in the Asset Setup tab.

The GoogleFinance Function that I am using does not have all possible tickers, so sometimes manual entry is needed.
The Asset Setup tab is the place for this, because you only have to do this once!

In the Holdings tab, when you select your asset it will pull in this info. This is especially handy if you own it in multiple accounts.

Hope this helps!

[aristotelian]

Excellent resource. 10/10, no notes!

[Harry Livermore]

Since I had mucked around with the setup of the Tree Map, I figured there was no fixing my original sheet, so I started fresh and re-entered everything, being careful to double-check asset classes,etc. and everything now functions perfectly. Thanks again!
Cheers

[LadyGeek]

TrackerDan's spreadsheet is now in the wiki. See Using a spreadsheet to maintain a portfolio (Sample spreadsheets) and Tools and calculators (Forum member contributions).

The wiki is a collaborative effort. grabiner contributed the first entry.

[musicislife]

2. Any tips on how to handle funky funds like "State Street Target Retirement 2035" which is what I have in my 401K plan? The ticker is marked as SSTC35 but that doesn't get picked up.

If it's in your 401k plan it could be a collective investment trust (CIT) that isn't traded. But it almost certainly mirrors the publicly traded version of the fund which google says is SSCKX, so try that instead. The price might be different, in which case just normalize the price to get at the right total value and it should track.

Great tip, thank you! Yes, the price is different so I changed the formula to include a multiplier = (P1/P2). Might have to track P1 and P2 if it is constant.

[musicislife]

The GoogleFinance Function that I am using does not have all possible tickers, so sometimes manual entry is needed.
The Asset Setup tab is the place for this, because you only have to do this once!

In the Holdings tab, when you select your asset it will pull in this info. This is especially handy if you own it in multiple accounts.

Hope this helps!

Thanks, appreciate the clarification.

[CatsAreCute]

Another feature request....

Since I already all the different custodians, it would be great if on the dashboard there was a total by custodian (e.g. $## at Fidelity, $## at Schwab, $ ## at Treasury Direct). It would make it easy to quickly verify balances on a weekend that all share quantities are correct.

[TrackerDan]

Another feature request....

Since I already all the different custodians, it would be great if on the dashboard there was a total by custodian (e.g. $## at Fidelity, $## at Schwab, $ ## at Treasury Direct). It would make it easy to quickly verify balances on a weekend that all share quantities are correct.

I have good news... I am already working on an "Account Dashboard" for v2.1.0 that will allow you to group by "Institutions" (i.e. custodians) among other groupings

[coyote2]

I went looking for a spreadsheet that calculates rebalancing by ticker (I've already chosen ticker percentages to achieve the asset class allocation I want). I wanted to use Portfolio Tracker 2.0.1; it's a thing of beauty, it's wonderfully powerful. But all I really need I don't see that it has:

My asset allocation is percentages of total assets of various tickers symbols. I want columns for each ticker for how much in US$ I hold now across custodians, how much I would hold once my rebalance is achieved, and how much thus to buy or sell of each. I would also like to be able to enter an amount in US$ I want to add or remove before rebalancing. Am I right that I should modify one of the simple rebalance worksheets out there to do what I need, or do I not see that Portfolio Tracker 2.0.1 already do what I want?

[musicislife]

Another feature request....

Since I already all the different custodians, it would be great if on the dashboard there was a total by custodian (e.g. $## at Fidelity, $## at Schwab, $ ## at Treasury Direct). It would make it easy to quickly verify balances on a weekend that all share quantities are correct.

I have good news... I am already working on an "Account Dashboard" for v2.1.0 that will allow you to group by "Institutions" (i.e. custodians) among other groupings

This would be a great enhancement, thanks! One more request to consider but it might be difficult. Some kind of an "import" feature to load the data we've already entered into a particular version to the next would be nice. Otherwise, I'm visualizing copying a bunch of rows from one version to the next, which might result in more errors. Thanks for this great tool!

[DesertInvestor]

The only thing missing from your spreadsheet that I track on mine is net worth tracking. I press a button and it records each category to a column, with the date. That way I can have a historical net worth log and graph.

Since I like to track net worth, it would also be nice if you had a way to exclude an asset from target calculations. For instance, you can't rebalance real estate. So I like to see my real estate totals on the sheet, but for Asset allocations it ignores the real estate. If there was a toggle to exclude Asset classes from target allocation calculations it'd be awesome.

What i do is use these spreadsheets for liquid asset allocation and net worth I use personal capital in addition. To get an idea of the big picture and update any real estate equity on there. I find the two of the together to be helpful.

[POINT3R]

I have another feature request.

On the Dashboard tab- can you have a sum of daily change % - dollar amount?

On the Holding tab have the dailty change on each holding

Its a great improvement, thanks for sharing

RW

[aristotelian]

I have another feature request.

On the Dashboard tab- can you have a sum of daily change % - dollar amount?

On the Holding tab have the dailty change on each holding

Its a great improvement, thanks for sharing

RW

Daily change is there if you expand the hidden columns

[SpookyEng]

Thank you for your work on this sheet. It is simple to use and I love the dashboard. I use Rob’s tracker as well but I prefer this one. Many of my current assets are not tracked, (TSP, SPAXX, I bonds, CDs) but the manual entry was easy to do.

[aelfa]

Awesome! Thank you! When you do an update, we'll need to copy the sheet and populate it again, I presume?

[POINT3R]

I have another feature request.

On the Dashboard tab- can you have a sum of daily change % - dollar amount?

On the Holding tab have the dailty change on each holding

Its a great improvement, thanks for sharing

RW

Daily change is there if you expand the hidden columns

-

I expanded the colums on the Holding tab, and there is a lot more information available.

The daily change in total dollar amount - i am not seeing - am i missing it somewhere?