Tick value spreadsheet - Dallas

We were all new to Bet Angel once. Ask any question you like here and fellow forum members promise not to laugh. Betfair trading made simple.
Post Reply
User avatar
brimson25
Posts: 504
Joined: Sat Apr 08, 2017 11:42 am

Dallas - sorry to confess my inability to use the search function but -

I think you posted here a spreadsheet showing the different values of a tick at different places on the ladder.

I thought - that will be useful I must save that.

Then didn't.

And now I can't find it. (Or imagined the whole thing...?)

Please help, if you can! Cheers.
Top
User avatar
Dallas
Posts: 22713
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

I can't remember the thread myself but here's the sheet again
You do not have the required permissions to view the files attached to this post.
Top
User avatar
brimson25
Posts: 504
Joined: Sat Apr 08, 2017 11:42 am

Cheers. Formidably efficient as always!

Thanks again.
Top
User avatar
jimibt
Posts: 3658
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

brimson25 wrote: ↑
Tue Jan 29, 2019 2:49 pm
 Cheers. Formidably efficient as always!

Thanks again.
FYI - Spreadbetting did a nice little function library for calculating ticks in vba. i refactored it a little and if it would prove useful, i'll upload it also.
Top
User avatar
brimson25
Posts: 504
Joined: Sat Apr 08, 2017 11:42 am

Thanks very much - would be really interested in that.
Top
User avatar
jimibt
Posts: 3658
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

brimson25 wrote: ↑
Tue Jan 29, 2019 3:11 pm
 Thanks very much - would be really interested in that.
here you go (with thanks to SB):

Code: Select all

Option Explicit

Function getValidOdds(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.99
        oddsInc = 0.02
    Case 3 To 3.999
        oddsInc = 0.05
    Case 4 To 5.9999
        oddsInc = 0.1
    Case 6 To 9.9999
        oddsInc = 0.2
    Case 10 To 19.9999
        oddsInc = 0.5
    Case 20 To 29.99999
        oddsInc = 1
    Case 30 To 49.999
        oddsInc = 2
    Case 50 To 99.9999
        oddsInc = 5
    Case 100 To 1000
        oddsInc = 10
    End Select
    
    If Math.Round(odds + oddsInc, 2) <= 1000 Then
        getValidOdds = Round(odds / oddsInc, 0) * oddsInc
    Else
        getValidOdds = 1000
    End If
End Function

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

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, Optional ByVal asAbsolute As Boolean) 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
        If IsMissing(asAbsolute) Then
            getTicks = tickCount
        Else
            If asAbsolute = True Then
                getTicks = tickCount + 1
            Else
                getTicks = tickCount
            End If
        End If
        
    Case Is > odds2
        tickCount = 0
        i = odds1
        Do While i <> odds2
            thisStep = getOddsStepDown(i)
            i = i - thisStep
            tickCount = tickCount + 1
        Loop
        If IsMissing(asAbsolute) Then
            getTicks = tickCount - (tickCount * 2)
        Else
            If asAbsolute = True Then
                getTicks = tickCount - (tickCount * 2) + 1
            Else
                getTicks = tickCount - (tickCount * 2)
            End If
        End If
        
    Case Is = odds2
        getTicks = 0
    End Select
    
Xit:
End Function
example usage:

Code: Select all

    Debug.Print plusTicks(1.5, 50) (adds 50 ticks onto 1.5 odds) = 2.00
    Debug.Print minusTicks(7.5, 10) (subtracts 10 ticks from 7.5 odds)   = 6.1
    Debug.Print getNextOdds(3.5) (gets next step odds) = 3.55
    Debug.Print getPrevOdds(3.5) (gets previous step odds) = 3.45
    Debug.Print getTicks(3.5, 5.5) (get number of ticks between two odds prices) = 25
enjoy...
Top
User avatar
brimson25
Posts: 504
Joined: Sat Apr 08, 2017 11:42 am

Thank you!
Top
User avatar
brimson25
Posts: 504
Joined: Sat Apr 08, 2017 11:42 am

I just wanted to say, this is brilliant. Does exactly what I wanted. Cheers!
Top
User avatar
jimibt
Posts: 3658
Joined: Mon Nov 30, 2015 6:42 pm
Location: Narnia

brimson25 wrote: ↑
Fri Feb 01, 2019 8:49 am
 I just wanted to say, this is brilliant. Does exactly what I wanted. Cheers!
the tick spreadsheet, the vba code or both :D
Top
User avatar
brimson25
Posts: 504
Joined: Sat Apr 08, 2017 11:42 am

both! I grappled with the code - I had to google what VBA was, so ignorant am I, but got it sorted now.
Top
MartinJWilliams
Posts: 50
Joined: Tue May 10, 2022 3:10 pm

Hi,

I came across this post this afternoon and it looks just like the sort of thing I'm after to be able to calculate tick differences between two prices on some data I'm regularly using. I've no experience at all with VBA so have spent the last few hours watching youtube videos... and now have a very basic understanding.

The getTicks function looks perfect for what I need, calculating the tick difference between two columns in a spreadsheet but I can't seem to get the function to reference two columns. I'm guessing I may need to replace odds1 and odds2 with cell references but am struggling to find the right tutorial. Any pointers would be greatly appreciated.
Many thanks,
Martin
Top
MartinJWilliams
Posts: 50
Joined: Tue May 10, 2022 3:10 pm

jimibt wrote: ↑
Tue Jan 29, 2019 3:26 pm
brimson25 wrote: ↑
Tue Jan 29, 2019 3:11 pm
 Thanks very much - would be really interested in that.
here you go (with thanks to SB):

Code: Select all

Option Explicit

Function getValidOdds(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.99
        oddsInc = 0.02
    Case 3 To 3.999
        oddsInc = 0.05
    Case 4 To 5.9999
        oddsInc = 0.1
    Case 6 To 9.9999
        oddsInc = 0.2
    Case 10 To 19.9999
        oddsInc = 0.5
    Case 20 To 29.99999
        oddsInc = 1
    Case 30 To 49.999
        oddsInc = 2
    Case 50 To 99.9999
        oddsInc = 5
    Case 100 To 1000
        oddsInc = 10
    End Select
    
    If Math.Round(odds + oddsInc, 2) <= 1000 Then
        getValidOdds = Round(odds / oddsInc, 0) * oddsInc
    Else
        getValidOdds = 1000
    End If
End Function

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

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, Optional ByVal asAbsolute As Boolean) 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
        If IsMissing(asAbsolute) Then
            getTicks = tickCount
        Else
            If asAbsolute = True Then
                getTicks = tickCount + 1
            Else
                getTicks = tickCount
            End If
        End If
        
    Case Is > odds2
        tickCount = 0
        i = odds1
        Do While i <> odds2
            thisStep = getOddsStepDown(i)
            i = i - thisStep
            tickCount = tickCount + 1
        Loop
        If IsMissing(asAbsolute) Then
            getTicks = tickCount - (tickCount * 2)
        Else
            If asAbsolute = True Then
                getTicks = tickCount - (tickCount * 2) + 1
            Else
                getTicks = tickCount - (tickCount * 2)
            End If
        End If
        
    Case Is = odds2
        getTicks = 0
    End Select
    
Xit:
End Function
example usage:

Code: Select all

    Debug.Print plusTicks(1.5, 50) (adds 50 ticks onto 1.5 odds) = 2.00
    Debug.Print minusTicks(7.5, 10) (subtracts 10 ticks from 7.5 odds)   = 6.1
    Debug.Print getNextOdds(3.5) (gets next step odds) = 3.55
    Debug.Print getPrevOdds(3.5) (gets previous step odds) = 3.45
    Debug.Print getTicks(3.5, 5.5) (get number of ticks between two odds prices) = 25
enjoy...
Sorry, should have probably referenced this post in mine .
Top
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

You need to pass the cells individually rather than pointing to a whole column at once
This would work

Code: Select all

=getTicks(A1, B1)
This wouldn't

Code: Select all

=getTicks(A:A, B:B)
Top
MartinJWilliams
Posts: 50
Joined: Tue May 10, 2022 3:10 pm

Thanks, got that bit sorted. Stuck on this bit now... :lol:

viewtopic.php?f=59&t=28458
Top
Post Reply

Return to “Bet Angel for newbies / Getting started”