how to run solver continuously for several rows

[hirastikanah]

hi
i want to plot an efficient frontier in excel
once i saw this video VBA Excel - Optimization Efficient Frontier - YouTube
i wondered how he plots it by clicking on optimize button while i have to do it row by row !

how should i follow this method?!

thank you

 

[GooberTron]

How is your VBA? He has just automated calling Solver using VBA. If you record a macro whilst using Solver that will get you started.
This page also very useful;
Using Solver in Excel VBA

 

[hirastikanah]

How is your VBA? He has just automated calling Solver using VBA. If you record a macro whilst using Solver that will get you started.
This page also very useful;
Using Solver in Excel VBA

emmm
i know almost nothing about VBA!

here, i record a macro for the first row (while using solver to minimize portfolio risk)

but what should i do with this recorded macro?

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Range("C10").Select
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverDelete CellRef:="$H$27", Relation:=1, FormulaText:="1"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverDelete CellRef:="$G$27", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverDelete CellRef:="$F$27", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverDelete CellRef:="$E$27", Relation:=3, FormulaText:="0"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverDelete CellRef:="$C$27", Relation:=2, FormulaText:="$B$27"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:="$C$10", Relation:=2, FormulaText:="$B$10"
    SolverAdd CellRef:="$E$10", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$F$10", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$G$10", Relation:=3, FormulaText:="0"
    SolverAdd CellRef:="$H$10", Relation:=2, FormulaText:="1"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$D$10", MaxMinVal:=2, ValueOf:=0, ByChange:="$E$10:$G$10", _
        Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
End Sub

 

[GooberTron]

Ok generally for your first row you are trying to minimise the calculated result in D10 by changing cells E10,F10 and G10. Is that correct? If you move onto the next row does that mean you would be trying to minimise D11 by changing cells E11, F11 and G11?

 

[hirastikanah]

yes, exactly !

 

[GooberTron]

Ok, are there any standard initial guesses for cells E, F & G? This can help Solver converge on a solution if you have rough guesses available to begin with. How many rows do you need fitting? Does that change or is it fixed? Is there a column which would have values in so that the VBA code could use to figure out how many rows you have used? Column D presumably is already filled out with an equation which goes down as many rows as you need fitting?

 

[hirastikanah]

i am exactly doing what he has done in that video
there are initial guesses for cells E, F and G (50, 30 and 20% )
the number of rows depends on "Sharpe ratio"
and that is ((C10-"a fixed cell")/D10)
for next row that is ((C11-"a fixed cell")/D11)
.....
and this ratio will increase row by row...
we Continue to run Solver with increasing target averages(C) until the Sharpe ratio is no longer rising with each iteration

 

[GooberTron]

Ok you need to set a reference to Solver in the VBA editor for this to work;
Alt+F11 to get to VBA window then do Tools>References and then make sure Solver is ticked.

My attempt at some code to automate this is as follows. Looking at the video the other constraint seems to be that you should have column H sum to 100% - so I have added that into the Solver. I have also set the initial guesses by VBA but if you have already copied down like the video that doesn't actually matter. Add this code into a standard (non-worksheet) module.
This also assumes you have formatted your % columns as standard numbers not %.

Option Explicit

'''Separate function to find the last used row in a defined column letter
Private Function FindLastRow(ColumnToCheck As String) As Long

    FindLastRow = Range(ColumnToCheck & Rows.Count).End(xlUp).Row

End Function


Sub Optimise()

    'The starting row - this would be better done automatically in code but I don't know your sheet layout
    Const lSTARTROW As Long = 10
    Dim lEndRow As Long
    Dim i As Long
    Const GUESS_E As Long = 50, GUESS_F As Long = 30, GUESS_G As Long = 20
    
    Application.ScreenUpdating = False
    
    lEndRow = FindLastRow("D")
    
    For i = lSTARTROW To lEndRow
        'set initial values programmatically
        ActiveSheet.Cells(i, 5).Value = GUESS_E
        ActiveSheet.Cells(i, 6).Value = GUESS_F
        ActiveSheet.Cells(i, 7).Value = GUESS_G
        'Solver section
        SolverReset
        'Constraint that E+F+G is 100% (assuming formula in H is sum of E+F+G)
        SolverAdd CellRef:=Cells(i, 8), Relation:=2, FormulaText:="100"
        'Run Solver to minimise D by chaging E,F,G columns
        SolverOk SetCell:=Cells(i, 4), MaxMinVal:=2, ValueOf:=0, ByChange:=Range(Cells(i, 5), Cells(i, 7)), Engine:=1, EngineDesc:="GRG Nonlinear"
        SolverSolve True
    Next i

    Application.ScreenUpdating = True
    
End Sub

 

[hirastikanah]

thank you

but still it doesnt work
there are 2 problems
the first one i could find and fix it
'Constraint that E+F+G is 1 (assuming formula in H is sum of E+F+G)
SolverAdd CellRef:=Cells(i, 8), Relation:=2, FormulaText:="1"
"1" instead of 100

but the other problem, emmm, i think its related to a constraint
that is in every row C should be equal to B
C10 = B10
C11 = B11

is it?

 

[GooberTron]

Yeah if columns E,F,G,H are formatted as % then you should change that to 1 in the constraint and also change GUESS_E etc. to be 0.5, 0.3, 0.2 instead of 50,30,20.

Other constraints it's hard for me to tell without having a copy of the workbook and seeing how B & C related to E,F,G. When you recorded the Solver macro above there was a lot of extraneous stuff going on that presumably wasn't necessary. Try re-recording very clearly by resetting Solver and being very clear about what things you are adding.