Hi,
Can anybody help me with how to record the back price of a runner say every 5 seconds from the spreadsheet so I can plot the data into a chart to look at later on?
Sorry for bothering the forum with this but I've spent the best part of the past 2 hours between races looking online for an answer to this and cant find one.
How to record data from Bet Angel spreadsheet
There is a few data capture sheets available for download in the Betfair data section
http://www.betangel.com/forum/viewforum.php?f=54
http://www.betangel.com/forum/viewforum.php?f=54
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Yeah I've seen those but they're a bit overkill for what I need and would take up a lot of processing power.
I just need excel to make a note of the back price every 5 seconds
I just need excel to make a note of the back price every 5 seconds
I know the one by Swietish is very in depth but think there is at least one in that section that just records the basic back,lay, ltp and volume for each selection at the intervals you set which should be easy enough to take the 3 columns you dont need out, i think its the one by i-XL
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Hi Dallas,
The sheet is fine for what I want to do but I don't understand how its put together.
I'd like to add other elements to it but have no clue where I would begin. I've tried using the Trace Dependents function within Excel to see how it was designed and put together but it doesn't really tell me anything.
Ideally I'd like to be able to design my own spreadsheet so I wouldn't have to bother anybody else but I can't seem to deconstruct this sheet to find how it works.
Any ideas?
The sheet is fine for what I want to do but I don't understand how its put together.
I'd like to add other elements to it but have no clue where I would begin. I've tried using the Trace Dependents function within Excel to see how it was designed and put together but it doesn't really tell me anything.
Ideally I'd like to be able to design my own spreadsheet so I wouldn't have to bother anybody else but I can't seem to deconstruct this sheet to find how it works.
Any ideas?
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Specifically If I can figure out how the 'Tick to record' function works I can then add it myself to another spreadsheet
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Feel like I've just jumped from 1+1 into applied physics
Thanks for the starting point Dallas.
Thanks for the starting point Dallas.
Theres a routine in module 1
which calls this routine once a second.
Then theres the routine to stop it recording:
To create and record you own data once every 5 seconds it would be along the lines of:
Code: Select all
Sub StartTimer()
dTime = Now + TimeValue("00:00:01")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub
Code: Select all
Sub ValueStore()
Dim dTime As Date
' Count of non empty data cells for this market
'ThisWorkbook.Sheets("ChartView").Range("$S$34").Value = i
Call StartTimer
i = i + 1
' If there are no more data cells, or if the next cell is blank
If i = ThisWorkbook.Sheets("ChartView").Range("YA19").Value - 6 Then
Call StopTimer
Sheets("ChartView").PlayButton.Caption = "Play"
Else
ThisWorkbook.Sheets("ChartView").Range("$S$34").Value = i
End If
End Sub
Then theres the routine to stop it recording:
Code: Select all
Sub StopTimer()
On Error Resume Next
Application.OnTime dTime, "ValueStore", Schedule:=False
i = 1
' Reset to the oldest Time
ThisWorkbook.Sheets("ChartView").Range("$S$34").Value = 1
End Sub
Code: Select all
Sub StartTimer()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "RecordMyData", Schedule:=True
End Sub
Code: Select all
Sub RecordMyData()
'put your own code here
''''''''''''''''''
''''''''''''''''''
'etc etc
Call StartTimer
End Sub
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Thanks to everyone who took the time to respond to my query. With your help (especially NIgelK) I've got a better understanding now of how it works.
One last thing though is it possible to plot the data into a real time chart? Like my own version of the market overview chart?
I've checked online and have seen some videos where this is possible but they all seem to need some other software and look very complicated.
Just to be clear I'm looking I'd like to be able to plot the updated date into a chart in real time.
Thanks
One last thing though is it possible to plot the data into a real time chart? Like my own version of the market overview chart?
I've checked online and have seen some videos where this is possible but they all seem to need some other software and look very complicated.
Just to be clear I'm looking I'd like to be able to plot the updated date into a chart in real time.
Thanks
you could have a fixed range with the data updating dynamically within that range or you could look at dynamic data range. Personally fixed range is more simple and easy to interprete.
Let's assume you data ranges across 60 columns, I would make the last column (60) update every interval with fresh data then copy from range 2 to 60 to range 1 to 59. You do this in VBA. Visualise for a few minutes how this will play put. You will see you data within the range consistents always of the last 60 intervals. Your graph looking at this range willl show this. In other words if each interval was 1 second, the graph will always show the last 60 seconds of data thus dynamically updating.
Let's assume you data ranges across 60 columns, I would make the last column (60) update every interval with fresh data then copy from range 2 to 60 to range 1 to 59. You do this in VBA. Visualise for a few minutes how this will play put. You will see you data within the range consistents always of the last 60 intervals. Your graph looking at this range willl show this. In other words if each interval was 1 second, the graph will always show the last 60 seconds of data thus dynamically updating.
-
- Posts: 198
- Joined: Mon Dec 09, 2013 4:12 pm
Hi Workpeter,
I've been trying to do something similiar with the Bet Angel spreadsheet. I attached a copy of it that another forum user modified so that when its connected to excel and the 'Tick to record' function on the data sheet is enabled it copies data from Row 5 every second. I've tried to capture this data as its updating and populating rows below using fixed ranges and populate a line chart in real time but it wont work, the chart just gets pushed down the sheet as another row of data appears.
I thought something like this would be relatively simple but the only video I could find that shows what I'm trying to do is this one : https://youtu.be/xLTEnd1u5Eo
And that seems to suggest its very complex
I've been trying to do something similiar with the Bet Angel spreadsheet. I attached a copy of it that another forum user modified so that when its connected to excel and the 'Tick to record' function on the data sheet is enabled it copies data from Row 5 every second. I've tried to capture this data as its updating and populating rows below using fixed ranges and populate a line chart in real time but it wont work, the chart just gets pushed down the sheet as another row of data appears.
I thought something like this would be relatively simple but the only video I could find that shows what I'm trying to do is this one : https://youtu.be/xLTEnd1u5Eo
And that seems to suggest its very complex
You do not have the required permissions to view the files attached to this post.