Tuesday, December 22, 2009

Stock Screening in Excel

Just finished creating an Excel based stock screener. The screener leverages content from http://www.finviz.com/, a truly phenomenol site for investing content, and allows you to leverage the power of Excel by enabling you to filter on each column in the screener. Approximately 80 columns of data are available, including real time price and volume data. See the results and impact of your filter changes immediately. The workbook also allows you to calculate Market Cap weighted aggregates at the industry and Sector level off of the same content that you can screen on. As a result, when you're done screening you can compare your results to aggregate values at the sector or industry level.

Looking ahead, a future version will include bubble and or scatter plots on variations of content allowing you to see visually where the outliers are, as well as conditional formatting of "good" and "bad" values, and possibly multi factor rankings across the data items allowing you to "score" the best investment options. Take a look, and let me know what you think:

http://www.toteboard.net/Models/ZipFiles/StockScreener.zip

Note: This is a rather large file, and the aggregate calculations are quite "expensive" in terms of CPU time. So, have a little patience when opening, and be warned about the possibility of locking up Excel for short periods of time. I tried, so I recommend against calculating aggregates for all the sectors and industries in this workbook.

5 comments:

  1. Thanks so much for putting all these together and sharing! They are way awesome and helpful.

    ReplyDelete
  2. Hey JP,

    Just downloaded the stock screener and for some reason it's not getting any data only shows #NAME? in all fields... do you have any idea what might be wrong?

    ReplyDelete
  3. I've been reviewing your excel sheets etc. They truly are impressive. I notice that its coming up to 2 years since your last post. It would be great to see the blog continue one!

    ReplyDelete
  4. Great work.

    Noticed that the industry averages don't match up to the values you get when you manually calculate the average...

    ReplyDelete
  5. Hi,

    I have 2 queries:

    (1) I would like to use the stock screener spreadsheet to get the same data from yahoo instead of Finviz site, how do I do this as I cannot see how to input the Stock Exchange, Indice or stock codes. Have these been input on a hidden sheet (eg setup or workbook), if so how can I access ?

    (2) As yahoo only allows ten yahoo tags per query, how did you overcome this to get data for all 80 plus yahoo tags and extract them to Excel ?

    ReplyDelete