Thursday, June 18, 2015

Building your own Dividend Stock Criteria Table using Google Sheets

Dividend Growth Stock Blog Hello,


This is Div4Son. FIMonkey recently asked a question on my Dividend Stock Criteria table which I use for my dividend stock analysis. The question was whether I use automation.

First of all, I took the actual format came from various bloggers. In particular, please check out Money Unbinding's set of criteria. 

http://moneyunbinding.blogspot.com/2015/01/stock-purchase-criteria.html





My Criteria Selection

For me, I use the following criteria set:

Quality Criteria


  • Dividend Growth
  • ROE/EPS/Op Margin
  • Long term Avg Growth
  • FCF over Dividends
  • M* Moat
  • M* & S&P 
  • Credit Rating
  • Cash to Debt & Interest Coverage, Debt to Equity
  • M* & S&P Stars
  • Payout Ratio

Dividend & Yield

  • Dividend Yield
  • Dividend Growth
  • Chowder

Valuation

  • Yield/Avg Yield
  • Dividend Yield Theory Mid Point
  • DRRM
  • M* Estimate
  • S&P Estimate

The thresholds of the criteria are based on reading many blogs and books (e.g. Payout Ratio below x% etc). Each investor will have a different thresholds they are comfortable with.

Automation

I use google sheets to store and present the criteria list. The advantage of google sheets is that you can use 'web scraping' to automatically populate the table. (Excel also has an Add-In method where you can grab data from websites).

Please check the following blogs on how to use google sheets:

http://www.tawcan.com/using-google-spreadsheet-dividend-investment/
http://www.nomorewaffles.com/2014/11/using-google-spreadsheets-to-track-your-dividend-portfolio-updated-template/
http://www.allaboutinterest.com/2014/05/using-google-docs-for-your-portfolio.html
http://www.twoinvesting.com/2014/12/investing-formulas-for-google-spreadsheets/

My Example

In the example below, I used a slightly different set of criteria list from my own:

TRV 6/2015AreaCriteriaResult
Quality Company
Dividend Growth>5 years11
M* Quality>BBBA-
M* MoatWide MoatNarrow
Cash to Debt & Interest CoverageCash Debt >1 or Interest Coverage > 5Cash to Debt 0.05
Interest Coverage 13.79
Payout Ratio<60%21.00%
Dividend Growth
& Yield
Dividend Yield>3%2.50%
Dividend Growth>5%11.79%
Chowder>12%14.29%
Valuation
Yield/Avg Yield>1.11.04
Price-101.60
Price Low-88.81
Price High-110.49
P/E<209.87
P/S< Market1.17
P/B< Market1.28

I use a combination of gurufocus, Yahoo key stats, Yahoo CSV API, Morningstar & Google finance to get the desired data.


    Quality Criteria

    Dividend Growth

    For some reason, this one was difficult to grab directly from the websites.However, the CCC list maintained by David Fish provides this data. You can create a local copy of the data you need and then match and index the result you need.

    M* Quality Rating

    This is extracted from the Morningstar website. First, you use the following to dump the data on a separate tab.

    =importhtml("http://www.morningstar.com/credit-rating/corporate.aspx?PageIndex=all", "table", 0)

    In the criteria table, you just match and index to get the result you need.

    M* Moat

    The same method above is used to match and index the results.

    Cash to Debt

    I use the gurufocus website for the cash to debt and interest coverage data.

    =index(importhtml("http://www.gurufocus.com/stock/"&A1, "table", 2),2,2)

    Interest Coverage

    =SUBSTITUTE(index(importhtml("http://www.gurufocus.com/term/interest_coverage/"&A1&"/Interest%2BCoverage/", "table", 7),2,11),"*","")

    Note: A1 is where I put the ticker.

    Payout Ratio

    This is derived from Yahoo key stats.

    =index(importhtml("http://finance.yahoo.com/q/ks?s="&$A1, "table",32),7,2)

    Alternatively, you can use the CCC list to match and index the payout ratio.

    Dividend Growth & Yield

    Dividend Yield

    This is taken from all about interest

    =REGEXreplace(REGEXextract(REGEXreplace(index(importhtml("http://finance.yahoo.com/q?s="&A1&"&ql=1", "table", 3), 8, 2), "[()]", "") , "..[^a-zA-Z][.0-9].%$"), "[%]", "")/100

    Alternatively, you can use the CCC list to match and index the payout ratio.

    Dividend Growth 5 Yrs

    There are several methods used by various websites.

    CSI Market Method

    =substitute(index(importhtml("http://csimarket.com/stocks/Dividends.php?code="&A1, "table"),3,2),"%","")/100

    Guru Focus Method

    =index ( importhtml("http://www.gurufocus.com/dividend/"&A1&"", "table", 2), 2,5)

    Alternatively, you can use the CCC list to match and index the 5 Yr Growth number.

    Chowder

    This is just the adding the dividend growth with the yield.

    Alternatively, you can use the CCC list to match and index the chowder number.

    Valuation

    5 yr Average Yield

    The average yield can be extracted from Yahoo Key stats. 

    =index(importhtml("http://finance.yahoo.com/q/ks?s="&$A1, "table",32),6,2)

    You can then use the yield above to derive the yield over average yield.

    Current Price

    You can use google's method.

    =googlefinance(A1,"price")

    Price High/Low

    Yahoo's csv API have these numbers.

    52 week Low Price

    =IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=j"))

    52 week High Price
    =IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=k"))

    P/E

    =IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=r"))

    P/S

    =IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=p5"))

    P/B

    =IMPORTDATA(CONCATENATE("http://finance.yahoo.com/d/quotes.csv?s=",A1,"&f=p6"))

    I've included the P/E since this is one of the most common method to use valuation. You can change to use forward P/E also.

    P/S and P/B are also mentioned in the SBI book as indicators for valuation.


    Google Sheet Example

    You can download the example using the link below:

    https://docs.google.com/spreadsheets/d/14yzNrbtnPFl0H1gZ6RCd3sG6AiWl7r6EWpOGtQZk2lA/edit?usp=sharing

    I think you will need to make a local copy on your google docs account to make any modifications.

    Also, you can modify the spreadsheet accordingly for multiple tickers, and then use this as your own screener.

    --

    That's it for now! Please let me know if this is useful for you. 

    Div4Son


    • Note 1: Please use caution with the information from third party sites. It is best to visit the company's IR page to double check the numbers. Moreover, the location of the data may change so you need to change your spreadsheet accordingly.
    • Note 2: Please respect the web-site's rules - e.g. The information should only be used for personal, non-commercial purposes.
    • Note 3: Sometimes, you get stuck with the data "Loading" into your sheet. When this happens, just wait. You can try to reload also. 


    11 comments:

    1. Thanks for the great resource and explanations of each item. I plan on reviewing my own portfolio watchlist and seeing how i can improve it with what you have. Nice work!

      ReplyDelete
      Replies
      1. Adam, thanks. The key is to personalize the watch list according to your own investment style. Please also let me know if there are other metrics you want which I haven't covered.
        D4S

        Delete
    2. Thanks for sharing, Div4Son. i use several of the methods described here...love the google spreadsheets.

      R2R

      ReplyDelete
      Replies
      1. R2R, thanks for visiting. Yes, I love google sheets especially the fact that you can access the data using multiple devices!
        D4S

        Delete
    3. Wow, impressed - I use google spreadsheets just to track my investments across different brokers never even though of pulling data from other sources to evaluate anything. Saving it now, will let you know how it goes.
      -Rich

      ReplyDelete
      Replies
      1. Rich,
        Thanks. I am glad this is useful for you. Just ping me if you need me to look at other metrics to pull.
        D4S

        Delete
    4. D4S,

      First of all, thanks for the mention!

      Secondly, information is incredible and I did not realize you could do that with Google Sheets! Holy crap. I'm definitely bookmarking this page for future use.

      I think more people should hear about this.

      FM

      ReplyDelete
      Replies
      1. FM, I am glad you liked the article. Just ping me if you need other metrics/ratios/KPIs.
        D4S

        Delete
    5. I had been using this. It really works for me just fine.
      http://marketxls.com/stock-quotes-in-excel/

      ReplyDelete
      Replies
      1. Appreciate the comment - but you need to pay a hefty sum to use marketxls. If you want to use excel, I would suggest learning how to use the free SMF add in. I used this many years ago - but ended using google sheets now since it is portable.
        D4s

        Delete
    6. I had use this. It works great for me.
      http://marketxls.com/stock-quotes-in-excel/

      ReplyDelete