So, recently I’ve had people asking me how I track my portfolio and how I stay on top of what my yields are portfolio wide. The answer is this: I use spreadsheets. Now before I begin, I am in no way a Microsoft Excel or Google Sheets expert, I have just enough working knowledge to make spreadsheets that work for me and that I find easy enough to understand and make adjustments too.
First off, here is a look at what my finished product dividend spreadsheet looks like:
*Note this is in Google Sheets and while the same spreadsheets can be made in desktop Excel, there won’t be the auto fill feature from the information pulled out of Google Finance*
Now that is one handsome spreadsheet if I do say so. So, what’s special about this spreadsheet? Nothing really… but the reason why I like it is the fact that there are some auto-fill features that keep it up to date without me having to do very much maintenance.
For those of you who would like a simple tracker you can simply use the Shares, Symbol, Name, Price, Current Value, Book Value and Gain/Loss columns and that would be sufficient to track your returns but for those of us who are chasing passive income I prefer to include my dividend returns both current and on cost.
STEP 1- The first step to building this spreadsheet is to enter the symbol of the company. If the stock trades in the US markets then simply use the ticker ie. “T” for AT&T. If it is on the Canadian exchanges then you must use a “TSE:” in front of it as you can see in the Enbridge example above “TSE:ENB”.
STEP 2- In the “NAME” column, simply type in =(GOOGLEFINANCE(C2, “name”))
The C2 indicates the block where the ticker is located so its searching for the name of “TSE:ENB” found in C2 and auto-fills with Enbridge Inc. Simple enough? Good.
STEP 3- This column is the heart of the spreadsheet, that is the current price of the stock. The great part of this column is it will actually update in real time for you. Simply input =(GOOGLEFINANCE(C2,”price”)) to automatically draw the price directly to your spreadsheet.
The above steps will get you most of the way to the finished product, now is the time for a little bit of manual work, but have no fear once you’ve got it done for one row (company) you can then simply select the whole row and drag down to copy the formulas down the page for each company of your portfolio.
STEP 4- Manual labour.
Shares- Manual input of the quantity of shares you own in the company
Symbol, Name, Price- already covered above
Current Value- For your top company enter =B2*E2 into the cell of F2
Annual Income- Manual entry found on my iTrade account income page for each company
Book Value- Manual entry at the time of purchase for each company
Yield- Manual enter =I2/F2 into the cell H2
Yield on Cost- Manual enter =I2/J2 into cell K2
Gain/Loss- Manual enter =F2-J2 into cell L2
**For the portfolio wide Yield, Yield on Cost, and Gain/Loss simply auto sum the columns and repeat the formulas for that new made row.**
And there you have it! It may seem like quite a few steps, but honestly it only takes a few minutes to get up and running and a matter of a couple minutes per week/month to update to 100% accuracy which in my opinion is well worth the time and doesn’t feel like much work at all since I love checking in on my dividends anyways.
To recap, once you have the first row done, so in this case our whole line of “Enbridge” information you can then highlight it and drag down however many rows you need to cover all of your companies and you’re set.
The only manual entries once up and running are the “Annual Income” and “Shares” columns. I usually do these as I see the announced dividend increases or when I know I’ve just dripped an extra share or two.
Anyways, I hope this is helpful to you and as always if there are any questions or comments feel free to leave them below. We’re always willing to exchange ideas and hopefully learn as we go along. Thanks for stopping by.