Monte Carlo & solver

**dabrows** · 03-02-2011, 10:07 AM

Hoping someone can give me an idea of how to do this. I basically want to generate random numbers and then run solver based on those numbers. I need want to do this roughly 10000 times at least. I have my solver code how to i incorporate the randomness to the code? suggestions would be MUCH appreciated.


Sub Wealth_Solver2()
 
Dim finalrow As Integer
Dim firstrow As Integer
 
            finalrow = Cells(Rows.Count, 5).End(xlUp).Row
            finalrow2 = Cells(Rows.Count, 8).End(xlUp).Row
            firstrow = Cells(Rows.Count, 1).End(xlUp).Row
            
'solver can;t find solution for 25yrs, > 25yrs problem is too big

'target
    SolverReset
    
    SolverOk SetCell:=Cells(finalrow2, 8).Address, MaxMinVal:=1, _
            ByChange:=Range(Cells(firstrow, 9), Cells(finalrow, 10)).Address
    
'constraints
          
          '> than
       
        'by change range > 0
        SolverAdd CellRef:=Range(Cells(firstrow, 9), Cells(finalrow, 9)).Address, _
                Relation:=3, FormulaText:="0"
        SolverAdd CellRef:=Range(Cells(firstrow, 10), Cells(finalrow, 10)).Address, _
                Relation:=3, FormulaText:="0"
        
        'final total account value >=0
         SolverAdd CellRef:=Cells(finalrow, 5).Address, _
                Relation:=3, FormulaText:="0"
       'final total account value <=1
       SolverAdd CellRef:=Cells(finalrow, 5).Address, _
                Relation:=1, FormulaText:="50"
       
       'consumption in yr 't' is <= to consumption in yr 't-1'
SolverAdd CellRef:=Cells(finalrow, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 1, 8).Address
SolverAdd CellRef:=Cells(finalrow - 1, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 2, 8).Address
SolverAdd CellRef:=Cells(finalrow - 2, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 3, 8).Address
SolverAdd CellRef:=Cells(finalrow - 3, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 4, 8).Address
SolverAdd CellRef:=Cells(finalrow - 4, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 5, 8).Address
SolverAdd CellRef:=Cells(finalrow - 5, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 6, 8).Address
SolverAdd CellRef:=Cells(finalrow - 6, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 7, 8).Address
SolverAdd CellRef:=Cells(finalrow - 7, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 8, 8).Address
SolverAdd CellRef:=Cells(finalrow - 8, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 9, 8).Address
SolverAdd CellRef:=Cells(finalrow - 9, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 10, 8).Address
SolverAdd CellRef:=Cells(finalrow - 10, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 11, 8).Address
SolverAdd CellRef:=Cells(finalrow - 11, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 12, 8).Address
SolverAdd CellRef:=Cells(finalrow - 12, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 13, 8).Address
SolverAdd CellRef:=Cells(finalrow - 13, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 14, 8).Address
SolverAdd CellRef:=Cells(finalrow - 14, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 15, 8).Address
SolverAdd CellRef:=Cells(finalrow - 15, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 16, 8).Address
SolverAdd CellRef:=Cells(finalrow - 16, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 17, 8).Address
SolverAdd CellRef:=Cells(finalrow - 17, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 18, 8).Address
SolverAdd CellRef:=Cells(finalrow - 18, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 19, 8).Address
SolverAdd CellRef:=Cells(finalrow - 19, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 20, 8).Address
SolverAdd CellRef:=Cells(finalrow - 20, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 21, 8).Address
SolverAdd CellRef:=Cells(finalrow - 21, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 22, 8).Address
SolverAdd CellRef:=Cells(finalrow - 22, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 23, 8).Address
SolverAdd CellRef:=Cells(finalrow - 23, 8).Address, _
                                    Relation:=1, FormulaText:=Cells(finalrow - 24, 8).Address

            
        'consumption > 0
        SolverAdd CellRef:=Range(Cells(firstrow, 8), Cells(finalrow, 8)).Address, _
                Relation:=3, FormulaText:="0"
     
        'withdrawal values <= account values
        SolverAdd CellRef:=Range(Cells(firstrow, 9), Cells(finalrow, 9)).Address, _
                Relation:=1, FormulaText:=Range(Cells(firstrow, 3), Cells(finalrow, 3)).Address
        SolverAdd CellRef:=Range(Cells(firstrow, 10), Cells(finalrow, 10)).Address, _
                Relation:=1, FormulaText:=Range(Cells(firstrow, 4), Cells(finalrow, 4)).Address
        
    
    SolverSolve UserFinish:=False 'true= don;t show result box

**shg** · 03-02-2011, 10:32 AM

Sub Wealth_Solver2()
    Dim lRowA       As Long
    Dim lRowE       As Long
    Dim lRowH       As Long
    Dim i           As Long

    lRowA = Cells(Rows.Count, "A").End(xlUp).Row
    lRowE = Cells(Rows.Count, "E").End(xlUp).Row
    lRowH = Cells(Rows.Count, "H").End(xlUp).Row

    SolverReset
    
    'solver can't find solution for 25yrs, > 25yrs problem is too big

    SolverOk SetCell:=Cells(lRowH, "H").Address, _
             MaxMinVal:=1, _
             ByChange:=Range(Cells(lRowA, "I"), Cells(lRowE, "J")).Address

    SolverAdd CellRef:=Range(Cells(lRowA, "J"), Cells(lRowE, "J")).Address, _
              Relation:=3, _
              FormulaText:="0"
              
    SolverAdd CellRef:=Cells(lRowE, "E").Address, _
              Relation:=3, _
              FormulaText:="0"
    
    'final total account value <=1
    SolverAdd CellRef:=Cells(lRowE, "E").Address, _
              Relation:=1, _
              FormulaText:="50"

    'consumption in yr 't' is <= to consumption in yr 't-1'

    For i = 0 To 23
        SolverAdd CellRef:=Cells(lRowE - i, "H").Address, _
                  Relation:=1, _
                  FormulaText:=Cells(lRowE - i - 1, "H").Address
    Next i

    'consumption > 0
    SolverAdd CellRef:=Range(Cells(lRowA, "H"), Cells(lRowE, "H")).Address, _
              Relation:=3, _
              FormulaText:="0"

    'withdrawal values <= account values
    SolverAdd CellRef:=Range(Cells(lRowA, "I"), Cells(lRowE, "I")).Address, _
              Relation:=1, _
              FormulaText:=Range(Cells(lRowA, "C"), Cells(lRowE, "C")).Address
    
    SolverAdd CellRef:=Range(Cells(lRowA, "J"), Cells(lRowE, "J")).Address, _
              Relation:=1, _
              FormulaText:=Range(Cells(lRowA, "D"), Cells(lRowE, "D")).Address

    SolverSolve UserFinish:=False
End Sub

Randomness in what?

**dabrows** · 03-02-2011, 10:58 AM

sorry i should have explained. i have yet to introduce randomness, but I want to. essentially columns 5 and 6 are currently growing linearly [modeled in excel] and I need to change that to random. I tried to change the if statement in excel to incorporate randomness but solver can;t find solutions. thanks for the reply and the cleaner code

**shg** · 03-02-2011, 04:11 PM

There is no IF statement in your code.

**dabrows** · 03-03-2011, 11:59 AM

True, sorry I should have been more clear. I want to put an if statement in the code or excel file- which ever is easiest.

Basically, columns F and G need to be random.[Note only F is random in the attached file- trying to figure out F before I move on to G]

I need to generate random values for columns F and G and then run the solver code that is in the macro on each of the random numbers that are generated. I need to do this 100,000 times.

Again, column N may not work for you b/c its results stem from a proprietary add-in I have installed.

Any help/suggestions would be great. Please and thanks

**dabrows** · 03-03-2011, 03:20 PM

any ideas? I would appreciate any help at all. Please and thanks

**Greg777** · 07-03-2011, 08:53 PM

I looked at your spreadsheet and I am not able to make sense of everything that is going-on. An abstract version with only the question at hand may be your best chance of getting one of the excel gurus to help on this. My suggestion for a quick solution would be to introduce your randomness by specifying a distribution and a random variable from 0 to 1. The distribution (normal or whatever) works on the random number, generating a coefficient. Use that to multiply your columns F & G. Apply your code to your new F & G columns. That way it sidesteps additional VB code.