Placing advanced bets on spreadsheets so they stay on the selection if there is a non runner....?

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
Willygubbins
Posts: 109
Joined: Wed Nov 23, 2016 6:03 pm

So I have been attempting to place bets on 3-4 selections in advance to be placed at a specific time, all to be placed before the race, for example in the morning. So far I have managed to (with the help of other forum members) Convert the countdown timer into minutes so it can be referenced using the following formula, I used cell F5 and typed Minutes in E5 so it looked neat.

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2))+(VALUE(MID(TEXT(F4,"hh:mm:ss"),1,2))*60),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),5,2)))

Then I used this formula to place bets 3mins before the off to trade in play.

=IF(F5<3,"BACK OFFSET_PERC:66.6 WITH_GREENING:TRUE","")

Which places the bets exactly as it should, Then I had the problem of the lay bets being cancelled at in play, first I just put KEEP_ALL in the global command cell L6 when i put the formula in but it didn't work, I realised that it had to be entered after the bets had been placed so I used the below formula to get over this.

=IF(F5<2,"KEEP_ALL","")

All of this worked perfectly, apart from when the bets were placed they were not placed on the selections I picked, Two possible things have happened here the selection order has changed which I dont think is the case as the odds are not in order relative to size, or as i suspect there has been non runners and the selections have shuffled up. So what i have done is basically placed these bets by row not specific runner.
So my questions are as follows:

How do i Make sure the order of the runners is locked?

Is there any way of attaching the bet to a specific runner?

Is there a way of locking the sheets so that if there is a nonrunner it just creates a blank cell / row and ll the other runners stay in
the cells they were in before?
Russell
Posts: 9
Joined: Wed Nov 18, 2015 2:38 am

Have you tried incorporating an AND() formula to link a bet to a specific runner?

This would be straightforward for one horse per race, but if you are placing 3-4 bets in advance you could end up with a complex nested IF formula.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I've been thinking about this and one way to do it would be to store the list of runners names when the market first opens (ie when you're setting up your day's business) in a separate list of runners names you want to target, perhaps also with action required/target price/stake etc , and then use code or VLookUps to identify the correct runners in the 'current' market list... it's pretty heavy going.
Russell
Posts: 9
Joined: Wed Nov 18, 2015 2:38 am

I agree with Shaun for multiple selections.

Willygubbins, can I ask a stupid question? Why do you have such a badass formula for calculating the time? You can format a cell with "00:03:00" in it and just compare the countdown with that cell or replace IF(F5<3, with IF(F4<180/(24*3600),
User avatar
Willygubbins
Posts: 109
Joined: Wed Nov 23, 2016 6:03 pm

Russell wrote:
Tue Feb 28, 2017 4:19 pm
I agree with Shaun for multiple selections.

Willygubbins, can I ask a stupid question? Why do you have such a badass formula for calculating the time? You can format a cell with "00:03:00" in it and just compare the countdown with that cell or replace IF(F5<3, with IF(F4<180/(24*3600),
I did use a simelar formula but it didn't take into account the "-" if the race was late
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

Russell wrote:
Tue Feb 28, 2017 4:19 pm
I agree with Shaun for multiple selections.

Willygubbins, can I ask a stupid question? Why do you have such a badass formula for calculating the time? You can format a cell with "00:03:00" in it and just compare the countdown with that cell or replace IF(F5<3, with IF(F4<180/(24*3600),
I think the original conversation was about deriving a variable to store seconds in his VBA proceedures...things may have moved on now and it's relevance is questionable. It's always best to store variables of the same data Type in the same units, saves head scratching later.
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

If we step back from the problem for a sec willy.

Make a blank sheet
Set one cell to '=Now()'
Set another cell to be a ficticious start time

Then create cells that show every possible thing you want to do.

Time until start in seconds ( also cells for fractional minutes, hours and days as a bit of practice)
Time since event started in seconds etc
In-play yes or no
etc etc

I've a feeling you can't see the wood for the trees
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

I wish I only had Excel problems...terrible mains supply here, again.

In one afternoon....
4 blackouts totalling 20 seconds
6 undervoltage dips totalling 38secs
and 28 ! instances of electrical noise totalling 2 mins & 6 secs
burntheory
Posts: 58
Joined: Tue Sep 09, 2014 9:49 am

I may be missing something, but if you're talking about unmatched lay bets, Betfair automatically cancels all unmatched lay bets in the event of a non-runner...unless they're flagged to take SP at the off, in which case there's no way of knowing what the matched odds will be.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”