Build or Download Your Portfolio & Dividend Tracker Spreadsheet in Excel

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.

Portfolio & Dividend Tracker
Tracking My Sectors Allocation

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.

This image has an empty alt attribute; its file name is LinkTree-1.jpg

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 Excel Stocks Data Type
Microsoft Excel Stocks Data Type

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.

Portfolio & Dividend Tracker in Microsoft Excel - Canadian Dividend Tracker

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?

LinkTree 1

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:

CountryMarket Identifier Code (MIC)Exchange Name
CanadaXTSEToronto Stock Exchange
CanadaXTSXTSX Venture Exchange
United KingdomXLONLondon Stock Exchange
USXNASNasdaq Stock Market
USXNYSNew York Stock Exchange
USARCXNYSE Arca
USOTCMOTC Markets
Microsoft Excel Market Identifier Codes

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.

LetterTitleDefinition & PurposeFormula
ATickerStock Symbol= P2.[Ticker symbol]
BAccountAccount TypePersonal, TFSA, RRSP, RESP, LIRA
CNameCompany Name= P2.Name
DAverage CostAverage Cost of PurchaseEnter Average $ Cost
ECurrent ValueCurrent Stock Value= P2.Price
FSharesNumber of Owned SharesEnter Number of Shares
GTotal CostTotal Cost of Purchase= D2*F2
HChange %Percentage % Gain or Loss= IF(I2=0,0,I2/G2)
ITotal ChangeTotal $ Value Gain or Loss= (E2-D2)*F2
JDividendDividend $ Per ShareEnter Dividend $ Value
KAnnual PayAnnual Forward Dividend= (J2*F2)*L2
LPay Freq / YearDividend Payment FrequencyEnter Payment Frequency (2, 4, 12)
MYield on Cost (YOC)Yield on Cost of Purchase= IF(D2=0,0,(J2*L2)/D2)
NYield on Price (YOP)Yield on Current Share Price= (J2*L2)/E2
OSectorSectorEnter Sector
PCompany NameCompany NameMIC:Ticker (XTSE:TD)
QContribution %Share % From Contributions= G2/Trades!D52
RPortfolio %Total Share % in Portfolio= (E2*F2)/Trades!D53
SRegionShare RegionCanada, US, Global, Developed
TTypeInstrument TypeStock, 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.

Portfolio & Dividend Tracker in Microsoft Excel - Canadian Dividend Tracker
Columns H & I with Conditional Formatting

Calculating Total Gain or Loss & Forward Dividends

Portfolio & Dividend Tracker in Microsoft Excel - Canadian Dividend Tracker

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.

CellNameFormulaMore Info
D52Total Contribution= SUM(Tracker!G2:G50)G is Total Cost of Purchase
D53Total Market Value= D52+D55Total Market Value in $
D54Total Gain or Loss= (D53-D52)/D52Percentage % Gain or Loss
D55Total $ Change (Gain or Loss)= SUM(Tracker!I2:I50)I is Total $ Value Gain or Loss
D56Total Annual Dividends= SUM(Tracker!K2:K50)K is Annual Forward Dividend
D57Annual Dividends Yield= D56/D52Percentage % 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.

Stock Market Portfolio & Dividend Tracker - Canadian Stock Market

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

image 11

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.

0 0 votes
Please Rate
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
error

Did you enjoy the blog? Please support me by spreading the word. THANKS!

0
Would love your thoughts, please comment.x
()
x