Status cells sometimes getting stuck at placing

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

I noticed that my status cells are sometimes getting stuck at "PLACING", anyone encounter this before?

I can't be sure but I suspect that it may occur while im tampering with the workbook while a bet is being placed. Any ideas?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

No help I'm afraid but I've had this too. It 'fixed itself' which wasn't ideal because I don't know what caused it. My issue was placing bets on markets later in the day not the current active one.

Try looking in the settings and you'll see something like 'scan later markets' in the excel tab... Can't check it because I'm not home but it's definitely one of the settings I might have switched on and off.
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

And I think I remember that you never tamper with connected sheets yeah?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Some do and are fine but I generally don't. Habit really as I like everything paused while I'm editing so I don't have values changing while I'm debugging.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

Most cells are refreshed on each excel refresh cycle but I suspect that the bet status cell and maybe a few others only gets updated once so if it is missed then it probably never gets set. I think that BA excel updates are temporarily suspended whilst macros are running (including automated worksheet change macros).
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Wolf1877 wrote:
Sun Apr 22, 2018 10:50 am
I think that BA excel updates are temporarily suspended whilst macros are running (including automated worksheet change macros).
Not the case, updates happen when excel has a spare cycle. If not then my worksheets would never update because my sub procedures run non stop polling for individual field changes every x milliseconds. I never use worksheet change events because it's a so processor heavy. You can force updates if you don't think they're getting done by inserting a DoEvents command in your code.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

ShaunWhite wrote:
Sun Apr 22, 2018 2:45 pm
Wolf1877 wrote:
Sun Apr 22, 2018 10:50 am
I think that BA excel updates are temporarily suspended whilst macros are running (including automated worksheet change macros).
Not the case, updates happen when excel has a spare cycle. If not then my worksheets would never update because my sub procedures run non stop polling for individual field changes every x milliseconds. I never use worksheet change events because it's a so processor heavy. You can force updates if you don't think they're getting done by inserting a DoEvents command in your code.
Well it might vary by Excel version. I have 2010 office professional. Try this experiment Shaun. Open a BA worksheet tab that is being frequently refreshed by BA. Click the developer and click the Macros icon to open the macros dialog box. At that stage the refresh stops for me even if I havent selected a macro to run. When I close the dialog box by clicking Cancel the refresh starts again. My macros are being triggered by worksheet refresh several time a second but thats not continuously. Typically they are being launch up to 8 times a second and my processes take less than 20ms. If my macros were slow say 200ms then I suspect that any BA refresh whilst my macro was running would not be able to get in. No solid hard evidence for this other than what I have observed.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

ShaunWhite wrote:
Sun Apr 22, 2018 2:45 pm
Not the case, updates happen when excel has a spare cycle. If not then my worksheets would never update because my sub procedures run non stop polling for individual field changes every x milliseconds. I never use worksheet change events because it's a so processor heavy. You can force updates if you don't think they're getting done by inserting a DoEvents command in your code.
Shaun, are you using Sleep in a VBA loop to delay your polling or some other technique? And also are you using DoEvents in your code? I'm thinking that maybe Sleep and DoEvents both yield to the operating system and allow the BA events to process which I assume are done via microsoft interop.
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

ShaunWhite wrote:
Sun Apr 22, 2018 2:45 pm

If not then my worksheets would never update because my sub procedures run non stop polling for individual field changes every x milliseconds. I never use worksheet change events because it's a so processor heavy. You can force updates if you don't think they're getting done by inserting a DoEvents command in your code.
would you reveal how you do this? Presumably with gettickcount or such but how do you trigger the polling without a worksheet change?
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I'll get back to you on that when I'm at my pc.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

This isn't necessarily the absolute best way, but it works for me.

I have 2 buttons on my sheet, one runs the StartRecording sub, and the other runs the StopRecording sub

Code: Select all

Sub StartRecording()

    'Set Running indicator to Yes
    Sheets("Preferences").Range("RecordStatus") = "Yes"
            
    'Loop until not recording
    While Sheets("Data").Range("RecordStatus") = "Yes"
       Call RecordData
       Call WaitForNext
    Wend

End Sub

Sub StopRecording()

    'Set Running indicator to No
    Sheets("Preferences").Range("RecordStatus") = "No"
    
End Sub

Sub WaitForNext()
    Dim Start As Single
    
    PauseTime = Sheets("Preferences").Range("RecordInterval")    ' Set pause duration.
    Start = Timer    ' Set start time.
    'Loop until time has passed or Recording has been stopped
    While Timer < Start + PauseTime And Sheets("Data").Range("RecordStatus") = "Yes"
        DoEvents    ' Yield to other processes.
    Wend
    
End Sub

Sub RecordData()

    'Process and store data
End Sub
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

ShaunWhite wrote:
Mon Apr 23, 2018 1:29 pm
This isn't necessarily the absolute best way, but it works for me.

I have 2 buttons on my sheet, one runs the StartRecording sub, and the other runs the StopRecording sub

Code: Select all

Sub StartRecording()

    'Set Running indicator to Yes
    Sheets("Preferences").Range("RecordStatus") = "Yes"
            
    'Loop until not recording
    While Sheets("Data").Range("RecordStatus") = "Yes"
       Call RecordData
       Call WaitForNext
    Wend

End Sub

Sub StopRecording()

    'Set Running indicator to No
    Sheets("Preferences").Range("RecordStatus") = "No"
    
End Sub

Sub WaitForNext()
    Dim Start As Single
    
    PauseTime = Sheets("Preferences").Range("RecordInterval")    ' Set pause duration.
    Start = Timer    ' Set start time.
    'Loop until time has passed or Recording has been stopped
    While Timer < Start + PauseTime And Sheets("Data").Range("RecordStatus") = "Yes"
        DoEvents    ' Yield to other processes.
    Wend
    
End Sub

Sub RecordData()

    'Process and store data
End Sub

Thanks Shaun, I've been trying to optimize things a bit (my sheets are taking 3-5) secs to refresh and this may help. Cheers.
Wolf1877
Posts: 367
Joined: Fri Sep 08, 2017 10:59 am

sa7med wrote:
Mon Apr 23, 2018 1:38 pm
Thanks Shaun, I've been trying to optimize things a bit (my sheets are taking 3-5) secs to refresh and this may help. Cheers.
Shaun's code is a really interesting idea to improve performance by running a single macro continuously in a loop, but regarding your comment about your sheets taking 3-5 seconds to refresh that is not an issue I have and I use worksheets change to launch my code. I get up to about 8 refreshes per second mainly depending on how I have set Guardian up.

I use 2 instances of BA/Guardian/Excel each with all of the days UK/Ireland Race win markets - so 43 races today and 43 connected worksheets per BA/Guardian instance. The first instance of BA/Guardian/Excel is connected to all races from when I set it up at the start of a day through til each race is suspended - this handles my processing until 20 minutes before the off in each market and Guardian cycles through all 43 worksheets in about 3 or 4 seconds and gets faster as more races are finished.

The second instance of BA/Guardian/Excel is set up almost exactly the same except that I use the Restrict Refresh option in Guardian so a race only connects 1200 seconds / 20 mins until 15 mins after the scheduled start time. The second instance handles processing from 20 minutes out through the inplay period until the end of the race. The advantage of the 2nd instance is that only a few races are connected in Guardian hence the refresh cycle from BA/Guardian is faster as fewer sheets are connected which is more critical in the period just before and during a race. I get about 8 worksheet updates a second processed by my second instance with a 100ms connection rate.

If your stuff is taking 3-4 seconds to refresh you need to check if that is that due to he speed that Guardian is refreshing the sheets because it updates all 43 race worksheets in a cycle. If so you may be able to speed that up with Restrict Refresh. If the 3-4 seconds is really because your code is taking 3-4 seconds per sheet then then you need to optimise your code and excel calculations. Shaun's solution might help you to improve performance but it is unlikely to save you 3-4 seconds per sheet.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

+1 Wolfy

I only used my way because i'm usually just grabbing key info at various intervals rather than needing every tiny tic. It's got it's own issues, mainly me not remembering to press start & stop :roll:
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

Wolf1877 wrote:
Mon Apr 23, 2018 3:16 pm
If your stuff is taking 3-4 seconds to refresh you need to check if that is that due to he speed that Guardian is refreshing the sheets because it updates all 43 race worksheets in a cycle. If so you may be able to speed that up with Restrict Refresh. If the 3-4 seconds is really because your code is taking 3-4 seconds per sheet then then you need to optimise your code and excel calculations. Shaun's solution might help you to improve performance but it is unlikely to save you 3-4 seconds per sheet.
Thanks for your post Wolf. I meant that it takes about 3-5 secs to cycle through all the sheets (all the racing for the day), not just a single sheet (that would be a disaster.) It used to be better, around 2-3, but the sheets got a little bigger and more complex - mainly that i started recording more selections (15 instead of 10). Ive been looking for ways to optimize but im no excel guru and only recently learned how to code.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”