It’s finally here! I’m releasing my TD e-Series Excel Portfolio Tracker! When it comes to software, applications, or even spreadsheets to track your TD e-Series investments, I’ve found there is very little support on the web. I used to use Google Finance to track my portfolio, but they shut down that functionality unfortunately. Over time, I realized that the solution I was looking for to track my portfolio wasn’t out there – so I decided to make my own. After requests from many of you, I’m releasing it for download!
Version update history:
- Version 1.0 – initial release
- Version 1.1 – updated error where Canadian Index Price didn’t update properly from web
- Version 1.2 – fixed error calculating RRSP book values, added value of change of funds in 24 hours, updated error messaging on return values of holdings over time, if user hasn’t held asset for long enough
- Version 1.3 – updated parse error that was causing incorrect data to be copied to the ‘TD e-Series Price List’ sheet when automatically downloading index fund prices
- Version 1.4 – Unhid ‘Data from web’ sheet to fix Macro error, updated parse error that was causing incorrect data to be copied to the ‘TD e-Series Price Lost’ sheet when automatically downloading index fund prices.
- Version 1.5 – Updated macro to detect if there is a parse issue, and remove the extra data from the ‘TD e-Series Price List’ sheet if it starts with incorrect data.
- Version 1.6 – Updated macro to fix price list updating.
What TD e-Series Excel spreadsheet does
The excel spreadsheet I created allows you to track all of your TD e-Series transactions in both your TFSA and RRSP accounts, pull recent fund price data from the web, store information about the value of your portfolio on any given day, and show the return of your portfolio over a given period of time! The spreadsheet uses Macro’s to run a lot of it’s functionality – that’s why it is an .XLSM file extension, and that’s why you’ll need to Enable Content for the spreadsheet to work properly.
It can be a little nerve wracking enabling content on an excel file you download from the internet, but I assure you there is no funny busy with this file – all it does in the background is calculations, and connect to the web to download current fund prices.
Track your transactions
Insert all of your TD e-Series fund portfolio transactions, and the spreadsheet will calculate your total portfolio, value, and return over certain time periods:
And the spreadsheet will calculate the rest (if you’ve downloaded the most recent price data):
Display 1 or 2 accounts
You may have your TD e-Series portfolio stored in a TFSA, an RRSP, or maybe both. Use this option to select which of your accounts that you want to look at:
Download most recent price data from web with 1 click
Click the “Download recent price data” button to get price data from the last 60 days for the following funds:
- TD Canadian Index-e
- TD U.S. Index Fund – e
- TD International Index Fund -e
- TD Canadian Bond Index Fund -e
The tracker will download the data, parse the information, and add it to the overall TD e-Series price list history. This function will take a little bit of time, and will cycle through the different sheets of the excel file while it is working. You will get a message when it is all done.
Adding price data manually
If you need to add data older than the last 60 days, or just want to manually update data instead of automatically downloading it from the web – insert your own data in the TD e-Series Price List sheet, and short by date:
Disclaimers
I just want to put out a few disclaimers for those who want to use this excel sheet:
- It was built and designed in both Microsoft Excel 2013 and 2016 for Windows. I haven’t tried it in older versions of Excel or on Mac compatible version – so don’t know how well it will work if you are using those versions.
- It holds a lot of data and does a lot of calculations, so it’s not the fastest running spreadsheet. If it goes too slow for you, you can turn off “Automatic Calculations” (in the Formulas Ribbon) and only run calculations manually (Calculate Now option) when you add in new data.
- I can’t confirm that all data calculated is always correct. Price data may be inaccurate at times, and not all of the calculations and formulas have been verified. Use the data as a guide, but it may not always be 100% accurate (although it should be close).
- Please don’t re-distribute the file yourself – send friends and peers to the site to download. This gives me a better idea of how many people use it, and whether I should put more time into making it better.
- Please let me know if you have any feedback!!! Does something not work? Do you wish it calculated something that it doesn’t? This will hopefully be the first version of many!
Ilan says
This is wicked excellent. Just came across your blog and i’ll stay tuned. Good work!
Let's Talk About Money says
Thanks for the feedback!
Ilan says
Just a heads up – since May 2 when I started with your spreadsheet, its seemingly not pulling in the book value of the 4 funds. Is it because there is only a small amount of buy transactions (the 4 initial ‘buy ins’ and an additional)? I can switch up the formula myself to try and pull in the amounts but I dont want to mess anything up.
Thoughts?
Let's Talk About Money says
Thanks for flagging your issue, it’s hard to fully test all the different ways someone might use the spreadsheet.
It’s a bit difficult for me to investigate the issue without knowing what transactions you have tracked. If you’re comfortable with it and would like me to follow up, can you email me ([email protected]) the data on your transaction page. The amounts probably don’t even matter, but if you send the dates of transactions, and with which account and which funds, I can try to re-create the error on my end of things and attempt to fix it.
Rob says
This is a great spreadsheet – thanks!
Would love to have more than just 2 account options, and be able to create custom account names (i.e. “spousal rrsp”, “unregistered acct” etc). Any way you could briefly tell us how to do this? Or add that option to your sheet? Thanks!
Let's Talk About Money says
Glad you find it helpful! At first glance, I don’t think additional accounts would be particularly easy to add-on. There are some hidden sheets in the background that are doing calculations by account type (the Running Portfolio Value sheet) and track asset value by date. You could copy the formulas for RRSP and TFSA columns, and add a new account type, but that might be a little easier said than done, as there are a lot of account references in formulas throughout the excel file.
Also, if you add more account types, you might find that it really slows down the excel file, as you’re going to greatly increase the number of calculations that need done.
This is something I could look into in the future, but likely wouldn’t have time over the next few months. A simple (but not elegant) solution might just be to use multiple versions of the file, and go in with the understanding that file 1 is your registered accounts, and file 2 is your unregistered accounts. File 2 won’t have the correct account names, but it will still be able to track everything correctly.
Rob says
Thanks for the response – I think I’ll use the workaround you suggest of creating separate files for each account.
Jordan says
Thanks for the spreadsheet,
I had a mini spreadsheet on google sheets for a while but their finance funtion broke.
What i like most about this is the portfolio returns chart you have, would it be possible to add seperate charts for each fund? I believe it would give a better idea of how to allocate your funds.
Thanks
Let's Talk About Money says
Definitely something I have in my mind for the next iteration of the spreadsheet – a drop down menu that would allow the 2 charts to be specific to a fund/account. Not sure when I’ll get around to those kind of updates, but you could update the formulas yourself by clicking on the line in the the chart, and replacing all “$AL” values in chart 1, or “$AM” values in chart 2 with the appropriate column from the “Running Portfolio Value” sheet.
For example. if you want to see the 365 day running profit of your TFSA Canadian Bond Index Fund, click on the line in chart 2, and when the formula comes up at the top =SERIES(‘Running Portfolio Value’!$AM$3,’Running Portfolio Value’!$A$4:$A$371,’Running Portfolio Value’!$AM$4:$AM$371,38 replace all “$AM” with “$Q”.
so it would be =SERIES(‘Running Portfolio Value’!$Q$3,’Running Portfolio Value’!$A$4:$A$371,’Running Portfolio Value’!$Q$4:$Q$371,38)
Guillaume says
Hi,
I really like your website, it’s a good reference for my begining in that new world! Thanks!
I would like to use your spreadsheet, but I always get a Visual Basic “run-time execution error 1004” when trying to download the recent price data. I tried on 2 different computers, but still getting the same error. I’m using Excel 2016.
In the debug, it seems to be for the line “Sheets(“Data from web”).Select”.
Any idea?
Thanks for the help!
Let's Talk About Money says
Hi Guillaume,
Glad you’ve been finding the website helpful. I took a look at the spreadsheet download, and was getting the same error that you described. Looks like it had something to do with the sheet that the data was being pulled from being hidden. I’ve updated it and fixed another minor issue as well, and have re-uploaded it as version 1.4.
Let me know if that doesn’t fix the issues for you.
Guillaume says
Hi!
No more error when I clicked on the “Download data” button.
But after the download of the new data of yesterday, I now have duplicate price, like :
2018/10/30 24.93
2018/10/30 €24,93
Don’t know if it’s because my computer and Excel are in french.
Any idea?
Thanks a lot for your quick help!
Let's Talk About Money says
I think you might be right, if you have a different default language and/or currency it might be causing that issue. If you set your defaults to English and dollars, does that fix the problem?
Guillaume says
Hi,
I have changed my default dollar sign to $ which fix the Euro issue. For the other issue, I was still getting duplicate when updating the data but I think it’s coming from the fact that it was creating all new number using a dot ( . ) and not a comma. With the test I did I see that everything is working fine when using a comma in both the Shares and Price numbers.
I have manually edit all the duplicate from the past month of data I need until I figure out where it’s coming because it’s configured properly in the default settings of Windows.
Thanks for the help!
Agnes says
Hello,
Many thanks, this is wicked!
However I’m on MacOS and cannot not use it.
Could you edit one version without Active X controls so that I can run it with my Mac? Thanks so much!
Let's Talk About Money says
Hi Agnes,
Unfortunately, I think the spreadsheet might just not be compatible with MacOS. By removing the Active X controls, it would essentially break all of the functionality that is built into the excel file.
Sam says
Can you explain how the dividends affect the overall performance? If these are distributed but the price drops the same amount, can we ignore these and just provide our contributions?
Let's Talk About Money says
Hi Sam,
Dividends are not included in the book value in these calculations and are solely reflected in return calculations. In the spreadsheet:
I don’t think you would want to ignore the distributions if they are offset by losses, as this would affect the calculations of the number of shares that you own, and would throw off your numbers in the future if the prices rise again.
But this spreadsheet can really be used in any way that you like. If you find it easier to only track your contributions, and look at dividends separately, you can definitely do that. You’ll just have to take note in discrepancies in calculations.
Brian J Zacher says
I down loaded the spreadsheet and ran the macro to update the data. Is it possible to change the “fund” to stocks and pick up the info on them. Is this spreadsheet limited to the 4 funds or can it look at more?
Thanks,
Let's Talk About Money says
Unfortunately, the excel file is designed to be pretty specific to the 4 TD e-Series funds. With some work and playing around you would probably be able to update it to other funds, but at that point it might just be easier to design a new spreadsheet.
Perer Al says
Nice sheet! What if I don’t use TD Series Fund?
I own some MAW104 Canadian Balanced Funds, and was wondering how to add those.
Some ideas?
Let's Talk About Money says
Thanks for the feedback. Unfortunately, the way I’ve made the tracker, it’s pretty specific to pulling the information for these 4 specific funds and it wouldn’t be very simple to sub in another fund.
I’ve been working with Google Sheets interface a bit lately, and depending on your comfort with Sheets and making macros what I can suggest is using this command
=importhtml(“https://ycharts.com/mutual_funds/M:MAW104.TO/price”,”table”,1)
to pull in up to date information for that specific fund.
I may be releasing some new articles soon around updated versions of the tracker using google sheets, so keep your eyes peeled.
Peter Al says
Forgot to tick the “Notify me via Email” button 🙂
Bob says
Hi,
When I clicked on the download data button today, I noticed in the TD e-Series Price List sheet, it did not fetch the Oct 31 data and added three lines
View and export this data going back to 1999 Start your Free Trial
Export Data Date Range:
Date Close
Anything I can do to fix it?
Let's Talk About Money says
Hi Bob,
It looks like the site that the spreadsheet pulls data from didn’t include updated data for Oct 31 for some reason. I would try again tomorrow or Monday to see if they have updated data (every once in a while this seems to happen, and I’m not quite sure why the site does that).
As for the extra text line that was pulled, that may fix as well when the data is updated. For a quick fix, you can always remove extra data from the ‘TD e-Series Price List’ sheet in the file. If this stays as a consistent problem, I’ll look to make an update.
Guillaume says
Hi,
I’m using your excel to track my portfolio for the last 2 years and I love it!
Now, it’s me or I can’t get price list update anymore? Last one I have is July 22nd.
Thanks!
Let's Talk About Money says
Glad you’ve been finding the Excel tracker helpful! I took a look at the spreadsheet and found the issue was due to an update on the site where the data is pulled. I’ve updated it and have re-uploaded it as version 1.6.
Guillaume says
I have downloaded the latest version 1.6 and it’s working now! Thanks for fixing that!!