Automating Markowitz Optimizations Using VBA

Abstract

In the interest of reducing grading workload and managing cross-student sharing (cheating), we provide an example of automating the construction of Markowitz (1959) efficient frontiers. The program automates the entire procedure from downloading data for a specified number of securities (using either randomly generated securities from a provided list or specified tickers) to generating graphs of the efficient frontier. Many commercially available optimization programs are available, but this program is free to use and works within Microsoft Excel (2007-2010), the software package most professors use when creating optimization assignments. The program is written in Visual Basic for Applications (VBA) butdoes not require any knowledge of VBA to use. The program allows easy creation of random portfolios for student assignments so that cross-student sharing can be minimized without adding an additional grading burden on the professor or TA. The professor can also be assured that the stocks selected produce the desired type of optimization (actually work) before using each set of securities. Where students are allowed to select their own portfolios, the program allows professors to easily check computations and optimizations without having to manually optimize each portfolio. The program can also be used to demonstrate the similarity of efficient frontiers across different stocks, portfolio sizes, and dates.