Tracking your portfolio performance is a really important step to successful investing. If you don’t know how your portfolio is doing, you won’t know if you are making the right decisions. If you can’t evaluate your investing decisions, you will go in circle and realize years later your portfolio is not really growing the way it should. It can lead to drastic investment decisions to recover time lost … We don’t want that. A dividend tracker can help assess the income gap in your retirement and allow you to take appropriate action.
You also need to know what’s in your portfolio and what it means to your performance such as your exposure to the different sectors or the dividend income your investments bring in. With respect to planning for retirement, you certainly want to know how much dividend you can earn in a year or by month. The dividend income should grow and you can then forecast how much to save and invest to reach your goals.
Here is a simple view on how money flows when it comes to investing. Are you asking yourself those questions? Are you able to answer them?
As you can see, it all works together but it’s not easy to get setup properly to answer all of those questions. You need to have Excel and understand how to use macros to get stock quotes. Without stock quotes, it becomes really manual to get a quick overview of where you are at. Fear not, below are the steps to do it yourself or you can save the time and get the Dividend Snapshot Tracker all setup and ready to go.
DIY Dividend Tracker
Step 1 – Stock Quotes
The first step is to find a spreadsheet sample that retrieves Yahoo Finance stock quote. Do a search for the following keywords: “Excel Stock Quote Yahoo Finance” and from the results, try out the different options to find one you like. I personally like the sample from Market Index, it should be one of the first that comes up. Please note that all those spreadsheet will use macros to fetch the data and Excel will ask for your permission.
The Yahoo Finance API (Application Program Interface) has a number of options to retrieve many data points on the stock. Unless you want more information, you pretty much only need the quote for the stock ticker to create a portfolio and dividend tracker. If you want more, there are some fundamentals and some technical data that you can fetch. As it happens, Yahoo Finance also provides the annual dividend per share but I have found it to be incorrect at times that I don’t rely on it.
The USDCAD=X seen above is the code for tracking the exchange rate. Just swap USD and CAD to go the other way. I listed the company name and the market capitalization to make sure I had the right stock in the list. It just helps validate it’s the proper stock ticker.
One thing I learned is to keep the tab with the Yahoo Finance data on its own tab as it will clear cells. Again, it’s also important that you enable macros as it needs to make a request to the Yahoo service.
Step 2 – Setup Your Stock List
List all of the stocks you want to track and the information you want for each stocks and setup the math functions. The peach coloured cells are meant to have user inputs where as white are calculated.
The following columns are up to you to define but I track at a minimum the following columns:
- The account the stock is held. I sometimes have the same stock in multiple accounts.
- The stock ticker (to reference the other tab).
- The sector if you care about it. Each stock exchange appear to track their own categorization of a company. Sometimes it’s obvious but not always and the industry, which is a sub-category of sector, can also be different. Stick to one source of truth for the sector.
- Total shares represents what your account has in total.
- Quote is retrieved from the other tab using a VLOOKUP function
- Market value is the simple math of total shares multiplied by the quote.
- Primary currency value is where I multiply the market value by the CADUSD=X exchange rate where applicable. If the ticker ends in a “.TO”, then the exchange rate is applied.
Following that, you can decide on the information you want on this tab. At a minimum, you have your current market value of your investment. The amount invested can tell you how your holdings are doing vs your purchase price but it’s not a good performance metric.
The stock valuation is an important step in setting up your portfolio and dividend tracker to get an overall picture of your accounts. I prefer to look at the account performance rather than the individual stock performance otherwise, it becomes a stock picking process and you get into bad habits of trying to predict the performance of an individual stock.
Step 3 – Sector Diversification
All you need to do is assign a sector to your stock holdings as seen above and you can report on your sector exposure by using the SUMIF function in Excel. The target per sector is for you to identify based on your investment goals. Over time, you will see that sectors perform differently and you can use that to decide which investment to add to. It took me a few years to really adjust the target to what worked for me. Do a first pass and review it every quarter or year to see if it matches your investment philosophy.
Step 4 – ROR and Contribution Tracking
As I have explained a number of times, the performance of your portfolio is a factor of your contributions to your accounts and your overall portfolio value. The individual stock transactions or stock holdings are not the focus or the DRIP price. You just need to track the money going in and out of the account versus the total value setup in Step 2 above. With that information, you just have to apply the XIRR Excel function and you will have the ROR for your portfolio.
Step 5 – Transactions Tracking
You either want to track all of your transactions or you want to track those in accounts where you need to pay taxes. Either way, it’s going to take time on your part to track the BUY, SELL and DRIP.
As seen above, all of the peach shaded cells requires the information from your accounts. It’s all the information you need to track your capital gains. The following fields are needed for the capital gains tax calculation.
- Stock Ticker – To know which stock your transaction is for
- Trade Action – Is it an addition or a substraction
- Shares – The number of shares you added or removed
- Quotes – The price of the stock at the time of the transaction
- Amount – This is just the share count multiplied by the price of the transaction
- Transaction Fee – That’s usually tax deductible, don’t forget it.
- Currency Exchange – Currency exchange needs to be considered for your transactions as profit from currency is taxable
Remember, you can hold those investments for years if not decades and it’s your responsability to file the capital gains properly. The onus is therefore on you to stay on top of it.
Step 6 – Dividend Income Tracking
If one of your investment goals is to have your dividend income provide in retirement, tracking it is a must. Otherwise you won’t be able to extrapolate future income, especially once you stop adding investment money.
The Google DYI Dividend Tracker
While Google discontinued their API for use with Excel, you can use the GoogleFinance API with Google Sheets. You will need a google email account to get started though. If you have an account, Google Sheets is a nice option in my opinion since it’s in the cloud and you can access it from everywhere in the world on any devices. Just note that you can mirror the cloud setup with Excel by using Dropbox on your devices. It’s free to have an account and you can use your own email address to sign up.
Below are two dividend tracker examples with Google Sheets using GoogleFinance that can get you started.
- Investment Moats has a Google Sheet version with a capital gains section if that’s the most important to you. Lots of details on how spreadsheet works and retrieving real-time quote data but it can get complicated. The overall view is very simple but it does not meet my needs with sector diversification and ROR tracking.
- Dividend Meter has detailed step by step instruction for Google Sheet to track your Dividend Income progress. This one is really for beginners. I pretty much started this way and it grew to include all the features highlighted above.
They are basic introduction to tracking capital gains and dividend income but it’s a good start depending on what you need.
One key point to be aware of is that Canadians will usually deal with both USD and CAD dollars whereas Americans will usually only have USD. It becomes important that USD stock holdings are converted to CAD dollars to establish the proper market value in the home currency.
Dividend Snapshot Tracker
If you aren’t an Excel expert or just want to save time, Dividend Snapshot Tracker is available for you. If you chose to buy the spreadsheet, all you need to do is enter the information needed for your stock holdings, the dividends and your account contributions. If you want to go as far as tracking your capital gains, you can do so as well.
I have literally provide everyone with the step by step process to creating your own tracker with examples and options to get you started. I spent over 5 years getting to this stage and refining my process for tracking all the details I need to make informed decisions. Now you have it all available. With some Excel magic, you should know exactly what your rate of return is and be confident in making good portfolio decisions.
A lot of options have been offered from a step by step Excel process as well as ready-made options. There really are no reasons to not have a dividend tracker and understand the performance of your portfolio.
Image courtesy of Master isolated images – FreeDigitalPhotos.net