Loading next day races automatically

Please post any questions regarding the program here.

Moderator: 2020vision

Re: Loading next day races automatically

Postby Captain Sensible » Thu Aug 06, 2020 11:19 am

Always hard to give code snippets when you don't know how the workbook or workbooks are set up and what code is already there . But it seems from the code you posted you're only using one workbook and each tab is linked to a corresponding worksheet and simply called Sheet1, Sheet2 etc, so this may work.


Under ThisWorkbook you enter the following code only. This code runs when the workbook is opened and sets up the loadQuickPickList to run at the next 6am

Code: Select all
Option Explicit

Private Sub Workbook_Open()
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"
End Sub




In a module you need to enter the following , you can add it to an existing module but make sure to declare the Public Boolean variables at the top.

Code: Select all
Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean

Public Sub loadQuickPickList()
    triggerQuickPickListReload = True
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"
End Sub


Sub Refresh()

        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

End Sub



Because you already have a worksheet change routine running we need to call the Refresh sub routine from that, because you having code running to switch markets we need to ensure the code runs at the bottom of your Worksheet_Change routine so any changes to cell Q2 aren't overwritten. I don't know if you're turning events on and off in the worksheet_change routine but if you are it should be put before them. So to check the Refresh at each refresh we would stick in Call Refresh at the botto of your exisiting Worksheet_Change in Sheet1 only, it doesn't go in all Sheets. So Sheet1 Worksheet_Change will now look like

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False 'Turn off events

.....all your exisiting code






Call Refresh
    Application.EnableEvents = True 'Turn on events again
End Sub


I haven't tested it but you can easily test it yourself by tweaking the time in loadQuickPickList to say 2 minutes from now and running the loadQuickPickList from macros. I'll test it later if i get time.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Thu Aug 06, 2020 1:05 pm

Thank you very much for taking the time to reply. My current routine is just the one from the sample files that move to the next market - all bets are fired using formula's - it was just understanding how to embed this new code into the existing code that moves to new markets.

i will try it tonight as I've set it up manually to trade today
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Thu Aug 06, 2020 3:40 pm

OK if it's the file from here viewtopic.php?f=8&t=5163 you'd just need to amend it as follows

Into a module insert this code

Code: Select all
Public triggerQuickPickListReload As Boolean


Public Sub loadQuickPickList2()
    triggerQuickPickListReload = True
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"

End Sub


Under ThisWorkbook insert this code

Code: Select all
Option Explicit

Private Sub Workbook_Open()
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"
End Sub


Amend the VBA on sheet1 to this

Code: Select all
Option Explicit

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim timeFromStart As Date, beforeStart As Boolean, secondsFromStart As Integer
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If Left([D2], 1) = "-" Then
            timeFromStart = Mid([D2], 2)
            beforeStart = False
        Else
            timeFromStart = [D2]
            beforeStart = True
        End If
        secondsFromStart = (Hour(timeFromStart) * 3600) + (Minute(timeFromStart) * 60) + Second(timeFromStart)
        If Not beforeStart Then secondsFromStart = -secondsFromStart
        If [A1] <> currentMarket Then marketSelected = False
        currentMarket = [A1]
        If secondsFromStart <= 10 And Not marketSelected Then
            marketSelected = True
            [Q2] = -1
        End If
       
       
       
        ''''''''''''''this is our additional code
        If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -4
               
       
        End If
        ''''''''''''''''''''
       
        Application.EnableEvents = True
    End If
End Sub




When we log a sheet to excel you have the option to select the first market in the Quick Pick list if the list is reloaded so we'll use that instead of VBA. Just tick the box highlighted when logging the sheet, it will remember the option after you've logged out so won't need to keep ticking it.
Untitled1.png
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 06, 2020 3:42 pm

The code for the module should be Public Sub loadQuickPickList() not Public Sub loadQuickPickList2(), I'd changed the name whilst checking the code all worked.

Code: Select all
Public triggerQuickPickListReload As Boolean


Public Sub loadQuickPickList()
    triggerQuickPickListReload = True
    Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"

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 07, 2020 8:48 pm

I will try that tonight as the previous failed, it was trying to open some other random file - I have no idea what is going on with it, so confused. Just want it to update itself but seems a nightmare and I'm having to log it off each night and log in again and link the file the next day
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Wacom78 » Fri Aug 07, 2020 8:54 pm

I should say thanks though for your efforts and perseverance, I will have my fingers crossed for 6am tomorrow
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Wacom78 » Fri Aug 07, 2020 9:41 pm

Here is my full code, I've pinched bits from other queries and examples I have seen. I basically track the odds in running and fore bets from formula's.

I have added your piece of code at the end as wasn't sure where it should go?

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:bzb60").Value = Range("AA4:bzb60").Value
    Range("AA5:AA60").Value = Range("O5:O60").Value
    Range("AA4").Value = Time()
End If


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

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

 
Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
Worksheets("Sheet1").Select
Call Transfer
   
       
       
    Range("Q2").Value = -1
   
   
   
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
       If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -4
   
End Sub
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Wacom78 » Sat Aug 08, 2020 7:40 am

Unfortunately it failed again, opens about 3 VBA sessions but didn't load today's races
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Sat Aug 08, 2020 1:28 pm

Wacom78 wrote:Unfortunately it failed again, opens about 3 VBA sessions but didn't load today's races


Not sure what you mean by 3 VBA sessions and the code should go before the bit where you have Xit: otherwise it'll only get to that code when your Switch_Market code runs. Other than that it'd be worth you testing out -4 to see if it's doing what you cant rather than waiting to 6am each day. YOu can stick it in manually or even tweak your code so it runs in half and hour or so and reload.

ONly other thing would be to search the forum as quite a few people do use BA by opening courses into separate tabs and run automatically.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Fri Aug 14, 2020 9:33 am

Hi Captain, trued the above, putting new code above Xit but nothing even triggers. Had the time amended to 9:30 and nothing happend at all when the clock turned 9:30.

Struggling to understand why there is code in the module AND in the Thisworkbook - but it didn't trigger anything.

I don't think I'll be able to make Gruss useful if I can't find a way to let it update markets automatically
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Fri Aug 14, 2020 2:11 pm

Wacom78 wrote:Struggling to understand why there is code in the module AND in the Thisworkbook - but it didn't trigger anything.


The code in ThisWorkbook is needed to set off the routine. Any changes you make to that code whilst it's open won't ever run until it's been saved and reopened, because it only runs on the sheet being opened i.e. Workbook_Open.

So the Workbook_Open() does what it says on the tin, when the workbook is opened that code runs the Application.ontime which sets the time for the sub routine, loadQuickPickList ,in the module to run. The code in loadQuickPickList then pretty much runs in a loop setting itself to run at say 00:00 everyday by another Application.OnTimeand sets the variable triggerQuickPickListReload to true so that gets picked up by the routine If triggerQuickPickListReload Then and if true simply puts -4 in Q2 and resets triggerQuickPickListReload so the code doesn't keep putting -4 in Q2.

All the code is ever doing is sticking -4 in Q2, probaby best that you check that does what you need first. Simply et your sheet run as normal and when you wake up stick -4 in Q2 for sheet1. There are numerous methods to stick -4 in Q2 at a set time it's possible other code withn your modules is siply overwritting it. most of us use the method in Gary's example because it works for us. Check -4 in Q2 works firstly as it's simple to write a different method of sticking it in there.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Sat Aug 15, 2020 8:45 am

Hi Captain, thank you for the explanation - I have progress, at 6am today the new races loaded in each tab - BUT the races shown in the display panel where still yesterday's last races (showing as closed) - it basically didn't select the first market for today.

I set the Excel file up using log multiple sheets quick link - one of the criteria is "Auto select first market" - which is ticked, but it didn't do that part. I know the code you presented didn't do this, but ticking the box should - however that part didn't complete for me automatically
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Sat Aug 15, 2020 2:50 pm

Wacom78 wrote:Hi Captain, thank you for the explanation - I have progress, at 6am today the new races loaded in each tab - BUT the races shown in the display panel where still yesterday's last races (showing as closed) - it basically didn't select the first market for today.

I set the Excel file up using log multiple sheets quick link - one of the criteria is "Auto select first market" - which is ticked, but it didn't do that part. I know the code you presented didn't do this, but ticking the box should - however that part didn't complete for me automatically



The excel multiple links auto select will only work once the sheet is run for the first time, you'd need to tick the quick pick reload triggers first market as per the picture a few posts above. If you want to do it using code just use the code i posted where it put -5 into all sheets. I've no idea how you navigate markets but if you use the auto select markets at a set time it will still jump to the market at whatever time you stated. If you navigate markets using VBA it's easy enough to use the -1,-8 Q2 triggers
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Loading next day races automatically

Postby Wacom78 » Wed Aug 19, 2020 8:45 am

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.
Wacom78
 
Posts: 38
Joined: Mon Nov 04, 2019 2:32 pm

Re: Loading next day races automatically

Postby Captain Sensible » Wed Aug 19, 2020 1:47 pm

Did you try ticking the box as per my earlier post? If that's ticked then the first market is selected whenever the quick pick is reloaded

Untitled1.png
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

PreviousNext

Return to Help

Who is online

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