Financial Analysis Automation

VBA Final Project – Financial Analysis Automation
Jeff Jenkins
December, 2009

Executive Summary
Financial analysis, specifically company research, can be a time consuming process. For equity investing, company analysis is critical. My VBA application strives to decrease the time it takes to complete two key steps in company analysis – valuation and comparative analysis. My project is explicitly designed for use in the Investment Management Internship and Asset Management classes (BUS M 496R and 415). An important part of both classes is analyzing one or more firms sufficient to determine whether they are under or overvalued. Presently they use a multi-sheet Excel workbook to store and analyze financial data for individual companies. This data is obtained through Standard and Poor’s Research Insight (RI hereafter), a proprietary analytical program that accesses public company financial data. Once the data is input into Excel, a series of formulas derive five to six intrinsic values for the company. Manually pulling in the data for each individual company and then outputting select data takes the users quite some time as they are dealing with dozens of companies a handful of times per semester.

My VBA application allows the user, in a separate workbook from that described above, to create a list of ticker symbols for publicly traded stocks that he or she wishes to value and compare. The application then opens the workbook mentioned prior, pulls in company data (based on the ticker) from RI, cleans up the data, retrieves the most current stock price and earnings per share forecasts, and then outputs the intrinsic values back to the workbook containing the ticker list. This process loops for as many ticker symbols as the user has entered. Once finished, the output data is sorted on two criteria: 1) whether or not the average intrinsic value is over or under the current price and 2) the percentage under/overvalued.

To summarize:

Problem:	it is time intensive to run company after company through a set of valuation models and then store the output data somewhere for comparison
Solution:	automate a looping process that will analyze as many companies as the user would like (by ticker) and output the data in an organized manner

Find the rest of the project write up here

Important Note to the Reader

Research Insight is only installed on a handful of computers throughout the University. Due to the fact that this VBA application requires that RI be installed on the machine running the application, the Excel file associated with this write-up has not been included. Additionally, some of the templates used by BUS M 415 and BUS M 496R are not for public distribution.