VBA prompted Solver to generate efficient frontier in equity ptf management - code issue

[pdaguet45]

Dear All-
short intro first. I am a prof of Finance in a Business School and teach portfolio management to master students.
In this context, I develop concepts around efficient frontier. I would like to show my students how an efficient frontier could be built out of excel using solver and VBA.
I am currently trying to automate a VBA-prompted Solver to generate an efficient frontier; The solver is pre-calibrated, so no need to set goal, constraints etc...
When I run the macro, nothing executes..... big frustration.
See my code below
See also the level of automation I would ideally want to reach.
YOUR HELP MUCH APPRECIATED
Thanks and Best Regards
https://www.youtube.com/watch?v=fa3TG4ZpJY8 - This vid link show the level of automation I would want to reach. My xlsm is comparable to his
See code below
Sub SolverMacro()


Dim datastart As Integer 'dimension variable




OrigCalculation = Application.Calculation 'store current calculation mode.


iter = 1 'define iter


'Loop


Do While iter <= totiter 'totiter is the cell named for total number of iterations


'take the first target return of the list and put it as target return for the sim
Sheets("Sheet1").Range("targret").Value = Sheets("Sheet1").Range("Anchor").Value

'run the solver
Call SolverSolve(True)
SolverFinish


'define dynamic target row
datastart = Sheets("Sheet1").Range("b20").Value

'report results in the table
Sheets("Sheet1").Range("datastart").Offset(1, 0).Value = Sheets("Sheet1").Range("j5").Value
Sheets("Sheet1").Range("datastart").Offset(1, 1).Value = Sheets("Sheet1").Range("j6").Value
Sheets("Sheet1").Range("datastart").Offset(1, 2).Value = Sheets("Sheet1").Range("j7").Value

iter = iter + 1


Loop


Application.Calculation = OrigCalculation 'reset calculation mode to same as before macro was executed.


End Sub

 

[MoshiM]

@pdaguet45 is totiter given a value in code you haven't shown? As it is, it will initialize to 0 and prevent your do loop from starting. If it is a named range then replace it with Sheets("Sheet1").Range("totiter").Value

Also you could shorten

[COLOR=#333333]Sheets("Sheet1").Range("datastart").Offset(1, 0).Value = Sheets("Sheet1").Range("j5").Value[/COLOR]
[COLOR=#333333]Sheets("Sheet1").Range("datastart").Offset(1, 1).Value = Sheets("Sheet1").Range("j6").Value[/COLOR]
[COLOR=#333333]Sheets("Sheet1").Range("datastart").Offset(1, 2).Value = Sheets("Sheet1").Range("j7").Value[/COLOR]

to

With Sheets("Sheet1")
    For Y = 0 To 2
        .Range("datastart").Offset(1, Y).Value = .Range("j" & 5 + Y).Value
    Next Y
End With

 

[pdaguet45]

Thanks for this. it works much better.
but it only execute a run, not 12. as if the loop was not working....
any idea?

@pdaguet45 is totiter given a value in code you haven't shown? As it is, it will initialize to 0 and prevent your do loop from starting. If it is a named range then replace it with Sheets("Sheet1").Range("totiter").Value

Also you could shorten

[COLOR=#333333]Sheets("Sheet1").Range("datastart").Offset(1, 0).Value = Sheets("Sheet1").Range("j5").Value[/COLOR]
[COLOR=#333333]Sheets("Sheet1").Range("datastart").Offset(1, 1).Value = Sheets("Sheet1").Range("j6").Value[/COLOR]
[COLOR=#333333]Sheets("Sheet1").Range("datastart").Offset(1, 2).Value = Sheets("Sheet1").Range("j7").Value[/COLOR]

to

With Sheets("Sheet1")
    For Y = 0 To 2
        .Range("datastart").Offset(1, Y).Value = .Range("j" & 5 + Y).Value
    Next Y
End With

 

[MoshiM]

Thanks for this. it works much better.
but it only execute a run, not 12. as if the loop was not working....
any idea?

inside the vba editor navigate to the immediate window and type:

?Sheets("Sheet1").Range("totiter").Value

What number is returned when you press enter?
Is "totiter" the correct name?

 

[pdaguet45]

inside the vba editor navigate to the immediate window and type:

?Sheets("Sheet1").Range("totiter").Value

What number is returned when you press enter?
Is "totiter" the correct name?

It returns 12, ie the number of iterations

 

[MoshiM]

It returns 12, ie the number of iterations[/QUOTE

In that case you are likely overwriting the range at some point so let's store the value in a variable at the start.

Sub SolverMacro()

Dim datastart As Long, iter As Long, totiter As Long, Y As Long 'dimension variables

With Application

    OrigCalculation = .Calculation 'store current calculation mode.
    .ScreenUpdating = False

End With

iter = 1 'define iter
totiter = Sheets("Sheet1").Range("totiter").Value

'Loop

Do While iter <= totiter 'totiter is the cell named for total number of iterations

    'take the first target return of the list and put it as target return for the sim
    Sheets("Sheet1").Range("targret").Value = Sheets("Sheet1").Range("Anchor").Value
    
    'run the solver
    Call SolverSolve(True)
    SolverFinish
    
    'define dynamic target row
    datastart = Sheets("Sheet1").Range("b20").Value
    
    'report results in the table
    With Sheets("Sheet1")
        For Y = 0 To 2
            .Range("datastart").Offset(1, Y).Value = .Range("j" & 5 + Y).Value
        Next Y
    End With

    iter = iter + 1

Loop

With Application

    .Calculation = OrigCalculation 'reset calculation mode to same as before macro was executed.
    .ScreenUpdating = True
    
End With

End Sub

 

[pdaguet45]

Thanks for your appreciated efforts but it does not work.
It would have been easier if I had shared the xlsm with you.
anyway, i'll see how I can get it fixed
Thanks and Best regards

It returns 12, ie the number of iterations[/QUOTE

In that case you are likely overwriting the range at some point so let's store the value in a variable at the start.

Sub SolverMacro()

Dim datastart As Long, iter As Long, totiter As Long, Y As Long 'dimension variables

With Application

    OrigCalculation = .Calculation 'store current calculation mode.
    .ScreenUpdating = False

End With

iter = 1 'define iter
totiter = Sheets("Sheet1").Range("totiter").Value

'Loop

Do While iter <= totiter 'totiter is the cell named for total number of iterations

    'take the first target return of the list and put it as target return for the sim
    Sheets("Sheet1").Range("targret").Value = Sheets("Sheet1").Range("Anchor").Value
    
    'run the solver
    Call SolverSolve(True)
    SolverFinish
    
    'define dynamic target row
    datastart = Sheets("Sheet1").Range("b20").Value
    
    'report results in the table
    With Sheets("Sheet1")
        For Y = 0 To 2
            .Range("datastart").Offset(1, Y).Value = .Range("j" & 5 + Y).Value
        Next Y
    End With

    iter = iter + 1

Loop

With Application

    .Calculation = OrigCalculation 'reset calculation mode to same as before macro was executed.
    .ScreenUpdating = True
    
End With

End Sub

 

[MoshiM]

Thanks for your appreciated efforts but it does not work.
It would have been easier if I had shared the xlsm with you.
anyway, i'll see how I can get it fixed
Thanks and Best regards

Upload it to dropbox ,open it for sharing and post the link it gives you.

 

[pdaguet45]

Dear MoshiM
here is the link
https://www.dropbox.com/s/icwyomm09lr1p3a/11-Equity Revisited-v2.xlsm?dl=0
Hope it works
tell me if not
best

 

[MoshiM]

Dear MoshiM
here is the link
https://www.dropbox.com/s/icwyomm09lr1p3a/11-Equity Revisited-v2.xlsm?dl=0
Hope it works
tell me if not
best

The code was messing up at "datastart = Sheets("Sheet1").Range("b20").Value" and I noticed that it is no longer present in the original version of the macro that you left inside the file, so just comment out that line in the macro I supplied.

The macro runs the requested 12 times. If you still aren't getting the result you want then the problem is with your solver macro. You can set breakpoints by pressing to the left of the code line and then use F8 to step through the code line by line or use F5 to run the code until the breakpoint is encountered again.