Trigger 1 but cancel others

[todger]

Still new and trying to find my way around excel triggers. I got some great help trying to get a basic "IF" trigger going and that is reaping some rewards now.

What I would like to know now is if there is a way of cancelling triggers (of the same conditions) on the other horses in a race once the trigger for one horse is met.

On a side note, is there a way of using the lay the field and cancel when matched button on the grid interface, but only applying both to a portion of the field.

Ultimately what I am trying to do is to set conditions on a number of horses so that the first one to meet the trigger a) cancels all the other horses and their triggers, and all their corresponding lays except for the triggered horse. At the moment I am doing this manually in running using the clear cells button on excel and then cancelling all the corresponding lays on the Gruss page manually - quite a hairy exercise. Hope this m,akes sense and hope soemone can help. Captain Sensible was fantastic last time. Thanks

[Captain Sensible]

If you're using excel formulas instead of VBA you need to get a bit creative with the formulas but all the info you need is sent to the excel sheet.

You can nest IF statements within each other so say we stick this formula in A2 and use A1 as our trigger

=IF(A1=1,"One",IF(A1=2,"Two","Not One or Two"))

We can now check for more than two separate scenarios in cell A1, not sure how many IF's you can nest within each other but it's at least 7 so instead of displaying "Not One or Two" we can stick in even further IF statements if needed .

So now you can have your original IF statement trigger to place bets and the second condition to cancel any unneccessary bets.

OK so how can we put that in theory, well alot depends on how you're betting into the market what other bets have been placed etc

we can use the Avg matched odds or Matched stake columns to show us a bet has been matched or part matched and do a simple sum of the amounts and once thats over 0 we know we have a match and send a CANCEL request to any cells that don't have matched sums i.e. we nest something like


IF(AND(SUM($W$5:$W$46)>0,W5=0),"CANCEL","") within our formula

Just read thru the triggers as there are plenty ranging from cancelling all back bets, all lay bets etc just choose the one that fits whatever you're doing. You can also use the MyBets sheet if needed where you'll have more access to any other bets you may have placed in the market and they'll all be listed with matched status etc

[Captain Sensible]

Obviously that's a very simplistic solution and you should think thru all possible outcomes to any triggers you code before using them to make sure you cover all bases. i.e. you may get more than one bet part matched do you want to cancel any remaining etc do you only want to cancel once one bet is fully matched etc and so on.

You can never have enough criteria to cover yourself when botting as it's rare you'll ever profit from a mistake in your coding.

[todger]

That's taking it on another level for me, but I will try to get my head round it and will no doubt be prompted to ask for clarification when i reach the inevitable stumbling blocks.

I do get the sum of a column greater than 0 as a bail out trigger aspect and see how that's a neat solution. Thanks again - will no doubt be on here asking for back-up to this one soon.

[todger]

Yep, that's a bit of a stretch for me and my limited knowledge of Excel formulas (had to look up VBA too - is it worth me going on a crash course?)

This is what I tried sticking is as a formula before Excel laughed at me cruelly and spat it back derisively with a host of suggestions I didn't understand.

I think I need a bit more help with the nesting concept - where and how I place my secondary condition etc.

What I want is for the the first condition to trigger the back and the second to cancel the bet. I take your point about partially matched bets etc but I figure this is a good place to start as I develop strategies and delve deeper into excel. This script probably is full of schoolboy errors but there it is...

=IF(AND(F5>=5.2,F5<=5.9,$E$2="In Play"),"BACK","",IF(AND(SUM($W$5:$W46)>0,"Cancel"))

Is there a formula that cancels all triggers and bets in a market that I can just put in a vacant trigger cell (ie next to a horse I am not interested in) if this is an easier solution)

Please could you point out just how stupid I have been>?

[Ian]

I think this should be the formula

=IF(AND(F5>=5.2,F5<=5.9,$E$2="In Play"),"BACK",IF(SUM($W$5:$W46)>0,"CANCEL",""))


The triggers must be in capitals.

[BERTRAND]

Hi Todger
I'm a novice too, but I believe you can only "nest" IF statements from within previous IF statements. ie IF (THEN, ELSE) becomes
IF(THEN,IF(THEN,ELSE)) etc.
Bert

[todger]

OK, came up with some more tinkering on the formula, not really knowing what I was doing but going on trial and error plus some logic and came up with the following.

=IF(AND(F5>=3.5,F5<=4.1),"BACK",IF(AND(SUM($W$5:$W$46)>0,W3=0),"CANCEL",""))

Tried it on some markets to test it and the weird part was that it worked on most but not all horses in the market - where the "CANCEL" command didn't pop up in in the trigger cell, that trigger remained very much live (cost me a few pence experimenting but not quite up to the price of a pint yet)

Any ideas why the anomalies should arise?

[Ian]

The way the formula is written, CANCEL will only happen if the odds of selection are not between 3.5 and 4.1. So you could get more than one bet matched per race.

[Captain Sensible]

OK, came up with some more tinkering on the formula, not really knowing what I was doing but going on trial and error plus some logic and came up with the following.

=IF(AND(F5>=3.5,F5<=4.1),"BACK",IF(AND(SUM($W$5:$W$46)>0,W3=0),"CANCEL",""))

Tried it on some markets to test it and the weird part was that it worked on most but not all horses in the market - where the "CANCEL" command didn't pop up in in the trigger cell, that trigger remained very much live (cost me a few pence experimenting but not quite up to the price of a pint yet)

Any ideas why the anomalies should arise?

OK For a start you have W3=0, I'm assuming that's a typo as it should refer to the same line as F5 i.e. W5.

Otherwise it's possible bets had been matched on more than 1 selection so the trigger would not cancel bets on selections that had a bet match. Your criteria AND(F5>=3.5,F5<=4.1) is pretty broad and you'll have lots of runners within those ranges especially when markets are forming, I'd knock that idea on the head right away as it'll cost you bigtime.

I mentioned you should look thru all triggers to cover all scenarios that may crop up and use the one best suited. Because you mentioned

'and all their corresponding lays except for the triggered horse' I'd assume you want each selection's trigger to be independant. If it wasn't two because the uncancelled selection's had bet matches you need to check the formulas against those runners to see where thing went wrong.

There's a trigger CANCEL-ALL-MARKET you can use but remember this will cancel all bets within the market, all lays/backs on every runner. So if you're using offset betting or want to leave any part matched bets up in the hope they're fully taken it will not be suitable. To stick that in your current formula we'd remove the W3=0 (W5) because we want to cancel all parts even part matched bets.



=IF(AND(F5>=3.5,F5<=4.1),"BACK",IF(SUM($W$5:$W$46)>0,"CANCEL-ALL-MARKET",""))

Again I wouldn't recommend using this formula as it will cost you on partially formed markets bigtime.

I'l stick some reply to the first post later about vba etc

[Captain Sensible]

The way the formula is written, CANCEL will only happen if the odds of selection are not between 3.5 and 4.1. So you could get more than one bet matched per race.

Yep ian's right if the odds are in that range it'd still be displaying BACK rather than move on. You can just insert another AND condition to remove that criteria i.e. AND(SUM($W$5:$W$46)=0,F5 etc )

The trouble with your current idea is that I could lay a 30 runner field at 3.6 when the market opens and you'd back them all if we hit the market at the same time. You're not taking into account the fact you can be betting into a very overround and bad value book

[todger]

Thanks for all the input. Spent a good few hours today trying to get my head round this - haven't quite succeeded yet but all the help is great.

Whichever I tried, I still didn't always get the desired results. Ie, not all bets were cancelled, even though the majority were. There was no "back" instruction in the cells initially, just a blank (presumably representing the trigger) so i don't think it was down to more than one horse meeting the conditions - even allowing for the delay in play. Have looked at the formulas and they were consistent.

It's a pain because I have something I think could work if I could semi-automate it.

To explain, what I am trying to do is to set up conditions on a selected number of horses so that in play, the first to meet the conditions is traded and the remainder are cancelled. Provided I quit early enough in the race I can get some good trades (with a pre-existing lay) with very little risk - the only real problems so far have arisen from finger trouble because I am having to cancel bets manually etc, and mistakes get made or the delay in operating manually proves costly.

I have no idea why the formulas don't apply to every horse consistently - if they did it would make this idea a relative breeze. Any suggestions?

Thanks

[Captain Sensible]

Thanks for all the input. Spent a good few hours today trying to get my head round this - haven't quite succeeded yet but all the help is great.

Whichever I tried, I still didn't always get the desired results. Ie, not all bets were cancelled, even though the majority were. There was no "back" instruction in the cells initially, just a blank (presumably representing the trigger) so i don't think it was down to more than one horse meeting the conditions - even allowing for the delay in play. Have looked at the formulas and they were consistent.

It's a pain because I have something I think could work if I could semi-automate it.

To explain, what I am trying to do is to set up conditions on a selected number of horses so that in play, the first to meet the conditions is traded and the remainder are cancelled. Provided I quit early enough in the race I can get some good trades (with a pre-existing lay) with very little risk - the only real problems so far have arisen from finger trouble because I am having to cancel bets manually etc, and mistakes get made or the delay in operating manually proves costly.

I have no idea why the formulas don't apply to every horse consistently - if they did it would make this idea a relative breeze. Any suggestions?

Thanks

When you say a selected number of horses how are you actually putting in those formulas to ensure you don't do the field? Have you made sure when copying formulas against each horse the formulas are relevant to each horse

i.e. if the horse is in A5 any price formula references the prices in F5 and for a horse in A6 the formula contains F6 for any prices and so on. Also if you want to see something showing in the cells just amend the bit that shows nothing "" to something like "Waiting"

......"CANCEL-ALL-MARKET","")) to .............."CANCEL-ALL-MARKET","WAITING"))

Also if you have pre exisiting bets in the market it might be an idea to use the MyBets sheet to ensure you play those runners only.

One of the main problems you'll have in play is that odds are moving so fast by the time excel has displayed and fired your bet the markets moved on , plus if the market is that mature and filled with orders you may end up firing for bets in your range not because they represent any true odds but just because they're the only offers up.

[todger]

Thanks again Captain. I am copying and pasting and making sure that the relevant references are in place as you suggest.

What puzzles me is why there would be cells waiting and not triggered or cancelled once the conditions are met. What is happening is that the condition is met on one horse but a random amount of other horses (one or two normally) are still waiting for the trigger whereas the majority are cancelled according to the script.

If I place these conditions against the field I only put stakes against those horses I am interested in and zeros against the rest.

I get what you mean about the in play markets but I have factored that in to my calculations and it is working in my favour in principle - at least at my current very small stakes.

I am bewildered as to why the formula isn't consistent because if it was i would be on to a winner I reckon.

Cheers, Todger

[Captain Sensible]

You have to step thru the formulas to see why something isn't working .

This is the formula you stuck up does it actually contain W3 instead of the W5 which it should ??


=IF(AND(F5>=3.5,F5<=4.1),"BACK",IF(AND(SUM($W$5:$W$46)>0,W3=0),"CANCEL",""))

It may be better to rearrange any formula so you actually check for bets being placed and cancelling rather than checking the odds range first as that should avoid the formula getting stuck i.e.

=IF(AND(SUM($W$5:$W$46)>0,W5=0),"CANCEL",IF(AND(F5>=3.5,F5<=4.1),"BACK",""))

You might want to consider using things like fill and kill or offset betting to acheive the same things, and also stick limits in your odds cell not use 1.01