In-running football

[Drunkenight]

Hi, Does anyone know a way of stopping excel from placing bets, for say 10 seconds, after the market has reopened following a suspension? For example during an in-running football match, when there is a goal the market is suspended, then about 20-30 seconds later the market is normally reopened. I find that the prices that come back after the market reopens can be very volitle. I would like to stop my robots putting up any prices until the market has settled, say about 10 seconds after the market has reopened. Does anyone know of a way of doing this in excel?

Hope this makes sense

[Captain Sensible]

You could add a timer to log the time the markets in play and have it reset to zero for anything other than "In Play" in E2(?). Then just add another condition to your triggers saying don't be til that's a least 10 seconds.

[Drunkenight]

Hi Captain, thanks - yes that is an excellent idea! I can set a trigger to block any bets within 10 seconds of the market opening. If the market is suspended it should set the timer to Zero and only allow bets once the timer has got to say 10 seconds. Do you have any idea what the formula would be?

[Captain Sensible]

You'd need to use VBA to log the time, my vb is pretty limited so hopefully one of the VB experts will post something up but my effort would be something like


Dim check As Integer
Dim timer As Date

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub


If Range("F2").Value = "Suspended" Or Range("E2").Value <> "In Play" Then
check = 0
Range("X1").Value = ""
timer = Now()
GoTo Xit
ElseIf Range("E2").Value = "In Play" And check = 0 Then
check = 1
timer = Now()
End If

If Range("F2").Value = "" And Range("E2").Value = "In Play" And check = 1 Then
Range("X1").Value = Now() - timer
Else
Range("X1").Value = ""
End If



Xit:
End Sub


That should take a snapshot of the current time when the markets in play and not suspended then output the difference to X1. If the market gets suspended or not in play it resets all variables

[Drunkenight]

Thanks! This has given me a lot of ideas. How about if I just add the following code to my VBA:-
If Range("F2").Value = "Suspended" Then Application.Wait Now + TimeValue("00:00:10")

Will this pause the macro for 10 seconds if the market is suspended?

[Captain Sensible]

Like I said I'm a VBA novice DK, no idea how Application.Wait works and if it stops the app receiving data as well i.e. when it starts responding again "Suspended" is no longer present but cleared a second or two ago so you wouldn't get the full benefit of 10second delay if that makes sense.

[Drunkenight]

Yes you are right, it will have the effect of anywhere between 0 - 10 seconds delay after the market has been reopened. I have tested it out and seems to work ok, but will keep an eye on it. Thanks for your help!

[Captain Sensible]

You could always tweak it slightly so "Suspended" sets a marker i'.e.

Dim suspendcheck As Integer

If Range("F2").Value = "Suspended" Then suspendcheck =1 Exit sub

If Range("F2").Value <> "Suspended" And suspendcheck =1 Then
Application.Wait Now + TimeValue("00:00:10")
check = 0
End If

That way it'll continue updating and only go into the wait routine after the market has reopened then clear the suspended check

[Drunkenight]

Brilliant! Yes this is exactly what I want to do.

A couple of questions:-
1. Can I just add this to my existing macro?
2. If Range("F2").Value = "Suspended" Then suspendcheck =1 Exit sub. What does the 'Exit sub' do? Will I have to change this to fit in with my existing code?
3. 'check = 0' should this be 'suspendcheck = 0'

[Captain Sensible]

Brilliant! Yes this is exactly what I want to do.

A couple of questions:-
1. Can I just add this to my existing macro?
2. If Range("F2").Value = "Suspended" Then suspendcheck =1 Exit sub. What does the 'Exit sub' do? Will I have to change this to fit in with my existing code?
3. 'check = 0' should this be 'suspendcheck = 0'

1. can't see any reason why not especially if your code is already using

If Range("F2").Value = "Suspended" Then Application.Wait Now + TimeValue("00:00:10")

2. I just put Exit Sub in there so it would exit the VBA routine and not bother with processing any of the other stuff in your routine cos there's probably no need for it to execute the rest of the code. There's no need to add it especially if the rest of the code is doing something useful whilst the market is suspended.

3. I just changed the variable name to suspendcheck as it was a bit more representative of what it did, and forgot to change the check=0 as I cut and pasted


You'd just need to add the following to your current Worksheet_Change routine.

Declare the variable suspendCheck

Dim suspendCheck as Integer

then inside your Worksheet_Change routine add these at the top of the routine

If Range("F2").Value = "Suspended" Then
suspendCheck =1
End If

If Range("F2").Value <> "Suspended" And suspendCheck =1 Then
Application.Wait Now + TimeValue("00:00:10")
suspendCheck = 0
End If


The first If should set suspendCheck as 1 once the market shows as suspended. As soon as the suspension is lifted the next If with the delay code will come into play as F2 is no longer "Suspended" & suspendCheck contains 1, that should delay for 10 seconds then reset suspendCheck back to 0 and run the rest of your code.

[Drunkenight]

Excellent. Thanks for the code - works a treat!

My robots now wait at least 10 seconds after the suspend comes off - which is exactly what I wanted.

[Captain Sensible]

No problem, glad it worked for you. I'd never heard of Application.Wait Now and used to use sleep for my delays which is more long winded so it'll come in useful next time i need delays in my code