Thursday, February 17, 2011

Rule #1 Analysis Spreadsheet: Updated with Auto External Data Import

My cousin, Kelvin, works a major Canadian bank.  So, he's used to working with automated spreadsheets with VB scripts, etc.  So, when I sent him my manual Rule #1 spreadsheet, he mocked at how rudimentary it was.  So, thanks to him, I've decided to educate myself on how to further automate my spreadsheet.  I already knew how to pull external data from websites, but didn't know how to dynamically update the query when I changed the stock symbol.  The solution was simple: use the record macro function on Excel.  What I did essentially was record how I would manually change the web query and modify the code that was recorded.  It actually took about 10 minutes to make the change.  I wonder why I didn't do that earlier!

So, with all its glory, I present to you my automated Rule #1 Analysis Spreadsheet!  With this spreadsheet, you really only need to look at 3 tabs.

  1. Go to the "Stock" tab.  Fill out the 3 pieces of information there.  Click on "Refresh Data" and wait for Excel to update the data from the web.
  2. (Optional) Go to the "Sticker Price" tab.  Change the growth and P/E numbers if you want.
  3. Go to the "Summary" tab.  Look at the results.  Voila!
The previous spreadsheets weren't bad.  One would need to spend about 2 minutes populating the data by copy and pasting.  Now, you can save about 90 seconds every time you use the sheet!

The story with Kelvin didn't just end here.  I sent the spreadsheet to my friends (thanks Jit), including Kelvin, for some real-life stress testing.  The sheet worked perfectly, for all of them...except Kelvin.  D'oh!  I use Excel 2007 and he's got 2003.  I believe that could be the root cause of the problem.  Since I don't have Excel 2003 on my computer, I couldn't do much debugging.  I've given up!

Instead, I realized that there could be potentially a better solution: I moved to Google Spreadsheets!  Now, investors, take note.  This is a classic example of how Google continues to draw user base from Microsoft, and how cloud computing is starting to take hold.  Mind you, I'm almost always an early adopter in technology...In any case, I'm now working on a Google Spreadsheets version of the Rule #1 spreadsheet.  I have it running already, but there is some fine tuning to be done.  If you want to check out the current version, it's here: http://goo.gl/goru5.  You'll need to log into your Google account and make a copy of the file in order to actually use it.

Give me some feedback, on either version of the sheet!  Enjoy!

Update December 3, 2011: I've updated the sheet.  Go to the Investing Resources to download the latest sheet.

24 comments:

  1. Felix, enjoy the blog. I am new to rule 1 investing and investing in general; about to read payback time here soon. I have been paper trading some and wanted your feedback on Capital One Financial (COF). It is trading below book value but just a quick overview shows 10 yr equity growth of 16% and EPS growth of 10%; haven't found ROIC yet. After doing a quick future price calculation its approximate MOSS is $41-$42. What do you think???

    On another note, I am short Under Armor, but love that company.

    ReplyDelete
  2. Hi Hamp,

    This is the Rule 1 analysis that I ran on COF (http://goo.gl/DLNM4). There was no sales data for the past 10 years. So, I just used the EBIT numbers instead of sales. The numbers aren't that bad. ROIC has not been great. EPS growth was severely stunted during the crash, but seems to have recovered nicely. FCF looks kinda funny...I'd say this is a borderline Rule #1 stock. The MOS I calculated was $21.37 (sticker price of $42), however, using EPS growth of 9.8% and P/E of 10.

    What numbers did you use to get $41 for MOS? Seems like the stock is overpriced currently.

    I've looked at UA a few times in the past, but it was always overpriced. It seems like it still is now. Is this the reason why you are short it? I would almost never short a stock that I like. If it's overpriced, I would just wait for a pullback, but I wouldn't short it, because there's probably a good reason why you like it, and others make like it even more to drive up the price, even as it's a bit overpriced.

    ReplyDelete
  3. You're right; what I didn't do is use the smaller number of BVPS growth vs. projected EPS (5 years) to calculate.

    What is your rationale when you say that you wouldn't short a stock like that (UA)?

    Your spreadsheets are awesome and save a ton of time..I acutally made up some very simplistic excel spreadsheets after reading rule 1, but it is really neat to import all the data.

    ReplyDelete
  4. When I talk about not liking shorting stocks like UA, what I mean is, the stock looks like a Rule #1 stock by its numbers. It may be overpriced right now, but its price may continue to rise and be elevated for a long time. At the same time, since it's a Rule #1 stock, there's a high chance its earnings will also continue to rise. So, even if the price drops back to a reasonable P/E ratio, your position may still not be profitable in the end. My opinion is that if you're going to short a stock, it's probably better to short a stock that's not profitable, has a lot of debt, and doesn't look like it's about to turn around soon.

    ReplyDelete
  5. Makes good sense; thanks for the input.

    ReplyDelete
  6. Hey Felix, your sheet works great. Thanks a lot. I read rule 1 investing and i like the concept. But havent read the second book - payback time.

    Also what is the payback time in the excel sheet - does it mean if the growth continues at the same rate it will take "x" years to get our investment. Pls elaborate.

    ReplyDelete
  7. Hi KB,

    Thanks for the compliments!

    Payback Time is slightly different strategy than Rule #1. The fundamentals are all the same, but the buy-sell strategy is different. In Rule #1, you use technical indicators to buy and sell. Because of the volatile nature of the market in recent years, Rule #1 investors often jumped in and out of stocks, resulting in, as Phil Town calls it, "death by a thousand cuts".

    So, Payback Time uses the idea of accumulating a stock at low prices, without selling until you make lots and lots of money. First, you need to evaluate the company by Rule #1 standards. If the stock is a Rule #1 stock, then you proceed to calculating Payback Time. It is simply the time it takes for earnings to accumulate to the value of the current stock price, assuming a certain growth number. If Payback Time is <10, you're good to start accumulating the stock. However, it's best when the number is closer to 6, which is almost like a lower limit for payback time.

    You don't literally get your investment back, because if the company doesn't pay dividends, there's nothing to be gotten unless you sell. So, I believe the idea behind the payback time methodology is that as the company accumulates cash from earnings, the net assets of the company will rise. Eventually, investors will realize the value of the company and the price will adjust up accordingly.

    I don't know if this answers your question...let me know. Cheers!

    ReplyDelete
  8. so lower the payback no. the better?

    ReplyDelete
  9. Yes, that is correct. They shorter the better. It's like you replacing an old water boiler with a newer one. The new one saves you natural gas costs, but you also have to pay a sum initially for it when you bought it. The savings will "pay back" your initial investment at some point in the future. Obviously, the shorter the better...this is a similar concept.

    ReplyDelete
  10. Seems like there is an error with your sticker price calculation. Compared to Phil's calculator, it is not factoring in the future PE to calculate future value per share. Am i missing something?

    ReplyDelete
  11. I am indeed using future P/E to calculate the Sticker Price. I first find the 10-year EPS by taking the current EPS and then calculating what it will be in 10 years using the estimated growth rate or historical BVPS growth rate. I then multiply that EPS by the P/E (the lower of historical P/E and 2 x EPS growth rate) to find out what the future stock price would be, and then work backwards assuming we need 15% growth.

    Let me know if you're still confused! Thanks.

    ReplyDelete
  12. I see what I was misinterpreting: in my initial calculations (before stubmling upon your fantastic workbook!), I was using a higher multiplier b/c the "average" historical PE was higher for my stock. In your version, it appears you are taking the minimum PE from the last three years (which, in this case, is much lower than the average). Thanks!! I just may have to manually adjust this one!

    ReplyDelete
  13. Awesome! Just use the "User Input" cell to override the automatically calculated values. Cheers.

    ReplyDelete
  14. Felix,
    Your dedication to this blog and your values has been an incredible inspiration for me. Your Rule 1 spreadsheet is fast becoming my MOST used tool in my analysis. In fact I have found that I am spending way more time trying to find companies to plug into your Rule 1 sheet then I am actually locating stocks to buy.

    I modified a tracking spreadsheet that I have been using to import the values from your spreadsheet and then compare those to the 52 week numbers and current price. How difficult would it be to create an automated Rule 1 screener that uses your spreadsheet to scan the 10K+ stocks and output the ones that meet Rule 1 criteria?

    Please feel free to contact me at bradc@racolby.com

    ReplyDelete
  15. Hi Brad, seriously, I was thinking about this just this past weekend!! I haven't explored Google Docs too too much, but I think I can have a sheet with all the symbols listed and write a short script to run through the symbols and spit out the Rule #1 results for each. It shouldn't be too difficult, but would be time consuming. I’ll try to squeeze this in amid moving into a new house and preparing for a new addition to the family!! Hopefully, I’ll get something running in the next few weeks. Cheers, Felix

    ReplyDelete
  16. Hey Felix!

    CONGRATULATIONS on your newest addition! We have 5 so I can understand the excitement and nerves. Good luck with the move as well, although that is a significantly smaller challenge!

    Interesting how people are put into each others path isn't it? I build pipe organs www.racolby.com and have seriously turned to investing to protect my future as well as to insure that my wife and I won't be a burden on our kids during retirement. As you might imagine the economic slowdown has had a dramatic effect on our "cyclical" product.

    In any event as I mentioned in an earlier post, I found your blog as I began putting together my own Rule 1 spreadsheet! Your work was much more elegant and detailed than mine was, so I have been using diligently since!

    All of that is to say that I don't feel it is fair or ethical for you to do all of the work if we are traveling the same road together. While I don't know much at all about writing scripts within Google Docs, I am more than willing to share the burden of creating it. I haven't put much thought into it's structure at this point but can brainstorm the idea and creation in any way that you feel would best reach our goal.

    Thanks!

    ReplyDelete
  17. Brad, thanks! Rule #1 has really been an eye-opener for me and that's why I've dedicated so much time/effort into making a usable spreadsheet. Don't worry about the workload...after a few hours of learning some new syntax, I've got a working spreadsheet!

    You can probably help me do one thing: find a list of all stock symbols available (you can email it to me). Once we have that, I think we'll be off to the races! I just need to test the sheet out a little more and polish it up.

    I'm excited about this!

    ReplyDelete
  18. I like keeping the human part in the investing process.

    ReplyDelete
  19. The latest version of the Excel spreadsheet is throwing an error message from the VBA code (I'm running under 2007). I realize that you've moved on to Google's spreadsheet. Just thought you and others might be interested. From what I can see.... I REALLY like what you've done.

    And isn't it great when you can add some code to a spreadsheet? Adds a whole new dimension.

    ReplyDelete
    Replies
    1. Hi, I think the error occurred because MSN Money, which is where the data is pulled from, has changed its site since I last updated the sheet. There's been a number of updates made to the sheet in the Google version. I do encourage that you check that one out! Thanks.

      Felix

      Delete
  20. Hello Felix, The Google Sheet 1.3 is no longer pulling in the Sales Growth numbers. Do you think there is an communication issue with MSN Money? Thanks

    ReplyDelete
    Replies
    1. Hi Jason, should be fixed now...try using the "locked" version of the sheet and make a copy for your own use. Should be more robust! Let me know how it goes!

      Delete
  21. Hi felix, I tried to download your excell sheets and i keep getting error messages. Can you help me

    ReplyDelete
    Replies
    1. Hi Richard,

      I tried downloading the file and it works...HOWEVER, I've stopped supporting the Excel version of the spreadsheet and have moved on to a Google spreadsheet version. Please see the following post for details:
      http://catholicinvestor.blogspot.ca/2012/01/rule-1-analysis-spreadsheet-minor.html

      Let me know if you have any issues. Thanks.

      Felix

      Delete