Serving the Quantitative Finance Community

• 1
• 2

Collector
Topic Author
Posts: 5012
Joined: August 21st, 2001, 12:37 pm
Contact:

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

I need to run some large scale loops with hopefully like 10 billion or more simulations. (yes it will take a few days in Excel, no worry about time)In Excel it seems like function not can take much more than 2.1 billion....is this the limitation on "Long" ??Also to get more speed and capacity I moved over to C++, here the programed crashed much earlier on....In Excel use this as test examplePublic Function MCLoop(n As Long, b As Double) As Double Dim i As Long For i = 1 To n Step 1 NextMCLoop = nEnd FunctionOnly reason for the "b" is that you get time to type in "n" before excel starts to calculate... n=2.1 billion works....but 2.2billion and up, I get #NUM!Tips to increase loop capacity in Excel and/or C++ is highely appreciated, I am sure there must be a very simmple solution just in front of my nouse?
Last edited by Collector on September 29th, 2006, 10:00 pm, edited 1 time in total.

Cuchulainn
Posts: 65000
Joined: July 16th, 2004, 7:38 am
Location: AV_63

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

Quote I need to run some large scale loops with hopefully like 10 billion or more simulations. (yes it will take a few days in Excel, no worry about time)In Excel it seems like function not can take much more than 2.1 billion....is this the limitation on "Long" ??Also to get more speed and capacity I moved over to C++, here the programed crashed much earlier on....Difficult to say. Is the loop very big or is is some array? What's the Gist of the algorithm? QuoteI moved over to C++, here the programed crashed much earlier on....What error did you get? Overflow, address problem etc. (run time error?) Maybe break loop into a number of smaller ones (like a kind of wrap around???)
Last edited by Cuchulainn on September 29th, 2006, 10:00 pm, edited 1 time in total.
planetoid 65000 == 2002 AV_63

Collector
Topic Author
Posts: 5012
Joined: August 21st, 2001, 12:37 pm
Contact:

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

I get the following message in C++ (XCode for mac)"error: integer constant is too large for 'long' type"Actually C++ (Xcode) seems to have about same limitation capacity as "Long" in Excel.....somewhere above 2 billion C++ starts to do wrong calculations and somewhat higer it returns error message above. Actually Xcode gives all types of crazy calculations somewhere above 2 billion, first at 4.4 billion it gives error message....error message should be much earlier. Not sure about other compilers. Could this also be computer specific? For Excel I tested out both on Mac and PC and got same error, but for C++ I have so far just run it on my Mac (XCode)so is there any other type that take larger integers? Yes I could build loop around loop or something like that.... (There are 6 billion people on this planet and I can not even do ONE singe simulation per person, And how can I calculate expected profitt if limit is 2 billion ?)
Last edited by Collector on September 29th, 2006, 10:00 pm, edited 1 time in total.

AVt
Posts: 1074
Joined: December 29th, 2001, 8:23 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

Sub L1()Dim i As Integer ' or use longsDim k As IntegerWhile (i < 10001 And k < 101) i = i + 1 If i = 10000 Then i = 0 k = k + 1 End IfWend'Debug.Print CDbl(10000) * CDbl(k - 1)Debug.Print CDec(10000) * CDec(k - 1)End SuborSub L2()Dim ii = CDec(2147483647) ' max for long, 2^31=2147483647 +1While (i < CDec(2148000000#)) i = CDec(i + 1)WendDebug.Print CDec(i)End Sub

Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

You could run the sim 2.1 billion iterations at a time, save the interrim results, rinse and repeat (A macro/VBA/Applescript might help you automate the multiple runs). Or have multiple computers running the same sim and merge results.Note that if you are doing sums over billions of runs, you may run into rounding error issues when you add the next sim's output to accumulating total. The magnitude of this problem depends on the Max/Min range of the output variables and what you are doing with the output. Doing operations that involve differences or matrix inverses might amplify the round-off error.

Collector
Topic Author
Posts: 5012
Joined: August 21st, 2001, 12:37 pm
Contact:

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

Thank you all, now I just need to buy faster computer!

schizoidman
Posts: 123
Joined: September 21st, 2005, 7:17 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

Hi,The limit for:- Int is 2^16 - 1 = 32,767- Long is 2^32 - 1 = 2,147,483,647which is about the 2.1 billion mark that you're observing.

mutley
Posts: 745
Joined: February 9th, 2005, 3:51 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

does VBA allow looping using doubles?

Cuchulainn
Posts: 65000
Joined: July 16th, 2004, 7:38 am
Location: AV_63

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

QuoteOriginally posted by: mutleydoes VBA allow looping using doubles?like?double BIG = 1000000.0;double TOL = 0.00001;double START = 0.0;for (double d = START; d < BIG; d += TOL){// do it}
Last edited by Cuchulainn on October 2nd, 2006, 10:00 pm, edited 1 time in total.
planetoid 65000 == 2002 AV_63

AVt
Posts: 1074
Joined: December 29th, 2001, 8:23 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

as shown below one can do that in 'any' programming language based oninteger data types only, no need for decimal floating & rounding issues(the type cdec is Excel specific, the first example always works)

Posts: 23951
Joined: September 20th, 2002, 8:30 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

QuoteOriginally posted by: AVtas shown below one can do that in 'any' programming language based oninteger data types only, no need for decimal floating & rounding issues(the type cdec is Excel specific, the first example always works)The reason to do the loop with doubles is that doubles offer an extended counting range versus 32-bit ints. With a 52-bit mantissa (and the hidden MSB of 1), you can count up to 2^53 - 1 using doubles (although given the IEEE spec and fun Pentium quirks, I might not trust round-off errors for addition over totals of 2^51. Anyway that's much better than the 2^32-1 limit for signed ints or 2^32-1 limit fro unsigned 32-bit ints.

mutley
Posts: 745
Joined: February 9th, 2005, 3:51 pm

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

QuoteOriginally posted by: CuchulainnQuoteOriginally posted by: mutleydoes VBA allow looping using doubles?like?double BIG = 1000000.0;double TOL = 0.00001;double START = 0.0;for (double d = START; d < BIG; d += TOL){// do it}Yeah, this actually works: Dim big As Double Dim small As Double Dim sum As Double Dim something As Double big = 10000000000# 'this is 1E10 small = 0.0000000001 'this is 1E-10 sum = 0 While (sum < big) something = Now sum = sum + small WendIt will be a bit slower but it allows for much longer loops, without resorting to nested loops.

Cuchulainn
Posts: 65000
Joined: July 16th, 2004, 7:38 am
Location: AV_63

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

QuoteIt will be a bit slower but it allows for much longer loops, without resorting to nested loops.It dependsWith the 'double' loop1. No 'if else'2. The counter can sometimes be used directly in a calculation; in that case it is faster. e.g. for (double d = START; d < BIG; d += TOL){ // Use d DIRECTLY}
Last edited by Cuchulainn on October 9th, 2006, 10:00 pm, edited 1 time in total.
planetoid 65000 == 2002 AV_63

MikeCrowe
Posts: 388
Joined: January 16th, 2006, 8:20 am

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

QuoteOriginally posted by: CollectorI need to run some large scale loops with hopefully like 10 billion or more simulations. (yes it will take a few days in Excel, no worry about time)In Excel it seems like function not can take much more than 2.1 billion....is this the limitation on "Long" ??Also to get more speed and capacity I moved over to C++, here the programed crashed much earlier on....In Excel use this as test examplePublic Function MCLoop(n As Long, b As Double) As Double Dim i As Long For i = 1 To n Step 1 NextMCLoop = nEnd FunctionOnly reason for the "b" is that you get time to type in "n" before excel starts to calculate... n=2.1 billion works....but 2.2billion and up, I get #NUM!Tips to increase loop capacity in Excel and/or C++ is highely appreciated, I am sure there must be a very simmple solution just in front of my nouse?Your problem has nothing to do with the loop. Its simply that you can't pass a value bigger than 2.1bn into a long variable. Your n won't let you. You will get this problem even if you delete the For Next part of your code.I'd suggest you split the loop into an inner and outer loop, its faster. Most logical to set the function up so that it takes n as the number of million iterations. If you need to do billions, then it is unlikely that you will want to specify n=2,564,345,621 rather than simply n=2,564,000,000

MikeCrowe
Posts: 388
Joined: January 16th, 2006, 8:20 am

### Excel Large Scale Loop Capacity Max 2.1 Billion ??

Function Test(n As Long)Dim d As Doubled = 0For x = 1 To nFor y = 1 To 1000000d = d + 1Next yNext xTest = dEnd FunctionThat works fine, but slow