Champion. Thanks for that. Yep, familiar with the macro...

First 166 Last

Share
Profile

Follow
journeyman

2,072 Posts.

33

21/03/18

23:20

Post #: 31933783
Share

Originally posted by Freehold ↑

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...

Expand

Champion. Thanks for that.

Yep, familiar with the macro record/edit approach.

Will attack a portfolio 'manager' first, then all going well on to more complicated watchlist stuff with scanning etc.

Cheers

XAO Value at posting: 6,053.1
1 Upvote

0 Great analysis

Reply

First 166 Last

Add XAO (ASX) to my watchlist

(20min delay)
Last 8,586.9		Change -23.500(0.27%)		Mkt cap ! n/a

Open	High	Low
8,610.4	8,644.8	8,585.3

Champion. Thanks for that. Yep, familiar with the macro...

Featured News

Featured News