Simplified Holdings Assessor

Spreadsheets gSheets Templates
1

Simplified Holdings Assessor: where is my money actually invested?

Built on Tiller Investments Beta: Holdings and Holdings History.

Sample outputs [views 1 and 2 shown below]:

Picture1
Picture1940×260 62.5 KB

Picture2
Picture2940×388 58.4 KB

The purpose – simplified views of portfolio allocation

Tiller’s investment beta now pulls every holding from every investment account into one place. What it doesn’t show is how much of that money is in stocks, bonds, or cash when you add it all up across your entire portfolio. That mix is what sets your risk, and diverges over time from your portfolio target allocations. It’s easy to lose track when your money is spread across a 401(k), an IRA, a brokerage account, and funds that hold other funds.

This tab presents the same data in a simple, graphical way, so you can see your money from a different perspective: not by account or by fund name, but by what it is actually invested in. Every holding is placed in one of eight asset groups (US stocks, international stocks, US bonds, international bonds, real estate, commodities, cash, options), including major pieces inside a target-date (composite) fund.

New to stocks versus bonds? The SEC’s short guide covers it:

The four key questions this visualizes for you

1] What am I actually invested in?
Most people know their fund names but not what those funds hold, so this section answers that in dollars and as a portion of the entire portfolio. The table and pie show how your entire portfolio is allocated across the eight asset groups, with target-date funds split into their parts. If your stock share is higher or lower than you expected, that may be unknown risk. Add allocation targets per group and the sheet identifies where you are out of balance.

2] Where is my money?
Your overall mix is the sum of several accounts that may each look very different. One row per account shows that account’s allocation across the same asset groups, with a stacked bar beside it. Seeing that your HSA is all stocks while your 401(k) holds all your bonds tells you which account to adjust, and which to leave alone, when you want to change your mix.

3] Your biggest holdings
Concentration is a risk you cannot see one account at a time. This section adds up your fifteen largest positions across all accounts, labels each with its asset group, and draws a treemap to visualize the relative size of those holdings. If one fund or one company is a quarter of everything you own, this is how you quickly find out, and verify that against your expectations.

4] How has it changed over time?
Your portfolio allocation drifts over time as markets move and as contributions land in one account more than another. The chart shows your allocation across the asset groups by day, from Tiller’s Holdings History. A stock share that has crept up ten points over a year may be a signal to rebalance. This historical view allows you to easily track this over time.

Also on the tab: how each account is taxed, a short list of anything the sheet could not categorize, a data check, and a search box over every holding. Something you can build into to determine tax advantaged opportunities in the future.

Installation

  1. Requires: Google Sheets + Investments (Beta) in Tiller Money Feeds, with Holdings and Holdings History added and filled with sample data.
  2. Open the template link, then File, Make a copy. (or use the direct copy link)
  3. In your copy, right-click the MM-HoldingsAssessor tab, Copy to  Existing spreadsheet, choose your Tiller sheet or a test sheet to look things over first.
  4. Rename the new tab from “Copy of MM-HoldingsAssessor” to “MM-HoldingsAssessor”.
  5. Open it. If anything appears under “couldn’t classify,” [B13 shows the dollar value of anything identified here] scroll to Fixes (Row 134), type its symbol or name on a row here, and pick an asset group (Columns A-C).

Usage

Teaching it a fund it doesn’t know

About 300 example suggested funds and 95 target-date and balanced funds are pre-loaded. No list covers everything so this will grow over time. In the meantime, you can add your own data for holdings that do not exist in the data set currently. Quick fix: one row in Fixes. Permanent fix: expand the Lookups group (the + above column Z) and add the fund to the Funds list, or to Compositions with its mix. Post a machine-readable copy of data you add so we can verify and include over time.

The Template Link

Simplified Holdings Assessor - Simplified Holdings Assessor - Share - Google Sheets

Direct copy link: https://docs.google.com/spreadsheets/d/1MPksXHfbqybKaP4fOUqn5xotIG0uSPW4dQx8uo7dkyE/copy

Permissions

Permission to copy, use, modify freely.

Notes

Holdings History currently logs every fill, not just changes. The HoldingsAssessor tab collapses this data to one row per holding per day and looks back 365 days by default; this is customizable, but the aggregated data is captured in the tab here for ease of use and computation.

Individual stocks are categorized as US stocks when they have a plain ticker and a company-like name. A foreign an unfamiliar ETF may need a Fixes row to correct assumptions.

Pre-loaded fund mixes are a snapshot. A target-date fund’s mix drifts a little each year. These can be audited over time for correctness and modified in-line.

4 Likes
2

This is really nice. Lots of good info and presented well. I will be keeping this sheet in my main Tiller spreadsheet.

A few things I’ve noticed.

In “Which accounts are taxed how?”, HSA accounts work much like Roth accounts in that taxes have been pre-paid and qualified withdrawals are not taxed. I “fixed” my HSA account to be taxed like a Roth account, but the treatment should be labeled “Tax-free” not “Roth”.

In “Where is my money”, I have five accounts and the default position of the chart obscures part of the list beyond the first four. Of course I fixed it by simply moving the chart. Not sure if it’s possible to automatically position the chart so it does not overlap the account list, especially for anyone with a large number of accounts.

The “Where is my money” table and chart does not include any unresolved holdings, so the account values are underreported until the unresolved holdings are fixed. This might be confusing to someone who leaves some holdings unresolved for a long time because they’re struggling to understand how they should be categorized.

In “All your holdings” at the end of the sheet, the Share value in the first row is formatted as a percentage but the following rows are not. They should be formatted consistently one way or the other. The Value column would look better if it were formatted as dollars.

1 Like
3

@mysterytea Excellent feedback, thanks for testing things out. I’ve made updates in the live copy to account for your suggestions - solid updates!

  1. There is now a new Tax-free category that catches and categorizes HSA specifically.
  2. I struggled with default placement of this also - I’m not set I have a good way to auto update or move around based on extra rows but I’ll continue to see what options exist. In my version i actually just push the graphic off to the right side.
  3. Great call on the missing unresolved in the chart here, I had missed that in my testing. Updated now and I have a new formula in to serve this data and display it in red (matches pie chart now too).
  4. Good catch on incomplete format updates too. Updated.

Thanks very much for sharing all this, I tested a few new edge cases to verify the updated functions work as expected so hopefully all is complete. Very much appreciate you taking the time to provide feedback. Cheers!