How to record data from Bet Angel spreadsheet

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

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.
User avatar
Dallas
Posts: 22673
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

There is a few data capture sheets available for download in the Betfair data section
http://www.betangel.com/forum/viewforum.php?f=54
RonObvious
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
User avatar
Dallas
Posts: 22673
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

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
RonObvious
Posts: 198
Joined: Mon Dec 09, 2013 4:12 pm

Ok I'll have a look

Thanks Dallas
RonObvious
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?
RonObvious
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
User avatar
Dallas
Posts: 22673
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

What you would actually need to change im not sure but if you click the 'developer' tab of your excel and then 'Visual Basic' or 'Macros' > Edit Macros you should be able to at least how its written
RonObvious
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.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

wheres the "Tick to record button" ?
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Theres a routine in module 1

Code: Select all

Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub
which calls this routine once a second.

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
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: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
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

For those of you wishing to take your excel to the next level I would suggest you look at GetSystemTime in combination with worksheet change event. This is more granular than timers and doesnt cause your application to freeze.
RonObvious
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
User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

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.
RonObvious
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 :?
You do not have the required permissions to view the files attached to this post.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”