How To See Your Allocation of Stocks with an Index Fund

Index Funds / ETFs are one of the best innovations in the history of finance. They are far and away the best investing option for most investors – even professionals.

But. They are boring. That’s both a feature and a bug. It’s a feature because investing capital should be a boring, long-term exercise. It’s a bug because everyone wants novelty and excitement.

And in a world of speculation FOMO on stocks, crypto, real estate, etc – it can be hard to just keep doing the boring thing.

The ironic piece of index fund / ETF FOMO is that if you own an index fund…you own all the individual stocks inside the index fund.

For example, VTSAX / VTI owns all the stocks in the US. VTIAX / VXUS owns all the stocks outside the US. And VTWAX / VT owns all the stocks in the whole world.

And yes, the point of an index fund is to own all the stocks. That’s probably obvious if you own one. It’s why you likely bought it. But it’s also easy to forget that you own all these individual stocks when random person on TV or Twitter is chatting up their Tesla stock purchase or talking about the next big thing*.

*And sometimes, it’s not obvious. For example, it’s easy to forget that America’s largest real estate investment firms…are all publicly traded and included in a Total Market Index Fund.

So here’s how to find out how much of each stock you own in your index fund.

Tools & Assumption

• You own a Vanguard Index Fund or ETF. They are the inventor, the world’s largest, and the one I use. The process is generally the same for other providers like iShares, etc.
• You have access to Google Sheets. Knowing formulas is a bonus, but not necessary.
• If Google Sheets sounds daunting, try using HoneyMoney’s Stocked tool. It uses data from 2019, but automagically does the same thing.

Step 1. Find Your Fund at Vanguard Financial Advisors

Vanguard’s personal investor site provides good information about their index funds and ETFs. But you have to go to their Financial Advisors website to get information on all their funds – and to get CSV spreadsheet exports.

Check out Vanguard’s Financial Advisors’ site here. For other index funds, you’ll have to track down their Export Holdings buttons on their site.

Step 2. Export Full Holdings to CSV File

Once you’ve found your fund, go to the Holdings section.

Then, Export Full Holdings.

Step 3. Import The File to Google Sheets

Open up Google Sheets, go to File –> Import. Choose the CSV and import it. You’ll see a spreadsheet like this.

Add a column called “My Allocation” or something starting on the same row as the other labels.

Then somewhere on the spreadsheet (I’ll use H1 in the example), type in or reference how much you own of the index fund.

Then, in the first cell under My Allocation, multiply the value under % of Funds by how much you own. The formula will be something like this (make sure you lock your reference cell) –

=$h$1*b4

Then fill down.

Step 4. Explore Your Holdings

With this setup, it’s a lot easier to see exactly what you own…by owning the index fund.

Step 5. Tidy Up & Explore More

There’s one catch on this method with index funds that own *all* of a market (like VTSAX, VTIAX, or VTWAX).

That is that there are *a lot* – like thousands – of companies where a company takes up <0.01% of the fund.

To fix this, you’ll have to ballpark replace those cells. I manually replace half those cells with 0.005%, a quarter with 0.0025%, and a quarter with 0.0010%. Those aren’t technically correct, but they do get my holdings to generally match up.

Once you’ve tidied up, there’s a lot more exploring you can do with the SUMIF formula. Some ideas that I check are – how much of a country do I own? and how much of a category (like REITs) do I own?

You can generally spend way too much time looking at this. After all, the point of an index fund is to be boring.

However, curiosity is one powerful cure for FOMO – as is appreciating & being grateful for what you already own (doubly more so if you are in the minority to have investments in a 401(k) or IRA).

Next time you see ads for a crypto marketplace, house flippers or some day trading brokerage, pull up your 401(k), download your holdings and be surprised at what you already own in your boring index fund.