Comparable Company Automation Project

EXECUTIVE SUMMARY

THE PROBLEM
Most professionals in the investments industry (investment banking, private equity, investment management) and the valuation business (accountants, actuaries, fair value specialists) use a program called Capital IQ in order to quickly pull lots of information on public companies for comparison and valuation purposes. Capital IQ has a special excel spreadsheet that can be downloaded and used by these professionals. The spreadsheet interfaces with the Capital IQ website and quickly pulls financial data on any public company that you type into the spreadsheet. Please click on the link below to become familiar with the Capital I.Q. excel plug in and the importance of having this data automatically generated. Also, open the PDF brochure to see part of an excel worksheet that I am trying to emulate.

https://www.capitaliq.com/Main3/ourproducts_platform_excel.asp

There is only one problem. Capital IQ is outrageously expensive for students who often need the same tool. Luckily, most of the core data is available on Yahoo.com or other websites. It just needs to be pulled, sorted, formatted, and manipulated.

THE SOLUTION
I used VBA to set up a spreadsheet that allows the user to enter ticker symbols of public companies for comparison purposes. I originally set out to have the spreadsheet automated to the point that the user could put in the time period that is desired, the base for the beta analysis (i.e. S&P 500, Russell 3000, or Wilshire 5000), the time interval for the beta calculation, the currency denomination, and other variables. I also wanted the spreadsheet to pull and organize every piece of data under the sun including the companies’ stock volatility, historical working capital, historical capital expenditures, etc. I soon learned that there is a reason that Capital I.Q. is so expensive. In order to completely re-create the Capital I.Q. excel plug in would demand about a year’s worth of work for one person. Therefore, I chose a couple of pieces of the project that I could complete and left the rest for a later date.

The resulting spreadsheet automatically downloads data from Yahoo Finance upon the user manually entering the ticker symbols of the companies that are comparable to the users target company. The spreadsheet then organizes this data for each comparable company and provides the user with a quick way to compare the following data to the target company:
• Historical Beta
• Leverage ratios to automatically un-lever and re-lever beta
• Current P/E ratios, and Enterprise Value ratios
• The last three years of income statements
• Current Stock Price and Market Capitalization
• Current Cash on Hand
• Company description and applicable markets/industry

The links to my work:
Worksheet: http://files.gove.net/shares/files/10w/rbc42/Final_Project.xlsm
Write-Up: http://files.gove.net/shares/files/10w/rbc42/Final_Project_Write_Up.docx