Hi,
Can anyone help.
I'm after an excel formula which will fire lay bets if 2 or more horses hit certain odds but has to be at the same time.
e.g out of all horses running if 2 or more horses hit odds of *** together at the same time then place a lay bet for ***.
Is there anyway on doing this?
Thank you
excel formula laying more than one selection at the same tim
Hi AlleyG,
The short answer is yes. I'm attaching a simple spreadsheet that shows the kind of thing you'd need to do. This is only really a proof of concept, you'd need to extend it to meet your full requirements. However, it implements what you've asked for in a really basic way.
Open up the spreadsheet attached to this post (not via BetAngel) and look at the following cells:
AH8: Enter the Lay value that you are watching for, into this cell.
Cells AH9, AH11, AH13 etc. will show "Correct Odds" when the Lowest LAY column matches the value in AH8. You could have it look at the other LAY columns if you wanted or change the formula so that it look for odds between a range of values.
Cells L9, L11, L13 etc. will do three checks:
If all three criteria are met, then it will put the LAY command into column L.
Cells M9, M11, M13 etc. just checks to see if you have a LAY command, in which case, it enters the current Low LAY odds.
Cells N9, N11, N13 etc. just checks to see if you have a LAY command, in which case, it enters a trade amount (set to £2).
Basic test - you'll see that the horse 'Wilde Inspiration' has a Low LAY of 40, so column AH is already saying 'Correct Odds' because that matches the value in cell AH8.
Now just change the Low LAY odds for another horse, e.g. 'Fracking', to 40. You'll see that two trades are attempted, one for Fracking and the other for 'Wilde Inspiration'. Obviously you haven't opened this with BetAngel so the trades won't actually be made.
I did try this with BetAngel running. The only thing I did was change the code in column AH to look for a range of odds (e.g. LOW LAY > 40 and LOW LAY < 500). The reason for this change was that I didn't want to wait hours for two horses to have identical LAY values! When running with BetAngel, on the race on this sheet (Goodwood @ 14:00), it successfully fired off trades for 'Best Kept' and 'Rock Of Dreams' (their odds were between 41 and 500) and then once the trades were made, it correctly removed the LAY command.
So, that's the short answer. I hope it answers your question and gets you part of the way to where you want to get.
This sort of reactive formula is only possible because it relies on cells that are filled in by BetAngel and so avoids something called Circular References.
Now the warning. Formulas can be pretty dumb, and so you'd need to do a lot of testing to avoid double betting etc. And, I haven't checked this thoroughly - this was just a 30 minute exercise. I wouldn't trust any Formula based trading without a huge amount of testing (VBA is much, much safer)!
The longer answer is - use VBA. It's far, far more versatile. If you look into the 'Worksheet_Change' SUB for the BetAngel sheet, you'll see the code you can use if you want to have VBA check the worksheet, each time that BetAngel updates it.
Hope that helps.
Regards
Ian
The short answer is yes. I'm attaching a simple spreadsheet that shows the kind of thing you'd need to do. This is only really a proof of concept, you'd need to extend it to meet your full requirements. However, it implements what you've asked for in a really basic way.
Open up the spreadsheet attached to this post (not via BetAngel) and look at the following cells:
AH8: Enter the Lay value that you are watching for, into this cell.
Cells AH9, AH11, AH13 etc. will show "Correct Odds" when the Lowest LAY column matches the value in AH8. You could have it look at the other LAY columns if you wanted or change the formula so that it look for odds between a range of values.
Cells L9, L11, L13 etc. will do three checks:
- Has NO trade already been made for this row this row (is O9, O11, O13 etc. blank. You could also check against rows P, Q, R, S etc.).
Are there exactly two occurrences of "Correct Odds" in all of column AH?
Does this specific row in column AH say "Correct Odds"
If all three criteria are met, then it will put the LAY command into column L.
Cells M9, M11, M13 etc. just checks to see if you have a LAY command, in which case, it enters the current Low LAY odds.
Cells N9, N11, N13 etc. just checks to see if you have a LAY command, in which case, it enters a trade amount (set to £2).
Basic test - you'll see that the horse 'Wilde Inspiration' has a Low LAY of 40, so column AH is already saying 'Correct Odds' because that matches the value in cell AH8.
Now just change the Low LAY odds for another horse, e.g. 'Fracking', to 40. You'll see that two trades are attempted, one for Fracking and the other for 'Wilde Inspiration'. Obviously you haven't opened this with BetAngel so the trades won't actually be made.
I did try this with BetAngel running. The only thing I did was change the code in column AH to look for a range of odds (e.g. LOW LAY > 40 and LOW LAY < 500). The reason for this change was that I didn't want to wait hours for two horses to have identical LAY values! When running with BetAngel, on the race on this sheet (Goodwood @ 14:00), it successfully fired off trades for 'Best Kept' and 'Rock Of Dreams' (their odds were between 41 and 500) and then once the trades were made, it correctly removed the LAY command.
So, that's the short answer. I hope it answers your question and gets you part of the way to where you want to get.
This sort of reactive formula is only possible because it relies on cells that are filled in by BetAngel and so avoids something called Circular References.
Now the warning. Formulas can be pretty dumb, and so you'd need to do a lot of testing to avoid double betting etc. And, I haven't checked this thoroughly - this was just a 30 minute exercise. I wouldn't trust any Formula based trading without a huge amount of testing (VBA is much, much safer)!
The longer answer is - use VBA. It's far, far more versatile. If you look into the 'Worksheet_Change' SUB for the BetAngel sheet, you'll see the code you can use if you want to have VBA check the worksheet, each time that BetAngel updates it.
Hope that helps.
Regards
Ian
Last edited by EyePeaSea on Thu Sep 26, 2013 8:29 am, edited 1 time in total.
<sigh>
I should have read your post more carefully. You said 2 or more runners needed to meet the criteria, whereas the sheet I attached looks for exactly 2 runners with matching odds.
So, change the formula in L9 (and copy to L11, L13 etc) to:
Also, if you're betting on the Grand National, with a large number of runners, then you'd need to extend references so that it covered more rows (rather than just AH$9:AH$68).
Regards
Ian[/color]
I should have read your post more carefully. You said 2 or more runners needed to meet the criteria, whereas the sheet I attached looks for exactly 2 runners with matching odds.
So, change the formula in L9 (and copy to L11, L13 etc) to:
Code: Select all
=IF(O9="", IF(COUNTIF(AH$9:AH$68,"=Correct Odds")>=2, IF(AH9="Correct Odds", "LAY",""),""),"")Regards
Ian[/color]
Attached is the updated sheet. Tidied up and now allows making selections based on a range of odds.
This is not a working trading system, just an example approach to a problem, that some people may hopefully find useful.
Regards
Ian
This is not a working trading system, just an example approach to a problem, that some people may hopefully find useful.
Regards
Ian
You do not have the required permissions to view the files attached to this post.