I’m coding a scenario analysis in Excel with solver so solver is part of the loop. How can I catch or ignore the maximum iteration message? I want to be able to run the scenario test overnigiht with no interceptions. Any feedback will be appreciated. Thank you!
some code I use (Excel 2007). You can set the iterations and maxtime as you wish. This will keep going regardless of the results. 'run the analysis strSolverTarget = MV Worksheets(“bla bla”).Activate SolverReset SolverOptions MaxTime:=300, Iterations:=2000, precision:=0.0001, AssumeLinear:= _ False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverOK SetCell:=Range("$M$2"), MaxMinVal:=3, ValueOf:=strSolverTarget, ByChange:=Range("$K$2") Result = Application.Run(“Solver.xlam!SolverSolve”, True) ’ report on success of analysis If Result < 3 Then ’ Result = 0, Solution found, optimality and constraints satisfied ’ Result = 1, Converged, constraints satisfied ’ Result = 2, Cannot improve, constraints satisfied Else ’ Result = 3, Stopped at maximum iterations ’ Result = 4, Solver did not converge ’ Result = 5, No feasible solution Beep debug.print “Solver was unable to find a solution.”, vbExclamation, “SOLUTION NOT FOUND” 'can write to log file etc with result and meaning End If
They sell some really nice Solver dll’s (www.solver.com). You could integrate them into your code in some real programming language and then you would have something stable and worthwhile. Anything that runs overnight in Excel is a big mistake - unstable, unscalable, and clunky as all heck.
wouldnt something like this work: Initiate Loop Application.ScreenUpdating = FALSE STUFF… SolverSolve (True) SolverReset Application.ScreenUpdating = TRUE Next
Douledip, i tried that method. It doesn’t work. The max iteration dialogue jumps up and asked me whether I want to continue or not. MS, that doesn’t work either. An error message pops out and ask me to halt the process… JDV, I’m basically coding a scenario testing on liquidity for a superannuation fund. I designed four market regimes and how the portfolio is likely to react to the regime shifts plus potential redemptions. So far, the test works on 1 parameter where all liquid market fall by 20%. However, we know it is clearly not the case - bond and equities may go different ways. In that case, I can get out off bond without breaching my corridor and realise any loss. The reason for explaining my situation is so I can ask you… which DLL are you recommeding? Right now, I’m still quite happy with VBA… or I should say I only have VBA…
If you can program in VBA you can program in VB, VB.Net, or C# (with a little work). Anyway, adding C# to your skill set is a marketable thing to do and it’s just not that hard (you’ll need to think objects more but they are really natural after awhile which is why everyone uses them). These dll’s http://www.solver.com/sdkplatform.htm are great (if someone else knows something better I’m all ears). I guess I don’t see where the solver is required from your description of the problem so it’s hard to recommend something. What kind of mathematical problem is contained in the scenario analysis? BTW - I didn’t know what “superannuation” meant, but Wiki tells me you are an Aussie working with retirement money.
yuoska Wrote: ------------------------------------------------------- > MS, that doesn’t work either. An error message > pops out and ask me to halt the process… > hmmm i clearly remember i had the same problem and wanted to run a loop with solvers in it without having to click ‘OK’ on a pop up… the screen.update=false did it for me, try to put it outside the loop instead.
I haven’t even started the loop. I trimmed it down to one occurence and nothing stop the dialogue box to pop up…
actually never mind the screen.updating, thats just to make your screen not go wild when you record a macro the default line the executes the solver is: SolverSolve this gives you a pop up. change it to: SolverSolve(True)
Cant help you spexificLly, but is there some soft of program that could be activated when the popup occurs?
So far, I found one solution that is guarantee to work…an indian quant mentioned to use stickytape on ALT+T… a friend asked me to sendkey events. JDV, spot on. I’look after retirement money in Australia. I’m equipped with C# given I wrote my thesis in C#. However, the company is not willing to throw money into Visual Studio, that probably include the SDKPlatform. Regarding the issue, I need to rebalance my portfolio. If Aussie shares dropped by 20%, likely couple with decreases in overseas shares and global REITs etc. I need to use my FX facility, cash and bond to rebalance my portfolio. I don’t think I need any algorithm for that but via trial and error using solver? That said, I’m open to any sugestions… Worst case is I let go of the all-automated calculations and keep few manual work. MS, I did that. That removes the dialogue at the end of the process. However, there will be one during the process that I need to catch.
OK, I see your problem. Sorry, this had not happened to me. I think this message box is not trappable by the user. The only thing I think you can do is to set a variable for elapsed time that you initialize at the start of each solver call. You have to keep testing this and if elapsed time >> MaxTime, assume that a message box is open (the “show trial solution” message box, that is) and then call to SendKeys to send a “Continue” to the box. I’ve done this kind of thing in the past, and there are hints on the web. On the other hand, I might have found another way to help. Make a test spreadsheet called “bla bla” and put the value of 0 in cell B3, the formula = sqrt(d3^2+1) in C3 and the value 10 in D3. This formula is not solvable in the real domain so should fail. If you manually try to Solve, you get a can’t converge message. For the code segment below, you’ll see your “show trial solution” dialog. But! what if you control this with the maxtime variable. Set MaxIterations to some huge number and it will never fail, but will fall out from the maxtime being exceeded. And, you don’t seem to get the dialog! I set MaxIterations to 100000 and MaxTime to 300. The solver failed, but I didn’t get the message box. Let me know what happens! Sub testsolver() On Error GoTo Errhandler Worksheets(“bla bla”).Activate 'run the analysis Application.DisplayAlerts = False strSolverTarget = Range("$b$3") SolverReset SolverOptions MaxTime:=300, Iterations:=1, precision:=0.0001, AssumeLinear:= _ False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverOK SetCell:=Range("$c$3"), MaxMinVal:=3, ValueOf:=strSolverTarget, ByChange:=Range("$d$3") ’ Do not display the Solver Results dialog box. SolverSolve UserFinish:=True 'result = Application.Run(“Solver.xlam!SolverSolve”, True) ’ report on success of analysis If result < 3 Then ’ Result = 0, Solution found, optimality and constraints satisfied ’ Result = 1, Converged, constraints satisfied ’ Result = 2, Cannot improve, constraints satisfied Else ’ Result = 3, Stopped at maximum iterations ’ Result = 4, Solver did not converge ’ Result = 5, No feasible solution Debug.Print "result = "; result Debug.Print “Solver was unable to find a solution.”, vbExclamation, “SOLUTION NOT FOUND” 'can write to log file etc with result and meaning End If Debug.Print "result = "; result Exit Sub Errhandler: Stop End Sub