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 investment decisions, you will go in a 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 portfolio & 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 the month. The dividend income should grow and you can then forecast how much to save and invest to reach your goals. Can your portfolio keep up with inflation is a critical question you also need to be aware of.
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 set up properly to answer all of those questions. You need to use Google Sheet (Excel or any other spreadsheet can also work) and understand how to lookup data in other sheets. 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 Portfolio & Dividend Tracker
Before we dive through the steps to build your portfolio tracker, we need to discuss the status of quotes retrieved online.
The Yahoo Finance API (Application Program Interface) used to allow fetching of the stock quote but it has been deprecated for use outside of their website. Google Sheet, with the GoogleFinance API, is now the only spreadsheet capable of fetching quotes. Excel is manual or requires add-ins.
If you go down the manual route, getting a stock quote can be done through Google Finance, Yahoo Finance, Morningstar or your discount broker. Your discount broker is probably best as you have all your holdings in one location (or a few if you have multiple accounts). Do note that Google Finance changed their portfolio management and I have research alternatives to Google Finance with MSN Money leading as the alternative.
IMPORTANT – There are very limited services that can pull quotes in a reliable manner for spreadsheet since Yahoo deprecated their web API. You either need to use Google Sheet with the GoogleFinance function or you have to manually enter the stock price.
Step 1 – Stock Info List
The first step is to itemize your holdings and the details pertinent for reporting. You can track more information if you want such as the industry, international exposure, growth vs income investment. It’s really up to you to track what you want. The information entered here can be looked up in the other tabs (note that the second row, hidden in the image, is a column number starting with 0 to facilitate lookups).
Step 2 – Setup Your Stock Details
The stock details tab is where you list all of your holdings per account. The light grey cells can be automated through a formula or a lookup into another tab.
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 appears to track their own categorization of a company. Sometimes it’s obvious but not always and the industry, which is a sub-category of a sector, can also be different. Stick to one source of truth for the sector.
- Total shares represent 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.
- Dividend yield can be listed once you enter the dividend and you can track your yield on cost if that matters to you based on your average purchased cost.
Following that, you can decide on the information you want on this tab. At a minimum, you have the 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.
Ater this step, you should have a second tab listing all of your stocks with all the columns you want per holdings per account. The summary portfolio performance is optional at this point.
Step 3 – Reporting & 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 or Google Sheet. 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.
After this step, you should have a third tab summing up your holding value per sector if that’s of interest.
Step 4 – ROR and Contribution Tracking
This is where the magic happens for my portfolio. I know exactly what the performance of my portfolio is and better yet, with Google Sheet, I compare it to indexes and I have a benchmark.
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 for that matter. You just need to track the money going in and out of the account versus the total value set up in summary tab of Step 2 above. With that information, you just have to apply the XIRR Excel function and you will have the ROR for your portfolio.
After this step, you should have your 4th tab tracking all the money going in and out of each account. This is simply set up to use the XIRR function to calculate the annual rate of return.
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 ‘light grey’ shaded cells are computed and white cells require 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 subtraction
- 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 responsibility to file the capital gains properly. The onus is therefore on you to stay on top of it.
After this step, you should have a 5th tab providing you with all the data necessary to calculate your capital gains if you want to track that.
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.
This is quite a bit of work as every month, you need to update the dividend income for each stock paying for that month. The dividend rate may have changed so it needs to be looked up to keep it accurate.
The effort is worth it as you can holistically have access to your dividend history. You can also start breaking down which stocks contributes the most to your dividend income. If you choose to track your dividend income, you have a 6th tab to your spreadsheet.
The Google DYI Dividend Tracker
While Yahoo 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 the best 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 if cloud access is important. It’s free to have an account and you can use your own email address to sign up. Google Sheets can give you the same Excel tracker since many of the formula exists for lookups and the finance functions are excellent. The results can provide a comprehensive portfolio tracker if you are willing to put in the time. Of course, you need to enter all of the data at some point and there is no easy way to avoid the data entry.
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 the 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 or Google Sheet expert or just want to save time, Dividend Snapshot Tracker is available for you. If you choose 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. You end up with a proven, and tested, portfolio tracker with dividend history per stock, per month or annually.
You get both an Excel version and a Google Sheet version with the Google spreadsheet providing you with automated quote updates.
BONUS: The Google version comes with the ability to compare your portfolio to an index. Know where you stand against your benchmark.
Provided above is 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. The discount brokers I have used and Quicken have all failed in giving me a clear picture of my performance until I got set up with this spreadsheet. No excuses, now you have it all available. With some minimal spreadsheet skills, you should know exactly what your rate of return is and be confident in making good portfolio decisions.
With all of the options offered above, there really are no reasons to not have a dividend tracker and understand the performance of your portfolio.
IMPORTANT – The Excel file will not automatically update stock price but the Google Sheet version will give you updates.