VBA overflow/ price data acquisition

[samuelr]

I'm writing a macro that reads price data from an excel file, write them on another excel file and does basic calculations (e.g. returns). The data is made of 2600 x 100 data points.
When I run it i have an overflow error! looks like it's handling too much data. Is it an issue with the computer's memory? (i have a modern laptop with 4GB Ram) or does it have to do with the way the data is stored during the calulations? What I don't get is that if I try to do that kind of calculations in the spreadsheet directly using formulas, i won't have an overflow message.

If i declare the looping variables as integer 60% of the calculation is done before the overflow, if I declare them as long... only 10%
Thanks for your help.
Sam


Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim n As Long
Dim o As Long
' FYI yearsconsidered = 10
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">For j = 1 To Group(k).TickerCount
For m = 1 To Quotes(j, k).ContinuationCount
For i = 1 To YearsConsidered * 261
If Sheets(j).Cells(i - GivenPeriod, 4 * m - 3) = 0 Then
Sheets(j).Cells(i, 4 * m - 2) = 0
Else
Sheets(j).Cells(i, 4 * m - 2) = (Sheets(j).Cells(i, 4 * m - 3) - Sheets(j).Cells(i - GivenPeriod, 4 * m - 3)) / Sheets(j).Cells(i - GivenPeriod, 4 * m - 3)
End If
Next i
Next m
Next j

Btw, I'm trying to avoid using Access.</code>

 

[RoryA]

FYI, cross posted here: excel vba - Overflow when processing historical price data (VBA) - Stack Overflow