Need Help with Data Capture Spreadsheet

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
TheNagsHead
Posts: 44
Joined: Fri Sep 07, 2018 4:30 pm

I am trying to set up a Data Capture spreadsheet. I know there is quite a few few on the forum but they are different from what I am trying to do and I am a novice programmer so have found it hard to adapt them to what I need. I have attached an excel spreadsheet showing what I am trying to achieve, any help greatly appreciated.
Data Capture.xlsx
You do not have the required permissions to view the files attached to this post.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I don't actually use BA so no idea of what format your data gets sent to the sheet in but there will be plenty of ways to acheive what you're after. The only possibly hard thing I can see is how rigid you want to be matching up your set times. With excel we have a function datediff so if you can get the scheduled off time into your sheet we can then simply compare that to your set times headings to get the nearest one and if that is within your capture parameters ( i.e. you won't always hit the time exactly so maybe happy to capture a second before or after depending on your refresh rate) we just copy the data into the corresponding column.

So before anyone can really start helping with code they need a bit more of a detailed spec as to what you're prepared to accept and some indication of the off time in your sheet.

If we simply wanted a trail of prices every five minutes we just use something like this to kick off a copying sub routine

Code: Select all

Application.OnTime Now + TimeValue("0:05:00"), "RecordData"
And just use the following to find our next empty column to put data into

Code: Select all

Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Column
But as you're looking to match set times exactly you need to do things slighty differently and that requires the off time somewhere on the sheet
Last edited by spreadbetting on Wed Nov 27, 2019 4:35 pm, edited 2 times in total.
TheNagsHead
Posts: 44
Joined: Fri Sep 07, 2018 4:30 pm

Thanks for replying. I was going to connect my BetAngel spreadsheet to Guardian and then have 1 sheet selected for each of the races i wanted to collect data for. Matching up the set times doesnt have to be really rigid, if it is out by a few seconds it doesnt matter. I just want to be able to start collecting data. Once I know how to do it I will totally change the spreadsheet. Regarding the off time. Bet Angel send data to the spreadsheet, Event start and Countdown, so this data could be used in the code so it starts collecting the data at 4 hours before the races starts. Hope I have explained it clearly.
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

You could use an automation file within Guardian and store the value for the first 4 runners every five mins, then simply just upload your log to Excel. You would have to be careful in regards to changing favs, so i'd probably fix the order of the runners, also a rule within Guardian.
TheNagsHead
Posts: 44
Joined: Fri Sep 07, 2018 4:30 pm

jamesg46 wrote:
Wed Nov 27, 2019 4:18 pm
You could use an automation file within Guardian and store the value for the first 4 runners every five mins, then simply just upload your log to Excel. You would have to be careful in regards to changing favs, so i'd probably fix the order of the runners, also a rule within Guardian.
I wanted to do it with Excel/VBA if possible because in future I wanted to save the data for all the runners.
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

I'm sure its possible but way above my level, good luck mate :D
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

TheNagsHead wrote:
Wed Nov 27, 2019 4:12 pm
Thanks for replying. I was going to connect my BetAngel spreadsheet to Guardian and then have 1 sheet selected for each of the races i wanted to collect data for. Matching up the set times doesnt have to be really rigid, if it is out by a few seconds it doesnt matter. I just want to be able to start collecting data. Once I know how to do it I will totally change the spreadsheet. Regarding the off time. Bet Angel send data to the spreadsheet, Event start and Countdown, so this data could be used in the code so it starts collecting the data at 4 hours before the races starts. Hope I have explained it clearly.
I don't use BA so wouldn't know how the data is formatted or appears on the sheet. There's a thread where someone wanted to log prices every 30 seconds that may be a start for you, especially if you eventually want all runners. You'd probably only need to tweak it to run once it got to the four hour mark - should be an easy IF to compare the current time with the scheduled start time and then simply change the logging from every 30 seconds to every 5 minutes.

viewtopic.php?f=19&t=16663&p=158026
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

This looks like it can be adapted to suit your requirements.

BetAngel Charting for Horses.7z

As part of the charting process it stores data at whatever interval you want. It is set up at the moment for 1 second. Doing a quick test....

Changing this
Module.jpg
to 60 looks to extend the recording of the data to this
Data.jpg
So go into the code and change 1 to 300

Then link the cells in Col B on the Data tab to the Bet Angel sheet and that should be it.
You do not have the required permissions to view the files attached to this post.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Doesn't seem particularily hard to do just a point of finding the column to enter your data, now racings over I'll see if I can stick some coding in the sheet you posted. i'll have to assume the off time as you didn't stick one on the sheet
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Here's a possible sheet for you, had to add it as a RAR files as BA don't let you upload macro files. I've posted the code below as it's never wise to open macro enabled files from people you don't know.

This code would go under the worksheet that's receiving data so it fires off the code.

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual
    
    
    Call checkTimes
    
    
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True 'Turn on events again
    End Sub

And this code goes in a module, looks a lot of code but most of it is simply comments to say what it's doing. To test it you could always simply add a timer to the sub routine so it continually runs itself once you run it something like Application.OnTime Now + TimeValue("0:00:30"), "checkTimes", that way you'd see it adds data every 5 minutes if the time is less than 240 minutes to the off. No idea how BA shows you when the race is off so you just add another And statement to the line If (Right(Minute(Now), 1) = 0 Or Right(Minute(Now), 1) = 5) And TimeToTheOff <= 240 Then, to cover that scenario.

Code: Select all

Option Explicit

Sub checkTimes()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Sheet1")
Dim ws2 As Worksheet
Set ws2 = Worksheets("Sheet2")

Dim Timein As Date, Timeout As Date
Dim TimeToTheOff As Integer, dataColumn As Integer



Timein = ws1.Range("F1").Value ' I have no idea where the time goes with BA so have stuck a dummy time in F1
Timeout = Format(Now(), "hh:mm AMPM") 'get the current time
TimeToTheOff = DateDiff("n", Timeout, Timein) ' get the minutes before the off time
' we know UK racing is only ever on the hour or in 5 minute slots so we'll exit if we're outside those
' as there's no point running the code unneccessarily. We also exit if we're more than 4 hour out 60*4=240

If (Right(Minute(Now), 1) = 0 Or Right(Minute(Now), 1) = 5) And TimeToTheOff <= 240 Then

' if the times are OK lets look at recording our data
' First thing we do is to find the column we need to enter data, I stuck them as minutes for simplicity rather than times
' you can simply add another column with times or amend the code so it calculates by time , bit of a pain when negative times
' so I couldn't be bothered as you may not even use it

' So now we simply find the column that corresponds to our current time and enter data
                          
Dim rgFound As Range
Set rgFound = ws2.Range("A1:CA1").Find(TimeToTheOff)

If rgFound Is Nothing Then

   Exit Sub
Else
  dataColumn = rgFound.Column
End If

' OK lets check we haven't aready put data in  if so we'll exit

If ws2.Cells(2, dataColumn).Value <> "" Then Exit Sub

' we'll dump it in two passes due to how it's on your sheet but probably easier ways, but trying to keep code simple


ws2.Range(Cells(2, dataColumn), Cells(3, dataColumn)).Value = ws1.Range("B2:C2").Value
ws2.Range(Cells(4, dataColumn), Cells(5, dataColumn)).Value = ws1.Range("B3:C3").Value


End If



End Sub
 


The times were entered on the sheet as minutes rather than hours as it was easier, and a dummy time entered in cell F1 obviously change that to whatever cell BA uses


Data Capture.rar
TheNagsHead
Posts: 44
Joined: Fri Sep 07, 2018 4:30 pm

jamesg46 wrote:
Wed Nov 27, 2019 4:27 pm
I'm sure its possible but way above my level, good luck mate :D
Cheers James
Atho55 wrote:
Wed Nov 27, 2019 6:14 pm
This looks like it can be adapted to suit your requirements.
Atho55 thanks I will check it out this evening
spreadbetting wrote:
Wed Nov 27, 2019 8:43 pm
Here's a possible sheet for you, had to add it as a RAR files as BA don't let you upload macro files. I've posted the code below as it's never wise to open macro enabled files from people you don't know.
Thanks spreadbetting for taking the time to write the code for me. I will check it out this evening.
TheNagsHead
Posts: 44
Joined: Fri Sep 07, 2018 4:30 pm

I will have the code completed in next 1 -2 weeks and will post it in the forum. Its really basic but might help someone who isn't an advanced programmer, thanks again to the people who contributed to the thread.
Zedzed1
Posts: 59
Joined: Sat Feb 15, 2020 9:01 am

Is there someone that have this spreadshead from post 1. Im also searching something like that?

Thank you already?
Atho55
Posts: 637
Joined: Tue Oct 06, 2015 1:37 pm
Location: Home of Triumph Motorcycles

From memory =SUM(VLOOKUP(xx,xx,x,x)-VLOOKUP(xx,xx,xx,x) sort of thing
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

Atho55 wrote:
Sat Jul 04, 2020 3:14 pm
From memory =SUM(VLOOKUP(xx,xx,x,x)-VLOOKUP(xx,xx,xx,x) sort of thing
Thanks Atho
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”