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.

Industry Overview Workbook

Just finished creating an Industry Overview Workbook. This was in partial completion state for quite some time, but with the help of Randy's SMF Addin, I was able to finally finish it off.

Purpose of this spreadsheet is to provide a high level view into the performance of various US based industries. The workbook is made up of 4 worksheets.

The first worksheet is the Industry Monitor worksheet. This allows the user the ability to monitor the performance of various industry specific "indexes" that have been created by Yahoo. There are currently 215 of this indexes and you can monitor a real time price for them. These indexes are generated by rolling up market cap adjusted pricing of the underlying stocks that make up the index. As part of this monitor you can also see a selected set of aggregates for the given industry. A filter is included in this workbook to enable the user to drill down to the industries of interest given their specific criteria.

The second worksheet is an Industry Visualizer worksheet that allows you to see a heat map of the various industries. A simple red (for down) and green (for up) set of conditional formatting is included on this page, which allows you to see at a glance what industries are up/down for the day.

The third worksheet is an Industry Constituent worksheet which allows you to look at the individual stocks that make up the selected industry. You also have access to see sector aggregates, for the sector that the industry belongs to. Again, filters are turned on for this worksheet allowing you to drill down to a specific set of constituents that you are interested in.

The final worksheet is an Industry News worksheet that allows you to access headlines (RSS feed) from Yahoo for the industry of interest.

In totality, this workbook should give you a good picture about what is currently going on within a specific industry. Unfortunately, I could not find data that would allow you to get a historical perspective on whether the industry was doing better or worse than it has done in the past.

This workbook is available at:

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

Feel free to provide your comments about this workbook on the blog, including enhancements or any bugs that you find within the workbook.

SMF Addin

If you're looking to create spreadsheets that manipulate information about stocks that you can buy or sell, then the SMF addin is the tool for you. It is extremely powerful, and provides access to thousands of individual data items about a company. The addin is free, and there is a yahoo group that has been set up to provide examples and a forum for questions and answers about its use.

The group is at the following location:

http://finance.groups.yahoo.com/group/smf_addin/

I've modified two of my spreadsheets to leverage this tool. One is a portfolio sheet that allows you to monitor your P&L through out the day. The sheet also allows you to look at other publicly defined portfolios. So, industries, sectors, adr's, preferreds, closed end funds, and industry identifiers. It is available at:

http://f1.grp.yahoofs.com/v1/IGz7Siwpdpq0faRp5vvRLTCdBoyMIXJSB9shJbiQkByoUllxhtk5xsNwQ6dHcQo38H6qbVdzQzFO2tHhtfdyX38GiNLsjCCU5u5zFsaLdbM0/Uploads%20by%20forum%20members/PortfolioSheet.201.zip

The other spreadsheet is a company overview sheet that allows you to look at critical investment information about a specific company. It is also integrated with the portfolio sheet defined above in that it allows you to cycle thru the various companies that you own and are tracking thru your portfolio sheet. It is available at:

http://f1.grp.yahoofs.com/v1/IGz7ShET3-C0faRp4mqqrh_5BjTRIJejQd-pEAw1q5jjh6mMW1V1WcVaHySYO0BcsOjsM0fobwgeHbnpVakIy-jtO9J2ffyhsXIS6wgAsYe3/Uploads%20by%20forum%20members/Company%20Overview%20v2.0.xls

If you get a chance take a look at these, and let me know what you think.