0

The code ( takes randomly generated 2d array (R,C)) and breaks results down in 2 arrays for each month (21 days) ( 1 for the average , 1 for the exposure )

 Public R As Double
 Public C As Integer
 Public Strike,Spot,Ton  As Double
 Public Ton As Double
 Public AVG(), EXP(), w() As Variant
 ''''''''''''''
 Sub Start()
 sss = SetParmeters()
 MonteCarlo (Copy2Array())
 i = calcW()
 cpSheet (True)
 End Sub 
 '''''''''''''''
  Function SetParmeters() As Boolean
 Dim w As Worksheet
 Set w = Worksheets("Copper daily moves")
R = w.Range("cc")
C = w.Range("row")
Strike = w.Range("strike")
Spot = w.Range("spot")
Ton = w.Range("ton")
End Function                       
'''''''''''''''''''
Function Copy2Array() As Variant
  Dim w As Worksheet
  Set w = Worksheets("Copper daily moves")
  Copy2Array = w.Range("price")
 End Function
''''''''''''''''''''
 Function MonteCarlo(p As Variant)
 ReDim w(R, C)
 For i = 0 To R
 s = Spot
 For j = 0 To C
 w(i, j) = s * (1 + p(Application.WorksheetFunction.RandBetween(1, 1275), 1))
 s = w(i, j)
 Next j
 Next i
 End Function
''''''''''''''''
 Function calcW() As Boolean
 ReDim AVG(R, (C / 21) - 1)
 ReDim EXP(R, (C / 21) - 1)
 Count21 = 0
 countW = 0
 For i = 0 To R
 Sum = 0
 countW = 0
 Count21 = 21
 For j = 0 To C
 Sum = Sum + w(i, j)
 If Count21 = j Then
 AVG(i, countW) = Sum / 21
 If Strike > AVG(i, countW) Then
 EXP(i, countW) = (Strike - AVG(i, countW)) * ((C / 21) - countW) * Ton
 Else
 EXP(i, countW) = 0
 End If
 countW = countW + 1
 Count21 = Count21 + 21
 Sum = 0
 End If
 Next j
 Next i
 End Function
''''''''''''''
Sub cpSheet(flag)
Addr = "$A$1"
Addr = Addr & ":" & Range(Addr).Cells(UBound(AVG), _
UBound(AVG, 2) + 1).Address
Worksheets("AVG").Range(Addr).Value = AVG
Worksheets("EXP").Range(Addr).Value = EXP
End Sub

The Main issue is that it takes to long to process the data ( especially when going for 100k simulations (rows) and above ) and thus it might be faster to do it with excel sheets rather than VBA !!.

i tested it with (R=100k ,c=252) and it takes about 8 mins , is there any way to optimize the code in order of it to run faster ?

ashareef
  • 1,846
  • 13
  • 19
user2522749
  • 1
  • 1
  • 8 mins for so many records is not too long. Bear in mind that copy/paste (or read from one cell and write the value to other one) in Excel is pretty time consuming. – varocarbas Jun 28 '13 at 14:08
  • I would write my own RandBetween function using VBA's built-in Rnd function, rather than make the interprocess call to Excel. That should speed things up a little, though dealing with that many records it does take time to read from the sheet and write back. You can try storing all the worksheet values in an array, though I've never tried it with that much data, so it could be slow, and may not even be possible. – Jon Fournier Jun 28 '13 at 16:52
  • @JonFournier the real bottleneck is in the CalcW ( where i try to go over the 2d array and calculate averages and exposures ) it takes about %70 of the time . The reading and storing of the data from the sheet seems to work fine . not sure y its taking that long since everything is being handled in the RAM – user2522749 Jun 29 '13 at 00:37
  • @varocarbas the whole idea of me using VBA here is 2 cut down the time ( the original version of the simulation made within the sheet manually seems to work just as quick + u can easily refresh (f9)and get new results instantly ) . – user2522749 Jun 29 '13 at 00:41
  • If what you wanted was speeding up the calculations, I am afraid that you made the wrong decision. Macros are for automating sets of complex acions which cannot be done by relying on conventional Excel formulae (or might create too big and unfriendly formulae); you might also rely on a combination of both approaches to deliver the most efficient solution (e.g., formulae slightly corrected/improved via macro). What you cannot think is that something requiring just refreshing (a set of Excel formulae) is slower than creating all these calculations one by one every time (what the macro does). – varocarbas Jun 29 '13 at 07:33

1 Answers1

1

Try to not use Variant types, and use 

Application.ScreenUpdating = False

early in the code and

Application.ScreenUpdating = True

...at the end of the code.

Another tip would be to replace "/" division (returns a single value) by "\" division (returns an integer value). This, specially inside a loop, can improve a little his performance.

aprados
  • 374
  • 4
  • 16