How to know when a bet has been cancelled?

[nanook]

I am experimenting with the VBA change routine to back, lay & cancel bets from excel. When cancelling a bet I had assumed that when the bet ref cell cleared the bet had been cancelled. However, now I'm running into the following situation sometimes:

Cancel triggers
Bet Ref clears
The "cancelled" bet matchs
The profit/loss cell updates to show the bet matching but the matched stake cell remains at zero

So, for example, when I want to cancel a stranded bet and re-submit at current odds, the change routine is running the code to do this but I'm ending up with both bets sometimes matching while the program continues on blissfully as if everything were okay. I tried putting in a 5 second delay from the time the bet ref cleared but this didn't solve it. It seems the moment the bet ref clears, the matched stake cell cannot update even if the bet matchs. I suppose I could try referencing the profit/loss cell after the bet ref clears. But then if I bet on more than one horse say, how would I know which profit/loss update is caused by which horse?

Anybody else encounter this problem or have some other method that works?

[Mitch]

I use the CANCEL trigger from a macro as well but the matched stake and odds columns do have values other than 0 in them if part of the bet is matched when I use it.
Are you cancelling the old bet first before you place the new one?

[nanook]

Mitch,

Yes, I am cancelling the old bet before placing the new one. The logic sequence is:

1) Place a bet
2 ) If the odds change cancel the existing bet (if the matched stake cell shows a match before the the cancel clears the
bet ref, the cancel simply fails but thats no problem because I can detect it by referring to the matched stake cell.)
3) If the cancel clears the bet ref cell and the matched stake cell is zero, assume the old bet has been cancelled

This is a little simplified as there is some code to handle partial matchs but its close enough.

The problem I am encountering is that intermittently while I am cancelling a bet, the bet matchs (as shown by the profit/loss cell updating) and the bet ref clears and the matched stake remains zero. In these cases the assumption in 3) that the old bet has been cancelled fails.

I believe I may have pinned down the cause of the problem. As I said I am using the VBA change routine to place the bets in excel. I added some code to print a log of the trigger cell values whenever the change routine was run. Printing the log to the same excel sheet had the effect of activating the change routine more frequently - about 8 times a second. What I discovered is happening is that when a bet matchs the profit/loss cell updates THEN the avg matched odds & matched stake update. Sometimes these three cells update simultaneously (to within one eight of a second anyway). However, intermittently there is a gap of up to one second. When this gap occurs it is a "race" between the matched stake cell updating and the cancel clearing the bet ref cell. If the matched stake cell updates, the cancel fails which is fine; if the bet ref cell is cleared, the matched stake cell won't update and the problem above occurs.

I have no idea why there is this variance in the timing of the proft/loss, matched stake & avg matched odds updates. I can't see how it could be caused by my code in the change routine. I wonder if the refresh rate has some bearing on it. I'm using a one second refresh rate at the moment. I will try increasing it to two seconds and see what effect that has. Ideally though I'd like to understand what is happening so that I can try to control it or at least know the limitations of the control that is possible.

[Mitch]

The main cause of the problem is what you have dicovered about the routine running more often when you create a log.

The reason (which you may already have guessed) is that as soon as you change something on the sheet it activates the worksheet_change routine. If you change the refresh rate on BA to 2 seconds I would guess that it will just keep running the change routine more times between refreshes.

The answer is to disable the worksheet_change event while the macro is running by using application.enableevents=false at the beginning of the code and then application.enableevents=true at the end of the code.

The other thing to note is that BA doesn't paste the data into Excel all in one go. It does the main info first all in one go i.e. columns A to M, then it does the bet info (N to U) one line at a time, so if there are 4 runners in a race BA puts information into Excel 5 times.
This means that the change routine is running 5 times for each refresh from BA.

The way round that is to have this code before you disable the event handler.
If Target.Columns.Count <> 13 Then Exit Sub

With the above code, the macro will only run if the changed area in Excel is 13 columns wide, so it will run when the main data is changed but not for each line of betting data.


I hope the above makes some kind of sense, if I can help any more either post back here or pm me.

Edit: Rethinking, you probably already have the enable events lines of code in your macro, in which case I'd guess that when it ran 8 times a second you were looking at a 7-runner event? The columns <> 13 will prevent that happening.

[nanook]

I have been switching application.enableevents on and off all along. If I have the change routine ignore changes in columns A to M, then the routine won't detect changes in odds which is essential?

I am beginning to think the problem is with the CANCEL trigger. Is it possible that when a cancel is triggered, the bet ref clears automatically ie not as a response to the bet actually cancelling in BA? What I am seeing in my logs is that when a cancelled is triggered and the bet ref succeeds in clearing , the same amount of time elapses in all cases before the bet ref clears - about half a second or five times in and out of the change routine. Also there are cases where the cancel is triggered, the bet ref clears then anywhere up to about a second LATER the bet actually matchs as evidenced by the profit/loss cell updating. In these cases, when I check my betfair betting history the bet is showing as matched with the very same bet reference that was cleared by the CANCEL trigger in excel.

[Mitch]

It does seem strange what is happening to you.

The line...
If Target.Columns.Count <> 13 Then Exit Sub
doesn't ignore changes in A to M, it will ignore everything else and only fire the macro when the odds are updated, not your bet info.

I might not be explaining it very well but I'd try that and see if it works.

[nanook]

Sorry I misread that part of your post. I will give that a shot tomorrow.

[nanook]

Tried using "If Target.Columns.Count <> 13 Then Exit Sub":

Out of 26 matched bets only two were "cancelled" in excel - a much better performance. The change routine was activated usually once per second, sometimes two or three times per second. I guess this reflects variance in the actual refresh rate - I am setting the refresh rate to one second in BA. Before I used this statement, it was running at least eight times per second.

(I'm testing on the greyhound races using auto select market to cycle through all of the races each day.)

What I see on my log now is:

When everything works okay, the profit / loss cell updates then the matched stake cell updates on the next line of the log a second later.
In the case of the two failures, the trigger cell = CANCEL, the bet ref clears and the profit / loss cell updates - all on the same line of the log. The matched stake doesn't update after this.

(The log is simply a print out from the change routine to excel of the values of the bet data cells and some of my own variables. Everything other than the race information gets printed to the right of column "M")

I have an idea to try tomorrow: when a cancel is triggered and the bet ref clears, replace the bet ref (and the bet time too in case that matters). In cases where the bet really cancels, this won't matter as the matched stake cell will be zero. In the problem cases, maybe having the bet ref will allow the matched stake to update on the next refresh.

[Mitch]

This still puzzles me. As far as I know, when you submit a CANCEL request the bet ref is only cleared if ALL of the bet is successfully cancelled. If part of it is matched then the bet ref should stay plus the matched stake in column T will be larger than 0.

Does your macro clear the bet ref when you think it has been cancelled?

[nanook]

The only time I clear the bet ref cell is when placing a bet. Here is a walkthrough of a lay bet that may clarify things:

A lay is executed ie there is a bet ref and a bet time.
The routine then monitors the current odds, doing nothing until the odds have changed.
When the odds have changed, a cancel is initiated by putting "CANCEL" in the trigger cell - nothing else.
The routine now does nothing for five seconds.
After five seconds, the routine then determines what happened with the lay bet by referring to the matched stake cell.
(Whether or not the bet ref cleared doesn't matter...or shouldn't matter if the matched stake cell is a reliable indicator of the status of a bet)
If matched stake = stake, the lay was matched fully.
If matched stake < stake and > 0, the lay was partially matched.
If matched stake = 0, the lay was not matched.

I had assumed that, after this series of events, whenever the matched stake = 0 and the bet ref had cleared, the lay had been cancelled. Then I ran into the cases described in previous posts where the bet ref had cleared, matched stake = 0 and the bet had really matched (sometimes fully matched, sometimes partially).

The bug, if I can call it that, has two aspects:

1) The bet ref is clearing when the bet has actually matched. The profit/loss cell updates to reflect the match. Also the
betfair betting history shows the bet as matched with the same bet reference that was cleared.
2) This wouldn't matter (for my purposes) if the matched stake cell would update. But it doesn't. It seems that the
clearing of the bet ref cell in these cases "prevents" the matched stake cell from updating even though the profit/loss
cell has updated correctly.


Today I tried the "bandaid" solution of putting back the bet ref whenever it was cleared by a cancel trigger. It appears to have worked - I got 34 matchs and no false cancels. I say "appears" because I can't tell if any of them would have ran into the "bug" if the bet ref had not been replaced.

I tested today on the first three traps in each greyhound race. The routine handled the extra work okay so tomorrow I'm going to try it on all six traps with no changes to the code.

[Ian]

Mitch ... I think you are wrong ... if you trigger a CANCEL then the bet ref disappears even if part of the bet has been matched and the remainder of the bet is cancelled.

[Mitch]

I am happy to stand corrected

[nanook]

Over the last three days I have about 125 matchs with no cancel problems. I am going to conclude for now that my bandaid solution is working ie. replacing the bet ref and bet time whenever a cancel clears them. Later I want to look at the 5 second delay I'm using after a cancel is triggered - that may be excessive. But for now at least I have something that works and I'm going to move on. Thanks for the inputs.

[Mitch]

Glad it now seems to work for you.

[funky]

I'm quite a long way through coding my trading routines now as well.

I'm trying to sort out the partially matched bets at the moment and thought I'd search the forums to see how this works and here I am.

so... If I cancel a bet, BA will clear the bet ref etc and the cell which tells me how much of my stake was matched?

But... If I put the bet ref back into column Q, and remove the "CANCEL" from column N, will the partially matched amount show up in column T?

If someone can confirm this, that'd be great