Tricky -Loops - Out of Memory Problem !

[NeedSomeAnswers]

Hi all,

I have been asked by a user to look at some VBA code that he has in a spreadsheet for him as he keeps getting out of memory error's

Now i have looked at the code and as far as i can see it's use of Massive nested loops is part of the problem.

Unfotunatley i don't fully understand what he is trying to do, i believe that it is a system to predict future prices by taking an initial price and by using a calculation & a random factor produce 3 new numbers on the next level so you build up a tree like structure of prices. This continues on down 100 times until a future price can be predicted, through an averaging of values.

The code in the attached spreadsheet (zipped up) will give you a clearer idea of what he is trying to achieve !

Now can anybody think of a more memory efficient way of doing this ?? as i am a little lost.

any suggestions would be apprieciated !!

[salvelinus]

Out of memory errors in loops are usually caused by an infinite loop, one that never reaches an end point. How much memory is in the user's machine?
If the loops are basically doing the same thing, instead of having tons of nested loops, maybe recursion would work better. Just make sure it ends at some point.

[Calibra]

An piece of the code you posted :

For d1 = 1 To 3
                For d2 = 1 To 3
                    For d3 = 1 To 3
                        For d4 = 1 To 3
                            For d5 = 1 To 3
                                For d6 = 1 To 3
                                    For d7 = 1 To 3
                                        For d8 = 1 To 3
                                            For d9 = 1 To 3
                                                                   
                                                Randomize
                                                S(9, d1, d2, d3, d4, d5, d6, d7, d8, d9, 0) = S(8, d1, d2, d3, d4, d5, d6, d7, d8, 0, 0) * (Exp((Risk_Free_Rate - Var / 2) * Delta_t + Standard_Deviation * Application.WorksheetFunction.NormSInv(Rnd) * Sqr(Delta_t)))
                                                                    
                                            Next d9
                                        Next d8
                                    Next d7
                                Next d6
                            Next d5
                        Next d4
                    Next d3
                Next d2
            Next d1

so this loop wil do this piece of code :

Randomize
                                                S(9, d1, d2, d3, d4, d5, d6, d7, d8, d9, 0) = S(8, d1, d2, d3, d4, d5, d6, d7, d8, 0, 0) * (Exp((Risk_Free_Rate - Var / 2) * Delta_t + Standard_Deviation * Application.WorksheetFunction.NormSInv(Rnd) * Sqr(Delta_t)))

((((((((3) x 3) x 3) x 3) x 3) x 3) x3) x3) x3 = 19683 x

and this is just one of the many nested loops, i guess the many loops just cause the memory probs, there doesn't have to be an infinite loop

[NeedSomeAnswers]

Calibra - My thoughts exactly! - to many loops !

The loops go from 1 (for/next) loop to 2 nested (for/next) loops all the way to 10 and then back to 1 again

e.g. -

for n = 0 to 3

next

for n = 0 to 3
for i = 0 3

next i
next n

then 3 loops nested, then 4 then 5 then 6 then 7 then 8 then 9 then 10

then 9 then 8 then 7 then 6 then 5 then 4 then 3 then 2 then 1

and all this in a sub which is called inside another (for/next) loop which goes from 0 - 100

Thats a whole lot of looping !

I suppose what i am asking is thier another way of doing this with out so many loops ????

ps - It will probabley help alot to look at the spreadsheet to understand exactley what i mean !

pps -salvelinus recursion ?? An Example would help me understand what you mean !

[salvelinus]

Recursion is when a function or sub calls itself. An example (I didn't download your code, this is just an example) -

Private Sub RecursiveX(ByRef x as Integer)
   If x > 0 Then
      If x = 10 Then
         x = x * 3.1415
         Exit Sub
      Else
         x = x + 1
         RecursiveX(x)   'recursive call
      End If
   Else
      x = 0
   End If
End Sub
 
Private Sub CallRecursiveX()
   dim i as Integer
 
   i = 0
   RecursiveX(i)   'i = 0 - was set to 0 in RecursiveX
   i = 3    
   RecursiveX(i)   'i = 30.1415 - was incremented to 10 via recursive calls, then multiplied
   i = 10
   RecursiveX(i)   'i = 30.1415 - no recursive call
   i = 20
   RecursiveX(i)   'i = ?? - this will be an infinite loop till memory runs out,
                 '           because there's nothing to end the recursive calls  
End Sub

That's not an exciting example, and recursive calls can have memory problems too (they keep adding to the stack), but it's easier to read than tons of nested functions.
If there are functions nested more than 5 deep, you've got too many. 3 or 4 are better.

[Comintern]

Hi all,

Unfotunatley i don't fully understand what he is trying to do, i believe that it is a system to predict future prices by taking an initial price and by using a calculation & a random factor produce 3 new numbers on the next level so you build up a tree like structure of prices. This continues on down 100 times until a future price can be predicted, through an averaging of values.

LOL! OK, this is one case where more is not better. The link below describes what he is trying to do, and if I were you, I'd draw this part to his attention:

While increasing the sample size is one technique for reducing the standard error of a Monte Carlo analysis, doing so can be computationally expensive. A better solution is to employ some technique of variance reduction. These techniques incorporate additional information about the analysis directly into the estimator. This allows them to make the Monte Carlo estimator more deterministic, and hence have a lower standard error.

If you really want a crack at coding this, this link will get you started:

http://www.riskglossary.com/articles...rlo_method.htm

If you don't, I'd forward this link to your co-worker:

http://www.add-ins.com/analyzer/index.htm