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

pdaguet45

New Member
Joined
Feb 7, 2016
Messages
5
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
@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
Code:
[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

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

pdaguet45

New Member
Joined
Feb 7, 2016
Messages
5
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
Code:
[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

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

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
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?
 
Last edited:

pdaguet45

New Member
Joined
Feb 7, 2016
Messages
5

ADVERTISEMENT

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

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Feb 7, 2016
Messages
5

ADVERTISEMENT

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.
Code:
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

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
370
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,061
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top