Gruss Forum • View topic

by **doris_day** » Sat Oct 08, 2011 1:05 pm

Anyone got a routine to show the spread in ticks ? I know we have plusTicks and minusTicks but I'm not certain how to show the Back/Lay spread in terms of ticks and wondered if someone already had a routine for that. Thanks.

by **mak** » Sat Oct 08, 2011 2:50 pm

here Doris

http://www.gruss-software.co.uk/forum/v ... n+getticks

by **doris_day** » Sat Oct 08, 2011 2:57 pm

Thanks Mak. That does what I need.

Strange that didn't appear when I did a search.

by **doris_day** » Sat Oct 08, 2011 4:38 pm

Now that I've tried it, it would appear to have an issue when the two prices you're getting the spread in ticks for aren't within the same tick increment case. If its not in the same case (or the second number cannot be found from the first using the tick increment) then it goes into an endless loop.
I'm not good enough a coder yet to be able to solve this so any help would be appreciated.

by **Captain Sensible** » Sat Oct 08, 2011 11:49 pm

Not sure what you mean by "same tick increment case", Doris?

Only problem I think I came across was where one of the figures wasn't a valid increment and it then looped, so I just set up a separate routine to ensure I was using valid odds increments within the calc if say I'd some other formula calculating odds.

This is what's in my module and don't get any problems

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

by **doris_day** » Sun Oct 09, 2011 1:44 pm

Thanks Capn. I'll give it a whirl.

by **jrarmstrong** » Tue Jan 24, 2012 12:08 pm

A big thanks to everyone involved in putting this together. It is a big help

Captain Sensible wrote:Not sure what you mean by "same tick increment case", Doris?

Only problem I think I came across was where one of the figures wasn't a valid increment and it then looped, so I just set up a separate routine to ensure I was using valid odds increments within the calc if say I'd some other formula calculating odds.

This is what's in my module and don't get any problems

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

by **jrarmstrong** » Mon Jan 30, 2012 7:08 pm

Hi,

I have been playing about with the Captain's getTicks code this weekend but have run into a problem when I try to use the function in more than one formula in a workbook.

When I try to use it more than once the CPU goes crazy each time I hit enter after typing the formula. I then have to end Excel in task manager to stop it.

Is there something that can be done to stop this?

Thanks,

James

by **Captain Sensible** » Tue Jan 31, 2012 1:23 pm

The code was something Gary had done the only amendment I did was adding an error trapping routine getValidOdds to ensure it used a valid odds increment to start with otherwise it'd loop on me.

If you're pulling the odds from the data BA sends to excel you shouldn't have any problem as the odds are in valid increments for the ladder but if you calculate them yourself it can go into a loop if the odds are outside betfair's ladder increments.

I just use getValidOdds prior to sticking the values into getticks as most of my odds are calculated by formulas.

by **jrarmstrong** » Wed Feb 01, 2012 10:31 pm

Thanks Captain

Spread in ticks required for Excel

Spread in ticks required for Excel

Who is online

Sports betting software from Gruss Software