Markowitz portfolio optimization with VBA code

Wouter, Butler and Kipnis [2015] recently demonstrated Classical Asset Allocation (CAA) for long only portfolios, based on Markowitz’ concepts. The method uses only two parameters thus minimizing the chances of curve-fitting and data snooping.  The parameters are lookback period (12 months) and target volatility.

Main results from the paper are as follows (from 1915 to 2015):



R annual return, V volatility, TV target volatility, D max. drawdown, EW equal weight

N=8 portfolio:

SP500, EAFE, EEM, US Tech, Japan Topix, T-Bills, US Gov10y, and US High Yield.

N=39 portfolio (N=8 +):

10 Fama/French US sectors, five US bonds, US Small Caps equities, GSCI, Gold, Foreign bonds, US TIPS, US Composite REITs, US Mortgage REITs, FTSE US 1000/US 1500/Global ex US/Developed/EM, JapanGov10y, Dow Util/Transport/Industry, FX-1x/2x, and Timber

Consistent results from all datasets gives further confidence in the method.

My main interest is in factor investing.  I applied the method to the momentum and value portfolios normally used in this blog plus Mkt factor and ‘risk-free’ (all from Ken French’s data library).  Dividends are continuously re-invested and trading frictions are neglected (this strategy only trades a few times per year).

Results from two target volatilities are shown below.  The lower volatility case exhibits a remarkable 65 year Sharpe Ratio of 1.3.  The t-statistic is 10.6!

Annualized returns are 9.7% and 12.6% respectively.


The next post will cover sector portfolios and real datasets.

I used Excel’s solver to maximize 12 month trailing return with a 12 month standard deviation target.  A constraint is applied of sum of weights = 1 (no leverage).

The spreadsheet is trivial to create.  The column layout to match the VBA code is as follows.  This is for 4 datasets but may be extended as necessary.  Columns F-L start at row 13 as they require 12 months of history.

Column    Description

A                Date

B-E            Monthly return data (4 datasets)

F-I             Portfolio weights (calculated by solver)

J                 Sum of weights (F:I)

K                Weights multiplied by 12 month returns, summed

L                 Weights multiplied by 12 month stdev of returns, summed


When the solver finishes, multiply monthly returns by weights from the previous row and sum to obtain the portfolio return.

VBA code for the solver is run as a macro in the sheet containing the data:

For i = 13 To 790 ‘ monthly return data in rows 2-790

SolverAdd CellRef:=”$J$” & i, Relation:=1, FormulaText:=”1″ ‘ sum of weights = 1
SolverAdd CellRef:=”$L$” & i, Relation:=1,   FormulaText:=”3″ ‘ target stdev = 3% (10% annualized)
SolverOptions AssumeNonNeg:=True
SolverOk SetCell:=”$K$” & i, MaxMinVal:=1, ValueOf:=”0″, ByChange:=”$F$” & i & “:$I$” & i
SolverSolve userfinish:=True
SolverFinish keepfinal:=1



5 thoughts on “Markowitz portfolio optimization with VBA code

    • Thanks, no but I follow your work on Quantocracy and would be happy to. In fact, applying money management to CAA (such as safe-f) may improve results but would need an R script.
      Every timestep, bootstrap the PDF of X period max. drawdown and solve for 10% risk of 20% drawdown.

  1. Pingback: Quantocracy's Daily Wrap for 12/03/2015 | Quantocracy

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s