Moving onto next race after existing one finished

Please post any questions regarding the program here.

Moderator: 2020vision

Moving onto next race after existing one finished

Postby Ferru123 » Sun May 06, 2018 11:17 am

Hi

Is there any coding I can put into a spreadsheet to achieve the above please?

The challenge I have at present is that, when I'm running my in play bot, auto select moves me to the next race before the existing one is over.

Thanks

Jeff
Ferru123
 
Posts: 87
Joined: Sat Feb 21, 2009 1:28 pm

Re: Moving onto next race after existing one finished

Postby Captain Sensible » Sun May 06, 2018 3:40 pm

Just use the special Q2 triggers to navigate thru the markets

http://www.gruss-software.co.uk/Betting ... _other.htm

All you do to move onto the next race is stick -1 in Q2 when you've finished with that particular market
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Moving onto next race after existing one finished

Postby Ferru123 » Tue May 08, 2018 12:24 pm

Thanks CS.

I've written a macro to change the value of Q2 to -1 when I want to move to the next market.

The challenge I have is that it remains at -1 when I enter the new market, before my macro has a chance to change the value to "." and as a result I skip a market.

Any suggestions on how I can overcome this?

Jeff

Captain Sensible wrote:Just use the special Q2 triggers to navigate thru the markets

http://www.gruss-software.co.uk/Betting ... _other.htm

All you do to move onto the next race is stick -1 in Q2 when you've finished with that particular market
Ferru123
 
Posts: 87
Joined: Sat Feb 21, 2009 1:28 pm

Re: Moving onto next race after existing one finished

Postby Captain Sensible » Tue May 08, 2018 1:24 pm

BA clears the special triggers after they're used so it's likely the criteria to switch markets is still being met by your code or you have your code ordered incorrectly. Best to leave adjustments to Q2 any other special cells til the end so all code has run


Remember excel picks up data twice on each refresh, one prices, one additional columns, I've no idea if your code accounts for this and is re-entering the -1. Probably your easiest way is to use a 'flag' i.e. a global variable that will hold the status of whether you've switched markets or not and then reset it to false after it's used .

So decalre as a public variable in one of your modules, by default it's false.

Code: Select all
Option Explicit
Public Switch_Market As Boolean



Then set the value of Switch_Market = TRUE when your -1 code meets the criteria and add something like this to the bottom of your code so the last thing is firing in -1 to Q2.

Code: Select all
If Switch_Market = TRUE Then Worksheets("YOUR_SHEET_NAME").Range("Q2").Value = -1:Switch_Market = FALSE



If it's still skipping markets it's likely your -1 is doubling up for some reason and without knowing what you're doing it's hard to say. But we can always introduce extra flags to check if a market change has happened before allowing the flag to be set as true.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Moving onto next race after existing one finished

Postby Ferru123 » Thu May 10, 2018 3:49 pm

Thanks CS

I wrote a test spreadsheet with the following macro, with the intention of moving to the next market when the time to off value in T2 goes to zero:


Public Switch_Market As Boolean

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo Xit:

If [A1].Value = MyMarket Then


If [T2].Value <= 0 Then Switch_Market = True
If [T2].Value > 0 Then Switch_Market = False

If Switch_Market = True Then Range("Q2").Value = -1: Switch_Market = False

GoTo Xit
Else
MyMarket = [A1].Value

Range("Q2").Value = ""

End If


Xit:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


I can't work out what I'm doing wrong. Any suggestions much appreciated.

Thanks

Jeff
Ferru123
 
Posts: 87
Joined: Sat Feb 21, 2009 1:28 pm

Re: Moving onto next race after existing one finished

Postby MarkRussell » Thu May 10, 2018 4:51 pm

Hi,

I have an example that moves to the next race at the end of the previous.
My example also captures the time the race went in play in cell AA1 and time in seconds since in play in AA2.

Code: Select all
Option Explicit

Dim currentMarket As String
Dim inPlay As Boolean
Dim marketChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
           
        If [A1] <> currentMarket Then
            currentMarket = [A1]
            inPlay = False
            marketChanged = False
        End If
        If [E2] = "In Play" Then
            If Not inPlay Then
                inPlay = True
            End If
            If [AA1] = "" Then
                [AA1] = [C2]
            End If
            If [AA1] <> "" Then
                [AA2] = DateDiff("s", [AA1], [C2])
            End If
            If [F2] = "Suspended" And Not marketChanged Then
                marketChanged = True
                Application.EnableEvents = False
                [Q2] = "-1"
                Application.EnableEvents = True
            End If
        Else
            inPlay = False
        End If
       
          If [E2] <> "In Play" And [F2] <> "Suspended" Then
            [AA1] = ""
            [AA2] = ""
        End If
       
    End If
End Sub


Hope that helps.

Regards,
Mark
User avatar
MarkRussell
Site Admin
 
Posts: 1787
Joined: Tue Feb 20, 2007 6:38 pm
Location: Birmingham

Re: Moving onto next race after existing one finished

Postby Captain Sensible » Thu May 10, 2018 4:58 pm

Haven't time to check it now but a couple of errors I can spot.

The time to the off is shown in Cell D2 not T2 , dunno if that's an error or your code is transposing D2 to T2 for some reason. The other main thing is that excel doesn't do negative time so BA actually send the data as text rather than a number to D2. If you want to check when a time has gone negative you should check to see if the cell is holding text data rather than numeric data. I'll stick up an example when there's a break in racing sure I've one lying around
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Moving onto next race after existing one finished

Postby Captain Sensible » Thu May 10, 2018 4:59 pm

I was a bit slow typing , hopefully Mark's code will work OK for you
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Moving onto next race after existing one finished

Postby Ferru123 » Thu May 10, 2018 6:32 pm

Thanks guys

CS - I have a formula in T2 that converts D2 to seconds.

Am I right in thinking that I would integrate this macro with my existing one as follows?

Option Explicit

Dim currentMarket As String
Dim inPlay As Boolean
Dim marketChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then

If [A1] <> currentMarket Then
currentMarket = [A1]
inPlay = False
marketChanged = False


# INSERT HERE THE STUFF THAT MY MACRO DOES AT THE START OF ANY EVENT

End If

#INSERT HERE STUFF THAT MY MACRO DOES ON A LOOP.

If [E2] = "In Play" Then
If Not inPlay Then
inPlay = True
End If
If [AA1] = "" Then
[AA1] = [C2]
End If
If [AA1] <> "" Then
[AA2] = DateDiff("s", [AA1], [C2])
End If

If [F2] = "Suspended" And Not marketChanged Then
marketChanged = True
Application.EnableEvents = False
[Q2] = "-1"
Application.EnableEvents = True
End If
Else
inPlay = False
End If

If [E2] <> "In Play" And [F2] <> "Suspended" Then
[AA1] = ""
[AA2] = ""
End If

End If
End Sub


Jeff
Ferru123
 
Posts: 87
Joined: Sat Feb 21, 2009 1:28 pm

Re: Moving onto next race after existing one finished

Postby Captain Sensible » Thu May 10, 2018 7:33 pm

I think you'd be better off trying to understand what the code people stick up is doing, Jeff, rather than trying to shoe-horn your own code into any code snippets people stick up.

The commented sections you've stuck up below are in the right places. But I think you'd be much better of having events turned on and off (Application.EnableEvents = False) at the start and end of your code rather than when you simply change Q2 in the code you've posted.

# INSERT HERE THE STUFF THAT MY MACRO DOES AT THE START OF ANY EVENT (this would only fire once when the market details changed)

#INSERT HERE STUFF THAT MY MACRO DOES ON A LOOP.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 13 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.