What am I missing - excel cancel bet

[bolpx001]

I noticed that Excel does not cancel bets in running as I would expect, when I cancel a bet it seems to have no effect. I have attached is a small log file I extracted from excel and an extract from my account statement in Betfair. Each line in the Excel log represents .2 of a second and the refresh rate of BA is set to .2 of a second. Any help or suggestions very much appreciated.

Time Stamp R5C17 R5C18 R5C19 R5C2020/11/2011 13:10 BACK 1.01 4.1
20/11/2011 13:10 BACK 1.01 4.1 PENDING
20/11/2011 13:10 BACK 1.01 4.1 PENDING
20/11/2011 13:10 BACK 1.01 4.1 PENDING
20/11/2011 13:10 BACK 1.01 4.1 PENDING
20/11/2011 13:10 BACK 1.01 4.1 PENDING
20/11/2011 13:10 CANCEL 1.01 4.1 PENDING
20/11/2011 13:10 CANCEL 1.01 4.1 PENDING
20/11/2011 13:10 CANCEL 1.01 4.1 PENDING
20/11/2011 13:10 CANCEL 1.01 4.1 PENDING
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666
20/11/2011 13:10 CANCEL 1.01 4.1 17215298666

2011-11-20
13:16 17215298666 2011-11-20
13:10 IRE Punch 20th Nov / 13:10 2m Nov Chs / Lucky William
Back 3.20 4.10 Lost (4.10)
[/img]

[bolpx001]

I should also have added that all betting is done via a VBA macro

[GaryRussell]

Are you sure your VBA code is correct? The cancel triggers do work so it is more than likely your code. A likely cause is that BA is not getting chance to process the trigger. If your code inserts the cancel trigger and then removes it before the next refresh then that will stop it working. It is best practice to make sure your code populates the trigger cells when the prices are updated because it is in this update that it processes the triggers. It is also good practice to prevent further events firing while your code updates.

Eg.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        ' your code goes here
        Application.EnableEvents = True
    End If
End Sub


Note that it checks if the updated columns count is 16, this is the update where the triggers are processed.

[Ian]

As a matter of interest, does the worksheet_change proc run before the triggers are processed on this refresh or after ?

[bolpx001]

Thanks for the reply Gary, I needed more control over the timing of the macro so I set an external program to trigger it every 160 milliseconds. If you look at the log file you will see that BACK statement is pending for the first 5 iterations and then the CANCEL command is launched the bet remains in the pending state for 4 iterations and is then matched despite the CANCEL command. The code is fine because the BACK command is accepted in the first instance and cells R5C20 are updated to pending and then to the bet ref later What seems to not be having any affect is the cancel statement. If I run the exact same code before the off but set the odds to 1000 the bet cancels fine and works as expected. The issue only arises in 'In Play"
The refresh rate is set to .2 and the macro runs every 160 milliseconds so Betfair have plenty of opportunity to process the commands and do in fact process the BACK command
Paul

Hi Ian, normally when I run BA the worksheet_ change proc trigger the macro, however in this instance the macro is run independently ever 160 milliseconds
Paul

[GaryRussell]

There isn't a problem with CANCEL not working so it still must be your code. It would be better if you inserted cancel during the change event as per my example. Your code could set a flag which the change event can check and insert CANCEL accordingly.

eg.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        If doCancel then
           ' insert cancel trigger
           doCancel = False
        End If
        Application.EnableEvents = True
    End If
End Sub

In the above example you could trigger cancel by setting doCancel to true.

[GaryRussell]

As a matter of interest, does the worksheet_change proc run before the triggers are processed on this refresh or after ?

Before the triggers are processed.

[Ian]

As a matter of interest, does the worksheet_change proc run before the triggers are processed on this refresh or after ?

Before the triggers are processed.

Thanks for that. I had a somewhat similar issue to bolpx001 whereby the triggers are being generated outside the BA update process which I have now sorted.

[bolpx001]

Thanks Gary, the reason I did not want to use the Worksheet_Change event is that the update time from Betfair can be quite erratic and dependent many factors. I think I may have figured out what is wrong but I need to test my theory first. I suspect my code is making excel busy when the updates are to be processed and as a result of this they are not being acted on. I will test when time allows and post the results. If what I suspect is true I will have to use the Worksheet_Change event and work out the timings in another way, probably using the GetTickCount function.

[bolpx001]

I now understand what is happening. When the bet is placed there is a delay in applying it to the market. This I understood was to allow for sudden changes in running, a horse falling or a sudden goal. I erroniously beleaved that the bet could be cancelled during this period, unfortunately it cannot. The bet can olly be cancelled when it is applied to the market. I also notice that the data being set to excel is paused from the time the bet is placed until it is applied to the market. For you information I have attached a log of how this works.
Seconds since last update r5c17 r5c18 r5c19 r5c20
0.219
0.218
0.218
0.203
0.250
0.125
0.187
0.218
0.219
0.218
0.156 BACK 1000 4.1
2.090 BACK 1000 4.1 17307432544
0.312 BACK 1000 4.1 17307432544
0.344 BACK 1000 4.1 17307432544
0.202 CANCEL 1000 4.1 17307432544
1.311 CANCEL 1000 4.1
0.328 CANCEL 1000 4.1
0.234 CANCEL 1000 4.1
0.202 CANCEL 1000 4.1
0.344
0.202
0.234
0.125