I hope you read My Portfolio Management Methodology – Diversification, Rebalancing, and Performance Tracking where I dive deep in how I manage my portfolio. I built the theoretical foundation for a successful and free Portfolio & Dividend Tracker on that post.
I should mention that there are for a fee products that help tracking your portfolio & dividend easier, but they usually cost money. One of the products is the Dividend Tracker by Sharesight. I never used any of these for a fee products so can’t comment on the quality.
The other tool which is a very popular and well respected product by Questrade clients as it is free, is Passiv. My investment account is with Wealthsimple so I don’t currently use Passiv personally.
All the screenshots on this post are for educational purposes. I am not giving financial advice to purchase any of the stocks or ETFs on the portfolio template. They also don’t reflect my holdings.
Requirements for Building Your Complete Portfolio & Dividend Tracker
There are two requirements which are Microsoft Excel and Patience. As long as you know how to open Excel everything can be built easily. It took me a while to get the file to this stage and I am sure I will be tweaking it along the FI journey but it should be a great starting point.
You can also build the file in Google Spreadsheet but I like to work locally and have access to my files offline so I chose Excel. Feel free to download the template as a reference which can help understanding and following along. You can freely use the template and modify it per your needs. However, if you reference it in any other places, I appreciate a link to this post.
The Excel File Basics & Stocks Data Type
We will use the “Stocks” Data Type which basically converts cells with company names or ticker symbols, like “AAPL”, to get current stock or ETF info like price, exchange, and more.
Microsoft has a very nice tutorial with video showing how to use this Stocks Data Type to get the stock info which I recommend you watching to familiar yourself with how it works.
To simplify, you need to convert some cells to Stocks Data Type in order to get the desired information about a stock. Below information can be pulled easily from any supported stock:
52 week high, 52 week low, Beta, Change, Change % (Extended Hours), Change %, Change (Extended Hours), Currency, Description, Employees, Exchange, Exchange abbreviation, Expense Ratio, Headquarters, High, Industry, Instrument Types, Last trade time, Low, Market Cap, Name, Official Name, Open, P/E, Previous close, Price, Price (Extended Hours), Shares Outstanding, Ticker Symbol, Volume, Volume Average, and Year Incorporated.
I highlighted the information I use in the Excel file but you can choose any of them depends on your requirement. As seen, unfortunately, dividend information isn’t available yet which we will have to enter manually. As I have Questrade, I can see all the dividend info on my watchlist there.
Have you checked my new LinkTree Referral Page to see how much you can make just by using the mentioned links?
Market Identifier Code in Stock Data Type of Excel
To represent any stock in Stocks Data type, it needs to be written in a specific format which is (Market Identifier Code: Ticker). Below is the list of US, Canada, and UK Market Identifier Codes:
| Country | Market Identifier Code (MIC) | Exchange Name |
| Canada | XTSE | Toronto Stock Exchange |
| Canada | XTSX | TSX Venture Exchange |
| United Kingdom | XLON | London Stock Exchange |
| US | XNAS | Nasdaq Stock Market |
| US | XNYS | New York Stock Exchange |
| US | ARCX | NYSE Arca |
| US | OTCM | OTC Markets |
For example, as soon as I type XTSE:AQN in a Stocks Data Type Cell and click Enter or Tab, it becomes Algonquin Power & Utilities Corp. (XTSE:AQN). Or typing XTSE:AX.UN gives ARTIS REAL ESTATE INVSTMNT TRUST UNT (XTSE:AX.UN). In my case, I have the Company Name listed on Column P which will my the main reference for other columns to pull the required info.
For instance, under Column A, I list the Ticker (Symbol) of each company. To get the ticker in the 2nd row, I enter below in A2 =P2.[Ticker Symbol] or for Name of the company in C2 =P2.Name
In total, I have 20 columns from A to T which I am going to define below.
Defining the Columns & Their Excel Rules
Don’t run! My Excel file has 20 columns to help me take full control of my portfolio. As shown above, my reference column for a stock’s information is P. You can find the formula under Formula in below table. However, when there is no formula, I will put more details to explain what is needed.
Just a heads up, don’t let D52 and D53 in Q and R respectively confuse you. D52 is the total contributions while D53 is the total value of the portfolio based on current market prices. All formulas in the table are relative. This means when you add / remove rows, D52 and D53 will be updated automatically.
| Letter | Title | Definition & Purpose | Formula |
| A | Ticker | Stock Symbol | = P2.[Ticker symbol] |
| B | Account | Account Type | Personal, TFSA, RRSP, RESP, LIRA |
| C | Name | Company Name | = P2.Name |
| D | Average Cost | Average Cost of Purchase | Enter Average $ Cost |
| E | Current Value | Current Stock Value | = P2.Price |
| F | Shares | Number of Owned Shares | Enter Number of Shares |
| G | Total Cost | Total Cost of Purchase | = D2*F2 |
| H | Change % | Percentage % Gain or Loss | = IF(I2=0,0,I2/G2) |
| I | Total Change | Total $ Value Gain or Loss | = (E2-D2)*F2 |
| J | Dividend | Dividend $ Per Share | Enter Dividend $ Value |
| K | Annual Pay | Annual Forward Dividend | = (J2*F2)*L2 |
| L | Pay Freq / Year | Dividend Payment Frequency | Enter Payment Frequency (2, 4, 12) |
| M | Yield on Cost (YOC) | Yield on Cost of Purchase | = IF(D2=0,0,(J2*L2)/D2) |
| N | Yield on Price (YOP) | Yield on Current Share Price | = (J2*L2)/E2 |
| O | Sector | Sector | Enter Sector |
| P | Company Name | Company Name | MIC:Ticker (XTSE:TD) |
| Q | Contribution % | Share % From Contributions | = G2/Trades!D52 |
| R | Portfolio % | Total Share % in Portfolio | = (E2*F2)/Trades!D53 |
| S | Region | Share Region | Canada, US, Global, Developed |
| T | Type | Instrument Type | Stock, ETF |
If you downloaded the template, you certainly noticed some additional tables at the bottom. I am going to discuss the external tables and their formulas in the next section.
Calculating Total Gain or Loss & Forward Dividends
These 6 rows are the results of our efforts where we see how the investments is working for us in order to make our life more prospect.
| Cell | Name | Formula | More Info |
| D52 | Total Contribution | = SUM(Tracker!G2:G50) | G is Total Cost of Purchase |
| D53 | Total Market Value | = D52+D55 | Total Market Value in $ |
| D54 | Total Gain or Loss | = (D53-D52)/D52 | Percentage % Gain or Loss |
| D55 | Total $ Change (Gain or Loss) | = SUM(Tracker!I2:I50) | I is Total $ Value Gain or Loss |
| D56 | Total Annual Dividends | = SUM(Tracker!K2:K50) | K is Annual Forward Dividend |
| D57 | Annual Dividends Yield | = D56/D52 | Percentage % Dividend Yield |
D53 is the total market value which I calculate by adding the gain (Positive) or loss (Negative) to the total contribution. I also like Conditional Formatting and I’d love to always see more GREEN! As shown in the template, I have the color formatting applied to the Total Change & Change % columns.
RED means my average cost of a share is higher than the current share price which might be an indicator to average down on that holding.
Calculating Contributions & Portfolio Weight per Sector
As I like to keep my portfolio diversified among sectors, I need to keep an eye on my sector weight. This table helps track the sector weight.
As shown and discussed in previous post, I have 11 sectors listed. Column B is the total sector contribution, C is the current sector market value, and D is the difference between the contribution and market value.
I am keeping each sector below 20% by adding to other sectors. I try to avoid selling and rebalance by purchasing more. For RRSP or TFSA, this means I have to either wait to get more contribution room or use the dividends.
You can check out my monthly money report to follow along my journey of rebalancing and moving my accounts.
To calculate each cell, I have to run a conditional formula to compare all the rows of column O which is the sector column and if it is the desired sector to add the content of cell Q (Contribution) or R (Market Value). Here is the formula for row 59, REIT sector.
- Total Sector Contribution Weight =SUMIF($O$2:$O$50, A59, $Q$2:$Q$50)
- Total Sector Market Value Weight =SUMIF($O$2:$O$50, A59, $R$2:$R$50)
At the bottom, as a checkpoint, I need to make sure the total comes to 100% from all sectors. This helps realizing a mistake in one of the cells which caused the imbalance.
Calculating Region & Instrument Weight
These 2 tables will give me more statistics about my portfolio which involves knowing my investments per region or Global ETFs. I also like to know my weight per instrument as I don’t want to invest too much in Covered Call ETFs.
Same as sector calculation above, I had to run conditional formulas but instead of running it against the Sector column, I ran it against Column S Region or Column T Instrument Type. I won’t bore you with the formulas here but you can see them by clicking on any cell from the template file.
Final Thoughts on Building a Portfolio & Dividend Spreadsheet Tracker
I personally like numbers and statistics which means I enjoy doing all the Excel work. However, you might not feel doing it so I recommend just downloading the template and tweaking it as needed.
It is important to update the file with every purchase or sell order or whenever dividend changes. As you might have noticed, I don’t include the gain or loss from selling a holding as the purpose of this tracker isn’t to show me how much money I have in total rather how much my portfolio is invested and making me passive income. My brokerage account clearly shows the total amount.
Hope this helps some of you great investors out there who are taking control over your money. Leave me your comments or questions below.