Stock Evaluation and Portfolio Optimization - Austin Weaver

Executive Summary

                Stock investment occurs hundreds of thousands of times every day. There are entire firms dedicated to searching for undervalued stocks and investing client assets accordingly. These investment funds have a variety of methods for evaluating stocks and making investment decisions.

                Whether or not I work in an investment fund or similar area of business I will be making investments in the stock market throughout my life. I wanted to create my own system for evaluating stocks and recommending investment amounts based on optimal portfolio weights.

                The “Stock Evaluation and Portfolio Optimization” workbook I created contains VBA code that accomplishes three main tasks.

1.       Gather data on all S&P 500 stocks

2.       Evaluate and recommend stocks based on the gathered data

3.       Optimize portfolio weights for the recommended stocks

These tasks can be accomplished through the “Stock Evaluation” user form (see below) that is launched by clicking the button on the customized “Stock Analysis” ribbon. The “Refresh Stock Data” button on this form accomplishes the first task by collecting performance data on each of the S&P 500 stocks according to 8 different stock evaluation methods. The “Evaluate Stocks” button undertakes the second task by scoring stocks against these 8 evaluation methods using weights and a cutoff measure supplied by the user. The “Run Optimizer” button then uses Excels Solver add-in to optimize portfolio weights to achieve the lowest standard deviation possible subject to the desired annual return supplied by the user.

Together these procedures allow the user to evaluate a vast number of stocks and to know how to invest in those stocks in a risk-conscious way. The rest of this report details how to work the user form and the processes employed to accomplish the three tasks outlined above.

Report: http://files.gove.net/shares/files/14w/aweaver2/Stock_Evaluation_and_Portfolio_Optimization.pdf

Excel File:http://files.gove.net/shares/files/14w/aweaver2/Stock_Evaluation_and_Portfolio_Optimization.xlsm