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.
Tick value spreadsheet - Dallas
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
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
-
- 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
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
-
- Posts: 50
- Joined: Tue May 10, 2022 3:10 pm
Sorry, should have probably referenced this post in mine .jimibt wrote: ↑Tue Jan 29, 2019 3:26 pmhere you go (with thanks to SB):
example usage: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
enjoy...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
You need to pass the cells individually rather than pointing to a whole column at once
This would work
This wouldn't
This would work
Code: Select all
=getTicks(A1, B1)
Code: Select all
=getTicks(A:A, B:B)
-
- Posts: 50
- Joined: Tue May 10, 2022 3:10 pm