timed auto level bet possible ?

[brecki]

Hi,

I managed to tell the BA to bet automatically 5 Minutes before the off. It can be a lay or a back bet this depends on several parameters.

My question: Now I want the BA to close this open position 30 seconds before the off on auto level profit, whatever the result may be. Is there a trigger to do so, f.e. a 99 tick offset bet that gets automatically changed after 270 seconds (or when the starting clock went down to 00:00:30) using the actual odds changing the amount for autoleveling the profit?

Thx for help !

[GaryRussell]

There isn't a trigger for this, but it is possible with a bit of effort. See http://gruss-software.co.uk/forum/viewtopic.php?t=4659 for an example which calculates the required level profit stake on every refresh. You could incorporate this into your spreadsheet and trigger the required bet at 30 seconds to the off.

[brecki]

Thx Gary, after testing a little bit I got it work.

But there is one problem remaining.

When writing the triggered bet in the "MyBets" sheet, not only the bet triggered by the actual workbook is written down but also bets on or against the same horse triggered by other opened independent workbooks are included.

So that means, that the greening bet is calculated against all bets for that horse but I want Excel to calculate the greening bet only against the bet that has been triggered by the actual workbook.

For that reason, I just clicked on "log my bets" for the actual workbook, all other workbooks have these option deactivated, but that has no effect.

May be you have an idea?

[GaryRussell]

You can adapt the calcGreenUp sub in sheet1 so that it only includes the bet reference in column T in the calculation. Substitute this sub with the following code. The "Greened up P&L" will still show the overall P&L, but the stake column will now only show the required stake to green up the last bet.

Code: Select all

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 myBetsRange As Range
    Dim betRef As String
    r = 2
    ReDim ifWin(100)
    ReDim ifLose(100)
    Set myBetsRange = Worksheets("MyBets").Cells
    If currentMarket <> Cells(1, 1) Then initMarket
    pl = plRange
    currentMarket = Cells(1, 1)
    Do
        ' calculate win and lose positions
        If myBetsRange.Cells(r, 5).Value = "F" Then
            selecName = myBetsRange.Cells(r, 2).Value
            idx = getIndex(selecName)
            If idx <> -1 Then
                betRef = Cells(idx + 5, 20).Value
                stake = myBetsRange.Cells(r, 3).Value
                odds = myBetsRange.Cells(r, 4).Value
                If myBetsRange.Cells(r, 1).Value = betRef Then ' only include last bet made against this selection
                    If myBetsRange.Cells(r, 6).Value = "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
        End If
        r = r + 1
    Loop Until myBetsRange.Cells(r, 1).Value = ""
    ' calculate green up stakes
    For r = 5 To 50
        getIfWinLose Cells(r, 1).Value, win, lose
        If win = lose Then
            betType = ""
            stake = 0
            odds = 0
        End If
        If win > lose Then
            betType = "LAY"
            diff = win - lose
            odds = Cells(r, 8).Value
            If odds <> 0 Then
                stake = diff / odds
            Else
                stake = 0
            End If
        End If
        If win < lose Then
            betType = "BACK"
            diff = lose - win
            odds = Cells(r, 6).Value
            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
        Cells(r, 25).Value = betType
        Cells(r, 26).Value = stake
        Cells(r, 27).Value = odds
        calculateFuturePL r - 4, betType, stake, odds
    Next
    levelPLRange = pl
End Sub


[brecki]

Can you PLEASE post the hole code for sheet 1, MyBets and module1 here for me ???

I tried to substitute but it didn´t work and obviously I made a mistake, but I am not familiar with macros, so I can´t find it.

Thank you very much !!!

[brecki]

Besides it is important that the greening bet depends on the bet reference taken from the MyBets sheet and NOT from column T on Sheet 1, because the bet reference must be cleared by the CLEAR trigger before the greening bet can be triggered, otherwise it won´t be possible to trigger the greening bet as there are no other trigger cells than in the column Q.

Thx.

[GaryRussell]

Just Module1 and Sheet1 require code. Here it is.

Sheet1

Code: Select all

Option Explicit

Private ifWin() As Currency
Private ifLose() As Currency
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
        calcGreenUp
        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 myBetsRange As Range
    Dim betRef As String
    r = 2
    ReDim ifWin(100)
    ReDim ifLose(100)
    Set myBetsRange = Worksheets("MyBets").Cells
    If currentMarket <> Cells(1, 1) Then initMarket
    pl = plRange
    currentMarket = Cells(1, 1)
    Do
        ' calculate win and lose positions
        If myBetsRange.Cells(r, 5).Value = "F" Then
            selecName = myBetsRange.Cells(r, 2).Value
            idx = getIndex(selecName)
            If idx <> -1 Then
                betRef = Cells(idx + 5, 20).Value
                stake = myBetsRange.Cells(r, 3).Value
                odds = myBetsRange.Cells(r, 4).Value
                If myBetsRange.Cells(r, 1).Value = betRef Then ' only include last bet made against this selection
                    If myBetsRange.Cells(r, 6).Value = "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
        End If
        r = r + 1
    Loop Until myBetsRange.Cells(r, 1).Value = ""
    ' calculate green up stakes
    For r = 5 To 50
        getIfWinLose Cells(r, 1).Value, win, lose
        If win = lose Then
            betType = ""
            stake = 0
            odds = 0
        End If
        If win > lose Then
            betType = "LAY"
            diff = win - lose
            odds = Cells(r, 8).Value
            If odds <> 0 Then
                stake = diff / odds
            Else
                stake = 0
            End If
        End If
        If win < lose Then
            betType = "BACK"
            diff = lose - win
            odds = Cells(r, 6).Value
            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
        Cells(r, 25).Value = betType
        Cells(r, 26).Value = stake
        Cells(r, 27).Value = odds
        calculateFuturePL r - 4, betType, stake, odds
    Next
    levelPLRange = pl
End Sub

Private Sub calculateFuturePL(r As Integer, betType As String, stake As Currency, odds As Currency)
    Dim i As Integer
    For i = 1 To UBound(pl)
        If betType = "BACK" Then
            If i = r Then
                pl(i, 1) = pl(i, 1) + (stake * (odds - 1))
            Else
                pl(i, 1) = pl(i, 1) - stake
            End If
        Else
            If i = r Then
                pl(i, 1) = pl(i, 1) - (stake * (odds - 1))
            Else
                pl(i, 1) = pl(i, 1) + stake
            End If
        End If
    Next
End Sub

Private Sub initMarket()
    Dim r As Integer
    Set selecIndex = New Collection
    r = 5
    Do
        r = r + 1
    Loop Until Cells(r, 1) = ""
    Set plRange = Range(Cells(5, 24), Cells(r - 1, 24))
    Set levelPLRange = Range(Cells(5, 28), Cells(100, 28))
    levelPLRange = ""
    Set levelPLRange = Range(Cells(5, 28), Cells(r - 1, 28))
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


Module1

Code: Select all

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


[brecki]

Hi Gary, that works well, but may be you didn´t reed my second post:

Besides it is important that the greening bet depends on the bet reference taken from the MyBets sheet and NOT from column T on Sheet 1, because the bet reference must be cleared by the CLEAR trigger before the greening bet can be triggered, otherwise it won´t be possible to trigger the greening bet as there are no other trigger cells than in the column Q.


Could you solve this for me ? Thx !

[GaryRussell]

Hi Gary, that works well, but may be you didn´t reed my second post:

Besides it is important that the greening bet depends on the bet reference taken from the MyBets sheet and NOT from column T on Sheet 1, because the bet reference must be cleared by the CLEAR trigger before the greening bet can be triggered, otherwise it won´t be possible to trigger the greening bet as there are no other trigger cells than in the column Q.


Could you solve this for me ? Thx !

You can create your own CLEAR trigger.

Change Worksheet_Change as follows.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
        calcGreenUp
        checkGreenUpTrigger
        Application.EnableEvents = True
    End If
End Sub


Insert the following Sub in sheet1.

Code: Select all

Private Sub checkGreenUpTrigger()
    Dim foundCell As Range, i As Integer, lastR As Integer
    Set foundCell = Range("Q4")
    Do
        lastR = foundCell.Row
        Set foundCell = Columns(17).Find(What:="GREENUP-CLEAR", After:=foundCell, _
                        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, MatchCase:=True)
        If foundCell Is Nothing Then Exit Do
        If foundCell.Row > lastR Then
            Cells(foundCell.Row, 29) = Cells(foundCell.Row, 26)
            Cells(foundCell.Row, 30) = Cells(foundCell.Row, 27)
            Cells(foundCell.Row, 20) = ""
        End If
    Loop Until foundCell.Row <= lastR
End Sub


You can then use "GREENUP-CLEAR" instead of "CLEAR". This will clear column T and at the same time copy the calculated green up stake & odds from columns Z and AA to AC and AD. You can then refer to columns AC and AD in your formula when triggering the green up bet.

[Shaun]

I am running this code but find it slugish on ny system, i have a clock on my excel but only refreshes every 2 seconds, the sheet the code should be on is that the sheet gruss connects to or the MyBets sheet.

[GaryRussell]

I am running this code but find it slugish on ny system, i have a clock on my excel but only refreshes every 2 seconds, the sheet the code should be on is that the sheet gruss connects to or the MyBets sheet.

It's all on sheet1, not MyBets. Shouldn't be that slow. Is there any other code you have added to it? If you could post up all your code we could have a look.

[brecki]

Hi Gary,

I am totally frustrated as my macro does more than it should. I worked on it for more than two weeks now and I can´t get it run correctly.

Could you please help me? It must have something to do with the worksheet change section but I can´t identify the problem.

May I send you my excel sheet so it would be much easier to understand what will / should happen ?

I will then give you additional information to the macros.

Please let me know where to sent, if you can/want to help me.

You are my last hope, I have no further ideas.

Thanks very much and best regards

Stefan

[GaryRussell]

If it's not too complicated, otherwise I usually ask that you put the request here http://gruss-software.co.uk/forum/viewforum.php?f=12

[brecki]

ok. I will post it there.