SpearianMarkowitzOptimalPortfolio

 

Syntax: SpearianMarkowitzOptimalPortfolio(Tickers, ReferenceFromDate, ReferenceToDate, TargetReturn, Fields, ShallTheWeightsBeNonNegative, AutoFormat)

 

Equivalent Function Names: SPMOP

 

Purpose: Function used to calculate the weights of the Markowitz's Optimal Portfolio

 

Parameters: 
 

1.Tickers are standard Spearian Tickers. Review the Tickers guidelines here.
 

2.Parameter ReferenceFromDate and ReferenceToDate are optional.

If provided, it can be specified either as an Excel date or in the text format "yyyy-MM-dd", e.g. "2013-02-25". The time range can be entered using relative time periods, see here.

If not provided, the default of the last 2 years is applied.
 

3.TargetReturn- the required return on the portfolio - NOTE: the TargetReturn is in the units of the time series, so if the time series are specified in a daily precision, divide the annual return by 252.
 

4.Fields- If not specified or specified as "All", returns the full result of the optimization:



"Objective function" is the variance of the portfolio, "Total Allocation" is the sum of the weights and the "Total Return Rate" is the weighted sum of returns of all components.

If specified as "W" or "Weights", returns only the optimal weights:



 

5.ShallTheWeightsBeNonNegative- By default, the weights returned are non-negative. If shorting is possible, set to FALSE.
 

6.AutoFormat - an optional flag signifying if the result should be automatically converted to a range and if the result should be automatically formatted as Excel Data Types

 

Calculation Methodology:

1.The matrix of all price data of the default columns is retrieved

2.All rows of the matrix where at least one number is NaN are removed

3.The matrix is transformed into a log returns matrix

4.A quadratic optimization is calculated using the minimization of the variance of the portfolio log returns (covariance matrix of the log returns multiplied by the weights), subject to the constraints of weights summing to 1 and non-negativity, if required.

 

Examples:

 

1. Calculate the Markowitz Optimal Porftfolio Weights for 5 stocks on a daily precision for the last 2 years with the target return of 10% - display full results
=SPMOP("ibm,gs,jpm,msft,aapl",,,0.10/252)

 

 

2. Calculate the Markowitz Optimal Porftfolio Weights for 5 stocks on a daily precision for the last 2 years with the target return of 10% - display just the weights
=SPMOP("ibm,gs,jpm,msft,aapl",,,0.10/252,"W")

 

 

3. Calculate the Markowitz Optimal Porftfolio Weights for 5 stocks on a weekly precision for the last 2 years with the target return of 10% - display full results
=SPMOP("ibm::w,gs::w,jpm::w,msft::w,aapl::W",,,0.10/(365.25/7))

 

 

 

Copyright © 2013-2017 Jiri Pik

Document Version: Sunday, May 7, 2017