Loading next day races automatically

Please post any questions regarding the program here.

Moderator: 2020vision

Re: Loading next day races automatically

Postby Wacom78 » Thu Aug 20, 2020 8:48 pm

Hi, no because I use the Log multiple sheets quick link option as this allows it to link to a different number of sheets if the amount of races change day on day.
Within the options for multiple quick link sheets I have this ticked yes but it doesn't select the first market
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Sat Aug 22, 2020 12:45 pm

That tick box is an option for all excel linked betting so includes markets linked by using the quick links option. It's a lot easier to tick a box than write your own VBA routine.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Captain Sensible » Sat Aug 22, 2020 12:48 pm

The tick on the multiple links option only runs when it sets up, the option I posted runs whenever the quick pick is reloaded.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Sat Aug 22, 2020 8:39 pm

Ive checked and indonuave that ticked (even though im linking via quick links - but when the next day races load, it fails to select the first market
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Sun Aug 23, 2020 12:48 pm

Wacom78 wrote:Hi Captain, I replaced the bit of code that worked (in terms of getting the next day races in the tabs) - as below, with your earlier code but that didn't even move to the next days races.

So I have the below, which fires and works - its just getting it to select the first race I am struggling with. Are you able to adapt the below so it will select the first race in each tab as well?

Code: Select all
       If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -4


Sorry for being a pain, I've given it a go but VBA isn't really my thing.


Just incorporate the code that was posted earlier making sure you declare triggerFirstMarketSelect as a Public variable along with triggerQuickPickListReload and change the names of the sheets in the code if they don't match your ones

Code: Select all
If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5
            End If
        End If
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Tue Aug 25, 2020 8:08 am

Thanks Captain, will give that a go
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Wacom78 » Tue Aug 25, 2020 4:11 pm

Hi Captain, unfortunately inserting that code prevented my usual code and bets firing. So I am still in the same scenario, as I mentioned, the -4 aspect worked and didn't interfere, but adding in that new piece of code stopped my usual routine working
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Tue Aug 25, 2020 4:54 pm

It's only an extra IF so without knowing the rest of your code it's hard to see why it's interferring especially if the bit you posted is fine


Code: Select all
If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -4



as it only needs replacing by

Code: Select all
If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5
            End If


and ensuring you declare triggerFirstMarketSelect .
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Thu Aug 27, 2020 8:45 am

Hi Captain, I'm not sure what you mean by ensuring I declare triggerFirstMarketSeletc?

here is my code as I previously posted which is on sheet1. I have embedded your code within it and will see if all works in terms of bets firing and markets scrolling through today,

Code: Select all
Option Explicit

Dim ba As BettingAssistantCom.ComClass

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

If [A1].Value = MyMarket Then



   Range("AF3").Value = switched
   

   
If Range("AA4").Value + Range("AB1").Value <= Time() And Range("E2").Value = "In Play" Then
    Range("AB4:bz60").Value = Range("AA4:bz60").Value
    Range("AA5:AA60").Value = Range("O5:O60").Value
    Range("AA4").Value = Time()
End If


 
   
    Else
   
    MyMarket = [A1].Value
    Worksheets("Sheet1").Range("AA4:bz60").Value = ""
    switched = "No"
   
 
End If

If [Y2] = "OK" And switched = "No" _
 Then
switched = "Yes"
GoTo Switch_Market
End If

 If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5
            End If


 
Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
Worksheets("Sheet1").Select

       
       
    Range("Q2").Value = -1
   
   
   
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
   
End Sub
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Wacom78 » Thu Aug 27, 2020 9:22 am

I can see already it is not going to work as none of the prices have updated in the files.

Its as if this locks something preventing the link working. As I say, when I have this in place below it works

Code: Select all
If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -4
               
       
        End If


When I amend it to this - the sheets stop updating
Code: Select all
If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5
            End If
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Thu Aug 27, 2020 10:38 am

You need another end if on the bottom code you posted. You hadn't included the end if on the code snippet you quoted so I just gave a direct replacement for you to overwrite.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Captain Sensible » Thu Aug 27, 2020 10:40 am

By declaring triggerFirstMarketSelect I meant you declare it as a public variable. I.e.

Public triggerFirstMarketSelect as Boolean
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Captain Sensible » Thu Aug 27, 2020 4:35 pm

Here's an alternative version to Gary's that doesn't need windows_open or Application.OnTime etc as that seems to be confusing for you.

It'll just check the date and if it's past 6 it'll run the QPL refresh and then load the first markets. You will need to remove the previous code from your file so start from scratch. All we're using is

Code: Select all
If Today <> Date And Hour(Now) > 11 Then
Today = Date
triggerQuickPickListReload = True
End If



If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5

        End If
End If
   



And here it is included in the main code you posted

Code: Select all
    Option Explicit
    Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
    Dim ba As BettingAssistantCom.ComClass

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

    If [A1].Value = MyMarket Then



       Range("AF3").Value = switched
       

       
    If Range("AA4").Value + Range("AB1").Value <= Time() And Range("E2").Value = "In Play" Then
        Range("AB4:bz60").Value = Range("AA4:bz60").Value
        Range("AA5:AA60").Value = Range("O5:O60").Value
        Range("AA4").Value = Time()
    End If


     
       
        Else
       
        MyMarket = [A1].Value
        Worksheets("Sheet1").Range("AA4:bz60").Value = ""
        switched = "No"
       
     
    End If

    If [Y2] = "OK" And switched = "No" _
     Then
    switched = "Yes"
    GoTo Switch_Market
    End If




If Today <> Date And Hour(Now) > 11 Then
Today = Date
triggerQuickPickListReload = True
End If



If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5

        End If
End If
   
     
Xit:
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Exit Sub
       
Switch_Market:
    Worksheets("Sheet1").Select

           
           
        Range("Q2").Value = -1
       
       
       
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
       
       
    End Sub



See if that's any easier to use.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Captain Sensible » Thu Aug 27, 2020 4:39 pm

Just change the bit Hour(Now) > 11 to Hour(Now) >= 6 , I'd just cobbled it together from some code snippets I had

Code: Select all
    Option Explicit
    Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
    Dim ba As BettingAssistantCom.ComClass

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

    If [A1].Value = MyMarket Then



       Range("AF3").Value = switched
       

       
    If Range("AA4").Value + Range("AB1").Value <= Time() And Range("E2").Value = "In Play" Then
        Range("AB4:bz60").Value = Range("AA4:bz60").Value
        Range("AA5:AA60").Value = Range("O5:O60").Value
        Range("AA4").Value = Time()
    End If


     
       
        Else
       
        MyMarket = [A1].Value
        Worksheets("Sheet1").Range("AA4:bz60").Value = ""
        switched = "No"
       
     
    End If

    If [Y2] = "OK" And switched = "No" _
     Then
    switched = "Yes"
    GoTo Switch_Market
    End If




If Today <> Date And Hour(Now) >=6 Then
Today = Date
triggerQuickPickListReload = True
End If



If triggerQuickPickListReload Then
            triggerQuickPickListReload = False
            Worksheets("Sheet1").Range("Q2").Value = -4
            triggerFirstMarketSelect = True
        Else
            If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                Worksheets("Sheet2").Range("Q2").Value = -5
                Worksheets("Sheet3").Range("Q2").Value = -5
                Worksheets("Sheet4").Range("Q2").Value = -5
                Worksheets("Sheet5").Range("Q2").Value = -5
                Worksheets("Sheet6").Range("Q2").Value = -5

        End If
End If
   
     
Xit:
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Exit Sub
       
Switch_Market:
    Worksheets("Sheet1").Select

           
           
        Range("Q2").Value = -1
       
       
       
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
       
       
    End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Fri Aug 28, 2020 8:13 am

Thank you very much for your time, I will give this a go over the weekend :D
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

PreviousNext

Return to Help

Who is online

Users browsing this forum: No registered users and 24 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.

cron