Help! my spreadsheet isn't triggering bets

Please post any questions regarding the program here.

Moderator: 2020vision

Help! my spreadsheet isn't triggering bets

Postby rhysw » Fri Aug 28, 2020 4:27 am

I have a simple strategy for scalping markets with only two selections (e.g. match odds) and I have tried to automate it using the triggers and some VBA code. I link the market to the excel sheet but nothing happens, no betting, not even an error just nothing. I have looked over my code several times, even took a day off just to look at it with fresh eyes but to no avail I can't get it to work.

Any suggestions?

Before anyone asks here it is
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)

Dim currentMarket As String
Dim marketChanged As Boolean
Dim FSB As Double
Dim FSL As Double
Dim SSB As Double
Dim SSL As Double
Dim stake As Double
Dim cancelbet As Double
stake = 5

    If Target.Columns.Count = 16 Then
   
   
        If Cells(1, 1).Value <> currentMarket Then
            currentMarket = Cells(1, 1).Value
            marketChanged = True
        Else
            marketChanged = False
        End If
        Application.EnableEvents = False
       
        If ba Is Nothing Then
            Set ba = New BettingAssistantCom.ComClass
            ba.includeNonRunners = False
            'ba.keepBetType = 0
            ba.refreshRate = 0.2
        End If 'ba isnothing
       
        If marketChanged = True Then
            FSB = -1 'first selection back price
            FSL = -1 'first selection lay price
            SSB = -1 'second selection back price
            SSL = -1 'second selection lay price
           
            Range("Q5", "ZZ100").Clear
           
           
        End If 'marketchanged
       
        Dim p As Byte, fav As Byte, n As Byte
        fav = 5
        p = 5
        Do While Cells(p, 1).Value <> ""
            If Cells(p, 6).Value < Cells(fav, 6).Value Then
                fav = p
            End If
            p = p + 1
        Loop
        Dim liquid As Boolean
        If Cells(fav, 8).Value < ticks(Cells(fav, 6).Value, 3) Then
            liquid = True
        Else
            liquid = False
        End If
        n = p - 4
       
        If n = 2 And Cells(1, 23).Value > 5 Then
            Dim bets As Variant, i As Integer
            Dim firstbackbet As Integer, firstlaybet As Integer
            Dim secbackbet As Integer, seclaybet As Integer
            bets = ba.getBets
            firstbackbet = -1
            firstlaybet = -1
            secbackbet = -1
            seclaybet = -1
           
            If Not IsEmpty(bets(0)) Then 'find unmatched bets
                For i = 0 To UBound(bets)
                    If bets(i).matched = False Then 'record bet index in bets and bet odds
                        If bets(i).selection = Cells(5, 1).Value And bets(i).bettype = "B" Then
                            If FSB < 1 Then
                                FSB = bets(i).odds
                            End If
                            If Not bets(i).odds / (bets(i).odds - 1) < Cells(6, 6).Value Or (bets(i).odds > Cells(5, 8).Value And Cells(5, 8).Value / (Cells(5, 8).Value - 1) < Cells(6, 6).Value) Then 'cancel bet
                                cancelbet = ba.cancelbet(bets(i).ref)
                                FSB = -1
                            End If
                            firstbackbet = i
                        End If 'first sel back
                        If bets(i).selection = Cells(6, 1).Value And bets(i).bettype = "L" Then
                            If SSL < 1 Then
                                SSL = bets(seclaybet).odds
                            End If
                            If Not bets(i).odds / (bets(i).odds - 1) > Cells(5, 8).Value Or (bets(i).odds < Cells(6, 6).Value And Cells(6, 6).Value / (Cells(6, 6).Value - 1) > Cells(5, 8).Value) Then
                                cancelbet = ba.cancelbet(bets(i).ref)
                                SSL = -1
                            End If
                            seclaybet = i
                        End If 'second sel lay
                        If bets(i).selection = Cells(5, 1).Value And bets(i).bettype = "L" Then
                            If FSL < 1 Then
                                FSL = bets(firstlaybet).odds
                            End If
                            If Not bets(i).odds / (bets(i).odds - 1) > Cells(6, 8).Value Or (bets(i).odds < Cells(5, 6).Value And Cells(5, 6).Value / (Cells(5, 6).Value - 1) > Cells(6, 8).Value) Then
                                cancelbet = ba.cancelbet(bets(i).ref)
                                FSL = -1
                            End If
                            firstlaybet = i
                        End If 'first sel lay
                        If bets(i).selection = Cells(6, 1).Value And bets(i).bettype = "B" Then
                            If SSB < 1 Then
                                SSB = bets(secbackbet).odds
                            End If
                            If Not bets(i).odds / (bets(i).odds - 1) < Cells(5, 6).Value Or (bets(i).odds > Cells(6, 8).Value And Cells(6, 8).Value / (Cells(6, 8).Value - 1) < Cells(5, 6).Value) Then
                                cancelbet = ba.cancelbet(bets(i).ref)
                                SSB = -1
                            End If
                            secbackbet = i
                        End If ' second sel back
                    End If 'bet unmatched
                Next i
            End If 'not isempty
       
            'place counter bet of any matched bet and exit sub
                'first selection back bet matched
                If FSB > 1 And firstbackbet = -1 Then 'select odds
                    Cells(6, 18).Value = 1.01
                    If FSB < Cells(6, 6).Value Then 'calculate stake
                        Cells(6, 19).Value = stake * Cells(6, 6).Value / FSB
                    Else
                        Cells(6, 19).Value = stake
                    End If 'firstbackbetodds < second sel back odds
                    Cells(6, 17).Value = "BACK" 'and place the bet
                    FSB = -1
                    Exit Sub
                End If 'FSB matched
               
                'first selection lay bet matched
                If FSL > 1 And firstlaybet = -1 Then
                    Cells(6, 18).Value = 1000
                    If FSL < Cells(6, 8).Value Then
                        Cells(6, 19).Value = stake * Cells(6, 8).Value / FSL
                    Else
                        Cells(6, 19).Value = stake
                    End If
                    Cells(6, 17).Value = "LAY"
                    FSL = -1
                    Exit Sub
                End If 'FSL matched
               
                'second selection back bet matched
                If SSB > 1 And secbackbet = -1 Then
                    Cells(5, 18).Value = 1.01
                    If SSB < Cells(5, 6).Value Then
                        Cells(5, 19).Value = stake * Cells(5, 6).Value / SSB
                    Else
                        Cells(5, 19).Value = stake
                    End If
                    Cells(5, 17).Value = "BACK"
                    SSB = -1
                    Exit Sub
                End If 'SSB matched
               
                'second selection lay bet matched
                If SSL > 1 And seclaybet = -1 Then
                    Cells(5, 18).Value = 1000
                    If SSL < Cells(5, 8).Value Then
                        Cells(5, 19).Value = stake * Cells(5, 8).Value / SSL
                    Else
                        Cells(5, 19).Value = stake
                    End If
                    Cells(5, 17).Value = "LAY"
                    SSL = -1
                    Exit Sub
                End If 'SSL matched
               
            'offer bets into the market
            If FSB = -1 And 1 / Cells(5, 8).Value + 1 / Cells(6, 6).Value < 1 Then
                FSB = 0
                Cells(5, 18).Value = Cells(5, 8).Value
                If Cells(5, 8).Value < Cells(6, 6).Value Then
                    Cells(5, 19).Value = stake * Cells(6, 6).Value / Cells(5, 8).Value
                Else
                    Cells(5, 19).Value = stake
                End If
                Cells(5, 17).Value = "BACK"
            End If
           
            If FSB = -1 And 1 / Cells(5, 10).Value + 1 / Cells(6, 6).Value < 1 Then
                FSB = 0
                Cells(5, 18).Value = Cells(5, 10).Value
                If Cells(5, 10).Value < Cells(6, 6).Value Then
                    Cells(5, 19).Value = stake * Cells(6, 6).Value / Cells(5, 10).Value
                Else
                    Cells(5, 19).Value = stake
                End If
                Cells(5, 17).Value = "BACK"
            End If
           
            If SSB = -1 And 1 / Cells(6, 8).Value + 1 / Cells(5, 6).Value < 1 Then
                SSB = 0
                Cells(6, 18).Value = Cells(6, 8).Value
                If Cells(6, 8).Value < Cells(5, 6).Value Then
                    Cells(6, 19).Value = stake * Cells(5, 6).Value / Cells(6, 8).Value
                Else
                    Cells(6, 19).Value = stake
                End If
                Cells(6, 17).Value = "BACK"
            End If
           
            If SSB = -1 And 1 / Cells(6, 10).Value + 1 / Cells(5, 6).Value < 1 Then
                SSB = 0
                Cells(6, 18).Value = Cells(6, 10).Value
                If Cells(6, 10).Value < Cells(5, 6).Value Then
                    Cells(6, 19).Value = stake * Cells(5, 6).Value / Cells(6, 10).Value
                Else
                    Cells(6, 19).Value = stake
                End If
                Cells(6, 17).Value = "BACK"
            End If
           
            If FSL = -1 And 1 / Cells(5, 6).Value + 1 / Cells(6, 8).Value > 1 Then
                FSL = 0
                Cells(5, 18).Value = Cells(5, 6).Value
                If Cells(5, 6).Value < Cells(6, 8).Value Then
                    Cells(5, 19).Value = stake * Cells(6, 8).Value / Cells(5, 6).Value
                Else
                    Cells(5, 19).Value = stake
                End If
                Cells(5, 17).Value = "LAY"
            End If
           
            If FSL = -1 And 1 / Cells(5, 4).Value + 1 / Cells(6, 8).Value > 1 Then
                FSL = 0
                Cells(5, 18).Value = Cells(5, 4).Value
                If Cells(5, 4).Value < Cells(6, 8).Value Then
                    Cells(5, 19).Value = stake * Cells(6, 8).Value / Cells(5, 4).Value
                Else
                    Cells(5, 19).Value = stake
                End If
                Cells(5, 17).Value = "LAY"
            End If
           
            If SSL = -1 And 1 / Cells(6, 6).Value + 1 / Cells(5, 8).Value > 1 Then
                SSL = 0
                Cells(5, 18).Value = Cells(6, 6).Value
                If Cells(6, 6).Value < Cells(5, 8).Value Then
                    Cells(6, 19).Value = stake * Cells(5, 8).Value / Cells(6, 6).Value
                Else
                    Cells(6, 19).Value = stake
                End If
                Cells(6, 17).Value = "LAY"
            End If
           
            If SSL = -1 And 1 / Cells(6, 4).Value + 1 / Cells(5, 8).Value > 1 Then
                SSL = 0
                Cells(5, 18).Value = Cells(6, 4).Value
                If Cells(6, 4).Value < Cells(5, 8).Value Then
                    Cells(6, 19).Value = stake * Cells(5, 8).Value / Cells(6, 4).Value
                Else
                    Cells(6, 19).Value = stake
                End If
                Cells(6, 17).Value = "LAY"
            End If
           
        End If 'n = 2, seconds until off > 5
       
        If Cells(1, 23).Value <= 5 Then
            Cells(5, 17).Value = "CLOSEC"
            Cells(6, 17).Value = "CLOSEC"
        End If
        Application.EnableEvents = True
    End If
End Sub
rhysw
 
Posts: 21
Joined: Tue Jun 30, 2020 12:48 pm

Re: Help! my spreadsheet isn't triggering bets

Postby alrodopial » Fri Aug 28, 2020 9:04 am

How about setting many breakpoints in your code and find out what is going on and when your macro exits?
Attachments
Screenshot_1.png
Screenshot_1.png (2.77 KiB) Viewed 5531 times
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Re: Help! my spreadsheet isn't triggering bets

Postby rhysw » Fri Aug 28, 2020 12:34 pm

I've never done that before as I'm a bit of a programming noob, but are you suggesting that I add breakpoints on every line that says "exit sub"?

I could change the order of the code to put the bits that say exit sub at the end so that the initial bet gets triggered before the sub is exited, but as far as I can tell the conditions that trigger the exit sub should be false before any bet is placed.
rhysw
 
Posts: 21
Joined: Tue Jun 30, 2020 12:48 pm

Re: Help! my spreadsheet isn't triggering bets

Postby alrodopial » Fri Aug 28, 2020 1:10 pm

no, I'm suggesting to place breakpoints in many lines while macros are running, while a bet is placed
and hovering the mouse over your variables you can see their values and check for example that this variable is zero and I want it to be 2-4 so that's why no bets are placed
You can also use
debug.Print "myvariable"
and the value will be written in the immediate window for further reading

breakpoints will do the job
alrodopial
 
Posts: 1353
Joined: Wed Dec 06, 2006 9:59 pm

Re: Help! my spreadsheet isn't triggering bets

Postby Captain Sensible » Fri Aug 28, 2020 1:14 pm

I usually just litter my code with Debug.Prints when writing new stuff just so I get some output into the intermediate window to check values are being populated correctly all throughout the code.

You can even load up a market and adjust, or remove, Target.Columns.Count = 16 just so you can test out your code before being logged in simply by changing values on your sheet. Even temporarily use Msgbox Cells(fav, 8).Value etc if it's easier to visualise what's going on especially as MsgBox will prompt you to carry on through the code a step at a time and you'll see which values are being added.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 27 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.