Cancel all bets after first bet is matched

[sjaak1943]

I use P/L column (X) for this. When I run more workbooks at the same time, you can't use this. Who knows another solution?

=IF(AND($E$2="Not in Play";R5=$H$53;S5=$H$54;"LAY-IP";IF($X$1<>0;"CANCEL-ALL"))

Sjaak

[GaryRussell]

Do you mean it doesn't work because you are using multiple workbooks for the same race?

[sjaak1943]

Do you mean it doesn't work because you are using multiple workbooks for the same race?

Yes, when you use one workbook, the sum of P/L column change from 0 to whatever when a bet is matched. So I can't use this when in another workbook with the same market a bet is matched.
Hope I'm clear.

Sjaak

[GaryRussell]

I thought so. You could use the matched stake column. For example:-

Put =sum(W5:W49) in cell W50.

Change your formula as follows.
=IF(AND($E$2="Not in Play";R5=$H$53;S5=$H$54;"LAY-IP";IF($W$50>0;"CANCEL-ALL"))

[sjaak1943]

I thought so. You could use the matched stake column. For example:-

Put =sum(W5:W49) in cell W50.

Change your formula as follows.
=IF(AND($E$2="Not in Play";R5=$H$53;S5=$H$54;"LAY-IP";IF($W$50>0;"CANCEL-ALL"))

Thanks Gary, so column W (matched stake) cannot change from another workbook with the same race?

Sjaak

[GaryRussell]

Thanks Gary, so column W (matched stake) cannot change from another workbook with the same race?

Not if they are different bets. Column W contains the matched stake for the bet ref in column T.

[excelhasey]

What does R5=$H$53;S5=$H$54 do ??

[excelhasey]

I used

=IF(AND(R5>0,S5>0,$S$1<=500,A5<>"",$F$2<>"Suspended",$F$2<>"Closed",$E$2="Not In Play"),"LAY-PL2",IF($AC$2>0,"CANCEL-ALL"))

AC2 being the Min of column X

But when all runners in the race went Green I still had 3 outstanding Unmatched bets that where not Cancelled and I had to manually do this within BA - why, what did I do wrong ??

[GaryRussell]

X is the profit and loss column. Are you confusing it with the level profit column? The P&L column will not change to show that a level profit is available, it will stay showing your current P&L. When you say 'the race went green' it would not have changed because a level profit is available.

[GaryRussell]

What does R5=$H$53;S5=$H$54 do ??

This will be specific to their spreadsheet. Only they will know. It's not the output from Betting Assistant.

[excelhasey]

Yes I know X is profit / loss as your matched bets currently stand (as it would show you in Betfair itself against the selection).

Sorry I have conditionally formatted my X column to go Green if above zero and Red if below, hence I had an all green situation, its nothing to do with BA's ability to show you if there is a level profit available

The bets that had been matched at that time, in my race, must have equated to me having a green book as they were all positive but I still had other bets unmatched in the system that I wanted to be automatically cancelled, obviously I have not written this correctly for Excel / Betfair to understand

So how should I have written my trigger ??

[GaryRussell]

I am guessing that CANCEL-ALL never appeared in column Q.

Are you sure that all the conditions to put "LAY-PL2" weren't still satisfied? Your formula is only putting CANCEL-ALL in column Q if these conditions are not satisfied and AC2>0.

Maybe your could change your formula to read as follows so that if the P&L is green then the bets are cancelled otherwise the trigger is evaluated.

Code: Select all

=IF($AC$2>0,"CANCEL-ALL",IF(AND(R5>0,S5>0,$S$1<=500,A5<>"",$F$2<>"Suspended",$F$2<>"Closed",$E$2="Not In Play"),"LAY-PL2",""))

[excelhasey]

Thanks Gary,

If you have a Lay bet matched on a particular horse and a corresponding Back bet in the system unmatched on the same selection (due to LAY-PL2) will the 2nd bit of the bet, the Back bet, be cancelled or will it only automatically cancel bets on the selections that have not yet had the the 1st bit of the bet, the Lay bet, matched ??

[GaryRussell]

It will cancel the unmatched back bet that has already been placed and cancel all offset bets that have not yet been placed.