Update 4/28/2020:

I added a similar post, but using Python instead of Google Sheets to build the dashboard: https://dividenddozer.com/2020/04/28/how-to-build-a-dgi-dashboard-in-python-and-plotly/

This is awesome!

I’ll soon be leaving my job and won’t have Microsoft Office paid for, so I’m migrating everything over to Google Sheets (and Docs, Slides, etc).

It’s been incredible. I believe you can do all this with Excel, but Google Sheets has been integrated with the internet from the start. I fairly quickly automated most of my personal portfolio. I can now check things on my phone that used to take me 15 minutes on a computer.

In the process, I made a Dividend Dashboard.

If you want to skip the tutorial and open/copy the sheet, here you go: Dividend Dozer’s Dividend Dashboard. Just log into your Google account and Menu >> Make a Copy. Then put your own values into the green cells and give it time to load.

I used to get my favorite DGI metrics from multiple sites, but now I have it all in front of me.

• Dividend growth rate (over a number of years)
• Current Yield
• Payout Ratio
• PE Ratio (and also that of the S&P 500)
• Consecutive years of dividend increases
• Share appreciation (over a number of years)

I’ll explain all my formulas if you’d like to follow along and make this for yourself. Buckle up – this will be a long ride.

Start off with a blank Google Sheets document. From now on, when I refer to “L6”, that means column L, row 6.

B3
Enter a dividend growth ticker (I’ll use JNJ for this example)

C3
Enter a number of years to look back (I’ll use 15, but you should use at least 5 until you get comfortable with the spreadsheet).

After the sheet is built, we will only need to enter a Ticker and a Number of Years to Look Back. Our automated spreadsheet will do the rest.

L6
=query(importhtml(concatenate(“https://www.nasdaq.com/symbol/”,B3,”/dividend-history”),”table”,0),”select Col3, Col6 LIMIT 500″)

Change L6 color to gray (to represent an equation being present).

Format all of Column L to be in currency ($).

It’s weird to start off in L6, but this will leave room for our dashboard later.

If you haven’t automated much in spreadsheets, prepared to be amazed. We are importing Nasdaq’s record of dividend payments into our spreadsheet. The concatenate function inserts our B3 ticker symbol into a url structure, and Google sheets does the rest of the work with the importhtml and query functions.

importhtml imports the entire table, and the query function selects only the columns we want (cash amount and payment date).

It’s basically magic. There’s going to be a lot more of this.

Next, we want to detect when there is a dividend increase, and calculate that increase.

N6
Hard-enter a column title, “% Increase.”

N7
=iferror(if(or((L7–L8)/L8=0,M8=“–“),,(L7–L8)/L8),)

Drag N7 down all the way to N506 and shade these cells gray to indicate formulas being present. Also, make all of Column N a percentage format ($).

*Why N506? Because our dividend import limits itself to 500 rows of data. Even a monthly dividend payer will only produce 240 rows in 20 years, so I am confident 500 is enough.

What is this doing? It’s just taking the most recent dividend payment and comparing it against the second-most recent. If there is no dividend raise, the cell shows as nothing. Likewise, if it is the oldest record, the cell will produce an error, but iferror also creates an output of nothing. If there is an increase (or decrease), the cell will show the % change.

Nice work. We now have an unfiltered dividend history import. I like to surround this entire table with a border to keep things organized. I also hard-entered “Unfiltered” in L5 to title to this table.

On to the second table (of three)…

If we want to see only two years of data, but are importing 5 years, we will need to create a filter of the first table. First, create the headers:

P5
=concatenate(“Dividend data filtered for last “,round((max(Q:Q)–min(Q:Q))/365.25,1),” years”)

P6
Hard enter, “Cash Amount.”

Q6
Hard enter, “Payment Date.”

R6
=N6

S6
Hard enter, “Historic Yield”

Shade cells with formulas gray if you’d like to keep using my format.

The second table title is a long concatenate formula. Remember, concatenate means to combine, and we are just combining words and cells values.

round((max(Q:Q)-min(Q:Q))/365.25,1) generates the number of years in the title. It subtracts the newest date from the oldest date to come up with a number of years of data. Then, it rounds it off to the nearest decimal, which is why the “1” is in the formula.

Next, create the formulas for the body of this table:

P7
=iferror(if(or(datevalue(M7)<(today()–365.25*$C$3+1),M7=“–“),,L7),)

*Notice we leave nothing between the two commas. This will come in handy later when we look for the oldest dividend payment (furthest non-blank cell down).

Q7
=iferror(if(datevalue(M7)<(today()–365.25*$C$3+1),,datevalue(M7)),)

R7
=iferror(if(or(datevalue(M7)<(today()–365.25*$C$3+1),M7=“–“),,N7),)

S7
=iferror(P7*$J$3/(index(V:V, match(int(Q7),W:W,1),0)),)

Drag these four formulas down to row 506 and shade all these cells gray.

Format Column P as currency ($). Format Columns R and S as percentages (%).

What did we just do? We are now filtering the first table to only show what data between today and the number of years back that we entered in C3. This will help us build our charts later on.

We also added a new column, “Historic Yield.” For now, it is blank. In a minute, we are going to populate a third table to import historic closing prices for our ticker. The historic yield column is going to match dividend payments with the stock price on that day in history, and tell us what the yield was throughout the duration of dividend payments.

To help further understand the formulas,

• today() is a handy Google Sheets function that produces today’s date.
• today()-365.25*(number of years to look back) creates a range of years.
• index(match()) lets Google sheets look for a date and return a stock price for that date. We will need to create a few more data imports for these to display.

Hopefully, your second table looks like this:

On to our third table (keep going, this is an easy one).

Start with the headers:

U5
Hard-enter “Price History.”

U6
=googlefinance(B3, “price”, min(Q:Q), today(), “Daily”)

V6
Hard enter, “Close.”

W6
Hard-enter “Date (integer)”

googlefinance() is a super cool function that looks for a ticker symbol (B3, or JNJ in our case). Then, it looks for a start and end date to import daily or weekly closing prices. We use min(Q:Q) to find the date of the earliest dividend payment.

Now, enter the column formulas:

Scroll to the very bottom of the sheet and add rows until you have 13,000 rows. This will allow for 50 years of closing price data, if we ever have that much to look at.

W7
=if(U7=“”,, int(U7))

Drag the formula in W7 down to W13000 and shade these in as gray.

Create a border if you like.

What did we just do here? googlefinance() imports dates with times attached. Sheets stores dates and times as something like 41527.755. The 41527 indicates a date, and the .755 indicates a time in that day. We want to match historic dividend data with historic closing prices, so making the date into an integer (whole number) eliminates the time portion and allows for an exact match later on.

We can now hide the columns with tables (L-W). We will check boxes in the charts later to tell Sheets to keep using hidden data.

Now, to manually bring in the Dividend Champions spreadsheet.

First, create a new tab in your Google Sheet. Name it “Dividend Streaks (Updated 1/30/2019)” or whatever the current date is. You will have to manually update this as often as you like to keep the dividend streaks current.

If you haven’t downloaded the US Dividend Champions Spreadsheet, it is one of the most effective dividend growth screeners out there. Originally maintained by David Fish, it is now maintained by Justin Law (thank you, Justin). You can find the link to the US Dividend Champions, Justin Law, and other non-US champions at http://www.dripinvesting.org/Tools/Tools.asp.

Download the champions sheet, go to the “All CCC” tab, and select all. Copy and paste into Cell D16 of your google sheet. This will leave a bit of room to the left and to the top. I paste the link to the champions sheet to the left in Cell A15, and enter tickers above that might not be in the sheet, for instance VTI. You can hand-enter other tickers if necessary.

If you want Canadian or UK DGI stocks, add them so the tickers are in Column E, the streak is in Column H, and the payouts/year are in Column O.

Congrats! We are ready to start making our Dividend Dashboard.

Alright, let’s switch back to our first tab. I renamed it “Div Graph” at this point to keep things clear. Now for the fun part, the Dividend Dashboard!

B2
Hard-enter “Ticker.”

C2
Hard-enter “Years to Look Back.”

D2
Hard-enter “Current Share Price”

E2
=concatenate(“Compound Annual Dividend Growth Rate”, char(10),”(Last “,round((max(Q:Q)-min(Q:Q))/365.25,0),” years)”)

F2
Hard-enter “Current Yield.”

G2
Hard-enter “Payout Ratio (Forward).”

H2
Hard-enter “PE Ratio (vs SP500).”

I2
Hard-enter “Consecutive Dividend Increases (Years).”

J2
Hard-enter “Dividends per Year”

Shade these any color you like, and put borders around each cell.

The only formula here is in column E, and uses a concatenate function to auto-generate how many years of data we are looking at. The “char(10)” acts as a return.

On to the numeric dashboard:

We already have our Ticker and Years to Look Back populated with JNJ and 15. The rest of these are going to be some fun formulas.

D3
=googlefinance(B3,”price”)

E3
=(P7/(FILTER(P:P,ROW(P:P)=MAX(FILTER(ROW(P:P),NOT(ISBLANK(P:P)))))))^(1/((max(Q:Q)-min(Q:Q))/365.25))-1

Our first formula here is familiar. Instead of giving googlefinance() a date range, we just get the current price for the ticker we are feeding it in B3.

Our second formula looks like a mess. The underlying
compound annual growth rate (CAGR) equation is actually not so bad:

I’ll let you dig more into this one if you like, but we are breaking it down into:

• Ending Value: Most recent dividend payment value (imported as cell P7)
• Beginning Value: Oldest dividend payment value in Column P (last non-blank cell)
• # Years: Difference between oldest and newest payment date

Pretty cool! If you’re still with me, I think you are a true spreadsheet lover.

F3
=S7

G3
=P7*J3/googlefinance(B3,”eps”)

You’ll have to trust me on these two. Our yield column still needs another piece. For now, we are calling googlefinance(B3,”eps”) to get earnings per share. The payout ratio is (Dividends per share / Earnings Per Share). We are missing J3, dividend payments per year, but not for long.

H3
=concatenate(B3,”: “,googlefinance(B3,”pe”),char(10),”sp500: “,query(IMPORTXML(“http://www.multpl.com/”,”//div[@id=’current’]”), “select Col2 LIMIT 1”))

I3
=iferror(index(‘Dividend Streaks (Updated 1/30/2019)’!H:H, match(B3,’Dividend Streaks (Updated 1/30/2019)’!E:E,0),0),”Not Listed”)

J3
=iferror(index(‘Dividend Streaks (Updated 1/30/2019)’!O:O, match(B3,’Dividend Streaks (Updated 1/30/2019)’!E:E,0),0),”Not Listed”)

The H3 equation is interesting. We are pulling the current PE ratio for our ticker from Google, then also pulling the overall S&P 500 PE ratio from http://www.multpl.com using IMPORTXML(). This makes it easy to compare our stock’s PE ratio with that of the overall market.

The I3 equation matches our ticker with tickers on our Dividend Streaks tab, and returns the number of years of consecutive increases.

The J3 equation does the same thing as the I3 equation, but returns the number of dividend payments per year. Voila! You’ll see that a few other cells needed this Dividends Per Year figure and now all should be populating. Not only that, but if you scroll over to Table 2 (filtered dividend data), you’ll see that our historic yields are now being calculated thanks to cell J3.

We are actually getting really close now.

We have a few things left to do.

Drag from B4 to J29 and color these cells dark gray.

C8
=text(MIN(U:U),”mm/dd/yyyy”)

I8
=text(MAX(U:U),”mm/dd/yyyy”)

F8
=concatenate(text((index(V:V, match(MAX(U:U),U:U,1),0)/index(V:V, match(min(U:U),U:U,1),0))^(1/((max(U:U)-min(U:U))/365.25))-1,”+0.00%;-0.00%”),” annual growth over last “,round((max(U:U)-min(U:U))/365.25,1), ” year(s)”)

Change the color of the cells we just added in Row 8 so they are easier to see.

Align-center cell F8.

C8 and I8 act as titles for a chart we will add later. These fetch the date range for Price History from Table 3.

Starting to take shape! Two more cells to add at the bottom.

B29
=concatenate(“Generated: “,text(today(),”mm/dd/yyyy”))

J29
Hard-enter, “*Accuracy and completeness of any stock information is not guaranteed.”

Change these cell text colors to the same as those in Row 8.

Align-right cell B29.

B29 now shows what date the dashboard was created, and J29 reads off a disclaimer. This disclaimer should be taken seriously, as my formulas could be off, etc.

Now we have a blank canvas. We’ll create two charts and be done.

Menu >> Insert >> Chart >> Setup:

*Chart type = Area Chart

*Stacking = None

*Data Range = U6:V13000

*Remove X-Axis

*Check box for “Include hidden/filtered data”

Chart >> Customize >> Chart Style:

*Background Color: None; Check the Maximize box

Chart >> Chart & axis titles:

*Delete Title text for chart title, horizontal axis title, and vertical axis title

Chart >> Gridlines:

*Major gridline count: None

On to the last step:

Menu >> Insert >> Chart >> Setup:

*Chart type: Combo chart

*Stacking: none

*Data Range: Q6:Q201,P6:P201,R6:R201,S6:S201

*Edit x-axis range: Q6:Q201

*Series: Remove Payment Date

*Check the “Use row 6 as headers” box

*Check box for “Include hidden/filtered data”

Chart >> Customize >> Chart style:

*Change background color to match your background cells

Chart >> Customize >> Horizontal axis:

*Check the box for “Reverse axis order”

*Select 60° for “slant labels”

Chart >> Customize >> Series:

*Drop down and select Cash Amount. Select the “data labels” box. Position: Inside end. Font size: 10. Text color: yellow.

*Drop down and select % Increase. Color: white. Axis: right axis. Line thickness: 0px. Point size: 7px. Select the “data labels” box. Position: below. Data label font size: 14.

*Drop down and select Historic Yield. Axis: right axis.

Chart >> Customize >> Chart and axis titles:

*Drop down select “chart title,”: Recent Dividend Payments and Dates (With % Increase)

*Drop down select “vertical axis title,”: Dividend Payment

*Drop down select “right vertical axis title,”: Historic Yield & Dividend Increase %

Resize as necessary!

If you’ve gone through this whole guide, I hope you had as much fun as I did.

Google Sheets lets us make some pretty cool tools. Not only do I have all my information in one place now, but I also can see historic yield trends, dividend growth trends, and share price trends.

I recognize a few shortcomings. The Nasdaq data often only goes back 5 years (which is usually enough for me). It’s not *fully* automated – we’ll still have to update the dividend streaks tab, and input any ETFs or other symbols that don’t come on the sheets from
http://www.dripinvesting.org/Tools/Tools.asp.

Another shortcoming is that it just doesn’t look that great on my iPhone. Some of the chart formats don’t transfer, so I’ll mostly use it on my computer.

Please let me know if you like/dislike this tool, and also if you find a way to break it. While creating this blog post, I found a few formula errors and corrected them. I’m looking for a way to add Free Cash Flow, so that may be a future addition. Until then, enjoy! If you’d like to use this dashboard for your own blog, I’d appreciate a link back to this post.

Here’s the link to the Dividend Dashboard Google Sheet if you want to just save a copy for yourself and skip all the work.