Notes:

Notes:

This script creates an excel file called Data.csv which is located inside the main program folder, in \ShareScript\Output\

Introduction to Modern Portfolio Theory:

Markowitz portfolio optimization is a method of optimising portfolio based on a mathematical model that maximises the return and minimises risk of a portfolio. This is based on historical price correlations in the securities held in the portfolio. The risk level of securities in a portfolio is measured by their volatility, the standard deviation their historical returns. The portfolio is also measured for risk using the standard deviation of its returns on all the shares as a whole. The risk of a portfolio is less than the sum of the risk of each security held in the portfolio. This is due to low or negative correlation between shares held in the portfolio. Shares that are inversely correlated move in different directions, so when one share held in the portfolio decreases in value, the inversely correlated share increases in value and the losses are offset. This is the mathematical interpretation of the common concept of maintaining a ‘diversified’ portfolio.

For more info on the Sharpe ratio, please refer to the Wikipedia article : http://en.wikipedia.org/wiki/Sharpe_ratio

There are an almost infinite number of portfolio combinations you can create for a given number of securities held in a portfolio. By changing the percentage of your portfolio that is allocated to any particular security, you form a new combination. Each of these combinations results in a portfolio with different risk and return properties. Some portfolio combinations will result in a larger amount of risk for the same level of return. These portfolios are clearly inferior. One frequently used method of determining the risk return profile of a portfolio is by measuring its Sharpe Ratio. This is the ratio between a portfolio’s historical return and its risk. Any portfolio combination that results in a larger sharpe ratio is superior than a combination that does not. Markowitz portfolio optimization seeks to find the portfolio combination that maximizes the Pharpe Ratio, in effect maximizing the return for a given amount of risk.

Markowitz portfolio theory was the precursor to many breakthroughs in financial economics including the CAPM (Capital asset pricing model). In 1990 Markowitz was awarded the Nobel prize in Economics for his contributions to this field. However, this form of portfolio optimization is rarely used on its own as there are many other factors that must be considered in portfolio management. Simply assessing the historical return, risk and correlation is a vast oversimplification. This tool is best used to assess your level of diversification. It should be used in conjunction with other portfolio management techniques.

For more information on Markowitz portfolio theory, its application and criticisms, please refer to this article: http://en.wikipedia.org/wiki/Modern_Portfolio_Theory

Using this script:

Step 1. Loading the settings

When you first load the script, you will receive a dialogue box prompting you for some information (see figure below).

In the first drop-down box please select the portfolio that you would like to run the analysis on.
In the second drop-down please select the period length used for the calculation. Generally this would be set to Monthly, so as to avoid the ‘noise’ that occurs during shorter periods.
In the third drop-down menu you can select whether you want the output to be displayed using the share’s name or EPIC codes.
In the ‘periods’ box, select the number of periods for the calculation. Generally the tool should perform the calculation over a full business cycle, 5 – 7 years. In reality, this may be difficult because there may not be enough history available in the program for the securities selected. In the next portion of this tutorial we will show you how to select the maximum number of periods available based on the historical data of the shares in your portfolio
The tick box that reads ‘Generate report’ allows you to output the data in a different format. This will be covered at the end of these instructions in Step 6.

Step 2. Reading the preliminary output.

For the sake of this tutorial, we have run this script on a portfolio with the following securities:

Name	EPIC
Apple Computer Inc	AAPL
British Airways PLC	BAY
Arriva PLC	ARI
BP PLC	BP.
Crude oil (Light Sweet) Composite	CL
Hang Seng	HSI
Gold Composite	GC

Upon running the script, you will receive a black box with some output:

The first line of the output indicates the sample size used in the calculation. This is essentially the number of periods used. This will likely differ slightly from the value you entered in the box, but this is normal. It also indicates to you the maximum value you can enter for the periods used in the calculation for this portfolio. In the example, a sample of 51 months was used out of a maximum of 57. That means 57 is the maximum number that can be set using a ‘Monthly’ setting in the dialogue box from Step 1.

The lower portion of this outputs the correlation matrix used for the portfolio optimisation. If you see this appear, then the calculation has performed properly. If there is an issue, the box will not display the matrix and it will output an error. The most likely source of an error is using ‘daily’ periods in the settings, when the portfolio contains shares that do not have matching dates. This is usually a result of mixing foreign exchange traded shares with LSE shares. The foreign shares will likely have different holidays that result in missed days on the data. When this occurs, the system halts with an error. For this reason it is best to avoid daily periods in the settings.

At the bottom it indicates to you where the data file has been saved. This file should be opened using Excel or any other spreadsheet.

Step 3. Spreadsheet – Security Statistics.

The spreadsheet is output with no formatting; this is a limitation of the file type. The coloured text and bold text in this example have been added for clarity.

The upper portion of the spreadsheet outputs the calculations performed on each security held in the portfolio. For this example, we have opted to output the data using EPIC codes. These can be referenced in the table in Step 2. The green text identifies what is being calculated:

The Weight is created arbitrarily and it represents the percentage of the portfolio that is held in this portfolio. By default it assumes and equal percentage is held in each share, so the weight for each share should sum to 1. This will be used later on, and for now it can be ignored.
The Annualised Expected Return is the historical return on the share displayed at the compound annual percentage rate.
The Variance is the statistical variance of the returns on the security. It is not calculated on the price but rather the change in price represented in percentage for each period. For more information on variance refer to : http://en.wikipedia.org/wiki/Variance
The Annualised Standard Deviation is the statistical standard deviation of the returns on the securities held in the portfolio. The standard deviation is similar to the variance except it has been standardised for comparability. This is a standard measure for risk when investing. Fore more info, please refer to this article: http://en.wikipedia.org/wiki/Standard_deviation
The Annualised Sortino Ratio is much like the Sharpe Ratio. The Sharpe Ratio relies on the standard deviation to quantify the risk on the security based on how volatile it is. This volatility can be both positive or negative for upward and downward price movements respectively. The Sortino ratio, on the other hand, performs the same calculation but only using downward movements in calculating the volatility. It eliminates the upward price movements from the calculation because some investors do not consider this to be ‘risk’. Traditionally risk is considered to be uncertainty so it should include both upward and downward movements, but this ratio is still considered useful in addition to the Sharpe Ratio.

The lower portion of the Excel document is the correlation matrix. It displays the correlation between each security by matching one from the horizontal axis to another from the vertical axis. For example, the correlation between Apple Computer Corp (AAPL) and the Gold composite (GC) is -0.1111. This makes sense because gold tends to rise in falling markets, and therefore the shares should be inversely correlated. Therefore gold would be a suitable candidate for a portfolio containing AAPL, because it would improve the diversification. Likewise, AAPL and the Hang Seng Index (HSI) have a fairly strong correlation of 0.5965. This may be due to Apple having large investments in China and therefore its returns tend to move with the index itself. All correlations should have a fundamental economic explanation. If none exists, it is likely to be a correlation arising out of randomness.

Note that diagonally across the matrix there is a value of 1 for every security. This is because the security has been correlated against itself. If this diagonal line does not display 1 for any particular item, there has likely been a fault in the calculation and you should not rely on the data.

All data below the correlation matrix is used to calculate the portfolio statistics. It should not be modified.

Step 4. Spreadsheet – Portfolio Statistics.

To the right of the statistics on individual securities, you will find the statistics relating to the portfolio as a whole (see figure below).

The Sum of Weights is simply the sum of the percentage weighting of each security as described in Step 3. As this represents the sum of the percentage of each security in the portfolio, this should always add up to 1 (100%).
The Portfolio Expected Return is the weighted average of the expected return for each security in the portfolio described in Step 3. It is weighted by the percentage holding of each security.
The Annualised Portfolio Variance is the variance of the returns on the portfolio. This is lower than the some of the variance for each security in the portfolio due to correlations calculated between the shares held in the portfolio. The value is annualised because it is calculated on the annualised Standard Deviations of the securities in the portfolio. See the appendix for the formula used to calculate this.
The Annualised Portfolio St-dev (standard deviation) is the square root of the portfolio variance. It is a standard measure of portfolio risk.
The Risk Free Rate is should be the rate of return received on risk free assets. Generally the yield to maturity on local government bonds are used. If local government bonds are not deemed to be risk free due to political or credit risk, US treasury bonds should be used with an adjustment on the rate of return for currency hedging cost using forward contracts. The bond maturity should be equal in length to the expected holding period of the securities of the portfolio. Generally the longest term possible is used (40 years) because it is assumed that the portfolio will never be entirely liquidated.
The Sharpe Ratio is described at the beginning of this document. It is the expected return on the portfolio above the risk free rate, represented as a ratio of the risk on the portfolio (the standard deviation).
The Annualised Sortino Ratio on the portfolio is also described above. It is the same as the Sharpe Ratio except the standard deviation used in the denominator is calculated using only downward movements in the portfolio’s value.

Step 5. Running portfolio optimisation models.

These techniques require that you install the Excel Solver. If you do not have the solver installed in Excel, click on the Tools menu and select Add-ins to add it. If you are on Excel 2007, the add-ins are located by clicking on the Microsoft office button (big circle in upper-left corner) and selecting 'Excel options' At the bottom of the menu.

Model 1. Solving for the Minimum Variance Portfolio.

These are the steps to model the least risky (most conservative) portfolio. Please refer to the figure below.

Open the excel solver, set the ‘target’ cell to the portfolio's Standard Deviation. Select 'Minimize' from the radio buttons.
Where it says 'By changing cells' select the entire row of cells for 'WEIGHT' (this tells excel to try different weights of shares to find the minimum variance).
Where it says ‘Subject to the constraints’ Add a condition that the cell for 'SUM OF WEIGHTS' which reads 1 must be equal to 1. This says that the sum of the % weighting of each security in the portfolio must add up to 100%.
Now set another condition but this time for the entire range of cells of portfolio weights (just as we did with the 'By changing cells' section). Set this to <= 1 (less than or equal to 1). This tells the solver to not allow the weight of a single share to be over 100% of the portfolio.
Now create another identical condition but make it >= 0. This tells the solver not to allow any of the weights of each share to be less than zero. Alternatively you can set this as -1, which will allow the portfolio to have negative weights in some shares. This can be interpreted as short selling those shares, and using the proceeds to purchase other shares.
Now click 'SOLVE'. Once complete, excel will ask you if you want to keep the values, click Yes (to the optimized values). The portfolio will now have new weights for each of the shares. This tells you the weighting to use to construct the minimum variance portfolio. Notice that the values for portfolio variance, standard deviation, expected return, and sharpe ratio have changed as well.

Settings for the minimum variance portfolio

Results for 52 periods of weekly returns (note: for best results use monthly returns over a longer period)

As you can see from the above figure, the portfolio has been reallocated between assets to display the least risky combination of assets using this portfolio. The percentage holding in each asset is displayed in blue. The pie charts indicate the allocations between assets before the optimisation and after. Originally it was equally weighted. With the optimised figures, crude oil, British Airways, and the Hang Seng index have all been removed. 44% percent has been allocated to Gold, followed by 14% in Arriva and 1% in Apple.

The graphs below display the periodic returns on the portfolio before for one unit of currency before and after the optimisation. Clearly the minimum variance portfolio has much lower volatility. Generally this would be at the expense of portfolio returns, but it in this case the returns are almost comparable.

The periodic return on the portfolio is displayed in a column below the Matrix and to the right of the periodic security returns. The header is ‘Portfolio return’. Experimenting with the weights in the portfolio as we just did with the optimiser will result in a new calculation for periodic portfolio returns.

Model 2. Solving for the Optimal Risky Portfolio.

This is the procedure to solve for the optimal risky portfolio. The optimal risky portfolio is defined as the portfolio that provides the maximum return for the minimum amount of risk. The simulation is almost identical to the minimum variance portfolio except the goal is to maximise the Sharpe ratio.

Open the excel solver, set the target cell to the portfolio's Sharpe Ratio. Select 'Max' from the radio buttons.
Repeat steps 2-6 from Model 1.

The result of the optimisation indicates that the optimal portfolio would eliminate all assets except for Apple Corporation and Gold with 29% in Apple and 71% in gold. The resulting portfolio returns displays a chart with very little volatility and very high returns (250%). Note that the model was based on 52 weekly periods from Dec 2008 to Dec 2009. For optimal results monthly returns over a full business cycle should be used.

Various other spreadsheet features.

Portfolio Variance:

Below the correlation matrix you will find another matrix under the header ‘Annualised portfolio variance calculation.’ This is the calculation for the portfolio variance which requires calculating the weighted average of each securities standard deviation, adjusted for the correlation between the securities. The formula is available at the bottom of this document in the appendix.

Periodic returns on each security and the portfolio as a whole:

At the bottom of the spreadsheet, below the correlation matrix and the portfolio variance calculation, the periodic returns are calculated for each asset in the portfolio in columns. The length of these columns should correspond roughly to the number of periods specified when first running the script. These are static and cannot be changed. To the right of the periodic asset returns is the total return. It is calculated on each security in the portfolio using the weights specified in each security at the top of the spreadsheet. These data series will allow you to plot the return of the portfolio or individual securities by applying a scatter diagram to the list of returns, just as displayed previously in this document.

Downside deviation:

To the right of the portfolio returns is the calculation for downside deviations used in the Sortino Ratio. This can be ignored.

Step 6. Generating a Report

If you tick the option to generate a report when you first loaded the script, the details calculated for each security will be output into an excel file named Report.csv in your \ShareScript\output\ folder. This file exports all the calculated data for each individual security and displays it in one large table as per the image below. The purpose for this is so that the data can be sorted easily in order to find negative correlations, or high/high volatility securities.

Graphing Portfolio Returns:

To Create your own graphs of the portfolio, simply graph the data in the column under the header ‘100’. This column is four columns to the right of the ‘portfolio returns’ column, as per the images below. The graphs represent the return on a portfolio with an original investment of 100 units of currency. You will want to use a scatter plot or a line graph as per the image below. The numbers in the x-axis represent the periods used in each return calculation, and the y-axis represents the return. As you adjust the values of the portfolio percentage holdings, the graph should automatically adjust to reflect the changes.

Discussion.

Clearly using the historical return as an estimate for expected return for each share does not reflect reality. Preferably, the values for the expected return on each share should be changed to reflect your estimate of the return for each share. Likewise, the correlations between shares will change over time as well, however, some of these correlations are based on economic fundamentals, and these are more likely to persist into the future.

This tool allows you to modify the assumptions on expected returns, future correlations, risk, etc, and model your portfolio accordingly by adjusting the matrix and other values. You can experiment with different portfolio allocations and compare the return of your portfolio to other possible historical allocations you could have created. Furthermore, the excel sheet creates a template which can be used with Excel macros and other tools to run simulations such as a Monte Carlo simulation or for calculating the Value at Risk.

APPENDIX:

Portfolio variance calculation:

$\sigma_p^2 = \sum_i w_i^2 \sigma_{i}^2 + \sum_i \sum_j w_i w_j \sigma_i \sigma_j \rho_{ij}$

wi = weight of asset i in percent
wj = weight of asset j in percent

σi = Standard deviation of asset i

σj = Standard deviation of asset j

ρij = Correlation between asset i and j

Standard Deviation / Variance of an individual security:

$\sigma = \sqrt{\frac{1}{N} \sum_{i=1}^N (x_i - \mu)^2},$

xi = Priodic holding period return for an individual security

u = the average return for that security in the sample period.

Note: Variance is the same formula without the square root

Holding period return:

$HPR_n \ = \ \frac{Income + (P_{n+1} - P_n)}{P_n}$

Pn+1 = price of security at period n+1

Pn = price of the security at period n

Income = dividend paid in the period of the return calculation. (this is not included in the script calculation which may result in a bias)

Sharpe Ratio:

$S = \frac{R-R_f}{\sigma} = \frac{E[R-R_f]}{\sqrt{\mathrm{var}[R-R_f]}},$

R = Return on portfolio/asset

Rf = The risk free rate

σ = Standard deviation of portfolio / asset

Note: $\sqrt{\mathrm{var}[R-R_f]}=\sqrt{\mathrm{var}[R]}.$