We are going to find out what the optimal portfolio weights, return and standard deviation for each month of our dataset, and store these values. The method we use to find these results are Mean-Variance/ Markowitz optimization, where max the Sharpe ratio in Solver.
We want to base the mean returns/ called "Expected Returns" in the excel sheet on the previous 24 observations at each point, meaning that the first optimization we will run will be for 1972-01-01, based on monthly data between 1970-01-01 to 1971-12-01. Then for the next month (1972-02-01) it will move accordingly down one row, so that the mean will now be based on monthly data between 1970-02-01 to 1972-01-01. And so on so forth, until we reach the end.
Our problem is that we have a file that has a total of 181 lines, and it is to time-consuming to manually run the Solver so many times (also in terms of the data-quality there are many possibilities for us to do something faulty). We also need to do this for several more time-series and set more constraints, so in total we would need to manually run it almost 4000 times.
We tried writing a code that runs the Solver automatic, and then storing the weights under the table called "Weights": starting from row 27 and onward. However, the code will not run as I do not really understand how to use offset in combination with a function calculation.
I am attaching a print-screen of our excel sheet with and without formula references, what we run in the Solver + the VBA code that I tried writing. We would really appreciate it if someone could give us a nudge in the right direction!
We want to base the mean returns/ called "Expected Returns" in the excel sheet on the previous 24 observations at each point, meaning that the first optimization we will run will be for 1972-01-01, based on monthly data between 1970-01-01 to 1971-12-01. Then for the next month (1972-02-01) it will move accordingly down one row, so that the mean will now be based on monthly data between 1970-02-01 to 1972-01-01. And so on so forth, until we reach the end.
Our problem is that we have a file that has a total of 181 lines, and it is to time-consuming to manually run the Solver so many times (also in terms of the data-quality there are many possibilities for us to do something faulty). We also need to do this for several more time-series and set more constraints, so in total we would need to manually run it almost 4000 times.
We tried writing a code that runs the Solver automatic, and then storing the weights under the table called "Weights": starting from row 27 and onward. However, the code will not run as I do not really understand how to use offset in combination with a function calculation.
I am attaching a print-screen of our excel sheet with and without formula references, what we run in the Solver + the VBA code that I tried writing. We would really appreciate it if someone could give us a nudge in the right direction!
VBA Code:
Sub Marko()
Dim i As Integer
For i = 0 To 180
' Variance-Covariance matrix
Range("J13").Select ' Selecting the cell where we want the Variance-Covariance VBA to start to run
Application.CutCopyMode = False
ActiveCell.Formula2R1C1 = "=VarCovar(RC\[-7\]:R\[23\]C\[-3\])".Offset(1,0).Select ' The first 24 months and then move 1 row down each time
' Expected return
Range("J11").Select ' Selecting the cell of the first mean-returns that needs to be calculated (SMB)
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=AVERAGE(R\[-8\]C\[-7\]:R\[15\]C\[-7\]).Offset(1,0).Select" ' Calculating the average of a 24 month window
Range("J11").Select
Selection.AutoFill Destination:=Range("J11:N11"), Type:=xlFillDefault '' Dragging the formula to calculate the average for the other assets
Range("J11:N11").Select
' Standard deviation - doing pretty much the same as for mean-returns
Range("J12").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=STDEV.S(R\[-9\]C\[-7\]:R\[14\]C\[-7\]).Offset(1,0).Select"
Range("J12").Select
Selection.AutoFill Destination:=Range("J12:N12"), Type:=xlFillDefault
Range("J12:N12").Select
' Solver
SolverReset
SolverAdd CellRef:="$J$14", Relation:=2, FormulaText:="1" ' Constraint where all the weights in total need to equal 100%
SolverOk SetCell:="Sharpe", MaxMinVal:=1, ValueOf:=0, ByChange:="Weights", _
Engine:=1, EngineDesc:="GRG Nonlinear"
' Also checking of for Non-negative results so that we get a portfolio of only "Long" weights
SolverSolve
' Approve the solution and avoid the popups
SolverSolve userFinish:=True
SolverFinish KeepFinal:=1
' Copy-Paste resultater
Sheets("First ts").Range("I27:O27").Offset(i, 0).Value = Sheets("First ts").Range("J13:P13").Value
Next i
End Sub
