I’m sharing this new spreadsheet so everyone can have an idea of “the combo” resulting from their ETFs allocations and the underlying holdings. It updates automatically once a day when the ETF holding CSV files are updated. So far, I've only been able to make it work with the public iShares & ARK ETFs holding CSV files, but you can try to reproduce it to use your own hosted CSV downloaded files from Vanguard, Invesco...
Hope you can understand how it is made and replicate it for your own use. It is all set up with sample info and for CAD/USD conversions but, as I said, feel free to customize the formulas for your own needs.
Automatically Loading Holding's CSV File
The main difference between this Google Sheet and the one I previously published a few months ago, is that the holdings are automatically calculated.
// Google Sheets allows loading an online CSV file using the following function:
=IMPORTDATA("Holdings CSV file")
// For example: loading all holdings of the XUU ETF (very popular among Canadians) would be like:
=IMPORTDATA("https://www.blackrock.com/ca/investors/en/products/272104/ishares-core-sp-us-total-market-index-etf/1464253357814.ajax?fileType=csv&fileName=XUU_holdings&dataType=fund")
You can clone the spreadsheet with the following link: https://docs.google.com/spreadsheets/d/1YBIMyJx0pko5x9NXFSLZMJMSkZsN8gee-5H1z36xbEs/edit?usp=sharing
Adding New ETFs
To add a new ETF, follow these steps:
1 - Add the new line on the dashboard page.
2 - Create a new page for loading the CSV file.
3 - Add the Query table to the "Query Pages"
4 - Make sure the new rows are loading on the respective Pivot table.
5 - That should be it.
Add the new line on the dashboard page.
By adding a new row, simply push down the small table that appears on the dashboard page. The idea is that you just define the ticker and the number of units. If somehow it is affected by the USD/CAD exchange, multiply it on the row calculation as it is set on the ARK ETFs of the sample data.
Create a new page for loading the CSV file.
Each sub-page that loads the CSV files does a column calculation of the total assets per ticker. If you need to add a new ETF, make sure to start from here.
Start by cloning any ARK/iShares page and editing the source CSV file URL. Then, make sure that the last column of the table is properly calculating the totals for each TICKER.
Add the Query table to the "Query Pages"
The query() function from Google Sheets allows bringing lots of data from one page to another. It is the one I used for importing all rows into a single page, before doing the final pivot.
// This function brings all rows from "XUU" page, from B33 to R3780 and excludes all empty rows. Then, it does the same with the data set on page XQQ...
=query({XUU!B33:R3780;XQQ!B16:R140},"Select * where Col1 is not null ")
Make sure the new rows are loading on the respective Pivot table
Once this is all done, the Pivot table data range must be updated to include the new data set. By doing this final step, then all the data on the dashboard should be connected all the way back to your data source and will begin to automatically update every day, once the CSV files are updated at the source.
Feel free to copy and use!
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.
