March 24, 2020 at 9:20 pm
I would provide a table creation script but I am getting the following error which I posted earlier >
Attempt to retrieve data for object failed for Server '(LocalDB)\MSSQLLocaDB'. (Microsoft.SqlServer.Smo)
Additional Information
Invalid Urn filter on server level: filter must be empty, or the server attribute must be equal with the true server name. (Microsoft.SqlServer.Smo)
Therefore if you go to the image I uploaded it shows the design view of the table for replication.
However you may be able to solve this mathematical issue with just the information from this web URL about the formula used to calculate "dutched" bets, e.g selecting more than 1 horse in a race to win and always guaranteeing a profit no matter which horse wins.
If you go to https://www.goalprofits.com/dutching-calculator/ and put the same values as I am inserting into my table you get the same result
So for 5 horses
Price - Amount to Stake
Selection 1 - 1.54 - £2.59
Selection 2 - 3.5 - £1.14
Selection 3 - 6.0 - £0.67
Selection 4 - 10 - £0.40
Selection 5 - 20 - £0.20
Total to stake across ALL selections = £5
The problem is that the dutching formula which you can get online > https://insights.matchbook.com/step-step-guide-back-lay-dutching/
and is used by all these free dutching calculators, is that if Selection 5 won, e.g £0.20 * 20.00 = £4.00 winnings/return, it would NOT cover the stakes of the other 4 runners e.g
SELECT 2.59 + 1.14 + 0.67 + 0.40 = £4.80
So the formula falls over and the aim is to make a profit whichever horse comes in.
The problem is the odds on horse e.g Selection 1 which has a price under evens (1/1 or 2.00) of 1.54. This causes the total probability over all runners to be above 100% which then causes the formula to calculate how much to bet on each runner to fall over by not covering all other runners stakes/bet amounts.
Obviously if none of the selections win then I lose my "Race Stake" of £5 to bet across all selections. So the aim is to pick runners with the best probabilities of winning - a selection process I want to automate but am doing it by hand at the moment by truncating the DUTCHING table and inserting each selection and price, then running my stored proc which gives me each selections stake/bet amount.
So to prove the issue of a selection < 2.00, if I changed selection 1 to have a price of 2.20 then the results are
Price Amount to Stake
Selection 1 - 2.20 - £2.15
Selection 2 - 3.5 - £1.35
Selection 3 - 6.0 - £0.78
Selection 4 - 10 - £0.47
Selection 5 - 20 - £0.24
Total to stake = £5 (across all runners)
So if selection 5 wins>> 0.24 * 20.00 = £4.80
So we add up the stakes of all the losing selections.
SELECT 2.15 + 1.35 + 0.78 + 0.47 = £4.75 (which is less than the winning amount of £4.80)
So a very small profit of £0.05 is made BUT it is a profit, whereas having an odds on runner in the selection process causes an issue.
Therefore I need to come up (if possible) with a new dutching formula that handles having an odds on selection.
For example today I had a total stake of £5 split over 3 horses and a 14/1 / 15.00 horse won
Horse - Price - Stake - Prob% - Potential Winnings
Frontal Assault - 3.25 - £2.58 - 30.76 - £8.38
Yousayitbest - 4.50 - £1.86 - 22.22 - £8.37
Captain Kangaroo - 15.00 - 0.56 - 6.66 - £8.40 (won)
So Captain Kangaroos winning amount of £8.40 easily paid off the other 2 selections total stake of £2.58 + £1.86 = £4.44 giving me £8.40-£4.44 = £3.96 profit.
I am obviously using small stakes to test this with as the selection process for the runners will be automated once I can guarantee any dutched bet will return a profit and at the moment having a selection with a price < 2.00 causes an issue.
Here is my current stored procedure which matches the output of free dutching calculators on the web.
NOTE: I am using the Locked=1 clause as I am working on the same table with this new proc whilst still using the existing formula for real life bets.
I know it uses cursors but I need two loops as per the dutching formula in the article, so I don't need comments about creating a SELECT statement solution as to debug it, it is very helpful to iterate through the selections and view the prices/bet amounts etc anyway.
If you can solve the issue then I wouldn't mind a recordset solution however I am not worried about that at the moment as the table is always small (5 records max), and I truncate it before each race, then add the selections in with their prices, then call the proc to get the bet amounts per selection.
CREATE PROCEDURE [dbo].[usp_sql_dutch_runners]
@RaceStake MONEY = 5.00 -- default to £5 per race
AS
BEGIN
SET DATEFORMAT YMD
SET NOCOUNT ON;
DECLARE @NoRunners INT,
@BetStake MONEY,
@TotalProb MONEY,
@RunnerProb MONEY,
@Calc MONEY,
@HorseName nvarchar(100),
@Price MONEY,
@PotentialWinnings MONEY,
@DEBUG BIT
SELECT @DEBUG = 1
IF @DEBUG = 1
BEGIN
PRINT 'IN usp_sql_dutch_runners'
PRINT 'Race Stake £' + CAST(@RaceStake as varchar(10))
SELECT 'CURRENT RUNNERS TO DUTCH'
SELECT * FROM DUTCHING WHERE Locked = 1
END
SELECT@NoRunners = COUNT(HorseName)
FROMDUTCHING
WHERELocked = 1
IF @DEBUG = 1
PRINT 'NO of Runners = ' +CAST(@NoRunners as varchar)
DECLARE R CURSOR FAST_FORWARD FOR
SELECTHorseName, Price
FROMDUTCHING
WHERELocked = 1
ORDER BY Price
OPEN R
WHILE(1=1)
BEGIN
FETCH NEXT
FROM R
INTO @HorseName, @Price
IF @@FETCH_STATUS != 0 BREAK
IF @DEBUG = 1
BEGIN
PRINT 'Calculate Horses Probability'
PRINT @HorseName
PRINT @Price
END
SELECT @RunnerProb = ROUND((1 / @Price * 100),2)
IF @DEBUG = 1
PRINT 'Runners Probability is ' + CAST(@RunnerProb as varchar)
UPDATEDUTCHING
SETProbability = @RunnerProb
WHEREHorseName = @HorseName
AND Locked = 1
END
CLOSE R
DEALLOCATE R
SELECT@TotalProb = SUM(Probability)
FROMDUTCHING
WHERELocked = 1
IF @DEBUG = 1
PRINT 'Total Probability is ' + CAST(@TotalProb as varchar)
DECLARE R CURSOR FAST_FORWARD FOR
SELECTHorseName, Probability, Price
FROMDUTCHING
WHERELocked = 1
ORDER BY Price
OPEN R
WHILE(1=1)
BEGIN
FETCH NEXT
FROM R
INTO @HorseName, @RunnerProb, @Price
IF @@FETCH_STATUS != 0 BREAK
IF @DEBUG = 1
BEGIN
PRINT 'Calculate Horses Probability'
PRINT @HorseName
PRINT @RunnerProb
END
SELECT @BetStake = ROUND((@RunnerProb / @TotalProb) * @RaceStake,2)
SELECT @PotentialWinnings = @BetStake * @Price
IF @DEBUG = 1
BEGIN
PRINT @HorseName + ' Bet Stake is £' + CAST(@BetStake as varchar)
PRINT 'Potential Winnings are £' + CAST(@PotentialWinnings as varchar)
END
UPDATEDUTCHING
SETStake = @BetStake,
PotentialWinnings = @PotentialWinnings
WHEREHorseName = @HorseName
AND Locked = 1
END
CLOSE R
DEALLOCATE R
IF @DEBUG = 1
BEGIN
SELECT 'ALL RUNNERS STAKES'
SELECT*
FROMDUTCHING
WHERELocked = 1
END
END
Any help on this formula would be great. Try not to think of horse racing if it helps but the one aim of a dutching formula is to pick X runners in each race that gives the best coverage to get a winner and ALWAYS get a profit if one of these selections win.
However having just ONE selection with a price less than 2.00 (or 1/1) causes an issue and I want to resolve it if possible as odds on favourites win roughly 50% of all horse races, odds against favourites (> 2.00 e.g 2.50 or 3.50) win roughly 33% of all races.
So adding a favourite in with some longer priced runners gives us a good chance of winning but if we cannot cover the other selections stakes there is no point in doing this.
Thanks in advance!
March 24, 2020 at 10:30 pm
The problem is not inherent to including runners with implied probability of winning greater than 50%.
This problem would arise any time the total implied probability of winning for all picks exceeds 100%.
Having a single runner with implied probability of winning greater than 50% is neither necessary nor sufficient for the issue you describe.
Dutching simply can't turn a profit if the implied probability of winning for all your picks exceed 100%. Nothing is wrong with the formula to calculate the amounts to bet; the selection of runners is what would have to be fixed.
Cheers!
March 25, 2020 at 1:27 am
Yes, if I had more than one runner odds on, or so many runners that the probability was over 100% then the problem occurs.
However this is the standard Dutching formula used by all the free Dutching calculators and even by a premium automatic betting BOT I was trialling the other month. I didn't want to extend my trial from £4.99 to a year - for £299 - and with UK Racing on lockdown, that was a good choice, especially as I have managed to pick 3 selections daily for an Irish race and get a winner. So my manual selection process is good but I am going to need to automate it based on my own ranking formula for horses. However what I don't get it how the total probability for say 5 runners in a 15 runner race should ever be over 100% when there are at least 10 other runners who could win the race still.
So surely it's more a case of how the probability of each runner is chosen. Just because one runner in 15 is odds on and gets a high probability or X runners are selected BUT still not the same number as those racing, then I don't see how OR why the probability should ever go above 100%.
So it is a selection process but also I think a logic/maths problem that betting on say 5 out of 15+ runners in a race should never give you over 100%. I don't know what the change in the formula should be but surely that is where the issue lies and some sort of change in the formula is needed.
I can carry on just selecting 3 runners a day and ensuring a profit is made if one of them wins but ideally I would like to fix this core problem which every Dutching system seems to use so that my formula is correct and only when 15 out of 15 total runners, or 5 out of 5 total runners are selected should the total probability ever add up to 100%.
I am sure you understand this but I admit I am not cleverer than the people who have Dutched before me and have no idea how to adjust the formula except tweaking every runners probability when its a number less than the total number of runners in a race, which should probably be a new variable in the equation, and ensure the figures never add up to more than 100% unless every runner in a race is bet upon.
Thank you for your help.
March 25, 2020 at 3:16 am
This is getting a bit away from SQL Server, but I'm up for the occasional digression (and there could be some interesting SQL Server tasks once all this is hammered out). 🙂
The probability in question here is the probability implied by the odds being offered, and that's not something that you can adjust.
The sum of the implied probability (determined by offered odds) over all outcomes will always exceed 100% in these settings as that's an easy way "the house" ensures they make money whatever the outcome, and some proper subsets of the outcomes will nearly always end up with implied probability greater than 100% as well.
This is all just a function of the odds offered, and doesn't depend on the "actual" probability of the events in question.
Imagine that I'm going to bet on just 2 outcomes.
I first pick outcome A, which is a heavy favorite with 1-to-2 odds (implied probability 2/3).
If outcome A happens, the wager on outcome A by itself yields profit of 50% of whatever I wagered on outcome A.
Of course, I will also be wagering on some outcome B, and when outcome A happens, I lose the amount wagered on B. This means that if I want outcome A to result in overall profit for me, then the amount I wager on outcome B must be no more than 50% of what I wagered on outcome A, since if it is more than 50% of what I wagered on outcome A, I will lose money across both wagers.
Now, if outcome B happens and I want to make sure I make money across both wagers in that event, the profit just from the wager on outcome B will need to at least offset what I lose from the wager on outcome A.
However, the amount wagered on outcome A will be at least 2x what is wagered on outcome B (remember that the wager on outcome B must be no more than 50% of what is wagered on outcome A), so my profit from a successful wager on B will have to return at least 2x the wagered amount, i.e., it will have to have odds of (2 or higher)-to-1, or an implied probability of no more than 1/3.
If for my second wager I pick anything with (less than 2)-to-1 odds, then I simply cannot satisfy the above requirements and will lose money in at least one of the two outcomes.
So, to reiterate, whether dutching across some outcomes can yield profit for all successful wagers is completely determined by the offered odds for the chosen wagers, regardless of what the "actual" probability for each of the events is, and the odds chosen by bookmakers will, if they're doing their jobs well, always end up implying probabilities greater than 100% across all outcomes; it matters not one whit that the "actual" probability across all outcomes can't be greater than 100%.
No amount of fiddling with the numbers or using different formulas will help if your chosen outcomes have a total implied probability greater than 100%. You simply have to choose different outcomes on which to wager if you want all successful wagers to result in profit when dutching.
Cheers!
March 25, 2020 at 2:03 pm
Thanks Jacob for the explanation. I have a book I am reading on the history of gambling and how probability is worked out, I just got thrown by the trial software I was using for Dutching, as some races he had 8 runners selected to Dutch and the winnings would have never paid off the other stakes.
Therefore I just didn't understand what he was doing or why and thought the core formula must be wrong. To sell a product for £299 and do that - well it was why I quit the trial and have been doing my own dutching. It just a shame the racing in Ireland has now stopped as I was doing okay with 3 runners per race.
So it's not something I can base on the actual market probability. I understand overround, underround markets, I just thought maybe the formula could have been based on the actual probability of each runner IF and only IF they all equalled 100% .
I know it's not really a SQL matter, the implementation could have been, but if the core formula I have is correct anyway then I guess I have to keep it.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply