Posted by: sanjayshetty | October 22, 2007

Update 1.1: Automatically Scanning the S&P 500 using method

(Note: If this is the first time you’re looking at this program, refer to the earlier post first, to understand requirements to run the program.) 

I’ve seen considerable interest in the Excel sheet I provided earlier, quite a few downloads :-), I’m happy people are finding it useful. I received, some useful feedback: providing better status updates, possibly speeding up the program etc. and hence, I’ve updated the program further.

I’ve added some new features:
1. Adding companies to a separate WatchList sheet, in case their current price doesn’t match the value you feel you should pay for the company, however they have excellent CROIC and FCF Growth. Basically Mr. Market is overpricing them currently.  Such a company should be kept on a watchlist and thats the additional functionality I’ve included.
2. Speed: I’ve updated the program, such that my code doesn’t add any significant additional time to the data retrieval process (by tapping in to Excel Events). So the program now works much faster.
3. Status indicators: I’ve added several status messages, to make sure the user is aware of what company is currently been scanned, the total time taken for a scan etc. This helps in knowing what the Excel sheet is doing currently, and prevents it from looking hung.

With the additional speed improvement, comes a bigger caveat: Don’t scan all 500 at one go. The sites providing data observe large repetitive requests, often they block data provided to the particular requester(based on the source IP address etc.). As always scan in batches of 10-20. Basically, just because you now have a Ferrari, don’t drive like you’re on the racetrack everytime. Use the free sites data responsibly. FYI there are two free sites from where data is referred to: MorningStar for Financial Statements and MSN Money for current price. Go online and provide feedback to them saying how glad you are that they are providing 10 years of free data. They’re doing a wonderful service to help the individual investor.

In addition, this time around I’ve picked up the list of S&P 500 companies directly from their website.

As usual I’ve done a scan of all 500 companies of the S&P 500 using the Excel and you can see the Candidates and WatchList companies in the respective sheets.

Download FWallStreet_Model modified for S&P500 2003 version 1.1
(Note: Download and rename the file to .xls & view the earlier post for requirements for running the program.)

I look forward to your feedback on areas of improvement via comments to this post.



  1. Hello sanjayshetty ,

    First, I´d like to congratulate you for your blog, I consider it one of my favourites!

    I have just playing with your excel sheet, and I think it is very useful, but I have one question: Is there any possibility to get the financial data of companies from other countries than USA??? I mean, for example, Europe. I have been looking in and I didn´t find anything….or how to do it!

    Thanks in advance. We´ll stay in contact

  2. Hi David,

    Currently the excel will pick up data from MorningStar only which just has data for the US market.

    However if there are sites in Europe like Morningstar who provide free data on European countries, the Excel could be modified to adapt to data from those sites.
    I’m not aware of European sites which offer free 10 year financial data for companies listed out there. If you’re aware, do let me know and I’ll look into it. No promises though 🙂

  3. Hi Sanjay,
    I tried the excel sheet, but don’t think it works. I tried entering other symbols but it keeps on displaying ‘#NAME?’. Whenever I load the sheet, it displays an error with a link to a file at location: c:/program files/SMF addin/ I don’t have this SMF addin… is that the problem? Thanks!

  4. Actually, it works fine now, but running macros in settings isn’t working. Just needed to download the SMF addin 🙂

  5. Since FCF data isn’t available on morningstar, you can still scrape the info from like how does. Why not do that? Will it work with Joe’s analysis of companies?

  6. What I meant was, FCF data isn’t available for financial companies. I read somewhere on Joe’s site about someone asking this, but don’t remember if Joe answered this question.

  7. There are two posts at Joe’s blog which talk about this, reading these will make you realize it’s a bit more complicated.

    Hope that helps.


  8. Sanjay:

    First of ‘Thank you! for providing this excellent tool and sharing your knowledge.
    I have been using this tool for the past few months and it is only recently that I am seeing some errors in the spreadsheet.

    I am getting #DIV/0! errors for Share Value and Buyer under columns on ‘Candidate’ tab for the following. I have been noticing these errors since the past couple of days

    Any thoughts?


  9. Hi Amit,

    The reason for the #DIV/0! errors are due to a lack of data at MorningStar which the spread sheet uses.
    For e.g. Symbol: KMB
    check the site:

    Last line Shares — there is no data on Morning Star, and hence the Per Share Value is not calculable and correspondingly Share Value etc. get affected.

    Same goes for Symbol M etc. Hope that answers your query. You could obviously manually pick data from some other source and input it.

    Hope that helps clarify.


    Sanjay Shetty

  10. Thanks Sanjay! 🙂

  11. Hi Sanjay,

    Firstly i will like to express a BIG gratitude for providing this excellent tools.

    Few queries on the use of the spreadsheet:

    1) Why is the current price on all the stocks “UNDEFINED” ? The same message error message goes for the 52wks High, 52wks Low etc? How to get the price details for them?

    2) As per your notes, the spreadsheet does not work on Financial & Insurance companies. Does it mean that all the FIN & INS companies in the S&P? For example, if i input Goldman Sach the results are not complete whereas if i inout Citibank there are errors in all the cells? Is there anywhere to overcome this & still search for FIN & INS companies?

    Your reply is greatly anticipated & appreciated.



  12. Hi Sanjay,

    i dowloaded the add in from Randy, then i downloaded your program, however, i am getting errors? In the Mstar section, all i am seeing is errors. am i doing something wrong? has anyone else gotten this?

  13. Hi Sanjay,
    Thanks for sharing this great piece of work. I downloaded the excelsheet vers 1.0 which is working fine, both the main sheet and the scan function.

    However the update (vers 1.1) is giving me some problems. In main sheet, I’m getting “Error” for ‘price’, “#VALUE!” for ‘share value’ and “#VALUE!” for ‘buy under’. Not sure if anyone out there is experiencing the same issue and could anyone suggest a fix for it. Thanks.

    • Yin, I am having the same problem. I think there may have been a change at Morning star that I am not able to decipher….

  14. hi sanjay, your excel sheet is exactly what i was hoping i could create until i stumbled upon your website through fwallstreet. thanks for sharing the sheet!

    but one problem. for me, the sheet appears to hang at the first row that i process. and to test the sheet i am just processing 2-3 rows at a time.

    btw, Main and Mstar do get populated alright. so i think i have installed SMF add-in alright.

    any ideas as to what I could look into?

    thank you!

  15. it appears that the process is stuck at ” ThisWorkbook.Sheets(“Settings”).Range(“A14”).Value = “Row No:”” code in Module1. I am no VBA expert. I hit escape, and then the row which is shaded yello seems to be from this portion of Modeule1 code – ” ‘ Get the Ticker Symbol Name, by looking up the value of “row” and pick the value of the Symbol from there.”.

    Net it appears that the sheet is stuck in this loop. Will appreciate any ideas you might have to fix this problem.


    • I’ll look at it and get back to you.


      Sanjay Shetty

      • Thank you.

  16. Hi Sanjay,
    I did stumble across your spreadsheet last year and was able to use it for a while. I am not sure what is going on but it seems that the sheet is not downloading all the information from Morning star especially from revenue through net income. I am no programming expert but it may have something to do with the data structure that Morningstar has or perhaps the add-in. I am not sure but just wanted to make sure that you are aware of the problem.


  17. Sanjay,
    I think I found the root cause of the issue. Morning star does not have the 10 year data as free anymore. Randy has updated and provided an SMF ad-in for “AdvFn”. I am not sure which macro is using the mstar site but I think that may need to be changed to another site. Though I am not an expert would it be possible for you to let me know which macro uses MStar or is this something where the smf ad-in may need to be reinstalled.


    • Hi G

      Yes I am facing the same issues

      Where can I get Randy’s new add-in ?


  18. I’m kind of late to the party just discovering FWallSt and your site recently. In trying the spreadsheet, I’m finding 1) Morningstar has changed their website and so getting the information has been difficult and 2) they no longer offer 10 years without the premium membership.

    Before I try some workarounds, I thought I’d ask if you have an updated version of your spreadsheet.

    • I haven’t had the time to create a workaround or work with Advfn data as yet. Will try and fix this during the weekend and update it online.

  19. Any luck…Joe has a new spreadsheet out with Advfn data and visual basic… but doesn’t have the great functionality as yours…

Leave a Reply

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

You are commenting using your 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


%d bloggers like this: