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
How does Excel work? Question for a VBA programmer
- ShaunWhite
- Posts: 9731
- Joined: Sat Sep 03, 2016 3:42 am
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.
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.
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
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
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
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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
$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
Also "subscript out of range" appears beacuse you have set "Recording" as base 1, not base 0
but the first time around i is equal to zero, so
is trying to access "Recording (0,1)" which does not exist.
You could try moving it up so it becomes
Code: Select all
Dim Recording(1 To RecordingLength, 1 To 2)
Code: Select all
Recording(i, 1) = Target.Address
You could try moving it up so it becomes
Code: Select all
i = i + 1
Recording(i, 1) = Target.Address
Recording(i, 2) = Now
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
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.
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.
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
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
You do not have the required permissions to view the files attached to this post.
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
Again, much appreciated nigelk. Cheers for that!
Callum.
Callum.