For a while, I've been trying to track my ETFs across multiple tools. At the very beginning, simply using the ones provided by the brokerage accounts, but eventually decided to get a simple and "easy to code" system. For sure, not many things better than Google Sheets.
To summarize it all, the following spreadsheet will allow you to have an idea of how your ETF underlying holdings are distributed across your multiple ETF units when these are all combined. It will be like a "dashboard", in terms of holdings, of how your money is distributed across the different holdings once these are all combined.
Having this "stock dashboard" will be very useful for:
Understanding your underlying holdings.
Identifying high-exposure to potentially "unwanted" stocks.
Estimate your next purchasing steps.
Google Finance Formulas
I won't dig much on it, as I'll write a future blog post about how flexible these are, but the Google Finance Formulas will make your life easy. As the simple purpose is to obtain the current value of a stock/ETF ticker, this will do all the job we need:
// To get the current price of a stock/ETF
=GoogleFinance(AAPL,"price")
// To get the Day change compared to the previous day
=GoogleFinance(AAPL,"changepct")
// Alternatively, you can also use this one
=GoogleFinance(AAPL,"change")/GoogleFinance(AAPL,"priceopen")
All formulas work pretty well for tracking both U.S. and international equities, although most international ones are subject to a ~15 minutes delay price update. It works great with bonds, stocks and ETFs.
Sample $100,000 Portfolio with U.S. Equities
For building up the spreadsheet, I used the following asset allocation (of course, feel free to clone the document in your own Google Drive and customize it accordingly to your own asset allocation strategy):
A $100,000 sample portfolio distributed as
~50% U.S. Total Market with iShares ITOT units.
~25% Nasdaq100 with Invesco QQQ units.
~25% ARK Innovation Active ETF with ARKK units.
Modifying the Spreadsheet
One of the first things you will do is setting up your own ETFs and stocks (and probably bonds too). Following, I will outline the different operations you will need to do. Please, take into account that some Google Sheets knowledge is required to do so:
Replacing the sample data
Yes, this will be probably the first thing you'll want to do, otherwise, why having this?!
1 - Replace the tickers with your ETF tickers.
2 - Update the units and the book price from your brokerage account.
By doing these simple steps, your portfolio value will be updated, although your underlying assets will not, YET.
Adding More Ticker Units
1 - Create a new row right under the current table from the Portfolio Dashboard page.
2 - Select a previous working table row and clone all it's behaviours to the new row.
3 - Add, to the Total Equity formula, the new Market Value cell to it is calculated in.
4 - Add, to the Unrealized Gains formula, the new Value Difference cell to it is calculated in.
Updating the Underlying Holdings Calculation
Underlying holding calculation is done automatically, although it requires a manual step, which is gathering the ETF allocation. It is a number that changes every market day, but you would not need to do it on a regular basis (I do it once a month). ETF management companies must update the asset allocation numbers daily, as required by the SEC.
On the other hand, if you were to make an investment decision such as balancing, selling or acquiring more assets, based on the current portfolio "snapshot", I would recommend updating all allocations prior to do so.
1 - Clone an existing ETF Page.
2 - Replace the existing "descriptive fields" and the page name with your ETF. Replace the ETF description link with one of your ETF. All info such as units, book price and so will be taken from the Portfolio Dashboard page, so you don't need to update it.
3 - As a sample one, I will assume you'll be adding iShares IVV S&P500 ETF. The official fund page is https://www.ishares.com/us/products/239726/ishares-core-sp-500-etf It will end up as follows:
4 - Now it is the moment to update the underlying holdings. Simply go to the iShares (or your respective ETF) fund page and copy/paste, preferably using a temporary table, the tickers and the weight you are interested in tracking. I use to take up to the top 20 of each fund.
5 - Update the Ticker & the % Portfolio fields on your ETF page.
6 - Make sure the "All other" field includes all rows into the calculation. It is important that non-weighted assets are taken into the math too.
Good Luck with it!
Well, that's it. Just wish it helps you improve your decision-making strategy when it comes to allocation as much as it has helped me over the past few months. If you have any questions, feel welcome to comment. I will be making continuous upgrades in the coming weeks.
Do you Need Help Setting this Up?
If you need help setting up the Google Sheet, you can book a call with me. I've helped lots of investors track their expenses by setting up their sheets, which result in a great time-saving opportunity and portfolio tracking dashboard across multiple accounts.
New To Investing?
If you are in Canada and are looking for ways to grow your wealth, you should start by opening an account at Questrade Inc. and begin investing your money. It is the #1, most prestigious and most affordable brokerage in Canada. By following this link, you will get a cash bonus, up to $250, depending on the amount you deposit when you open/transfer your TFSA, RRSP, RESP or brokerage account.
