Stock Evaluator

Stock Evaluator: Executive Summary

I spend a significant amount of time analyzing data for equity securities in the US market. I will also be doing equity research full-time for an investment bank after graduation. As such, I am constantly going online to look up current and historical data for those securities. This has become a time-consuming task for me, so I wanted to use VBA to program something that would allow me to simply enter the ticker symbol, tell it what analysis I wanted to perform, and have the program run the analysis for me.

In order to be useful, the program would have to allow the user to specify a past date, and have the program look at historical prices for the security itself, and for the benchmark indexes against which I wanted to compare the security. The program, therefore, retrieves historical data for the Dow Jones Industrial Average, the S&P 500, and the Nasdaq for the indicated date. It also captures current data for those indexes as well as the selected security and calculates the annual return on each. It then compares the annual return of the security to that of each of the benchmark indexes, and calculates the alpha (the difference between the annual gain of the security and that of each benchmark index) of the selected security, and then shows all of the results to the user.

In addition, the program calculates the 300-day moving average, the 60-day moving average, and the actual stock price of the selected security and graphs those three trend lines on a single graph that is then displayed to the user.

In an effort to facilitate stock comparisons, the program creates a new sheet for each security allowing the user to simply move between sheets (if the program is run multiple times) to look at the results for different securities.

Below are links to the full write-up for the project, as well as for the actual workbook:

Write-Up: http://files.gove.net/shares/files/10f/mfgrant/ISYS_520_-_Mark_Grant.pdf

Workbook: http://files.gove.net/shares/files/10f/mfgrant/StockEvaluator.xlsm