PDA

View Full Version : Lay Dutch Test of dynamic subsets



RINCONPAUL
04-13-2016, 03:18 PM
'Dutching' is a betting term whereby you spread the Risk (Liability) over several selections in a horse race and in return reduce your profit. "Lay" is betting that the horse does not win the race. In the attachment col A are horses prices. Col B denotes whether they won or lost. Col C denotes subsets (different races). Col D is simple enough, it converts the price of each selection to a probability% of winning implied by it's price.

Now it starts to get tricky (for me anyway). Crudely I have manually added up the Probability% for each selection to end with a subtotal (orange). This is important as we need this figure for a formula used in Col F. Col F determines the stake for each runner within a race using a total stake shown at cell C$1. Col G takes 6.5% commission off the stake if a successful lay and returned to bettor. Col H works within each race subset and determines the overall loss if any one of the race runners wins. Again a complex formula as it's subtracting one loss from the other gains but omitting the current row each time?

Col I finds any losses and col J determines whether there was a losing selection in any race and does the sums of profit/loss alongside the subtotal rows. The need for a method to speed these calculations up, is firstly every race is different, both in prices and number of selections. Secondly, I need to run a test over 100's of thousands of races to determine whether the selection strategy is profitable. That's what worries me as an array formula used to isolate subsets is so processor intensive, that excel will likely not be able to cope. Maybe a VBA solution would help. Thanks.

Bob Phillips
04-14-2016, 12:30 AM
My suggestion that you would be better to use Power Pivot and DAX measures. Other than that it is hard to say as you talk about array formulas, to do what? I am not a betting person, so I have no idea whether your approach is good or not, so the formula for profitability is not clear, but I bet DAX can do it.

RINCONPAUL
04-14-2016, 12:52 AM
Thanks for the reply. It's not so much a formula for profitability, it's a method to sense the start and end of a subset and carry out uniform formula to the rows in that subset. You can do it with Max, min type function array formula, but this is carrying out basic arithmetic functions. If every subset was the same number of rows, easy. What I should do is stack all say 3 row subsets, followed by 4, 5 etc, but seems a bit 'Fred Flinstone'?

Bob Phillips
04-14-2016, 01:05 AM
the only place I can see you using the last item is in column F. You can do that without knowing where the last item is, without an array nonsense, with the simple formula


=$D3/SUMIFS($D$3:$D$23,$C$3:$C$23,$C3)*$C$1

Are there any others?

Bob Phillips
04-14-2016, 01:26 AM
Just found some more.

F3: =D3/SUMIFS($D$3:$D$23,$C$3:$C$23,C3)*$C$1
H3: =(-F3*(A3-1))+(SUMIFS($G$3:$G$23,$C$3:$C$23,C3)-L3)
J3: =IF($C3=$C4,"",IF($I3<>"",$I3,$C$1*0.935))

But I still would use Power Pivot myself.

RINCONPAUL
04-14-2016, 01:41 AM
Thanks again XLD, I've shut the computer for the day, so will check those formulas in the morning. You've got me curious about Power Pivot and DAX now, so I'll look into it. You've been very helpful.

Paul_Hossler
04-14-2016, 08:06 AM
Your data sheet has a few errors in it I think. Looks like Kil6 and KIL7 were run together
15921

Here's a non-PT approach.

Look at the 'Data' worksheet in the attachment and run the 'Betting' macro since I made a minor format change





Option Explicit
Const cStake As Double = 5#
Const cCommish As Double = 0.935

Sub Betting()
Dim rData As Range, rDataNoHeaders As Range, rstart As Range, rEnd As Range
Dim wsData As Worksheet, wsSummary As Worksheet
Dim vData As Variant
Dim i As Long, iStart As Long, iEnd As Long, iData As Long
Dim dblBlockCommishTotal As Double, dblBlockLoserTotal As Double, dblTotalOutcome As Double
Dim iNumLoser As Long

Application.ScreenUpdating = False

'set up
Set wsData = Worksheets("Data")
Set rData = wsData.Cells(1, 1).CurrentRegion
Set rDataNoHeaders = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)

'sort into the order I want
With wsData.Sort
.SortFields.Clear
.SortFields.Add Key:=rDataNoHeaders.Columns(3)
.SortFields.Add Key:=rDataNoHeaders.Columns(2)
.SetRange rData
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

dblTotalOutcome = 0#
' 1 2 3 4 5 6 7 8 9 10
'PRICE L or W SERIES Probability% Subtotals Stake Stake-Commish Liability Loser Subset Outcome
vData = rData.Value
i = 2
Do While i <= UBound(vData)

iStart = i
iEnd = iStart - 1

Do
iEnd = iEnd + 1
If iEnd > UBound(vData) Then Exit Do
Loop Until vData(iStart, 3) <> vData(iEnd, 3)

iEnd = iEnd - 1


Application.StatusBar = "Processing Block " & iStart & "-" & iEnd & " for " & vData(iStart, 3)

dblBlockCommishTotal = 0#
iNumLoser = 0
dblBlockLoserTotal = 0#

For iData = iStart To iEnd
'probability
vData(iData, 4) = 1# / vData(iData, 1)

'subtotals
If iData = iStart Then
vData(iData, 5) = vData(iData, 4)
Else
vData(iData, 5) = vData(iData, 4) + vData(iData - 1, 5)
End If
Next iData


For iData = iStart To iEnd
'stake
vData(iData, 6) = (vData(iData, 4) / vData(iEnd, 5)) * cStake

'stake-commish
vData(iData, 7) = vData(iData, 6) * cCommish

dblBlockCommishTotal = dblBlockCommishTotal + vData(iData, 7)
Next iData

For iData = iStart To iEnd
'liability
vData(iData, 8) = -vData(iData, 6) * (vData(iData, 1) - 1) + dblBlockCommishTotal - vData(iData, 7)
Next iData

For iData = iStart To iEnd
'loser subset
If vData(iData, 2) = "L" Then
vData(iData, 9) = 0#
Else
iNumLoser = iNumLoser + 1
vData(iData, 9) = vData(iData, 8)
End If
dblBlockLoserTotal = dblBlockLoserTotal + vData(iData, 9)

Next iData

'outcome
If iNumLoser = 0 Then
vData(iEnd, 10) = cStake * cCommish
Else
vData(iEnd, 10) = dblBlockLoserTotal
End If

dblTotalOutcome = dblTotalOutcome + vData(iEnd, 10)

i = iEnd + 1

Loop
rData.Value = vData
wsData.Cells(1, 12).Value = "Total Outcome"
wsData.Cells(2, 12).Value = dblTotalOutcome
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

RINCONPAUL
04-14-2016, 12:08 PM
That's awesome Paul!! Couldn't believe my eyes, as if by magic....great work. Thankyou.

Bob Phillips
04-15-2016, 12:55 AM
Your data sheet has a few errors in it I think. Looks like Kil6 and KIL7 were run together

Here's a non-PT approach.

Look at the 'Data' worksheet in the attachment and run the 'Betting' macro since I made a minor format change


I picked up on the errors too, and my formulae accommodated them correctly.

But, the guy said he has hundreds of thousands of rows, I can't see VBA performing anywhere near as well as simple Excel formulae in that situation.

RINCONPAUL
04-15-2016, 01:25 AM
Paul's code took about 10 secs to crunch 276000 rows, faultlessly. Best code I've ever witnessed! My selections delivered a $69k profit in back testing for a $5 stake dutched for each race. Having a successful system is only eligible if you can properly test it over a big sample. This solution certainly achieved that :clap::clap:

Paul_Hossler
04-15-2016, 06:26 AM
I picked up on the errors too, and my formulae accommodated them correctly.

But, the guy said he has hundreds of thousands of rows, I can't see VBA performing anywhere near as well as simple Excel formulae in that situation.

1. Formulas were good, but I couldn't do any timing with such a small set of data


2. I think there was a typo in

H3: =(-F3*(A3-1))+(SUMIFS($G$3:$G$23,$C$3:$C$23,C3)-L3)

The last L3 should have been G3 I think

H3: =(-F3*(A3-1))+(SUMIFS($G$3:$G$23,$C$3:$C$23,C3)-G3)


3. If I were the OP, I would not want to pour through 100K's of lines, so I'd add some code to the macro to build a summary sheet as I went.