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) but
does 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.