VBA tick handling functions

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

I've just re-written my VBA tick handling functions. The new function is more compact and works by calculating the number of ticks to the next crossover point. If the request exceeds this it subtracts that number of ticks and calls itself from the crossover point. Most calls won't cross more than one crossover point but even 1.01 > 1000 is fairly quick. The four functions are:-

TickRound(Price As Currency) As Currency - Rounds Price to the nearest tick, 1.01 or 1000 if out of range. Automatically called by the other functions.

TickAdd(Price As Currency, Ticks As Currency) As Currency - Adds Ticks number of ticks to Price, stopping at 1000.

TickSubtract(Price As Currency, Ticks As Currency) As Currency - Subtracts Ticks number of ticks from Price, stopping at 1.01.

TickAdjust(Price As Currency, Ticks As Currency) As Currency - Calls TickAdd or TickSubtract depending on polarity of Ticks.

Code: Select all

Public Function TickRound(ByVal curPrice As Currency) As Currency
'
' Round curPrice to nearest tick
'

    If curPrice < 2 Then
        TickRound = WorksheetFunction.Max(1.01, Round(curPrice, 2))
        Exit Function
    End If
    
    If curPrice < 3 Then
        TickRound = Round(curPrice * 50, 0) / 50
        Exit Function
    End If
    
    If curPrice < 4 Then
        TickRound = Round(curPrice * 20, 0) / 20
        Exit Function
    End If
    
    If curPrice < 6 Then
        TickRound = Round(curPrice, 1)
        Exit Function
    End If
    
    If curPrice < 10 Then
        TickRound = Round(curPrice * 5, 0) / 5
        Exit Function
    End If
    
    If curPrice < 20 Then
        TickRound = Round(curPrice * 2, 0) / 2
        Exit Function
    End If
    
    If curPrice < 30 Then
        TickRound = Round(curPrice, 0)
        Exit Function
    End If
    
    If curPrice < 50 Then
        TickRound = Round(curPrice / 2, 0) * 2
        Exit Function
    End If
    
    If curPrice < 100 Then
        TickRound = Round(curPrice / 5, 0) * 5
        Exit Function
    End If
    
    TickRound = WorksheetFunction.Min(1000, Round(curPrice / 10, 0) * 10)
End Function
Public Function TickAdd(ByVal curPrice As Currency, ByVal intTicks As Integer) As Currency
'
' Add intTicks ticks to curPrice
'

Dim bytTicksToCO As Byte 'ticks to the next crossover
Dim curTickInc As Currency 'tick increment
Dim curTickMarker As Currency 'current price
Dim intNextCO As Integer 'next crossover
Dim intTicksRem As Integer 'ticks remaining to be subtracted

    curTickMarker = TickRound(curPrice)
    
' Ignore invalid input
    If intTicks < 0 Then
        TickAdd = curPrice
        Exit Function
    End If

' Stop at 1000
    If curTickMarker = 1000 Then
        TickAdd = 1000
        Exit Function
    End If
    
' Count ticks to next crossover
    Select Case curTickMarker
    
        Case 1.01 To 1.99
            curTickInc = 0.01
            intNextCO = 2
            bytTicksToCO = (2 - curTickMarker) * 100
    
        Case 2 To 2.99
            curTickInc = 0.02
            intNextCO = 3
            bytTicksToCO = (3 - curTickMarker) * 50
    
        Case 3 To 3.95
            curTickInc = 0.05
            intNextCO = 4
            bytTicksToCO = (4 - curTickMarker) * 20
    
        Case 4 To 5.9
            curTickInc = 0.1
            intNextCO = 6
            bytTicksToCO = (6 - curTickMarker) * 10
    
        Case 6 To 9.8
            curTickInc = 0.2
            intNextCO = 10
            bytTicksToCO = (10 - curTickMarker) * 5
    
        Case 10 To 19.5
            curTickInc = 0.5
            intNextCO = 20
            bytTicksToCO = (20 - curTickMarker) * 2
    
        Case 20 To 29
            curTickInc = 1
            intNextCO = 30
            bytTicksToCO = 30 - curTickMarker
            
        Case 30 To 48
            curTickInc = 2
            intNextCO = 50
            bytTicksToCO = (50 - curTickMarker) / 2
            
        Case 50 To 95
            curTickInc = 5
            intNextCO = 100
            bytTicksToCO = (100 - curTickMarker) / 5
            
        Case Else
            curTickInc = 10
            intNextCO = 1000
            bytTicksToCO = (1000 - curTickMarker) / 10
    End Select

    If intTicks <= bytTicksToCO Then
        TickAdd = curTickMarker + intTicks * curTickInc
    Else
        TickAdd = TickAdd(intNextCO, intTicks - bytTicksToCO)
    End If
        
End Function
Public Function TickSubtract(ByVal curPrice As Currency, ByVal intTicks As Integer) As Currency
'
' Subtract intTicks ticks from curPrice
'

Dim bytTicksToCO As Byte 'ticks to the next crossover
Dim curTickInc As Currency 'tick increment
Dim curTickMarker As Currency 'current price
Dim intNextCO As Integer 'next crossover
Dim intTicksRem As Integer 'ticks remaining to be subtracted

    curTickMarker = TickRound(curPrice)
    
' Ignore invalid input
    If intTicks < 0 Then
        TickSubtract = curPrice
        Exit Function
    End If

' Stop at 1.01
    If curTickMarker = 1.01 Then
        TickSubtract = 1.01
        Exit Function
    End If
    
' Count ticks to next crossover
    Select Case curTickMarker
               
        Case 110 To 1000
            curTickInc = 10
            intNextCO = 100
            bytTicksToCO = (curTickMarker - 100) / 10
        Case 55 To 100
            curTickInc = 5
            intNextCO = 50
            bytTicksToCO = (curTickMarker - 50) / 5
        
        Case 32 To 50
            curTickInc = 2
            intNextCO = 30
            bytTicksToCO = (curTickMarker - 30) / 2
            
        Case 21 To 30
            curTickInc = 1
            intNextCO = 20
            bytTicksToCO = curTickMarker - 20
            
        Case 10.5 To 20
            curTickInc = 0.5
            intNextCO = 10
            bytTicksToCO = (curTickMarker - 10) * 2
    
        Case 6.2 To 10
            curTickInc = 0.2
            intNextCO = 6
            bytTicksToCO = (curTickMarker - 6) * 5
    
        Case 4.1 To 6
            curTickInc = 0.1
            intNextCO = 4
            bytTicksToCO = (curTickMarker - 4) * 10
    
        Case 3.05 To 4
            curTickInc = 0.05
            intNextCO = 3
            bytTicksToCO = (curTickMarker - 3) * 20
    
        Case 2.02 To 3
            curTickInc = 0.02
            intNextCO = 2
            bytTicksToCO = (curTickMarker - 2) * 50
    
        Case Else
            curTickInc = 0.01
            intNextCO = 1.01
            bytTicksToCO = (curTickMarker - 1.01) * 100
    
    End Select

    If intTicks <= bytTicksToCO Then
        TickSubtract = curTickMarker - intTicks * curTickInc
    Else
        TickSubtract = TickSubtract(intNextCO, intTicks - bytTicksToCO)
    End If
        
End Function
Public Function TickAdjust(ByVal curPrice As Currency, ByVal intTicks As Integer) As Currency
'
' Adjust curPrice by intTicks by calling TickAdd or TickSubtract depending on polarity of intTicks
'

    If intTicks = 0 Then TickAdjust = TickRound(curPrice)
    If intTicks > 0 Then TickAdjust = TickAdd(curPrice, intTicks)
    If intTicks < 0 Then TickAdjust = TickSubtract(curPrice, intTicks * -1)
End Function
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”