If you have come across my post on Using Google Spreadsheet for dividend investment, you have probably figured out that I’m a big fan of Google Spreadsheet. One of the key benefits of Google Spreadsheet is the ability to update data from anywhere, on a laptop, a PC, a tablet, or on a smartphone, as long as there’s an internet connection. That’s why I have created a number of Google Spreadsheet templates to track my different investments. Today we will look at the index ETF Google spreadsheet template that I created.
Using the Canadian Couch Potato strategy to build a low-cost and well diversified portfolio is extremely easy. It’s a shame that some people still buy mutual funds and fork out the unnecessary fees. Although we are primary focused on dividend growth investing, I actually think a hybrid model of dividend growth investing and passive index ETF investing is a great investment strategy. This is why we plan to add more index funds to our dividend portfolio moving forward to gain more diversification.
What is a Couch Potato investing strategy?
The Couch Potato investing strategy’s goal is to keep costs extremely low by building a diversified portfolio through index ETF’s. ETF’s are getting very popular lately and there are a huge lists of index ETF’s available to pick.
For Baby T1.0’s RESP portfolio, we decided to model it after the recommended ETF portfolio model from Canadian Couch Potato, except that we tweaked the sector allocation slightly to get more exposure to stocks. Because we started this portfolio about 2 years ago, prior to the current recommended TSX listed 3 Vanguard ETF’s, Baby T1.0’s RESP portfolio contains US listed ETF’s like VTI and VXUS.
Tracking & re-balancing the Portfolio
When it comes to passive investing, it is very important to re-balance the portfolio over time to keep the desired sector allocation. Re-balancing is needed due to market movements. For example, the Canadian market can be going down the drain due to low crude oil price, but the US and international markets can be on fire due to strong economy.
Tracking the value of the portfolio is pretty simple by using the build-in GOOGLEFINANCE functions. However re-balancing the portfolio can be tricky due to the fact that we hold US listed ETF’s.
What do you do when it comes to buying more shares to balance the portfolio and taking the US to CAD currency rate into consideration?
Don’t worry, I have created a handy Google Spreadsheet template to help tracking and re-balancing a portfolio based on index ETF’s.
The index ETF Google Spreadsheet template is based on what we picked for Baby T1.0’s RESP portfolio. It consists of VCE, VTI, VXUS, ZRE, and VAB. TSX listed ETF’s are marked as red while US listed ETF’s are marked as teal. You can enter amount of cash available to invest and the spreadsheet will calculate how many shares to purchase. Feel free to change the listed ETF’s and allocation percentage to suite your portfolio.
You can get the spreadsheet here.
Note: Some of you have requested permission to edit this spreadsheet. Instead of grating access and potentially having someone messing up the spreadsheet. You could do the following to create your own spreadsheet.
If you don’t want to re-balance ETFs and want to keep it simple. The All-in-one ETFs may be a good option. You can still utilize the spreadsheet I developed by you wouldn’t need to worry about re-balancing regularly.
Let me know if you have any questions or if you have any suggestions on how to improve the spreadsheet. 🙂
39 thoughts on “Using Google Spreadsheet for ETF investing”
Hi T, Some info for your followers (specially New bee Div. traders) http://www.trackyourdividends.com this is a very good place to basic membership is free with limited resources. Premium cost $9/m or $99/ y with great resources. I personally use basic which give Email reminder when Ex Div. Date is near.
I use TOS (TDA) & streetsmart (schwab) is great to do stock/etfs/bonds etc. at great length. Hope every one will like this with comments here ,FB or email. thanks
I’m looking for a way to input distribution info on my ETFs next to my ETF holdings. Similar to your info on dividends, but it doesn’t seem like google finance or yahoo finance pull ETF distributions in the info. As an example, XFI pays a yearly distribution which shows up on their website here: https://www.ishares.com/us/products/239536/ishares-china-largecap-etf#/ but do you know of a way which I can pull this into a google sheet?
You’d need to figure out the Path on iShares’ site and pull the data directly from there. It can get a bit complicated though.
I would like to know the sector allocation for my portfolio. I currently enter the allocation data manually into excel. Is there a way to download sector allocation for each ETF into the sheet?
No, this needs to be done manually.
Great creative work on the sheet. I really like the investment portfolio tracker and want to use it. One question – how to include the ETF sector exposure (investment categories) data in the final pie chart. For example, I have ETFs/stocks specifically in different investment categories such as infotech, biotech, financials, industrials, healthcare, materials etc. I also have overall stock market ETFs such as VTI, VTWG etc. I want to include % sector exposure from VTI and VTWG into my final investment categories. I can get the % sector exposure information in another tab from the ETF website. I will really appreciate it if you could share a piece of function when generating the final pie chart. Based on the net diverse exposure, I want to rebalance my portfolio in the future.
Hmm that’s a good question. You can categorize the sector specific ETFs under the stock sector rather than put them under the ETF sector. Does that make sense?
Is there a way to identify which ones are ETF’s if you have a long list of tickers? Using Google Sheets
Hmm good question, not really. You as the spreadsheet user should know which tickers are ETF’s.
Any idea how to get current information for certain ETFs? For example, when I input “BWX” in my A column, it inputs the current price for BWX on the ASX (Australian Exchange), yet when I input it as Google has it listed (NYSEARCA:BWX), it shows up as N/A#…any workaround would be great!
You can try enter “NYSE:BWXT” for the stock symbol.
Ahh of course 🙂 I was thinking of maybe just entering the book values on separate lines on another tab and having the E column add those up. After purchasing the same ETF 20 times, the field might become a little cluttered 🙂
Thank you, was looking for something like this! Can you please explain what you need to enter in the “E” Column (Book)? I did my first purchase, filled in the shares and I placed the amount that I paid in Book column (E).
But now when I re-buy the same ETFs, I enter for example 1000$ in Cash, and it shows me how much shares to purchase of each but what do I need to do with the Book value?
You’d put down the additional $ amount you added in the book column and enter the additional shares in the shares column.
For example if you started off with 10 shares and $1000 then added 8 shares and $1000 you’d do.
Share = 10 + 8
Book = 1000 + 1000
Hope this helps.
Hi Tawcan, great stuff here!
I have a question about tracking ‘dividends’ from ETFs. I’ve been getting cash payments from ETFs such as VEE and VDU, and want to be able to see the yearly expected ‘dividend’ for my ETFs just like with other stocks. Any help would be appreciated. Keep up the great work.
ETF dividends are a bit tougher to track since the amount can different for each payment. I typically just use the current dividend yield to estimate the expected dividend amount from ETFs.
Hello – How are you importing Yield or payout per share for Dividend paying ETFs?
Please see here:
I’m also trying to implement something with a dividend and etf split, just curious what sort of split you add?
You can find the formula in this post – https://www.tawcan.com/using-google-spreadsheet-dividend-investment/
Basically did the following where B2 is the ticker symbol.
Hope this helps.
Thanks Tawcan. The formula is incredibly helpful and I have used it. I should have rephrased my question more clearly – on the overall allocation of your portfolio, what percentage split do you allocate to using ETFs versus dividend growth investing?
Your help is appreciated!
Right now I’d say it’s 90% dividend growth investing and 10% ETFs but we’d love to get to a 70-30 or 60-40 split in the future.
Hope this helps.
Any tips on getting the right name in the B column so the Google Finance lookups work? I’m trying to add some TD Index funds like TDB900
I assume you mean column A? Since all the formula comes from column A. Looks like GoogleFinance function is having some difficulties pulling Canadian listed mutual fund. I had no problem pulling info for FCNTX. I’ve tried using post and prefix for TDB900 but none of that work. Let me do some more investigation and see if I can figure it out.
Did you have any luck finding the correct tickers to use for Canadian Mutual Funds?
Thanks – Lony
You can use the “attribute” function to pull mutual fund info.
Well, I continue to learn new stuff all the time as I had no idea that I could use Google Finance to track ETFs in the same way as I could stocks!
Just off to play around with my spreadsheet – thanks very much! 🙂
Hope the excel sheet helped. 😀
Thx for this. My personal couch potato spreadsheet is on a xls on my harddrive, I need to enter prices myself… I will look at your spreadsheet for sure
I do use google spreadsheets to follow up an track my options portfolio and performance. I relly love it.
I used to like local spreadsheets but for ease of access I’ve been tracking things using Google Spreadsheet.
With this first tracksheet in the cloud I start to see the advantages. I just worry a little on Google knowing everything… Or being hacked as it is only pwd protected.
How do you treat this?
That’s definitely a concern, that’s why I use 2 way verification for my Google account. Furthermore, it’s only the portfolio tracking I’m keeping online, nothing related to how to log in to the different accounts. 🙂
Just today I activated the 2 way authentication. Feels safer now… About 3 years ago, I got an email my account was logged in out of South Korea. I was in Brussels that day…
That’d be scary for sure. Yeah 2 way authentication makes the account much more secure.
The spreadsheet is very helpful! I like the Idea of couch potato strategy even though I am heavily invested in dividend paying stocks.
I have XRE for REITS XEF, VXC for global and ZDV, XEI for Canadian exposure. It will be great to know how Bond ETF VAB works. Can you provide some information on this? just like the brief analysis you give for your Recent Buys. Any information would be very helpful. Thank you!
VAB tracks broad Canadian bond index, invests primarily in public, investment-grade fixed income securities issued in Canada. The fee is only 0.26%. Majority of the bond holdings mature in 1-5 years so the index is mostly short term bonds, which I like. Furthermore, over 40% of the bonds in the index have AAA rating and over 90% have A rating or higher. You can find more info here: https://www.vanguardcanada.ca/individual/mvc/detail/etf/overview?portId=9552&assetCode=BOND##overview
Thank you sir. I’ll study more into this and also its relationship with interest rate change