I have 12mths of data OHLCV which includes ASX/Chi-X data (data...

I have 12mths of data OHLCV which includes ASX/Chi-X data (data from ANZ PRo / Comsec currently but will change soon) ... Historically have about 15 yrs of data but slightly harder to retrieve that for back testing. Not using Json, Yes run VBA . VBA is fine but slow ... to speed it up Preload "all" data in all sheets into a VBA 2d Array... then ref array instead of cells which is 10x faster


So for example, to load rows 1 to 500 and up to col 50 into 2d array called Tradesim you use :-

TradeSim = Range(Cells(1, 1), Cells(500, 50))

To unload from Array back to Sheet

Range(Cells(1, 1), Cells(500, 50)) = Tradesim

So takes a short while a sec or two to load/unload but the increased speed using the array is more than worth it.


So normal Code would be to add two cells is:-

Results = Cells(1,2) + Cells(5,7)

this is slow...

So instead load data into arrays as stated above and

Results = TradeSim(1,2) + TradeSim(5,7)

Use Loops to step through Arrays


You can also use the record feature in Excel to record any actions (Mouse and Keyboard or anything picked from menus ) you do in sheet into a macro and edit the macro...