Sunday, January 24, 2016

Metrics: 10 Year Trends for DGI Stock Analysis (and Google Spreadsheet Tool)


As part of my analysis of DGI stocks, I like to see the 10 year trends of the key metrics. Sometimes this gives me a bigger picture of how a company is performing over a 10 year period - e.g. it is good to see how well a company performs during a recession etc.

I normally review the following metrics:
  • Revenue and EPS
  • ROE and OM
  • FCF, Earnings & Dividends
  • YoY Dividend Growth
  • Payout Ratio
  • Debt to Equity

So, where can I get this data?

To help with my analysis, I created a simple Google Spreadsheet that grabs data from Morningstar’s website.

Google Spreadsheet

As you may know, Morningstar provides an excellent 10 year view on the key metrics on most major companies. For example, you can check out JNJ’s metrics:

You can then export the data to excel format by clicking on the “Export” button.

However, if you are familiar with Google Sheets, you can use the following function:


Where A1 is where you put the ticker.

To save you the hassle of creating your own spreadsheet, you can copy mine below:

Instructions: Just enter the ticker in the YELLOW A1 cell.

Note1: The correctness of the data is very much dependent on M*. Please do your homework to make sure the data is correct.
Note2: Please respect M*’s information. i.e. No commercialization etc.

Example: 10-year Trend Analysis for JNJ

Let's see an example. Below, I've plugged in JNJ as the ticker for cell A1 in the summary tab.

  1. Rising and Stable Revenue and EPS

For Revenue and EPS, I generally look for the absolute 10 year view, 10 year growth and 3 year growth. Ideally, I am looking for companies with rising Revenues and Earnings.
With the JNJ example below, the Absolute 10 year view looks very good, but when you look  10 year and 3 year growth trends, it is clear the JNJ is not growing like 10 years ago.

  1. Absolute 10 Year View

b) 10 Year Growth View

c) 3 Year Growth View
2) Return on Equity (ROE) and OM (Operating Margin)

ROE and OM measures management effectiveness. They also provide an indication of the moat of a company. Here, I look for stability of the numbers. For JNJ, OM is very stable which is a good sign. There is a dip in the ROE during the years 2012-2013 - which would be interesting for further analysis. Overall though, these metrics and trends are very stable.
3) FCF and EPS covering Dividends

The FCF covering Dividends is very important for the safety of the dividend payouts. Based on the JNJ example, it is very clear that JNJ’s dividend is very safe.

4) YoY Dividend Growth

For the dividend growth, I am interested in stable growth. If you see a decline, then check out the payout ratios to see what is happening. For JNJ’s example, there is a slight decrease over the years, but overall, it has been pretty stable since 2010. Therefore, this is the rate of growth I expect going forward.

5) Payout Ratio

As you may know, my threshold for the Payout Ratio is 60%. If a company can consistently maintain their ratio below 60%, then this is a good sign. While JNJ payout ratio has been rising until 2013, it has dropped in the recent years - which is a good sign of management controlling the dividends payout and future safety of the dividends.

6) Debt to Equity

As you may know, my threshold for the Debt to Equity is 50%. If a company can consistently maintain their ratio low, then this is a good sign. For our JNJ example, the Debt to Equity has been very low for the last ten years. This is a very, very good sign!

Overall, it is clear that JNJ is an excellent company to invest in. The debt to equity, payout ratios are solid. FCF easily covers the dividends. Management and moat as very strong. There is a slight stagnation on growth, but for a Dividend Champion, this is forgivable. For me, JNJ is definitely a company to load up on. It just depends on the valuation. (Check out my valuation tool:

Of course, these metrics and the 10 year trends are just part of my overall analysis of a company. I normally try to read the company reports in conjunction with other metrics - e.g. dividend history, etc as well as a KISS view on how the company makes money.

Anyway, I hope the spreadsheet is useful for you. Please note that my investment philosophy will most likely be different your yours. Therefore, please do your due diligence and homework before making any investment decisions.

Thanks for reading!


Disclosure: Long JNJ


  1. Div4son,
    This is a great tool and I admire your ability with Google Docs. JNJ is my #1 holding (4% of portfolio) for all the reasons you mention.

    I am working with several friends on investing to show them how dividend stocks can be a good long term investment and these tools should help.

    As you piqued my interest, I started using the importhtml function to see if I could get the "Dividend Growth Number", (CCC) from into the Stock Criteria Template. So far all I can get is the Default Input which is HCP. I wanted to use this website rather then the CCC list so I don"t have to down load the CCC's every month.

    Here is what I have so far. =importhtml("","table",15)

    I'll keep working at it, but any help would be appreciated.

    Do you follow Seeking Alpha? Check me out there.

    Again Great Blog - very useful.

    Dividend Nut

    1. It is almost impossible to work on a phone, but when there is a will, there is a way. Anyway, the site seems to have a date stamp check which makes it difficult to scrape from google sheets. However, you can access the dividend history using this method:
      Replace KO "Coke" with your A1. This will work until they implement a checksum for the link.
      Thanks d4s

    2. Here's the function:
      In case my answer is misleading.

    3. Gave it a try and it worked fine although it took a minute to retrieve the data.

      Thanks so much.

    4. DivSon,
      As you expected has changed their website and I can no longer get the 5 yr DGR from their website.

      Any chance you could take a second look to see if you can figure out how to import it?

      Dividend Nut

    5. Dividend Nut,

      For 5yr DGR, you can use my article -

      There are two methods:

      1) CSI Market Method

      =substitute(index(importhtml(""&A1, "table"),3,2),"%","")/100

      2) Guru Focus Method

      =index ( importhtml(""&A1&"", "table", 2), 2,5)


  2. Google sheets is a great tool and the import data makes it so easy to perform the analysis and cuts down the time needed. Thanks for sharing with the community. I use the same tool in my analysis and import from morningstar.


    1. Thanks R2R. A long time ago, I used excel add ons. But Google sheet is much easier to grab information from the web.

  3. Hello

    Would you please share my blog in your link list? This blog is about economics, savings and value investing.


    1. LIMG, thanks for checking out my blog. I've added you to my blogroll.

  4. Somehow I missed this post in my feed. That's a great tool and it's definitely very useful. I'll have to play around with it over the next couple of days.

  5. PIP, thanks. Please let me know how your experiments go. I'm always interested in improving the tools.

  6. Thanks much for sharing the spreadsheet -- It popped up on G+ today, 7 months later! It's great!!

    1. Kevin, You're welcome! Better late than never :)

  7. I have downloaded as excel and uploaded back to google drive and editing as google sheet. I am running into this issue: ImportData function is failing with the error message "Array result was not expanded because it would overwrite data in A2."

    1. Never mind. I made a "copy" instead of download/upload and it fixed the issue. Thanks for sharing your work.

    2. Yes. You need to make a local copy - and then modify. I am glad you enjoyed the spreadsheet.

  8. The bar chart X-axis labels are incorrect and are skipping the first value and incorrectly associating data with next in the column label. For example, take a look at the FCV_DIV sheet and the first graph. The data for 2006 is labeled as 2007 and so on. This same problem on everywhere. I tried but unable to fix.

    1. Let me take a look. Can you send me a picture somehow?

    2. It is fixed. In the charts definition, you need to aggregate.
      You can download the updated sheet.

    3. Yes, it is looking good now. Thank you so much.

  9. I am using MarketXLS and it's great.