You have made an excellent decision in purchasing the portfolio & dividend tracker. You are about to view your portfolio in a completely different light!
I get a ton of positive feedback on how the tracker provides an amazing insight into your portfolio. All your accounts in one place.
Let’s take the tracker for a spin.
Tracker Overview
There are 10 tabs at the bottom to navigate various information. Some sheets are to present you data and others are to do data entry.
- Summary: The primary sheet showing your overall portfolio and performance.
- Stock Info: A page to list all the holdings regardless of the account and their details such as the sector, the dividend, the currency and the country. The information is used for other tabs.
- Stock Details: A per account display of all your holdings. You can see the profits and dividends from each holdings.
- Stock Exposure: This is where you see your exposure to different companies regardless of the account. If you hold the same banks across multiple accounts, this page shows you the total portfolio exposure.
- Dividend: The page where you see all the dividends you get per month. This is the actual dividends as recorded from the transaction page.
- Diversification: This page provides you with a view of your exposure to sectors and industry.
Summary Sheet
The steps needed on this page is to setup your list of accounts available. If you want, this is also where you enter the cash on hand in any of the accounts.
The trick here is to insert before column ? and after column C. Failure to do that, you will break the drop down list used for all the other sheets referencing the list. It also breaks the math for the column B summary.
The red rectangle is the list of accounts used in the drop-down menu from other sheets.
Diversification Sheet
This is a report sheet where you get a view of your diversification.
The sector table should all be set and good but the industry table needs your input. There are too many industries to list them so you will have to do it. Stick to the same source for your data.
As you know by now, I prefer the industry diversification simply because many companies have no business overlap within the same sector. Master Card and Royal Bank are in the same sector but do not have the same overlap.
The same goes for Telus and The Walt Disney Company as they are now both in the communication services sector now.
Stock Info Sheet
On this sheet, you list all of your holdings across every single accounts.
Enter all the details about the company in this location. The information you enter will be used in the other tab by referencing the stock ticker.
Unfortunately, there is no automation here. All the details here are manually entered. I use Dividend Snapshot Screeners to fetch the details I need but you can also get them from the TMX. I recommend you stick to the same source otherwise you may discover small differences.
When you enter the dividend, make sure you enter the currency of the dividend too. For example, Algonquin Power & Utilities Corp is a Canadian stock but it pays dividend in US currency. It’s important as currency conversion is needed in other tabs.
The Sector drop-down is fixed to the sector listed in the Diversification sheet and the same applies to the Industry drop-down.
Stock Details
This is where you see what you hold in each account.
You essentially have to select the account and the ticker for your holdings. The rest will populate from the other sheets.
What you need to do to add more rows is select the row all in grey, right click and add rows above. Add as many rows as you need.
The next step is to select one of the existing row, right-click and select copy. You then select all the new rows you recently added, right-click and paste.
The resulting set of actions will copy all the formulas and lookups. You just need to set the account and tickers for your holdings.
Stock Exposure
This view ignores the account and puts all of your holdings in one list. This is a good view to see if you have too much exposure to one holdings.
I like to sort it by the Portfolio Weigth Ratio. If you follow my dividend income reports and review my portfolio, you can see I almost have 10% exposure to Microsoft. This is where I see that as it combines the stock from all my accounts.
Excluded from this are ETFs. I don’t believe I need to know the full exposure from ETFs as it’s not the reason to buy an ETF.
Dividends
While there is some automation on this sheet, there is some ongoing manual updates.
First, you need to enter your holdings at the top and select the account and the stock for each. You need to insert a column for each stock if you have more stocks than the number of columns in place by default.
There is a section for Canadian dividends and a section for US dividends. I personally don’t spend any time converting my US dividends into Canadian dollars and just total it together. However, it’s setup to sum up the 2 currencies separately.
The need to convert really depends on what you want to do with the data. My goal is to see it grow and the currency conversion doesn’t do anything to show me that.
What you need to do annually is setup the annual tabulation. You should be able to copy and paste the 13 rows.
One key point that is important is to not delete any of the stock columns as you lose your historical dividends. You have to keep them and that’s why I change the background color to red. It means I don’t hold the stock anymore.