Editors note: Advertisers are not responsible for the contents of this site including any editorials or reviews that may appear on this site. For complete and current information on any advertiser product, please visit their Web site.
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).

As you know, recently I created a dividend income spreadsheet with Google Docs.  It took me probably 6 hours to do just because I am spreadsheet illiterate.  I thought it might be a good idea to immortalize a “step by step guide” on how to create a dividend income spreadsheet in case I need to do it again in the future.  And it might be helpful for those of you who want to create a spreadsheet too, of course.

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.

  • 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.

  • 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.

  • Enter Data

Entering data for number of shares and annual dividend income yield is easy and straight forward.  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.

  • Add Up Your Dividend Income

This is probably my most 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.

Screen shot 2012-12-27 at 5.47.21 PM

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?


Article comments

Hafenz says:

I don’t consider myself as spreadsheet illiterate, but I’ve never been able to make Investment Moats work for me.

Scott says:


Excellent guide! I actually used kyith’s great spreadsheet and optimized it for being used as a dividend portfolio tracker.

The only thing that I’m running into issues with now is that Google Sheets is becoming very temperamental with respect to expediently loading ImportHTML/XML calls and ImportData calls from the Yahoo Finance API. The main issue is problems loading the dividend yield. Anyone come up with a consistent solution?



Excellent post! This is pretty much how I track my dividend income. It’s great to show others how easy it is when you use some simple formulas.

Brian says:

We are most interested in our monthly dividend income, so we track our monthly or quarterly payouts. We put each stock across the top and the months down the page.

Leigh says:

Instead of doing:

you can do:

which means you can just copy/paste that formula into all the ones in the row instead of having to input the symbol in the formula on each line 🙂

Young says:

@Leigh- Sweet, thanks Leigh for the tip!

Rob says:

I actually use this free pre-made spreadsheet.


You can find more information on it from the guys over at Investment Moats. I’ve tweaked it a little for my own purposes, as I’m sure you can too; its not particularly complicated.


Young says:

@Rob- than you! That’s an awesome tip. I was looking for premade spreadsheets but couldn’t find them.

kyith says:

hi Rob, thanks for highlighting my spreadsheet. the purpose of the spreadsheet is to track a dividend portfolio by transactions, meaning buy, sell, div, splits, capital reductions.

I hope folks can take it and make it their own.

let me know if you have any problems