Need Help with Data Capture Spreadsheet
-
- 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.
You do not have the required permissions to view the files attached to this post.
-
- 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
And just use the following to find our next empty column to put data into
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
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"
Code: Select all
Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Column
Last edited by spreadbetting on Wed Nov 27, 2019 4:35 pm, edited 2 times in total.
-
- 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.
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.
-
- Posts: 44
- Joined: Fri Sep 07, 2018 4:30 pm
I wanted to do it with Excel/VBA if possible because in future I wanted to save the data for all the runners.jamesg46 wrote: ↑Wed Nov 27, 2019 4:18 pmYou 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.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
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.TheNagsHead wrote: ↑Wed Nov 27, 2019 4:12 pmThanks 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.
viewtopic.php?f=19&t=16663&p=158026
This looks like it can be adapted to suit your requirements.
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 to 60 looks to extend the recording of the data to this 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.
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 to 60 looks to extend the recording of the data to this 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.
-
- 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
-
- 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.
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.
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
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
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
-
- Posts: 44
- Joined: Fri Sep 07, 2018 4:30 pm
Cheers James
Atho55 thanks I will check it out this evening
Thanks spreadbetting for taking the time to write the code for me. I will check it out this evening.spreadbetting wrote: ↑Wed Nov 27, 2019 8:43 pmHere'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.
-
- 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.