Good Morning,
First off let me just be clear that I am very new to any type of programming so the code below is probably pretty messy. I created a spreadsheet that determines the asset weights in an investmest portfolio that are needed to minimize standard deviation for any given level of portfolio return using solver. I wrote the following program to run through the calculation multiple times with different constraints to find the optimized value under different portfolio return assumptions. I am getting an error message when I try to run the program that says:
"Solver: An unexpected internal error occurred, or available memory was exhausted."
Can someone with more experience than myself (i.e. everyone) try to help me work through this problem please?
Public Sub EfficientFrontier()
'Set Variable to hold paste row and target mean return
Dim OutputRow As Integer
Dim MeanConstraint As Double
Worksheets("Sheet3").Activate
'Find the efficient portfolios with returns between 6%
'and 10%.
For MeanConstraint = 6 To 10
SolverReset
SolverOptions Precision:=0.001
SolverOK SetCell:=Range("stdev"), _
MaxMinVal:=2, _
ByChange:=Range("Weights")
'the constraint named "constraint" below is simply a cell
'that sums to one if the portfolio weights sum to one. It
'is just to make sure all funds are invested and that it is a
'long only portfolio with no leverage.
SolverAdd CellRef:=Range("Constraint"), _
relation:=2, _
formulaText:=1
SolverAdd CellRef:=Range("Mean"), _
relation:=2, _
formulaText:=MeanConstraint
SolverSolve UserFinish:=False
'Copy and Paste Special Transpose our asset class
'weights from our weights column to our output row that
'starts on row 17 of sheet3.
'Copy and Paste Special Values from our mean, variance,
'and stdev cells ("graph") to our output row.
OutputRow = 17
Range("Weights").Select
Selection.Copy
Sheet3.Cells(2, OutputRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=None, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Range("Graph").Select
Sheet3.Cells(10, OutputRow).Select
Selection.PasteSpecial Paste:=x1PasteValues
Application.CutCopyMode = False
OutputRow = OutputRow + 1
MeanConstraint = MeanConstraint + 0.1
Next
End Sub
Bookmarks