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.

![Alt text](//media1.money.ca/a/30968/image (1).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?

About the Author

Money.ca

Money.ca

Money.ca Editorial Team

The Money.ca Editorial Team is a group of passionate financial experts, seasoned journalists, and content creators who are deeply committed to providing unbiased, relevant, and accurate financial information. With years of combined industry experience, our team is dedicated to maintaining the highest journalistic standards and delivering informative and engaging content. From personal finance and investing to retirement planning and business finance, we cover a broad range of topics to suit the financial needs of our diverse readership. You can trust the Money.ca Editorial Team to empower you with the knowledge and tools necessary to make wise financial decisions.

What to Read Next

Vinovest review

Vinovest uses AI-powered algorithms and master sommeliers to create a portfolio of wines to invest in. Learn more in our Vinovest review.

Disclaimer

The content provided on Money.ca is information to help users become financially literate. It is neither tax nor legal advice, is not intended to be relied upon as a forecast, research or investment advice, and is not a recommendation, offer or solicitation to buy or sell any securities or to adopt any investment strategy. Tax, investment and all other decisions should be made, as appropriate, only with guidance from a qualified professional. We make no representation or warranty of any kind, either express or implied, with respect to the data provided, the timeliness thereof, the results to be obtained by the use thereof or any other matter.