How does Excel work? Question for a VBA programmer

[CallumPerry]

Hi lads,

I've just started learning how to use VBA to code some simple strategies and I'd just like to know about data flow. The price gets streamed in from BetFair how exactly? And when it hits my spreadsheet, where does it then go? I'd like to catalogue prices and do a few more advanced things so a detailed explanation on how this mystical little setup magically moves all of it's data would really help.

Cheers,

Cal

[ShaunWhite]

The data comes into your spreadsheet from betfair via BA.

You specify the name of the spreadsheet and which items of data you want (out of that which is available) in ba, and that data arrives in predefined cells. B9 B11 etc.

You then use excel functions, usually within vba code to manipulate that data in any way you wish.

This would typically involve moving the data to another excel workbook or spreadsheet in a more usable format or time series. This can then used to provide real time graphics, for generating transactions or for later analysis.

Basically BA makes the data available and what you do with it is limited by your imagination.

[CallumPerry]

I'm just trying to find out a little more about BA's update style so I know best how to code my systems in the future. I have tried using the following code but it always comes up with a dialogue box which says "Subscript out of range". Any ideas what I'm doing wrong here lads?...

Option Explicit

Const RecordingLength = 1000 'Records. 'Adjust to suit
Const RecordingTime = 3 'Minutes 'Adjust to suit

Dim Running As Boolean
Dim Recording(1 To RecordingLength, 1 To 2)
Dim i As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then Exit Sub
If Target.Row < 45 Then Exit Sub

Recording(i, 1) = Target.Address
Recording(i, 2) = Now

i = i + 1
If i > RecordingLength Then
Running = False
SaveRecording
End If
End Sub

Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel")
ActiveSheet.Range("A1:B" & CStr(RecordingLength)) = Recording
End Sub


Private Sub RecordingTimer()
Application.OnTime Now + TimeValue("00:" & CStr(RecordingTime) & ":00"), "StopLogging"
End Sub

Public Sub StopLogging()
If Running Then
Running = False
SaveRecording
End If
End Sub

Public Sub StartLogging()
Recording(1, 1) = "Changed Address"
Recording(1, 2) = "Time of Change"
i = 2

RecordingTimer
Running = True
End Sub

[spreadbetting]

I don't use BA for my BOTS but when I looked at the Betdaq version it seems the spreadsheet is populated in 6 separate lots of data in each refresh

$A$1:$B$1
$G$1:$H$1
$C$2:$C$6
$F$2:$F$4
$A$9:$K$18
$T$9:$AE$18

Apparently the Betfair version populates $C$2:$C$6 as it last set. I'd guess your spreadsheet is getting caught up in a continual loop as the Worksheet_Change event is continually firing on each refresh . You should try to restrct you code to only fire when the last set of data is written to the sheet and turn events off whilst it fires (Application.EnableEvents = False/True) etc

You can check the order of the data using the immediate window and debug print ie

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address



End Sub

[nigelk]

Also "subscript out of range" appears beacuse you have set "Recording" as base 1, not base 0

Code: Select all

Dim Recording(1 To RecordingLength, 1 To 2)

but the first time around i is equal to zero, so

Code: Select all

Recording(i, 1) = Target.Address 

is trying to access "Recording (0,1)" which does not exist.

You could try moving it up so it becomes

Code: Select all

i = i + 1
Recording(i, 1) = Target.Address
Recording(i, 2) = Now  

[CallumPerry]

Thank you nigelk for your detailed reply!

I have another couple of questions, if anybody could help me out please. Does BA timestamp each update as they come through (even via high speed 20m/s) streaming? I'm trying to work out high speed calculations using purely VBA and would like to know if BA does this regular timestamp thing to coordinate my strategy alongside.

Next, are there any "custom settings" for the Excel sheet? I don't need all of the data it streams through and I'd rather not waste CPU if this would be helped. Does anyone know how to shave the main sheet down a bit?

Cheers Lads.

[nigelk]

You can always get the time of update by monitoring a cell/range of cells and use the 'Now()' to get the time of change.

In this example I've put it in a module so it can be run manually, but it really needs to go in as a sheet event.

The cell should format itself, but to see the seconds you'll need to add :ss to the format.


Cheers,Nigel

[CallumPerry]

Again, much appreciated nigelk. Cheers for that!

Callum.