Race Outcome detection with automated quick pick list

[Fixador]

hi, i am looking for a quicker way to detect the end of race - by monitoring before the race starts - i have a potential conflict , details below:-

I have setup a quick pick list, with uk win races

1.0 races spaced 10 mins apart.

2.0 Auto selection is set to= 1 min prior to race start

3.0 Excel log current prices export is targeted cell A10

4.0 Following through the events coming up on the Excel sheet , i get the following:-

4.1 BEFORE START:- E11 = “Not in Play” F11 = “” Sum(B14:M48) >0

4.2 START INSTANT #1 E11 = “Not in Play” F11 = “Suspended” Sum(B14:M48) >0

4.3 START INSTANT #2 E11 = “In Play” F11 = “Suspended” Sum(B14:M48) = 0

4.4 RACE STARTED E11 = “In Play” F11 = “” Sum(B14:M48) > 0

4.5 RACE NEAR END E11 = “In Play” F11 = “” Sum(B14:M48) > 0

4.6 RACE ENDED E11 = “In Play” F11 = “Suspended” Sum(B14:M48) = 0

4.3 and 4.6 are the same -and i really dont want to do something like =match(min(O14:O48),O14:O48,0 at 4.3 interval

should i add a timer at 4.3 and 4.6 , set to expire , say 30 secs , before looking for the winner ? So only 4.6 would qualify ?

Or is there a better way ?

[Fixador]

Hmm, i could express it a little better.........

Of the 3 conditions that are identical in 4.3 and 4.6 , should i also add a timing device, to detect no change in values of the other 3 conditions for say 30 secs , before determing the winner.

Seems that with such a sophisticated bit of software, resorting to using a timer - seems odd - ish ??? which is why i ask - is there a better way ?

[Mitch]

Are you using vba for this or formulas only?

What I mean is, what have you got triggered at the end of a race, a formula or some vba code?

[Fixador]

Hello Mitch,

I haven't got either as yet ! hmmm, will probably be some of each ! - a cell to change value when the 3 first conditions are met - and a vba timer , to detect the value in the cell doesnt change for 30 secs , before ( vba ) going to search for that value in Col O between 1.0001 and 2.0

But - what i a really wondering about is - am i going at this the right way ? because it seems to me to be potentially fraught - since i have to sit through possible brief "suspended" during "in-play", and ill opportune network issues - hence the 30 sec timer

Is there a better way to detect the end of the race ? ie, without resorting to a timer to detect no values changed in the cell ranges ?

Paul

[Fixador]

Mitch,

another aspect to this beyond coding - is the use in BA of exporting via Market , Auto select market , wait until after after OFF

I dont understand this - it seems to roll to the next race ( in quick pick list ) once the current race has started . So it isnt an "In-play" feature of BA - is that correct ?

Paul

[Mitch]

Wait until after the off will only switch to the next race once the current one is in-play. What you say is correct.

I don't think there is any surefire way to check the winner of a race because sometimes the last 'lowest priced' horse gets beat in a photo, but I'd just check for the lowest price horse once Suspended has appeared after the race has been in-play.
As far as I know that would have to be done with vba because you'd need a flag of some kind to note when the race went in-play.

[Fixador]

thanks Mitch,

I had thought "Wait until after the OFF" meant - only "In-play" intially ! - thanks for confirming it is not !

With avoiding getting into vba , as i just wanted to discuss / verify concepts re detection of lowest odds at end of race, and its 'inference' that it is the winner. OK - its not fool proof , but if it works most of time - thats ok.

Once at "In-play" cant "Suspended" occur several times before the end of the race ? - I suspect it is concides with say a runner falling ( a NH season thing ! ) - ok, these are brief periods of "Suspended" - hence , the notion of linking a countdown timer set at say 30 secs. Also the condition 4.3 gives a brief "Suspended" when "In play" - that is immediately UPON the OFF !

I couldnt think of anything else other than a countdown timer ....................

What do you think ?

[Fixador]

Mitch , i saw you did something simlar back on 25th dec06 posting, with following code

Private Sub Worksheet_Change(ByVal Target As Range)

If target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

If Cells(2, 5) <> "In Play" Then
Cells(1, 24) = "NO"
Cells(1, 25) = ""
Application.EnableEvents = True
Exit Sub
End If

If Cells(1, 24) = "NO" Then
Cells(1, 24) = "WAIT"
Cells(1, 25) = Cells(2, 3)
End If

If Cells(2, 3) - Cells(1, 25) >= TimeSerial(0, 0, 20) Then
Cells(1, 24) = "OK"
End If

Application.EnableEvents = True

End Sub


I reckon, with a little adaption ( condition 4.6 ) , this could become my intiating device , to examine Col O

[Fixador]

Mitch, whereas yr code was set to fire off 20 secs after start of in play, this lot - i hope - will fire off - 20 secs after the end of the race , based on start export 1 min before race start

Am going to try it in a bit. The display of value of counter , is just let me watch how far it gets - or watch for the sequence, rather than firing up debug tools

sheet calc in S1 =SUM(B14:M48)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
'export to A10
Dim counter As Integer
counter = 0
    If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Cells(1, 17) = counter: Cells(9, 18) = ""
    If (counter = 0 And (Cells(1, 19) > 0) And (Cells(11, 6) = "") And (Cells(11, 5) = "Not In Play")) Then
    '(1,24) is visual marker        '(1,19) is sum(...)         '(11,6) is suspended        '(11,5) is in play
            counter = 1: Cells(2, 17) = counter            ' 1st pass is b4 inplay
    End If
   
    If (counter = 1 And (Cells(1, 19) = 0) And (Cells(11, 6) = "Suspended") And (Cells(11, 5) = "In Play")) Then
        counter = 2    '2nd pass : clear and roll to inplay
        Cells(3, 17) = counter: Cells(3, 18) = " 2nd pass"
    End If
   
    If (counter = 2 And (Cells(1, 19) > 0) And (Cells(11, 6) = "") And (Cells(11, 5) = "In Play")) Then
        counter = 3 ' in play has confirmed
        Cells(4, 17) = counter: Cells(4, 18) = "gone in play"
    End If
   
    If (counter = 3 And (Cells(1, 19) > 0) And (Cells(11, 6) = "Suspended") And (Cells(11, 5) = "In Play")) Then
        counter = 2  'a temporary suspended has occurred - horse fall over ! '
        Cells(5, 17) = counter: Cells(5, 18) = "short period suspension "
    End If
   
    If (counter = 3 And (Cells(1, 19) = 0) And (Cells(11, 6) = "Suspended") And (Cells(11, 5) = "In Play")) Then
        counter = 4  'looks like end if race
        Cells(1, 24) = "NO": Cells(1, 25) = "": Cells(6, 17) = counter
    End If
   
    If counter = 4 And Cells(1, 24) = "NO" And (Cells(11, 6) = "Suspended") And (Cells(11, 5) = "In Play") Then
        Cells(1, 24) = "WAIT": Cells(1, 25) = Cells(11, 3): Cells(7, 17) = counter
        'C11 ie time ( not countdown : D11 not reliable)
    End If
   
    If (counter = 4 And (Cells(11, 6) = "") And (Cells(11, 5) = "In Play")) Then
        'suspended was a temporary  one - a different type , so reset timer start
        counter = 3: Cells(1, 24) = "": Cells(1, 25) = "": Cells(8, 17) = counter
        Cells(8, 18) = "timer false start - reset done"
    End If
   
    If (Cells(11, 3) - Cells(1, 25) >= TimeSerial(0, 0, 20)) And counter = 4 And (Cells(11, 6) = "Suspended") And _
                        (Cells(11, 5) = "In Play") Then
        Cells(1, 24) = "OK": counter = 0: Cells(1, 25) = "": Cells(9, 17) = counter: Cells(9, 18) = " job done once"
    End If
    Application.EnableEvents = True
End Sub

[/code]

[Mitch]

My brain feels a bit like Swiss cheese at the moment so I've not looked at the code you've posted, but just to answer an earlier point you made. Once the race goes in-play, the market is not suspended again until the race is over.

[Fixador]

Hello Mitch,

dont bother reading that code - its utter rubbish

had better success with a DO UNTIL ... LOOP firing on "In-play" - but still to sort out stuff inside the loop

Paul

[Fixador]

I mean:-

Do while ...........

Loop

to send a marker to a cell

Code: Select all

Do While (Cells(11, 5) = "In Play" And Cells(9, 18) = "") 

works ok , going to try see if it will pick up on the Sum(C12:M50) via Cells(1, 19) = 0

with the prospect of mutiple loops - one after each other maybe.

Start by jumping to 'in play" to avoid the multiple changes that occur at the start of in play

Mitch - i am puzzled by your remark of no "suspended" when goes "in play" - I have excel sheets from 2 days ago of when i managed to stop the data feed - at suspended and in play - before the race end

[Mitch]

As far as I know the market is never suspended whilst the race is being run.

Sometimes it can suspend for a few seconds before the race starts if a non-runner is taken out.

Sometimes the market can be suspended at the end of the race then turn in-play again to allow betting on the outcome of a photo-finish which would make it appear that the market was suspended mid-race maybe.

[Fixador]

Hello Mitch, thanks for your reply, after pondering over it, i reckon my next test session vba will take the form of :-

Do until - In-Play

Loop
Timer 1 : 30 secs - to get way clear of the start flashing of Suspended that can occur

Do until - Suspended

Loop

Timer2 : set 15 secs

Do until ( result of Col O agree at timer 3 snapshots )

Timer3 : set 15 secs : examining Col O to infer winner : both snap shots to agree )

Loop

This doesnt allow for photo finish - returns to inplay or suspended in play , but it is a better starting place. Perhaps , doing my Sum(C13:M50) can be used to verify at the end

And this is more VB in feel - rather than VBA !

What is Application.EnableEvents = False about ?

Paul

[Mitch]

If you're macro is triggered by cells on the worksheet changing, you need Application.Enableevents = False to prevent changes your macro makes to the sheet triggering the code again in a never ending loop. It just turns off the event handlers. Don't forget to turn it back on again when the code has finished.