1 Attachment(s)
Tricky -Loops - Out of Memory Problem !
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 !!
Re: Tricky -Loops - Out of Memory Problem !
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.
Re: Tricky -Loops - Out of Memory Problem !
An piece of the code you posted :
VB Code:
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 :
VB 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
Re: Tricky -Loops - Out of Memory Problem !
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 !
Re: Tricky -Loops - Out of Memory Problem !
Recursion is when a function or sub calls itself. An example (I didn't download your code, this is just an example) -
VB Code:
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.
Re: Tricky -Loops - Out of Memory Problem !
Quote:
Originally Posted by NeedSomeAnswers
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:
Quote:
Originally Posted by www.riskglossary.com
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
