catching the market at points in time

[surround]

Using an excel worksheet is it possible to catch the traded volume at two points in time at say at 10 minutes and 1 minute before the off?

[Captain Sensible]

What you mean by traded volume, do you mean the amounts matched at certain price points or the overall matched amounts? The traded volume at price points is available via the COM and the matched volume comes thru on the normal link via excel . BA sends time to the off to excel so it's simple enough to grab prices at certain times before the off. There's probably a few examples on the site of grabbing data at set times.

[surround]

I mean the matched amount that shows in Cell B3 using the excel sheet. I just can't see how to anchor that amount at any time point.
And I haven't found yet any examples of grabbing data at set times.

[Captain Sensible]

You'll need to use VBA, something like this should get you started.

It basically checks to see if it's a new market and if so it deletes old data, it'll then log the data in cells AA1 and AA2. I've no idea what refresh rate you use so I've set the times to grab as quite loose but it should grab as close to the 10 minute and 1 minute marks as possible depending on your refresh rate. If your refresh is longer than 1 minute just amend the times. The data goes in cells AA2 & AA1 again just amend those to whatever suits you.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


If Range("A1").Value = MyMarket Then

If Range("D2").Value >= TimeValue("00:10:00") And Range("D2").Value <= TimeValue("00:11:00") Then Range("AA1").Value = Range("B3").Value
If Range("D2").Value >= TimeValue("00:01:00") And Range("D2").Value <= TimeValue("00:02:00") Then Range("AA2").Value = Range("B3").Value



Else

MyMarket = Range("A1").Value
Range("AA1:AA2").Value = "" 'clear old market data

End If


Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[surround]

Thanks for the code Captain. Yes, that should do exactly what I require and will give it a go with tomorrow's racing. Will let you know how I handle it.
Much appreciated and thanks again.

[surround]

Well thank you Captain Sensible. That works just as I'd envisioned. It works in a new sheet but I've yet to work out how to incorporate it in my working sheet and thanks for your efforts.

[surround]

Got it into my working sheet and running like a beauty. Many thanks Captain.

[Captain Sensible]

No problem, good to see you got it all working.