Fat Pitch Finder Spreadsheet

Monday, July 31st, 2006 | Research Tools, Stock Tools, Valuation Tools with 32 Comments

I put together a spreadsheet to help me to track a list of wide moat companies that I’m interested in investing in for the long term. The spreadsheet automatically gathers financial info for the past 10 years from various websites. Feel free to download this beta version of my spreadsheet called Fat Pitch Finder 0.1.

You need to have Excel and install an Add-In to make it work.  The add-in you need is RCH_Stock_Market_Functions.xla at the smf_addin group files.  Be sure to install the add-in in the directory  C:\Program Files\SMF Add-In  in order for the spreadsheet to work.

I included my analysis of eBay Inc (EBAY) on this spreadsheet since some of you were interested in my intrinsic value calculation. Please provide feedback on the sheet.  I’m especially interested in your thoughts on the DCF models. 


Comments

  • I think there is a bug in this calculation. See what it does when you calculate for LUFK, it gives a hugely negative MOS …

    jonathan weitz July 31st, 2006 at 3:51 pm
  • I don’t believe there is an error with the calculator. I think the problem is that LUFK has had a major change in working capital (see the link). LUFK has increased inventories, capital expenditures, and account receivables. The large difference between free cash flows and net income indicates that earnings “quality” that is being reported could be rather low. If you own this stock you might want to explore further why earnings quality is rather low and determine if it is something to be concerned about.

    Another thing to note, if R-squared (a measure of how good the data is explained by the linear regression) is less than 0.75, you really should not rely on the resulting intrinsic value and margin of safety estimate.

    George July 31st, 2006 at 11:11 pm
  • Thanks for posting this, George. I’ve been interested in the process other value investors use to find potential candidates for investment. Right now, I have access to historical financials in excel format via Thomson Research and do some ratio calculations as a first pass, but that access won’t last forever. I’ve never tried using regression to determine cash flows, so this is interesting.

    Couple of questions/comments:

    - What source(s) does the .xla use to pull information? I couldn’t find info under the documentation section for the plug-in. Maybe I wasn’t looking in the right place.
    - Being able to change the discount rate and steady-rate growth from the first sheet is nice and allows sensitivity analysis. In previous models I’ve used, their creators have chosen to use 5% as the stable growth rate, citing population growth rate + inflation rate as the two components of steady-state growth. 3% is more conservative, of course.

    ricemutt August 2nd, 2006 at 2:02 pm
  • Hi ricemutt -
    The Excel Add-In .xla file pulls information from all the major online sources: Yahoo, MSN, Business Week, BarCharts, ADVFN, Morningstar, Zack’s, Goolge, and Reuters. My particular spreadsheet primarily gets data from ADVFN and MSN.

    I like to use the long-term GDP growth rate for the stable growth rate. I believe when I last looked it up, the long-term GDP growth rate was around 3.5 percent. I’m a rather conservative investor, so I rounded down to 3 percent. I would not use population growth rate plus inflation because neither of those two are directly correlated to earnings growth. Heck, both of those forces, population growth and inflation growth, can lead to devistating economic consequences (social strife) that could lead to very low stable growth rates. I think you’ll agree that when a country’s GDP growth rate is climbing that it’s business are prospering. You couldn’t say the same for inflation and population growth or even population decline.

    George August 3rd, 2006 at 7:56 am
  • Ricemutt:

    …What source(s) does the .xla use to pull information? I couldn’t find info under the documentation section for the plug-in. Maybe I wasn’t looking in the right place.

    The documentation that lists all of the elements for RCHGetElementNumber() has a column “Source”. That value identifies the web page the element is being retrieved from. Most are Yahoo, Google, MSN, etc. You can get the source value for any element yourself with a formula similar to:

    =RCHGetElementNumber(“Source”, 6856)

    Randy Harmelink August 4th, 2006 at 9:14 pm
  • Thank you both for the very helpful explanations!

    ricemutt August 4th, 2006 at 10:31 pm
  • George,

    Which version of RCH_Stock_Market_Functions.xla are you using? I grabbed the latest from the SMF group website (1.3f) and when I open your Fat Pitch xls, I get the error….

    “The workbook contains one or more links that cannot be updated”

    I have everything parked exactly where it needs to be – C:\Program Files\SMF Add-In – any ideas?

    Jason December 14th, 2006 at 2:17 pm
  • I just opened up the spreadsheet link and everything worked fine for me. Some versions of EXCEL don’t handle add-in links properly. It appears Microsoft changed how add-ins were handled from version to version. I haven’t found an easy way to save workbooks to get around all of the problems. The common installation path solved the problems on a few versions of EXCEL, but not all.

    However, if you tell it not to update links, you’ll get all of the formulas with the hard-coded location of the add-in as George saved it from his machine. You could always do a “Change All” on the formulas — just strip off all of the location information so you’re just left with something like:

    =RCHGetElementNumber($A6,4)

    Randy Harmelink December 14th, 2006 at 3:44 pm
  • The key thing is to make sure you do not open the spreadsheet directly from the link on this page. The spreadsheet must be first saved to the C: drive before opening it. I just learned this the hard way today when I tried to work off of these sheet from another computer. If you first open the sheet up by accident before it is saved to the C: drive or the plugin is installed, you must quit Excel completely or it will continue to screw up things.

    George December 14th, 2006 at 4:30 pm
  • I am using version 1.3f of the SMF Add-In.

    George December 14th, 2006 at 4:33 pm
  • After you put a ticker in, is there supposed to be a lag time of around 10 seconds or so? Excel actually freezes while it is retrieving data. I tried it on both my work laptop and home PC and had the same issues.

    Jason December 15th, 2006 at 9:32 pm
  • There will be a lag time. It’s not really “frozen” — it’s in the midst of recalculating. The add-in functions are retrieving data from the Internet and parsing the source code to extract the data requested by the various functions. You should see the cursor as an hourglass and/or the status line should indicate recalculation — but the symptoms vary depending on which version of EXCEL is opening the workbook.

    The lag time will depend on how many unique ticker symbols are on the worksheet when you open it. You can minimize it by saving the worksheet on your computer with only one data line and using “None” as the ticker symbol, or just leaving the cell empty. Either of those values for a ticker symbol cancels the Internet retrieval process of the add-in functions. However, the functions will return a value of “Error” when those ticker symbol values are used. And any calculations are likely to return the “#VALUE!” error message.

    Randy Harmelink December 15th, 2006 at 10:21 pm
  • Randy,

    Thank you for answering Jason’s question. I think on the next version of the spreadsheet, I’m going to use “None” as the ticker and only use one line of data to start. That should make it a bit easier to open and start using.

    George December 15th, 2006 at 11:42 pm
  • Don’t forget that the DCF model is “nominal” model. The 3-3.5% GDP growth rate is a “real” (excludes inflation) growth rate. The long-term nominal GDP growth rate has been about 7%. For any assumed required return, this would cause high prices if you rely on the DCF model.

    Sam February 8th, 2007 at 6:19 pm
  • Hi, I downloaded the Fat Pitch spreadsheet and also the smf_addin from Yahoo files and placed the addin in C:\Program Files\SMF Add-In.

    The spreadsheet will not update, I get an error that it cannot find RCH_Stock_Market_Functrions.xla, but it is in the SMF Add-In folder, any suggestions?

    I am using Excel 97 SR-1

    Thanks

    Leon March 18th, 2007 at 8:54 pm
  • Did you activate the add-in? That is, is it on the list with a check mark in front of it when you use menu option:

    > Tools > Add-Ins

    If not, you need to activate it by using the Browse button on that page to locate it and tell EXCEL where it is located.

    However, if I recall correctly, I think there are VBA functions the add-in uses (e.g. SPLIT()) that weren’t part of the VBA function set on EXCEL 97, so you may run into problems there…?

    Randy Harmelink March 18th, 2007 at 9:53 pm
  • I added in the file through >Tools>Add-Ins and browse. I noticed that there are two files not checked, Analysis ToolPak and Analysis ToolPak – VBA. I did not check those as yet.

    When I try to run the spreadsheet I get a message that says cannot find Microsoft Shared\VBA\VEENLR3.HLP file. Apparently that file does not exist on my machine. Any idea where I can download it from?

    Appreciate the reply and help.

    Leon March 20th, 2007 at 10:49 pm
  • You shouldn’t need the Toolpak add-ins.

    For the missing help file, see: http://support.microsoft.com/kb/158224

    But I wouldn’t think that would prevent the add-in from working. What I suspect is that it’s trying to display an error, but having that file missing is disabling its ability to put up the error message. The reason I suspect that is that I think the typical message for EXCEL 97 is a compiler error message related to the missing SPLIT() function, and VEENLR3.HLP is related to the VB editor.

    So the add-in may not work on your machine, with that version of EXCEL. I have seen a suggested solution, but can’t try it myself as I only have EXCEL 2002 — the suggestion was to add these two functions yourself, since EXCEL 97 doesn’t have them:

    Public Function split(ByVal source As String, ByVal delimiter As String) As Variant
    Dim ld As Integer
    Dim result() As String
    Dim rs As Integer
    rs = 0
    ld = Len(delimiter)
    Dim i As Integer
    i = InStr(1, source, delimiter)
    Do While i 0
    rs = rs + 1
    ReDim Preserve result(0 To rs)
    result(rs – 1) = Left(source, i – 1)
    source = Mid(source, i + ld)
    i = InStr(1, source, delimiter)
    Loop
    result(rs) = source
    split = result
    End Function

    Public Function replace(ByVal source As String, ByVal searchfor As String, ByVal replacewith As String) As String
    Dim ls As Integer, lf As Integer, lr As Integer
    ls = Len(source)
    lf = Len(searchfor)
    lr = Len(replacewith)
    Dim i As Integer
    i = 1
    While i

    Randy Harmelink March 20th, 2007 at 11:11 pm
  • I’ll try that last one again — the “less than” sign terminated the comment:

    Public Function replace(ByVal source As String, ByVal searchfor As String, ByVal replacewith As String) As String
    Dim ls As Integer, lf As Integer, lr As Integer
    ls = Len(source)
    lf = Len(searchfor)
    lr = Len(replacewith)
    Dim i As Integer
    i = 1
    While i <= ls + 1 – lf
    If Mid(source, i, lf) = searchfor Then
    source = Left(source, i – 1) + replacewith + Mid(source, i + lf)
    ls = ls + lr – lf
    End If
    i = i + 1
    Wend
    replace = source
    End Function

    Randy Harmelink March 20th, 2007 at 11:13 pm
  • Randy,
    Thanks for helping Leon with his technical problems using the Fat Pitch Finder Spreadsheet.

    Everyone please keep in mind that this spreadsheet was created on Microsoft Excel 2003. It might not work well on previous versions.

    George March 21st, 2007 at 8:33 am
  • You are correct Randy, I downloaded the VEENLR3.HLP file and now it tries to run, but I get an error message that says – Compile error Expected: end of statement.

    I am green when it comes to programming, so if you can tell me how to add the two functions I will do it.

    Thanks again for your help.

    Leon March 21st, 2007 at 9:44 am
  • You’d just need to cut and paste those two functions and place them at the end of one of the code modules of the VB editor. But I’m not sure they will even work, since I haven’t done it myself.

    Randy Harmelink March 21st, 2007 at 9:51 am
  • Well maybe I better start looking for a reasonably priced copy of Excel 2003.

    Thanks for all your help.

    Leon March 21st, 2007 at 10:41 am
  • I found an upgrade to Excel 2003 on the internet for about $80.

    Would that upgrade my Excel 97 to Excel 2003?

    Leon March 21st, 2007 at 10:52 am
  • A great program which works very well with all versions of Excel is XLQ from http://www.qmatix.com. I’ve used it for two years and have been very pleased with its ability to very quickly retrieve historical & real time stock and option quotes from several different feeds into an Excel spreadsheet. It has hundreds of powerful functions and cost about $100/yr.

    Spencer June 24th, 2008 at 6:53 am

Leave a Comment

Funded By