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.

Let me know if you have any questions or if you have any suggestions on how to improve the spreadsheet. 🙂

Get update via email:

You Might Also Like

22 Comments

  • Reply
    Dipu
    February 2, 2016 at 5:46 pm

    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
      Tawcan
      February 3, 2016 at 11:07 pm

      Hi Dipu,

      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

      • Reply
        Dipu
        February 4, 2016 at 9:03 pm

        Thank you sir. I’ll study more into this and also its relationship with interest rate change

  • Reply
    Kathy
    February 3, 2016 at 12:49 am

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

    • Reply
      Tawcan
      February 3, 2016 at 11:08 pm

      Thanks Kathy.

  • Reply
    ambertreeleaves
    February 3, 2016 at 10:16 pm

    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
      Tawcan
      February 3, 2016 at 11:09 pm

      I used to like local spreadsheets but for ease of access I’ve been tracking things using Google Spreadsheet.

      • Reply
        ambertreeleaves
        February 4, 2016 at 3:12 am

        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?

        • Reply
          Tawcan
          February 4, 2016 at 10:54 am

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

          • ambertreeleaves
            February 7, 2016 at 1:12 pm

            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…

          • Tawcan
            February 9, 2016 at 11:39 am

            That’d be scary for sure. Yeah 2 way authentication makes the account much more secure.

  • Reply
    Weekend Reading – Mortgage trends, bonuses, expats, ETF investing and more - My Own Advisor
    February 4, 2016 at 9:46 pm

    […] Tawcan highlighted using Google Spreadsheets for ETF investing. […]

  • Reply
    weenie
    February 6, 2016 at 11:32 am

    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
      Tawcan
      February 6, 2016 at 12:55 pm

      Hi weenie,

      Hope the excel sheet helped. 😀

  • Reply
    Chris
    February 10, 2016 at 9:00 am

    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

    • Reply
      Tawcan
      February 10, 2016 at 11:13 am

      Hi Chris,

      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.

  • Reply
    Justin
    February 20, 2016 at 7:22 am

    I’m also trying to implement something with a dividend and etf split, just curious what sort of split you add?

    • Reply
      Tawcan
      February 21, 2016 at 4:35 pm

      Hi Justin,

      You can find the formula in this post – http://www.tawcan.com/using-google-spreadsheet-dividend-investment/

      Basically did the following where B2 is the ticker symbol.
      =split(ImportXML(concatenate(“http://finance.google.com/finance?q=”,B2), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)

      Hope this helps.

      • Reply
        Justin
        February 22, 2016 at 8:04 am

        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
          Tawcan
          February 22, 2016 at 12:23 pm

          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
    JamesT
    February 6, 2017 at 2:03 pm

    Hello – How are you importing Yield or payout per share for Dividend paying ETFs?

  • Leave a Reply