Difference in prices as ticks in Excel

[doris_day]

I know about Gary's plus and minusTicks. Is there any way I can convert the following into tick values ? (PriceA-PriceB)

The answer is probably staring me in the face

[osknows]

There are 735 ticks in total. One way to do it is to list them in a column and use MATCH to find the row of each price. Then just minus one row from the other to find the tick difference.

[mak]

Probably I haven't understand right the question so my answer is wrong also

but there is a function GetTicks (don't remember right now the member - again probably was the Captain)

I am almost certain that you know about it
anyway here it is

Option Explicit

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



** Hi Os
Long time to "hear" from you
Hope you are fine

[osknows]

I'm wrong about 735 ticks. Not sure what I was thinking there?

I good thanks Mak!

[doris_day]

Thanks mak. That works fine....

[Captain Sensible]

Probably I haven't understand right the question so my answer is wrong also

but there is a function GetTicks (don't remember right now the member - again probably was the Captain)

I wish it was me mak, I thought it was either osknows or Gary.

I added a routine to convert a value to valid odds increment if needed

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

[mak]

Thanks Captain
very usefull

Doris there is deferences in tick increments between bf and bq

by the way i am writing with my new samsung galaxy s with android and itt is perfrect

sometimes it feels like laptop

highly recommended

[osknows]

It's not my code - most likely Gary's. It's also a lot faster than my earlier suggestion of using a list and MATCH

[dgs2001]

I'll own up it was an adaptation of some of Garys code which I originally posted here :-

http://gruss-software.co.uk/forum/viewt ... t=getticks

It has been tidied up since, but I'm still prepared to stand up and claim it

Duncan

[Captain Sensible]

:D

I'll own up it was an adaptation of some of Garys code which I originally posted here :-

http://gruss-software.co.uk/forum/viewt ... t=getticks

It has been tidied up since, but I'm still prepared to stand up and claim it

Duncan

Nice one Duncan, almost all of my bots use those snippets of code in some way or other these days