Lay Dutch Test of dynamic subsets

[RINCONPAUL]

'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]

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]

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]

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]

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]

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]

Your data sheet has a few errors in it I think. Looks like Kil6 and KIL7 were run together
Capture.JPG

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]

That's awesome Paul!! Couldn't believe my eyes, as if by magic....great work. Thankyou.

[Bob Phillips]

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]

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

[Paul_Hossler]

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.