**Click to Download Workbook: Efficient Frontier Visual**

**Overview: **

This workbook automatically generates the weights of a minimum variance portfolio as well as a graphical representation of Efficient Frontier for a portfolio of selected assets over a given time frame. This is mostly done using Visual Basic.

**Instructions:**

To start, there are multiple inputs the user must note.

1.) First, input a series of tickers in the designated column.

2.) As always, you must select a time frame from the data validation drop-down list.

Optional: The risk-free is simply there if you wanted to calculate and graph the Tangency Portfolio or otherwise known as the Capital Market Line (CML) by hand. This workbook does not support the generation of the Tangency Portfolio given a portfolio of assets. The Tangency Portfolio or CML essentially is a portfolio with a maximized Sharpe Ratio.

Optional: Next, in order to generate the Efficient Frontier, we can design two other optimal portfolios with different target volatilities in order to plot on the frontier. These target volatilities are already set to a predefined range between +1% and +2% from the minimum variance portfolio via VBA. This is why they aren’t highlighted in orange to indicate an input cell. Feel free to go into the VBA window and change the values to your desire.

3.) This workbook also automates solver in order to calculate weights for the three portfolios given constraints. We can modify solver directly through cell inputs. Choose to allow short positions, include a minimum boundary, include a maximum boundary, and choose a weight in the first (x) optimal portfolio calculated given a target standard deviation. This is crucial you input a value here because it will generate the curve of the frontier given this weight.

4.) Finally, if desired you can select a minimum and maximum percentage in a given stock under the output statistics.

Next, we can delve into the inner workings of this workbook. The full code won’t be displayed. Simply download the workbook and view it in the VBA window to see it in its entirety. We first download the stock data and calculate daily returns for each asset. We need to calculate a few statistics from the percent change sheet, including annualized standard deviation and annualized return. These numbers will help us calculate our portfolio mean and volatility. Once we do this, we can create a covariance matrix based upon our historical returns. This has to be done dynamically as users can enter different time frames for the price data thus altering the count of returns.

We have to modify the output of the covariance matrix in order to generate portfolio volatility This will involve a slight amount of linear algebra. We must first transpose the output to fill the entire matrix. Next, we must annualize the matrix (multiply each element by 252). We can accomplish both of these tasks by using the paste special function in VBA. Not for the tranpose function we have to skip blanks or else we will have an incorrect output.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
Sub cov_matrix() Dim lastCol, lastRow, lastRow1, lastColMatrix, lastRowMatrix As Integer '--------------Create Dynamic Correlation Matrix With Variables----------------------' lastCol = Sheets("Percent Change").Cells(1, Columns.Count).End(xlToLeft).Column lastRow = Sheets("Percent Change").Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row 'Add Sheet For Matrix With ThisWorkbook .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Covariance Matrix" End With 'Run Covariance App Application.Run "ATPVBAEN.XLAM!Mcovar", _ Sheets("Percent Change").Range(Cells(6, 2).Address(), Cells(lastRow, lastCol).Address()), _ Sheets("Covariance Matrix").Range("B2"), "C", True lastColMatrix = Sheets("Covariance Matrix").Cells(2, Columns.Count).End(xlToLeft).Column lastRowMatrix = Sheets("Covariance Matrix").Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row 'Trasnspose Matrix Sheets("Covariance Matrix").Range(Cells(3, 3).Address(), Cells(lastRowMatrix, lastColMatrix).Address()).Copy Range("C" & lastRowMatrix + 2).Select ActiveSheet.Paste lastRow1 = Sheets("Covariance Matrix").Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row Sheets("Covariance Matrix").Range(Cells(lastRowMatrix + 2, 3).Address(), Cells(lastRow1, lastColMatrix).Address()).Copy Sheets("Covariance Matrix").Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=True, Transpose:=True Sheets("Covariance Matrix").Range(Cells(lastRowMatrix + 2, 3).Address(), Cells(lastRow1, lastColMatrix).Address()).Clear Sheets("Covariance Matrix").Range("C" & lastRowMatrix + 2).FormulaR1C1 = "252" Sheets("Covariance Matrix").Range("C" & lastRowMatrix + 2).Copy lastRowMatrix = Sheets("Covariance Matrix").Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Row lastColMatrix = Sheets("Covariance Matrix").Cells(2, Columns.Count).End(xlToLeft).Column Sheets("Covariance Matrix").Range(Cells(3, 3).Address(), Cells(lastRowMatrix, lastColMatrix).Address()). _ PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False |

From this, we can generate formulas for the portfolio mean and volatility The portfolio mean will be calculated as the sum product of set weights and expected returns previously calculated. Portfolio volatility is a bit more complicated.

The formula in excel will go as:

‘=SQRT(MMULT(MMULT(TRANSPOSE(Parameters!I3:I8),’Covariance Matrix’!C3:H8),Parameters!I3:I8))

In English, this formula is saying we are taking the square root of the matrix multiplication of the transpose of the weights in each asset and the covariance matrix. This an also an array formula thus you must hit control-shift-enter in order for the value to be outputted.

Next, we can start to generate optimal weights using Solver within Visual Basic. From here, we can use the following code to find the Minimum Variance Portfolio. Not that Solver is used dynamically here with variable ranges. We first want to reset solver in order to add new constraints. Next, we want to set the objective function. What we are saying here is that we are minimizing portfolio volatility by modifying the weights of each asset. We are also constraining the portfolio so that all the weights add up to one. The other arguments passed in relation to the solver input cells on the main sheet. These primarily include upper and lower bounds for each assets respective weight.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Sub solver_func() Dim lastRow, lr As Integer lastRow = Sheets("Parameters").Cells(Sheets("Parameters").Rows.Count, "M").End(xlUp).Row lr = Sheets("Parameters").Cells(Sheets("Parameters").Rows.Count, "E").End(xlUp).Row Solverreset 'Set Objective Function SolverOk SetCell:="$L$3", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$3:$I" & lastRow, _ Engine:=1, EngineDesc:="GRG Nonlinear" 'Add Constraints 'Sum = 1 solveradd cellref:="$L$4", relation:=2, formulatext:="$L$8" If Sheets("Parameters").Range("C10").Value = "yes" Then SolverOptions Assumenonneg:=False Else SolverOptions Assumenonneg:=True End If If Sheets("Parameters").Range("C11").Value = "yes" Then 'Min Stock Val solveradd cellref:="$I$3:$I" & lastRow, relation:=3, formulatext:="$L$7" End If If Sheets("Parameters").Range("C12").Value = "yes" Then ''Max Val solveradd cellref:="$I$3:I" & lastRow, relation:=1, formulatext:="$L$8" End If 'Ignore dialog box SolverSolve userfinish:=True |

We want to save this portfolio in terms of the weights, variance and mean. We can now create two other optimal portfolios in order to construct the Efficient Frontier. We will again use solver here to solve for the portfolio weights. In this scenario, however, we are modifying the objective function to a target return set by the user. Of course, we still want the weights to sum to one. Other optional constraints used in the prior iteration of Solver will be used in these calculations also. The code is exactly the same except the objective function. The following line is changed to: MaxMinVal:=3, ValueOf:target cell. 3 means equal to and the value of is the target standard deviation in this case. Once the portfolio weights are calculated again, we want to save the scenarios and move onto generating the graph of the Efficient Frontier. The results of the three portfolio statistics are displayed on the sheet, “Portfolio Statistics.”

What we have to do next is create a linear combination of the two optimal portfolios calculated with the target standard deviations. In essence, we are combining the two portfolios in order to get a single mean, volatility. The reason we are doing this is to create a data table which will utilize the weight we set in portfolio x initially to generate the data table using excels Data Table function under the What If tab.

We must first calculate the covariance of the two combined portfolios. This must be done in order to calculate volatility.

Here is the formula we will use (Array Formula: Must hit control-shift-enter)

=MMULT(MMULT(TRANSPOSE(weights in optimal portfolio 1),original covariance matrix for the assets),weights in optimal portfolio 2)

Next, we can calculate the combined mean:

=Weight in portfolio x*portfolio 1 mean+(1-Weight in portfolio x)*portfolio 2 mean

Finally, we can calculate the variance or volatility:

=SQRT(weight in x^2*portfolio 1 volatility^2+(1-weight in x)^2*portfolio 2 volatility^2+2*weight in x*(1-weight in x)*covariance of two portfolios)

This is the linear combination output:

Now, we can generate the data table and graph the Efficient Frontier. We have to set up our data table first. The data table will consist of two variables that will be manipulated. These two variables must be linked to a cell with a formula. In this case, we are referencing the combined portfolio volatility and combined portfolio mean. We can also autofill a range of pseudo weights. What I mean by this is that within the data table these numbers will represent different allocations in portfolio x and portfolio y. This will thereby alter our risk-return for each point in our table. Under the Data tab, click What-If Analysis. Select data table and make sure the column input cell refers to the cell referencing the set weight in portfolio x. When finished, the output will be different risk-return values for different allocations within the optimal portfolios. The VBA code for the generation of the data table is located right under the images of the data table formation.

1 2 |
'Create Data Table Sheets("Portfolio Statistics").Range("T4:V122").Table ColumnInput:=Sheets("Portfolio Statistics").Range("R4") |

When we plot the risk-return columns of the data table, we get a curve which represents the Efficient Frontier for our portfolio.

From here, we can plot the individual risk-return values of each stock as well as the risk-return values of the minimum variance portfolio, optimal portfolio 1, and optimal portfolio 2. We can do this by adding multiple series onto the chart. With the risk returns of each stock, we will need to use a for loop in order to plot the values and generate data labels. The chart is created automatically in VBA with the following code.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
Sub crt() Sheets("Parameters").Activate On Error GoTo crt Sheets("Parameters").ChartObjects.Delete On Error GoTo 0 crt: Dim lastRow, lastTicker As Integer lastRow = Sheets("Portfolio Statistics").Cells(Rows.Count, "U").End(xlUp).Row lastTicker = Sheets("Parameters").Cells(Rows.Count, "E").End(xlUp).Row 'Create Chart Sheets("Parameters").Shapes.AddChart2(240, xlXYScatterLines).Select 'ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Sheets("Sheet1").Cells(4, 10), Sheets("Sheet1").Cells(lastRow - 1, 10)) With ActiveChart .Parent.Height = Range("P2:P25").Height .Parent.Width = Range("P2:AC2").Width .Parent.Top = Range("P2").Top .Parent.Left = Range("P2").Left .ChartTitle.Characters.Text = "Efficient Frontier" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Expected Risk" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Expected Return" .HasLegend = True End With Dim x As Integer For x = 3 To lastTicker With ActiveChart.SeriesCollection.NewSeries .XValues = _ "=Parameters!$H$" & x .Values = _ "=Parameters!$G$" & x .Name = "=Parameters!$E$" & x .Format.Line.Visible = msoFalse End With Next x With ActiveChart.SeriesCollection.NewSeries .XValues = _ "='Portfolio Statistics'!$E$4" .Values = _ "='Portfolio Statistics'!$E$3" .Name = "=""MVP""" End With With ActiveChart.SeriesCollection.NewSeries .XValues = _ "='Portfolio Statistics'!$J$4" .Values = _ "='Portfolio Statistics'!$J$3" .Name = "=""OPT1""" End With With ActiveChart.SeriesCollection.NewSeries .XValues = _ "='Portfolio Statistics'!$O$4" .Values = _ "='Portfolio Statistics'!$O$3" .Name = "=""OPT2""" End With With ActiveChart.SeriesCollection.NewSeries .XValues = _ "='Portfolio Statistics'!$U$4:$U$122" .Values = _ "='Portfolio Statistics'!$V$4:$V$122" .Name = "=""Frontier""" .MarkerSize = 3 End With Call AddDataLabels End Sub Sub AddDataLabels() Dim bubbleChart As ChartObject Dim mySrs As Series Dim myPts As Points With ActiveSheet For Each bubbleChart In .ChartObjects ActiveChart.FullSeriesCollection(1).Select Selection.Delete For Each mySrs In bubbleChart.Chart.SeriesCollection Set myPts = mySrs.Points myPts(myPts.Count).ApplyDataLabels With myPts(myPts.Count).DataLabel .ShowSeriesName = True .ShowCategoryName = False .ShowValue = False ' optional parameters .Orientation = 0 .Font.Size = 10 .Font.Bold = True End With Next Next End With Application.ScreenUpdating = True End Sub |

The output of all of this madness is below. Note that this is an approximate method using multiple user inputs in order to generate the Efficient Frontier graph. Thus, the Minimum Variance Portfolio point along with others will not always lie exactly on the line.

* UPDATE:* I changed the layout of the workbook including the graph displayed. The workbook is a bit more organized and the graph output is more succinct.

I tried to run the code ,but I am getting error like runtime error 1004 , I am using excel 2016 , can you explain me what’s the difference between excel version you are using and excel version of 2016