Delay multiple bets on same runner?

[Rinconpaul]

After spending months building an excel bot, it was finally time to connect it to BA. Then I struck a hurdle? On Sheet1 in Trigger cells col Q, I have the formula "=IF(T5<>"","CLEAR",Data!R$43)" ...'T' cells being Bet Ref.
The Data sheet is the sheet that triggers a "BACK" or "LAY" for each runner in a race. Now every 5 secs the Data sheet refreshes and another "BACK" or "LAY" signal will not appear if it's the same price that has already appeared for that runner in the Back or Lay columns. In other words I am averaging in with multiple bets on a steamer or drifter with unique prices only.

Trouble occurs on Sheet1 in that because a "BACK" or "LAY" signal can be sitting on the Data sheet for 5 secs max it'll fire a bet get matched then CLEAR and fire again and again. The result is 20 odd bets placed at same price for a particular runner. Then on next refresh if still a trigger signal it machine guns another heap of bets in. Thankfully I was lucky when I first tried it with very small stakes and the horse won...phew!!

Now I'm thinking I could reference 'My Bets' and have an Index match not allow another bet at the same price (duplicate of what already happens on the Data sheet), but I fear that there is a delay in the bet reference reaching the 'My Bets' sheet from Betfair and my code may have fired off 4 bets in the meantime?

Effectively I want the code in col Q to say this," the instance that cell Data!T? = "BACK" or "LAY" then "BACK" or "LAY" but wait 5 secs then "CLEAR".

It's not the same as a fill or kill order, as because I'm backing at current Back $ or laying at current Lay $, it should get instantly matched, I just want to kill any more bets being placed for a specified number of seconds until next refresh on sheet Data. I'd prefer not to go down the vba route and just keep it an excel formula fix if possible?

[alrodopial]

You can use in formula the info from cell U... (time that bet was accepted by bf)
something like
IF(AND(ISNUMBER(T5),(C2-U5)*86400>=5),"CLEAR",.......

C2 = current update time
be careful if bet is pending or partially matched - that's why I changed the T5<>""
also it would be good if you can insert in formula info from column W (matched amount) - as a firing signal????

[Rinconpaul]

Thanks alrodopial, chuffed to even get a reply on this forum so quickly! Well thought out solution, however?
Your solution would probably work if I was betting on only one horse/dog in a race. These are Place bets so It might call a BACK @ $1.10 on Horse Z at 12:00:00 then another LAY @ $4.5 on Horse X at 12:00:03, then maybe another BACK @ $1.6 on Horse V at 12:00:04....all within the 5 sec refresh? Not sure if your solution would handle these multiple bets?

You're in the "nearly there" zone though, I feel!

[alrodopial]

the formula in Q5
IF(AND(ISNUMBER(T5),($C$2-U5)*86400>=5),"CLEAR",.......
will handle bets for row5 (first horse)
the formula in Q6
IF(AND(ISNUMBER(T6),($C$2-U6)*86400>=5),"CLEAR",.......
will handle bets for row6 (second horse)
etc
the 5 sec rule will apply for each row/horse
I don't know if I'm clear enough or if I understood your question

[Rinconpaul]

I'll give it a try? You see on the Data sheet during that 5 sec 'window' at the end of which the last prices, vol, rank names etc get copied and pasted to a list of 5 sec snapshots. While in that 5 sec window, prices are continually updating and triggering independent of each other into the different rows of Sheet1. Get back to you with feedback, thanks again.

[Rinconpaul]

Feedback for alrodopial. Your method improved things as there was a min of 5 secs between consecutive bets. However I still have issues that I need to sort? The problem with recording results, interpreting them and formulating rules is that's fine, IF, you can exactly replicate live results with archived results!

My problem is that during these 5 second windows of time, a "BACK" signal might flash, set the trigger, but my strategies are based on the signal showing at the 5 second mark, and not the 4 secs of activity prior. I need to use the pasted 5 sec expiration signal as my trigger. I've probably lost you by now, but you helped me wipe out what I thought was the issue, and now I can work on different code to resolve things.

In summary well done to you, I'm only left with one problem to solve now, not two.

[alrodopial]

You're right
I lost you

[alrodopial]

why not take a snapshot every two secs? - instead of 5 -
cpu issues?

[Rinconpaul]

My database is built on 5 sec periods. I take the last 5 minutes before the off for AUS Gallops, and last 4 mins for UK Greys. These time brackets suit the time between races and when real money flows. You see it's all about determining whether a runner is drifting or steaming and averaging in to get a longterm superior starting price than just gambling on what the SP will be? I just need to add "INDIRECT("??") to my formula to reference the last 5 sec snapshot, as opposed being at the whim of an ever changing trigger condition for up to 5 secs. All good. Cheers.