Get Tick Difference

[dgs2001]

Hi All

I have been experimenting with Gary's PlusTick() MinusTick() code from another thread and wondered if anyone could help me convert it so that it reports the tick difference between two colomns.

Ie custom function

=TickDiff(AA1,AA2)

which returns the number of ticks difference (either negative, AA2 is lower, or positive AA2 is higher) between the prices in the two given cells.

Thanks Duncan

(OK Hi Gary as I'm guessing your the best one to ask!!)

[alrodopial]

Hi All

I have been experimenting with Gary's PlusTick() MinusTick() code from another thread

Can you give the exact link?

[dgs2001]

Yep
This should be it

http://www.gruss-software.co.uk/forum/v ... ight=ticks

Duncan

[dgs2001]

Hmm That didnt work.!
Try this-

http://www.gruss-software.co.uk/forum/v ... ight=ticks

Duncan

[alrodopial]

Thank you very much both Duncan and Garry.

[dgs2001]

Hi all

If anyone can make use of it I have finished messing with Garys code (Link Above) and created the custom function 'getTicks'

=getTicks(F5,H5)

In this instance the value returned would be the number of ticks between cell F5 and H5.

Just alter F5 and H5 to whatever cells you want to get the tick difference from.

Duncan

Code: Select all

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


[xraymitch]

=getTicks(F5,H5)

Cheers for this

I had created a lookup a table but yours and Garys code is so much better than my method !!

Nice one.


Many thanks

Ray

[bw]

I to had used Vlookup previously, thanks very much for sharing this

[danjuma]

Hi dgs2001,

I copied your code above into a module in a spreadsheet (not linked to BA, just a stand alone spreadsheet). However, when I ran it, it just gets stuck (continuous hour glass running and not responding). Any idea why this is so, or does it only work with a spreadsheet linked to BA? Obviously, I changed the cells in the "=getTicks()" formula to the relevant cells I wanted.

Cheers
Dan

[dgs2001]

Hi Dan

I wrote it to work with a sheet linked to BA.

The loop is probably caused by the starting never equaling the other price.

I.E

If the expression was getTicks(A1,A2) and cell A1 contained the number 33,cell A2 contained the number 40, a loop would occur because the odds increment at these prices would be 2 but counting up from 33 in twos never gets to exactly 40.

Hope that makes sense.

Ds

[dgs2001]

[quote]
The loop is probably caused by the starting never equaling the other price.
[quote/]

wheres the edit button!!

The loop is probably caused by the starting price not being an available betfair odds.

Ds

[danjuma]

Hi Dan

I wrote it to work with a sheet linked to BA.

The loop is probably caused by the starting never equaling the other price.

I.E

If the expression was getTicks(A1,A2) and cell A1 contained the number 33,cell A2 contained the number 40, a loop would occur because the odds increment at these prices would be 2 but counting up from 33 in twos never gets to exactly 40.

Hope that makes sense.

Ds

Alright, got cha!

Many thanks for the explanation. Cheers

[josthkko]

Can anyone help me i'm a newbie at VB and i cannot understand why whenever i type =getTicks(F5,H5) it says there is an error in the entered formula. I copied everything you pasted directly into the modules folder and it seems that excel recognises the function because when i start typing it automatically offers the function getTicks, but when i enter the data it says there is some kind of error in the entered formula. There were no other errors.
Does anyone have any idea why that could happen? Form the looks of it no one else has any problems.

Thank you!

[josthkko]

Don't need any more help... i just realised that you need to put ; instead of just , when declaring so.... Yeah, i'm a total newb

[mak]

Has anyone adapt this code - getticks - for betdaq odds and can provide it please. Tried a few things but can't get it right...