For those of you (like me) who are pretty much excel illiterate, this might be a helpful post for you if you’re interested in creating a dividend income spreadsheet that is connected to Google Finance and will provide you with up-to-date information on the latest dividend stock price. Also, like with other excel stuff, you can colour the title of your excel spreadsheet and make it all pretty (like mine, which is a pretty lilac shade, lol).
So without further delay, here’s a step-by-step guide on how to make a SIMPLE dividend income spreadsheet (because anything more complex than this, I can’t do). It simply helps me keep my stocks straight so that when I log into Questrade I have all the information in front of me and the process is streamlined.
Step 1: Create a Google account
If you don’t have one already, you should create a Google account so that you can use Google docs. Then click on Spreadsheet to begin. You’ll be taken to a blank spreadsheet and it will probably look overwhelming if you haven’t looked at a spreadsheet in a while. Just look at the lettered columns and the numbered rows and you’re off to a good start.
Step 2: Create headings
Some of the headings that I like to use in my spreadsheet were Company (or stock), number of shares, annual dividend yield, dividend income, annual yield (%), current price, and quarterly or monthly dividend payment (this is probably overkill but I like to include it in my spreadsheet anyways). I also have a column that says “DRIP” so I can remember which stock I drip and which ones I don’t.
This is where you can make your headings in BOLD and add a nice colour background. Everything else after this isn’t as fun (and can give you a big headache if you don’t know how to maneuver around a spreadsheet like me!)
Note: when you type in the company, make sure you type in the share name (e.g. HSE.TO to indicate a Canadian share or BCE (without the “.TO” to indicate an American share) so that you can utilize Google Finance to update the latest share price.
Step 3: Enter data
Entering data for number of shares and annual dividend income yield is easy and straightforward. To enter data for dividend income, you need to look at the column names. For example, for HSE.TO enter in the function text box “B2*C2” which is “number of shares” x “annual dividend income yield” = “dividend income”. For the respective rows, it will be “B3*C3” and so on.
For current price, enter: =GoogleFinance(“HSE.TO”;”PRICE”). Of course, this varies according to the share name. Some stocks do not have information in Google Finance, so you’ll just have to manually keep track of the share name then.
You can continue adding more attributes to your spreadsheet, like P/E (price to earnings ratio), 52-week high, 52-week low, earnings per share etc. All you need to do is substitute “pe” for “price” and so on. To see a list of attributes that you can add to your google dividend income spreadsheet, check out Google’s list here.
Step 4: Add up your dividend income
This is probably my favourite part of creating the spreadsheet. To add up your dividend income (which will automatically change once you update the number of shares that you own or to update the annual dividend yield), just type in in the f(x) field:
=SUM(D2:D15) (where D2:D15 is the column letter and the rows are the dividend shares you want to add up).
And voila, it will automatically add up all your dividend shares for you! See below for an example.
.png "Optional title")
Hopefully, you will need to be updating your dividend income spreadsheet often because the company keeps on increasing the annual dividend yield!
I used to keep a dividend income spreadsheet on paper, but it just got too cumbersome and messy to update, and it was difficult to keep track. I find this much more helpful and I can keep track of it more easily because it’s accessible anywhere.
Readers, do you have any other tips for a dividend income spreadsheet? How do you keep track of your dividend income?
