Refresh rate after race has completed

Please post any questions regarding the program here.

Moderator: 2020vision

Refresh rate after race has completed

Postby vanbuuts » Mon Feb 15, 2021 12:33 pm

Hello all, I am wondering if someone can assist with some code for the refresh rate.

I have each race on separate tabs all at max refresh rates which does slow Excel considerably, especially on days of 5, 6, 7 or more races.

I understand it is possible using VBA to slow or stop the refresh and then start it up again?

Would anybody be able to supply some code that would start the refresh (at current highest rate) say 5 mins before the scheduled start time and continue through the race?
I already have coding that then takes to the next race upon conclusion of the current one, so I would then need some code to stop / slow the refresh again at that stage - starting it up again 5 mins before the next race?

Any help much appreciated
vanbuuts
 
Posts: 321
Joined: Thu Oct 16, 2014 8:55 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Mon Feb 15, 2021 3:21 pm

You could simply use streaming if you want the most efficient and fastest speeds. You can add the following to your code to turn on and off streaming

Code: Select all
   

With Target.Parent
If .Range("D2").Value <= TimeValue("00:05:00") And .Range("K1").Value <> "FO" Then
.Range("Q2").Value = "FULL-STREAM-ON"
ElseIf .Range("D2").Value >= TimeValue("00:05:00") And .Range("K1").Value = "FO" Then
.Range("Q2").Value = "FULL-STREAM-OFF"
End If

End With


Bascially that will turn on streaming if it's not already on and the time is less than 5 minutes to the race start. If streaming is on and the time is more than 5 minutes to the start it will turn streaming off. Once streaming is off it will simply use the default setting you have set for the refresh rate.

But like everything with gruss nothing is too simple as any changes to a sheet don't get picked up til the next excel refresh. So if our refresh rate is 30 seconds (Options->Preferences->General->Default refresh rate) and we stick "streamin on " in Q2 it won't get picked up until 30 seconds later so in that case you might want to set your time to switch to TimeValue("00:05:30") .

A lot of the time it's actually easier to simply exit your worksheet_change routine early before any code is run

Code: Select all

If .Range("D2").Value >= TimeValue("00:05:00")  Then Exit Sub


Or goTo the end of the code to swerve any intensive/unnecessary code depending what your bot is doing
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Mon Feb 15, 2021 3:29 pm

Maybe change the streaming bit to check if D2 is numeric as it changes to text after the start time when it goes minus and you might not want it turning off streaming in running

Code: Select all
   With Target.Parent
If .Range("D2").Value <= TimeValue("00:05:00") And .Range("K1").Value <> "FO" Then
.Range("Q2").Value = "FULL-STREAM-ON"
ElseIf .Range("D2").Value >= TimeValue("00:05:00") And IsNumeric(.Range("D2").Value) And .Range("K1").Value = "FO" Then
.Range("Q2").Value = "FULL-STREAM-OFF"
End If

End With
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby vanbuuts » Mon Feb 15, 2021 8:56 pm

Thanks as ever CS I’ll see what I can do with regards to your code and checking the affect on performance
vanbuuts
 
Posts: 321
Joined: Thu Oct 16, 2014 8:55 pm

Re: Refresh rate after race has completed

Postby vanbuuts » Fri Feb 19, 2021 12:38 pm

Captain Sensible wrote:Y

A lot of the time it's actually easier to simply exit your worksheet_change routine early before any code is run

Code: Select all

If .Range("D2").Value >= TimeValue("00:05:00")  Then Exit Sub


Or goTo the end of the code to swerve any intensive/unnecessary code depending what your bot is doing


Hi CS, I've added this part of the code as it seemed more logical - the only problem then is that the time is frozen so it will never get to 5mins will it?

Its stopping the refresh so the time is just locked to whatever the time was when the code was entered?
vanbuuts
 
Posts: 321
Joined: Thu Oct 16, 2014 8:55 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Sat Feb 20, 2021 2:41 am

Shouldn't be stopping D2 from updating or freezing it as that's being sent by BA. When's it actually freezing D2
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Sat Feb 20, 2021 2:44 am

A lot of time you need to factor in things like closed markets etc if you write your own routine to navigate markets. I have separate modules just to handle that as there's always something to trip you up
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Sat Feb 20, 2021 2:49 am

Maybe it's just a case of where you're placing the code. If your code turns off events and you exit before turning it back on that will cause problems too
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby vanbuuts » Sat Feb 20, 2021 9:52 am

Captain Sensible wrote:Maybe it's just a case of where you're placing the code. If your code turns off events and you exit before turning it back on that will cause problems too

Yes possibly, but have tried in various parts of the code. Wherever I put it, it just stalls D2 so it will never get to 5 minutes before as its frozen for good. It stops all the streaming which is what I want - but if it also stops the countdown clock it will never know to turn itself back on will it?

I have a fair bit of code as below, which flicks from market to market and loads the new lists at midnight

Code: Select all
Option Explicit

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
       
           If [A7] <> currentMarket Then marketSelected = False
        currentMarket = [A7]
        If [AB2] = "OK" And Not marketSelected Then
            marketSelected = True
              ThisWorkbook.Sheets("R1").Select
                                            [Q8] = -1
                                                                             End If
        Application.EnableEvents = True
End If

       


          If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If triggerQuickPickListReload(1) Then
            triggerQuickPickListReload(1) = False
            Range("Q8").Value = -3
            triggerFirstMarketSelect(1) = True
        Else
            If triggerFirstMarketSelect(1) Then
                triggerFirstMarketSelect(1) = False
                Range("Q8").Value = -5
               
            End If
        End If
        Application.EnableEvents = True
    End If
       
   
End Sub
vanbuuts
 
Posts: 321
Joined: Thu Oct 16, 2014 8:55 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Sat Feb 20, 2021 12:47 pm

Have you adjusted the cell addresses as it look like you're sending excel data to A7 not the default A1 from the code above
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby vanbuuts » Sat Feb 20, 2021 12:53 pm

Yes I factored that in changed ref to D8
vanbuuts
 
Posts: 321
Joined: Thu Oct 16, 2014 8:55 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Mon Feb 22, 2021 3:24 pm

The data in D8 is controlled by BA not excel so maybe your VBA has frozen somehow. Can't see where you're exiting the sub in the code you posted but it's possible you're exiting after you've turned off events (Application.EnableEvents = False) so they never get turned back on.

The code you posted doesn't seem at all intensive so not sure why that would slow your sheets down, it's probably down to where you're placing the exit sub code. I'm not sure how familar you are with BA but the code you posted had unneccessary parts duplicating Application.EnableEvents = True so guessing it's been copied and pasted from the site.

In the code you posted the part
Code: Select all
If Target.Columns.Count = 16 Then 
controls whether the rest of the code runs i.e. if that's true then the rest of the code executes. But we can add additional conditions to that.

So we could add a condition to check it's less than 5 minutes

Code: Select all
Worksheets("Sheet1").Range("D8").Value <= TimeValue("00:05:00")


but because Gruss changes times to text after the off we'd need to account for that also

Code: Select all
IsNumeric(Worksheets("Sheet1").Range("D8").Value)


So adding all together becomes

Code: Select all
If (Worksheets("Sheet1").Range("D8").Value <= TimeValue("00:05:00") Or IsNumeric(Worksheets("Sheet1").Range("D8").Value)) And Target.Columns.Count = 16 Then


I'd consider streaming to be more efficient or simply turning on and off any intensive code only when it's needed but there's nothing intensive in the code you posted so amnedning you target columns count line may be easiest.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Mon Feb 22, 2021 3:30 pm

Just realised the line should be

Code: Select all
If (Worksheets("Sheet1").Range("D8").Value <= TimeValue("00:05:00") Or Application.WorksheetFunction.IsText(Worksheets("Sheet1").Range("D8").Value)) And Target.Columns.Count = 16 Then


Should be checking if it's text not numeric after the off
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Mon Feb 22, 2021 4:06 pm

You also need to remember that code is only doing the exit sub part it's not slowing the refresh rates down, streaming is best for that.

It's easy enough to code something to check when the time is below 5 minutes but you also have to take into account other code that may also be writing to cell Q2, you don't want to overwrite your refresh code etc if they both occur at the same time. If you simply want to swerve the code then the amended target column count will do that.

If you want to speed up and slow down things then you have to take into account other things being sent to Q2 and ensuring we don't continually send data to Q2, not hard but best to check that code snippet works if you want to do that. If you just wanted a simple and dirty way to do it we'd simply check the time and send the refresh rate to Q2 at the start of our code and if later code changed the Q2 data that'd be fine.
The dirty way would be slotting in the following code

Code: Select all
Option Explicit

Dim currentMarket As String, marketSelected As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Columns.Count = 16 Then
        Application.EnableEvents = False

'slot in here

If Worksheets("Sheet1").Range("D7").Value <= TimeValue("00:05:00") Or Application.WorksheetFunction.IsText(Worksheets("Sheet1").Range("D7").Value) Then
Worksheets("Sheet1").Range("Q8").Value = 0.2 'fast refresh
Else
Worksheets("Sheet1").Range("Q8").Value = 10 'slow refresh
End If



'slot in here

       
           If [A7] <> currentMarket Then marketSelected = False
        currentMarket = [A7]


............................rest of code


Slotting it in at the top means the refresh rate can always be overwritten by other code if needed, if you slot it in at the end if would always overwrite anything your code put in Q8. Hopefully not confused you too much but trying to post up between races :)
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Refresh rate after race has completed

Postby Captain Sensible » Mon Feb 22, 2021 4:11 pm

I think those D7's should read D8 but I'm sure you can change them to the correct refresh and time to off column addresses
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Next

Return to Help

Who is online

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