Cancelling Unmatched Bets

[boycee]

As I understand it the CANCEL trigger uses the bet reference cells. I am currently working on a strategy thet places bets with a tick offset included, it then involves clearing the Bet Ref under certain conditions but can still leave some of the offset bets unmatched, at the off. I want to be able to cancel these outstanding unmatched bets before the race goes off. how can I do this when I have cleared the Bet Refs for some of the runners?

cheers,

boycee

[GaryRussell]

The CANCEL-ALL trigger will cancel all unmatched bets for the selection. It doesn't need a specific bet ref, but it does expect something to be in the bet ref column because that's how it knows it should trigger. Once it has triggered it clears the bet ref cell. Just put anything in the bet ref column, "VARIOUS" for example.

[boycee]

Thanks Gary,

Whats the best way to achieve putting something in the Bet Ref. I have tried a formula in the Bet Ref column but obviousy it gets wiped on every refresh.

[boycee]

Struggling with this. Would I need a bit of VBA to get something into the Bet Ref cells or can it be done using functions via another cell?

cheers,

Boycee

[GaryRussell]

Yes you will need VBA. I will post an example later.

[boycee]

Ta Gary

[boycee]

I've had a little go at this myself and have managed to learn how to put text into a cell or range of cells using VBA. (Taken off the web)

What I need is how to link it to the timer, so that at say 10 secs before off, it will activate and allow me to use the CANCEL-ALL trigger.

They don't seem to have that on the web.

[GaryRussell]

Here's some example code as promised. Paste the following code into the sheet1 module. At 10 seconds before the off it will put "CANCEL" in all the bet ref cells. You can check for this in your trigger formula. eg. =if(T5="CANCEL","CANCEL-ALL","")

Code: Select all

Option Explicit

Dim cancelTriggered As Boolean
Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim secondsFromStart As Integer
    If Target.Columns.Count = 16 Then
        secondsFromStart = getSecondsFromStart
        If secondsFromStart <= 10 And Not cancelTriggered Then
            cancelTriggered = True
            Application.EnableEvents = False
            Range("T5:T50") = "CANCEL"
            Application.EnableEvents = True
        End If
        If currentMarket <> [A1] Then cancelTriggered = False
        currentMarket = [A1]
    End If
End Sub

Private Function getSecondsFromStart() As Integer
    Dim timeFromStart As Date, secondsFromStart As Integer, beforeStart As Boolean
    If Left([D2], 1) = "-" Then
        timeFromStart = Mid([D2], 2)
        beforeStart = False
    Else
        timeFromStart = [D2]
        beforeStart = True
    End If
    secondsFromStart = (Hour(timeFromStart) * 3600) + (Minute(timeFromStart) * 60) + Second(timeFromStart)
    If Not beforeStart Then secondsFromStart = -secondsFromStart
    getSecondsFromStart = secondsFromStart
End Function

[boycee]

Excellent stuff Gary, much appreciated.

I will fire it up for today's racing.

cheers,

boycee

[boycee]

OK like a child with a new toy I couldn't wait to try this so I had a wee test with the dogs. Of course it didn't work, but then that is probably down to me and my lack of affinity with VBA et al.

So here is what I did with the above code and also what other code I have in this excel file. Don't laugh please.

I copied and paste the above code by right clicking on Sheet 1(Sheet 1) and inserting module in the VB.

I now have 3 pieces of coding in this file, Sheet1 Code, Module 1 and Module 2.

Sheet 1 Code

Option Explicit

Private ifWin() As Currency
Private ifLose() As Currency
Private greenarray() As Variant, FullBA_Array() As Variant
Private selecIndex As New Collection
Private plRange As Range, pl As Variant, levelPLRange As Range
Private currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False
With Sheets(Target.Worksheet.Name)
FullBA_Array = .Range("A1:AB50").Value 'load range into array
calcGreenUp
End With
Application.EnableEvents = True

End If

End Sub

Private Sub calcGreenUp()
Dim r As Integer, i As Integer
Dim selecName As String
Dim stake As Currency, odds As Currency, win As Currency, lose As Currency, diff As Currency, betType As String
Dim idx As Integer
Dim myBetsArray() As Variant, myBetslastrow As Long

ReDim ifWin(100)
ReDim ifLose(100)
ReDim greenarray(5 To 50, 1 To 4)

myBetslastrow = Worksheets("MyBets").UsedRange.Rows.Count
myBetsArray = Worksheets("MyBets").Range(Worksheets("MyBets").Range("A1"), _
Worksheets("MyBets").Range("A1").Offset(myBetslastrow - 1, 5)).Value

currentMarket = FullBA_Array(1, 1)

For r = 2 To UBound(myBetsArray)
' calculate win and lose positions
If myBetsArray(r, 5) = "F" Then
selecName = myBetsArray(r, 2)
idx = getIndex(selecName)
If idx <> -1 Then
stake = myBetsArray(r, 3)
odds = myBetsArray(r, 4)
If myBetsArray(r, 6) = "B" Then
ifWin(idx) = ifWin(idx) + (stake * (odds - 1))
ifLose(idx) = ifLose(idx) - stake
Else
ifWin(idx) = ifWin(idx) - (stake * (odds - 1))
ifLose(idx) = ifLose(idx) + stake
End If
End If
End If
Next
' calculate green up stakes
For r = 5 To 50
getIfWinLose CStr(FullBA_Array(r, 1)), win, lose
If win = lose Then
betType = ""
stake = 0
odds = 0
End If
If win > lose Then
betType = "LAY"
diff = win - lose
odds = FullBA_Array(r,
If odds <> 0 Then
stake = diff / odds
Else
stake = 0
End If
End If
If win < lose Then
betType = "BACK"
diff = lose - win
odds = FullBA_Array(r, 6)
If odds <> 0 Then
stake = diff / odds
Else
stake = 0
End If
End If
If stake < 0.01 Then
stake = 0
odds = 0
betType = ""
End If


greenarray(r, 1) = betType
greenarray(r, 2) = stake
greenarray(r, 3) = odds

calculateFuturePL r, betType, stake, odds

Next
Range("Y5:AB50") = greenarray
End Sub

Private Sub calculateFuturePL(r As Integer, betType As String, stake As Currency, odds As Currency)
Dim i As Integer
For i = 5 To UBound(greenarray)

If IsEmpty(greenarray(i, 4)) And Not IsEmpty(FullBA_Array(i, 1)) Then

If betType = "BACK" Then
If i = r Then
greenarray(i, 4) = FullBA_Array(i, 24) + (stake * (odds - 1))
Else
greenarray(i, 4) = FullBA_Array(i, 24) - stake
End If
Else
If i = r Then
greenarray(i, 4) = FullBA_Array(i, 24) - (stake * (odds - 1))
Else
greenarray(i, 4) = FullBA_Array(i, 24) + stake
End If
End If

ElseIf Not IsEmpty(FullBA_Array(i, 1)) Then

If betType = "BACK" Then
If i = r Then
greenarray(i, 4) = greenarray(i, 4) + (stake * (odds - 1))
Else
greenarray(i, 4) = greenarray(i, 4) - stake
End If
Else
If i = r Then
greenarray(i, 4) = greenarray(i, 4) - (stake * (odds - 1))
Else
greenarray(i, 4) = greenarray(i, 4) + stake
End If
End If
End If
Next
End Sub


Private Function getIndex(selecName As String) As Integer
Dim idx As Integer
Dim r As Integer
Dim found As Boolean
On Error GoTo index_not_found
idx = selecIndex(selecName)
getIndex = idx
Exit Function
index_not_found:
On Error GoTo 0
r = 4
found = False
Do
r = r + 1
If Cells(r, 1).Value = selecName Then
selecIndex.Add r - 5, selecName
found = True
End If
Loop Until found Or Cells(r, 1).Value = ""
If found Then getIndex = r - 5 Else getIndex = -1
End Function

Private Sub getIfWinLose(selecName As String, ByRef win As Currency, ByRef lose As Currency)
Dim idx As Integer
On Error GoTo index_not_found
idx = selecIndex(selecName)
win = ifWin(idx)
lose = ifLose(idx)
Exit Sub
index_not_found:
On Error GoTo 0
win = 0
lose = 0
End Sub

------------------------------------------------------------------------
Module 1 Code

Function getPrevOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function
Sub startbetting()

'
Range("G1").Select
ActiveCell.FormulaR1C1 = "y"

End Sub
Sub stopbetting()

'
Range("G1").Select
ActiveCell.FormulaR1C1 = "n"

End Sub



Function getOddsStepUp(ByVal odds As Currency) As Currency

Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
getOddsStepUp = oddsInc
End Function

Function getOddsStepDown(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select

getOddsStepDown = oddsInc
End Function



Function getTicks(odds1 As Currency, odds2 As Currency) As Single
Dim i As Double
Dim tickCount As Single
Dim thisStep As Double
Dim thisodds As Double

Select Case odds2
Case Is < 1.01, Is > 1000
GoTo Xit
End Select

Select Case odds1
Case Is < 1.01, Is > 1000
GoTo Xit

Case Is < odds2
tickCount = 0
i = odds1
Do While i <> odds2
thisStep = getOddsStepUp(i)
i = i + thisStep
tickCount = tickCount + 1
Loop
getTicks = tickCount

Case Is > odds2
tickCount = 0
i = odds1
Do While i <> odds2
thisStep = getOddsStepDown(i)
i = i - thisStep
tickCount = tickCount + 1
Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

Xit:
End Function

----------------------------------------------------------------------

Module 2 Code

Option Explicit

Dim cancelTriggered As Boolean
Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
Dim secondsFromStart As Integer
If Target.Columns.Count = 16 Then
secondsFromStart = getSecondsFromStart
If secondsFromStart <= 10 And Not cancelTriggered Then
cancelTriggered = True
Application.EnableEvents = False
Range("T5:T50") = "CANCEL"
Application.EnableEvents = True
End If
If currentMarket <> [A1] Then cancelTriggered = False
currentMarket = [A1]
End If
End Sub

Private Function getSecondsFromStart() As Integer
Dim timeFromStart As Date, secondsFromStart As Integer, beforeStart As Boolean
If Left([D2], 1) = "-" Then
timeFromStart = Mid([D2], 2)
beforeStart = False
Else
timeFromStart = [D2]
beforeStart = True
End If
secondsFromStart = (Hour(timeFromStart) * 3600) + (Minute(timeFromStart) * 60) + Second(timeFromStart)
If Not beforeStart Then secondsFromStart = -secondsFromStart
getSecondsFromStart = secondsFromStart
End Function

-------------------------------------------------------

That's all that I have . The other pieces of code for Ticks and GreenUp work fine, but this latest piece for the Cancel Trigger did not.

So where have I gone wrong with it. should it be somewhere else or is some of my original code in the wrong place?

regards,

boycee

[GaryRussell]

My code should not be in a module. Sorry my fault for mentioning "module". It should by combined with the code for sheet1. The top of your sheet1 code should now read as follows. You can leave the getSecondsFromStart() function in module2.

Code: Select all

Option Explicit

Private ifWin() As Currency
Private ifLose() As Currency
Private greenarray() As Variant, FullBA_Array() As Variant
Private selecIndex As New Collection
Private plRange As Range, pl As Variant, levelPLRange As Range
Private currentMarket As String
Private cancelTriggered As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim secondsFromStart As Integer
   If Target.Columns.Count = 16 Then
      Application.EnableEvents = False
      secondsFromStart = getSecondsFromStart
      If secondsFromStart <= 10 And Not cancelTriggered Then
          cancelTriggered = True
          Range("T5:T50") = "CANCEL"
      End If
      If currentMarket <> [A1] Then cancelTriggered = False
      currentMarket = [A1]
      With Sheets(Target.Worksheet.Name)
         FullBA_Array = .Range("A1:AB50").Value 'load range into array
         calcGreenUp
      End With
      Application.EnableEvents = True
   End If
End Sub

[boycee]

Okay Dokay Gary

I have done that and I get an ambiguous error in part of the greenup code. (Highlighted.)

Option Explicit

Private ifWin() As Currency
Private ifLose() As Currency
Private greenarray() As Variant, FullBA_Array() As Variant
Private selecIndex As New Collection
Private plRange As Range, pl As Variant, levelPLRange As Range
Private currentMarket As String
Private cancelTriggered As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim secondsFromStart As Integer
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
secondsFromStart = getSecondsFromStart
If secondsFromStart <= 10 And Not cancelTriggered Then
cancelTriggered = True
Range("T5:T50") = "CANCEL"
End If
If currentMarket <> [A1] Then cancelTriggered = False
currentMarket = [A1]
With Sheets(Target.Worksheet.Name)
FullBA_Array = .Range("A1:AB50").Value 'load range into array
calcGreenUp
End With
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False
With Sheets(Target.Worksheet.Name)
FullBA_Array = .Range("A1:AB50").Value 'load range into array
calcGreenUp
End With
Application.EnableEvents = True

End If

End Sub

[GaryRussell]

You need to delete the code I have highlighted in red.

Option Explicit

Private ifWin() As Currency
Private ifLose() As Currency
Private greenarray() As Variant, FullBA_Array() As Variant
Private selecIndex As New Collection
Private plRange As Range, pl As Variant, levelPLRange As Range
Private currentMarket As String
Private cancelTriggered As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim secondsFromStart As Integer
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
secondsFromStart = getSecondsFromStart
If secondsFromStart <= 10 And Not cancelTriggered Then
cancelTriggered = True
Range("T5:T50") = "CANCEL"
End If
If currentMarket <> [A1] Then cancelTriggered = False
currentMarket = [A1]
With Sheets(Target.Worksheet.Name)
FullBA_Array = .Range("A1:AB50").Value 'load range into array
calcGreenUp
End With
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False
With Sheets(Target.Worksheet.Name)
FullBA_Array = .Range("A1:AB50").Value 'load range into array
calcGreenUp
End With
Application.EnableEvents = True

End If

End Sub

[boycee]

Throwing up more errors Gary.

I will send you the file rather than taking up your time here. If you can manage to look at it fine, I can always potter with it myself.

BTW I tried your code on it's own on a blank/virgin excel file and it still did not work for me. So I'm sure I have cocked up somewhere.

File on it's way.

boycee

[GaryRussell]

I have fixed the code in your spreadsheet and sent it back to you.