Using Google Spreadsheet for ETF investing

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.

index portfolio tracking & rebalancing spreadsheet

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.

Open the spreadsheet linked above then go to File -> Download As -> Microsoft Excel
You’ll get an Excel file.
Next create a new Google Spreadsheet.
Go to File -> Import -> Upload the downloaded excel file
You now have your own version of the spreadsheet

All-In-One ETFs

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. 🙂

Share on:
.

39 thoughts on “Using Google Spreadsheet for ETF investing”

  1. 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

    Reply
  2. 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?

    Reply
  3. 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.

    Reply
    • 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?

      Reply
  4. 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!

    Reply
  5. 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 🙂
    1000+1000+1000+1000 etc.

    Thanks again!
    Cheers

    Reply
  6. 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?
    Cheers
    Alex

    Reply
    • Hi Alex,

      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.

      Reply
  7. 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.

    Reply
    • Hi Neil,

      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.

      Reply
      • 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!

        Reply
        • Hi Justin,

          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.

          Reply
  8. Hi Tawcan

    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! 🙂

    Reply
  9. 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.

    Reply
        • 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. 🙂

          Reply
  10. The spreadsheet is very helpful! I like the Idea of couch potato strategy even though I am heavily invested in dividend paying stocks.

    Reply
  11. Hi Tawcan,

    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!

    Reply

Leave a Comment

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.