Posted by: sanjayshetty | October 15, 2007

Automatically Scanning the S&P 500 using Fwallstreet.com method

My quest for an easier method to find stocks worth analyzing in detail, got a boost by user PeteKoch(from the http://www.roicommunity.com) who had modified the basic spreadsheet created by Joe Ponzio of FWallStreet.Com to allow one to enter any stock symbol and automatically pick the values from MorningStar etc. and supply the values to Joe’s original spreadsheet. Pete was using the amazingly interesting Stock Market Functions add-in developed by Randy Harmelink which is available at http://finance.groups.yahoo.com/group/smf_addin/. A user at FWallStreet Robert mentioned that he had written a program to scan the S&P 500 and come up with possible investment candidates using Joe’s methodology. However since he was still refinining his program, I thought, hey! why not me write a simple macro which does this using the one Pete had modified. The file mentioned below is the result of that experiment. It saves me hoursssssssssssssssss and I hope you will benefit from it too. I’ll keep updating this Spreadsheet as and when time permits. The macro program only shows you candidates where the FCF and CROIC are greater than 15% (you can change these values via parameters) and the current share price is lower than estimated Share Value or Intrinsic value.

Ok, I know you’re wondering how do I get/use it? 
Note 1. Consider the stocks turned up as candidates worthy of further research, not a recommendation or a buy list.
Note 2. Don’t scan all 500 at one go, first scan one at a time, on the “Main” sheet, then scan in sets of 25, as the processing time will be HUGE  for 500 at one go which might cause your computer to slow down, unless you have a computer with 1+ Gig of RAM and a core 2 duo processor and a good high speed internet connection, then go for it.)
 
Step 1.
Goto http://finance.groups.yahoo.com/group/smf_addin/

Step 2. Download and install Randy’s add-in (It’s located in the Files section, in a folder called “Add-In Files”).
Without Randy Harmelink’s add-in installed my program is of no use. You can find instructions in the Files ->Documentation section on the yahoo group. ( The instructions are pretty simple, download the zip file containing the add-in and its supplementary files and extract all of the files into the “C:\Program Files\SMF Add-in” file folder on your computer. Then open Excel, make sure a blank workbook is open, choose the menu Tools->Add-Ins, in the dialog box which opens choose Browse and choose the folder “C:\Program Files\SMF Add-in” and then choose the file “RCH_Stock_Market_Functions.xla” and click OK.
The forum on the site, has all answers to question’s incase you get stuck installing the add-in. Don’t ask me look thru the forum there 🙂
(P.s. If you’re looking for Pete’s modified file it’s located in the section, Files > Uploads by forum members and is called FWallStreet_Model.xls .)

Step 3. Download my file -> FWallStreet_Model modified for S&P500 2003 version 1.0

Step 4. Rename the extension of the downloaded file to .xls. (Note: You require Microsoft Excel 2003)

Step 5. Open my file, and read the basic documentation given on the Notes Sheet, down to the limitations section. Excel might warn you about Macros, if you trust me :-), you need to enable macros to allow my program to run.
Note: The first time the file opens it will do some processing to update data in the Main sheet from the web, so hang in there. Remember I’ve already run the program and done one scan of the S&P 500, results of which, you will find in the sheet called “Candidates”. Remember scanning thru 500 companies of the S&P 500 is going to take a lot of time. So best would be, that if you run the program, do it when you have loads of time and good Internet bandwidth. If you need to run Excel in the interim just start another instance of Excel to continue other work.)

Step 6. Settings: This sheet, allows you to control the various parameters for the scan, median FCF, median CROIC and you can set the scan start row and end row, you would use this if you want to scan smaller sets of data at a time. For e.g. while I was building this macro I often scanned 25 companies at a time, as I was really impatient to see the results 🙂 In addition I set the  median FCF and CROIC to be above 15%. You can change these settings and do your own scans.

Step 7. Running the Program:Go to the Settings Sheet, and click on the graphic called “Run Program”. This will start the macro which will start scanning and finding likely candidates. Note: Incase you want to stop the program from running, Press the following keys together Control + Pause (or Break).

Note 1: The list of S&P 500 companies I picked from http://en.wikipedia.org/wiki/List_of_S%26P_500_companies
Note 2: Only companies where the current price is lower than the Buy Under price are added to the candidates sheet.
Note 3: If for whatever reason you want to interrupt and end the program go to excel and hit the Control Key + the Pause key. 

I believe the S&P 500 list does change over a period of time, the official source is out here you can also download the list out there as a .csv file and replace the list in my Excel sheet, S&P 500. IMPORTANT don’t delete the first row in my sheet.

Advertisements

Responses

  1. You are awesome. I name you “Shetty Dhandho”!

  2. Given that Joe Ponzio said that he prefers using 75% of CROIC in his valuation instead of free cash flow growth rates, users might want to play around with the formula in cell J20 to reflect that. Or, just punch it in manually like they have set up in cell c2.

  3. Not cell J20, cell G41

  4. Hi Allen,

    Joe seems to have changed his viewpoint as in his earlier posts and analysis of JNJ and KO etc., he was using Median FCF as the parameter for projecting Future Free Cash Flow growth.

    Quote from http://www.fwallstreet.com/blog/4.htm
    “JNJ’s Free Cash Flow grew an average of 16.1% in various timeframes—and that is the basis for our projection of its future cash. Because of JNJ’s multi-year consistency in the past, we can reasonably expect JNJ to grow its Free Cash Flow 16.1% in the future—at least for ten years. Beyond that, we can expect growth to slow down—say, to 5% a year. (If we’re wrong and JNJ grows 16.1% for twenty years, we end up making a lot more money.”

    The good part of the Excel sheet is that you can fine tune the criteria with parameters you feel matter. 🙂

  5. What’s the purpose of Step 4? Why is it a DOC file as a download? Why not create the file in Excel in the first place? This step doesn’t make sense.

  6. Hi Richard,

    The reason the file has a Doc extension is because WordPress doesn’t allow one to upload .xls files, hence I had to use this as a workaround.

  7. Sanjay:

    firstly, I wanna say a big ‘Thank you~!

    This is an awesome effort and more importantly I am learning so much from you and Joe!! Kudos on the job

    In your spreadsheet you have recommended to scan 25 companies at a time
    Please advise back in an email or on this post if these are the following steps

    1. Clear the candidates tab from row 2-13
    2. Run the macro from Run programs , looks like it is picking the first 20 companies from the S&P 500 tab
    3. Now you should see a new list of the candidates
    4. Rerun by changing Startrow as 20 and EndRow as 40 ?
    5. Hopefully more new candidates will show in the candidates tab

    Is this approach correct?

    Appreciate your response

    Thanks

  8. Yes

  9. I love the spreadsheet. I have been using the addin for some time and have been looking at the spreadsheet(s) from fwallstreet.com. This is a good automated scanning method that incorporates both.

    1) A couple of comments. In your code there is a line

    If (.Range(“J19”).Value > CROIC) And (.Range(“J20”).Value > FCF) And (.Range(“E2”).Value CROIC) And (.Range(“J19”).Value > FCF) And (.Range(“E2”).Value CROIC) And (.Range(“FCF_Growth”).Value > FCF) And (.Range(“Current_Price”).Value < .Range(“Buy_Under”)) Then

    I did this for the Ticker range and some others. It made it so I could add rows and columns to the main page for additional reference material while not breaking the code functionality.

    Thanks again for putting the spreadsheet together.

  10. I love the spreadsheet. I have been using the addin for some time and have been looking at the spreadsheet(s) from fwallstreet.com. This is a good automated scanning method that incorporates both.

    1) A couple of comments. In your code there is a line If (.Range(“J19”).Value > CROIC) And (.Range(“J20”).Value > FCF) And (.Range(“E2”).Value CROIC) And (.Range(“FCF_Growth”).Value > FCF) And (.Range(“Current_Price”).Value < .Range(“Buy_Under”)) Then

    I did this for the Ticker range and some others. It made it so I could add rows and columns to the main page for additional reference material while not breaking the code functionality.

    Thanks again for putting the spreadsheet together.

  11. I love the spreadsheet. I have been using the addin for some time and have been looking at the spreadsheet(s) from fwallstreet.com. This is a good automated scanning method that incorporates both.

    A couple of comments.

    In your code there is a line If (.Range(“J19”).Value > CROIC) And (.Range(“J20”).Value > FCF) And (.Range(“E2”).Value < .Range(“G2”)) Then

    I believe the CROIC and FCF cells are reversed based on the cell locations on Main page. It doesn’t make a difference if you leave them both at 15%, but will if you change them.

  12. 2) You could name the ranges the code uses so modifications to the pages don’t interfere with the operation of the code. For example, name the cell “J20” on Main page “FCF_Growth”. (Insert/Name/ Define). Using that logic the code becomes If (.Range(“CROIC_Growth”).Value > CROIC) And (.Range(“FCF_Growth”).Value > FCF) And (.Range(“Current_Price”).Value < .Range(“Buy_Under”)) Then

    I did this for the Ticker range and some others. It made it so I could add rows and columns to the main page for additional reference material while not breaking the code functionality.

    Thanks again for putting the spreadsheet together.

  13. hi there.

    i have a problem using the “fwallstreet_model-modified-for-sp500-2003-version-10” on a pc running vista. first of all, you can’t just rename a .doc to .xls. at least i can’t because i usually work on a mac where you just have to change the ending.
    second, you can’t create a folder called “program files” under C:\. i am using a german version of vista and can’t create a folder named “program files” because there is already a folder named “programme”….you got to love vista.
    does anyone have the same problems? can anyone help? thanks in advance!!

  14. awesome, Sanjay. Great posts here and at fwallstreet. Good luck.

  15. Does this require excel 2003 or will it work with excel 2007 also?

  16. And while I am at it, can one use excel 2000? Here is my situation. I have excel 2000 but I could not get it to work. I downloaded the 60-day trial of office 2007 however it will not allow you to add add-ins with the trial version.

    So I am trying to figure out if I can use 2000 or if I need to sink some money into 2003 or 2007.

  17. unfortunately I don’t have access to Excel 2000 to test it out, btw I use it using Excel 2007 and it does work alright.

  18. Hi Sanjay,
    I have been trying your excel spreadsheet for analysis. However I keep on getting a runtime error ‘438’. Since I am not a program this is well beyond my expertise. Also it keeps giving me a number of errors in the morningstar sheet so I am assuming that there is also another issue when getting the data from the web.
    G

  19. Hi Girish,

    Could you tell me which version of Excel you are using?

    Also have you followed the installation instructions for the smf addin?

  20. Sanjay,
    Great spreadsheet and is very helpful in crystallizing some of the investment ideas I already have.

    I am using the version that you have created for 2003-V11 which I think is working fine (I was using the earlier version earlier) with the exception of the financial and utility stocks. I will just work on those in more detail and have some fun there.

    How do you suggest that I can change the spreadsheet to evaluate NYSE stocks or stocks listed on other indices?

  21. Hi Girish,

    You can use the excel with a list of any scrips listed in the US markets, just replace the data in the sheet called “S&P 500”

    Regards,

  22. Awesome.
    Another question and maybe you can help with this. Some of the securities have special characters in their symbols like ” -, ^”. Classic BRK-A (Berkshire Hathaway Hld- A class). However the spreadsheet does not read the data from the web for such symbols. Any workaround suggestions?

  23. I haven’t noticed any problem with symbol’s I tried brk.a

    However, Note: Morningstar doesn’t provide cashflow data for Berkshire, if you notice in the Mstar sheet, only partial data is retrieved.

  24. aaha.. that explains it for Berkshire. thanks

  25. Sanjay,
    Great job on the automated scan. Thank you for your work on this. Question to this forum: Is anybody getting an error msg along the lines of “Too many web pages retrieved”
    I suspect that M* is probably watching for screen scraping in large volumes and blocking. I’m getting this error by the time I’ve processed around 300-400 stocks.

  26. Nik,

    Yes MorningStar will block extensive multiple requests, hence I’ve recommended scanning for a max of 20 at one go…

    Lets not abuse the free service provided by them.

  27. Sanjay,

    Thank you for the awesome automated too. It saves me so much time. I have a few questions.

    1. I have a run-time error ‘9’: subscript out of range when I try to run the program.
    2. What do you do with those companies, such as holding companies and banks where Morningstar do not provide FCF info? Do you have a site that you manually get the data?

    Thanks again for sharing the tool with us.

  28. Sanjay,

    Thank you for sharing the auto-scanning with us.

    I have a question. How and where do you get FCF for Financial stocks to be used for FWallstreet model?

    Do you manually enter them?

    Thanks,
    Kevin

  29. Hi Again Sanjay,

    what is strange is that when i enter a symbol, it gives me the price, 52wk high, 52wk low, 200MA and 50MA, however, everything else is either an Error or a #VALUE. It seems to be getting partial info and not the full hundred, why is this? in the MStar, everything is an error.

  30. Hi Sanjay,

    Do you have an updated version for 2009 (previous version is for 2008)?

    Sorry, don’t want to go through the work of adding columns and formulas, if you’ve already done it!

  31. great blog.

  32. Cannot get the word doc to convert to excel

  33. I download the excel file with the add in.
    But when I change the symbol, it is giving an error (#NAME?) in most of the cells.

    I did enable the macro also.

    Please appreciate your help.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: