Can anyone share a number of ticks moved function?

[ShaunWhite]

I could write one, but I thought it was worth asking if anyone can paste a vba function in a reply, to count the number of ticks between 2 prices? .. unless I'm missing something blindingly obvious about how to do it.

eg FnTicksMoved(StartPrice,EndPrice,TicksMoved)

[jimibt]

shaun,

It's a tad more involved than straight subtraction/addition as you have cross over points where the odds jump from 0.01 units to 0.05/0.1 etc, etc.

In .net I coped with this in a simplistic way by using injected classes which dealt with the crossover ranges (i.e each class only knows about its crossover range). These classes formed a collection class that I iterated thro to determine the tick ratios between two numbers (whilst traversing the crossovers).

You'd have to do something similar (or not ) in vba.

I'm sure some of the excel folks must have crossed this bridge.

[ShaunWhite]

hehe,
" a simplistic way by using injected classes which dealt with the crossover ranges (i.e each class only knows about its crossover range). These classes formed a collection class that I iterated thro to determine the tick ratios between two numbers (whilst traversing the crossovers). "

I know I could code it in about 15mins so I should just stop being so lazy
A few loops inside and ouside of a few case selects should do it... not as structured as your work but it'll do the job,
i'll put the kettle on. I love a puzzle.

[nigelk]

If you have a list of betfair prices in your workbook you just need to subtract one row from another.

ie, if 2.10 is on row 247 and 2.02 on row 251, depending on what you need, the answer could be 3 (ticks between the two prices 2.04,2.06,2.08), or, if you include the 2 prices 5 ticks

[jimibt]

If you have a list of betfair prices in your workbook you just need to subtract one row from another.

ie, if 2.10 is on row 247 and 2.02 on row 251, depending on what you need, the answer could be 3 (ticks between the two prices 2.04,2.06,2.08), or, if you include the 2 prices 5 ticks

nigel, that's a perfect way to do it and in fact was going to suggest using a dictionary keyed on the price and just subtract the offsets indexes of where they are located -different means, same end

[ShaunWhite]

If you have a list of betfair prices in your workbook you just need to subtract one row from another.

ie, if 2.10 is on row 247 and 2.02 on row 251, depending on what you need, the answer could be 3 (ticks between the two prices 2.04,2.06,2.08), or, if you include the 2 prices 5 ticks

nigel, that's a perfect way to do it and in fact was going to suggest using a dictionary keyed on the price and just subtract the offsets indexes of where they are located -different means, same end

narrr, it's not perfect if you have to have data in a special place
I wanted something more portable.... I'm pretty sure this works, if you find an issue let me know.

It's untested on unexpected BF prices, like 3.23. Someone else will have to do a tweaker for non-std prices, or maybe I'll do that tomorrow. The code is a bit old skool, but so am I.

The attached file is not actually a .BAF (Sorry for the naughty file naming Mr Forum Admin, I couldn't post a file with a .bas extension.

[nigelk]

Just to clarify the question?

to count the number of ticks between 2 prices

or the price difference between tick sizes?

Cheers,Nigel.

[spreadbetting]

Here's a load of VBA tick functions - Just stick code below into a module.

getTicks will give you the number of ticks between two prices, the others should be obvious but ask if you need any help with them. getValidOdds with convert any umber to a valid price.

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) 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
 getTicks = tickCount
 
 Case Is > odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepDown(i)
    i = i - thisStep
    tickCount = tickCount + 1
    Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

Xit:
End Function

[ShaunWhite]

Brilliant, many thanks SB. That's saved me quite some time.

How did you get that code window into your posting?

[spreadbetting]

Above the text box where you can set text to bold italic etc it's the fifth choice

[ShaunWhite]

Thanks