Traded range calculation

[jamesg46]

Wonder if one of you far more intelligent people would help me with a math problem.

How do I calculate in excel the traded range? I have the avg s.p the avg high & the avg low but i dont have the required brain to solve this. Thanks in advance.

[jamesg46]

Thinking about this, I've somehow confused myself & I now dont know what I'm actually asking for.

[CallumPerry]

If you simply want the range, do a look up for which of the 350 odds the high and low odds equal, for example 1.01 is '1' and 1.05 is '5', 5 - 1 = 4, you have a range of four ticks.

Otherwise if you just care about a general range value, again do the average high price subtract the average low price and analyse whether capturing this helps with some of your decision making?

[ruthlessimon]

This kinda thing?

C3: Avg low
D3: Avg high

C4 & D4 round it to the nearest tick

E3 is the range size in ticks

[Derek27]

I've dug up some old code I haven't used for a couple of years that convert prices to tick numbers and vice versa. I don't know if it's quicker than a lookup table or perhaps I wasn't aware of the lookup commands when I wrote it. The error message variables will need editing.

?PriceToTickNum(1.01)
1
?PriceToTickNum(1000)
350
?TickNumToPrice(133)
2.66
?PriceToTickNum(6.6) - PriceToTickNum(5.7)
6

Code: Select all

Function PriceToTickNum(sngPrice As Single) As Integer

Dim sngTickNum As Single

On Error GoTo PriceToTickNum_Error

sngTickNum = 0

Select Case sngPrice

    Case Is < 1.01
        DisplayError (strErrorMsg01)
        GoTo PriceToTickNum_Exit

    Case Is < 2
        sngTickNum = (sngPrice - 1) * 100

    Case Is < 3
        sngTickNum = 100 + (sngPrice - 2) * 50

    Case Is < 4
        sngTickNum = 150 + (sngPrice - 3) * 20

    Case Is < 6
        sngTickNum = 170 + (sngPrice - 4) * 10

    Case Is < 10
        sngTickNum = 190 + (sngPrice - 6) * 5

    Case Is < 20
        sngTickNum = 210 + (sngPrice - 10) * 2

    Case Is < 30
        sngTickNum = 230 + (sngPrice - 20)

    Case Is < 50
        sngTickNum = 240 + (sngPrice - 30) / 2

    Case Is < 100
        sngTickNum = 250 + (sngPrice - 50) / 5

    Case Is <= 1000
        sngTickNum = 260 + (sngPrice - 100) / 10
        
    Case Is > 1000
        DisplayError (strErrorMsg01)
End Select

    sngTickNum = Round(sngTickNum, 2)
    If sngTickNum <> Int(sngTickNum) Then
        DisplayError (strErrorMsg01)
        sngTickNum = 0
        GoTo PriceToTickNum_Exit
    End If
    
PriceToTickNum_Exit:
    PriceToTickNum = sngTickNum
    Exit Function

PriceToTickNum_Error:
    MsgBox Err.Description
    Resume PriceToTickNum_Exit
End Function
Function TickNumToPrice(intTickNum As Integer) As Single

Dim sngPrice As Single

On Error GoTo TickNumToPrice_Error

sngPrice = 0

Select Case intTickNum

    Case Is < 1
        DisplayError (strErrorMsg02)
        GoTo TickNumToPrice_Exit

    Case Is < 100
        sngPrice = 1 + intTickNum * 0.01

    Case Is < 150
        sngPrice = 2 + (intTickNum - 100) * 0.02

    Case Is < 170
        sngPrice = 3 + (intTickNum - 150) * 0.05

    Case Is < 190
        sngPrice = 4 + (intTickNum - 170) * 0.1

    Case Is < 210
        sngPrice = 6 + (intTickNum - 190) * 0.2

    Case Is < 230
        sngPrice = 10 + (intTickNum - 210) * 0.5

    Case Is < 240
        sngPrice = 20 + intTickNum - 230

    Case Is < 250
        sngPrice = 30 + (intTickNum - 240) * 2

    Case Is < 260
        sngPrice = 50 + (intTickNum - 250) * 5

    Case Is <= 350
        sngPrice = 100 + (intTickNum - 260) * 10
        
    Case Is > 350
        DisplayError (strErrorMsg01)
End Select
    
TickNumToPrice_Exit:
    TickNumToPrice = sngPrice
    Exit Function

TickNumToPrice_Error:
    MsgBox Err.Description
    Resume TickNumToPrice_Exit
End Function

[ShaunWhite]

Simlar to Dereks,

=TickFnAdd(price,ticks) to add ticks
=TickFnMinus(price,ticks) to deduct ticks
=TickFnValid(price) to returns nearest valid price (eg if you are adding a %age etc)
=TickFnDiff(price1,price2) to count ticks between prices

Stick in a module attached to your workbook to use the short syntax above (will need doing in each workbook you use)
OR put it in a module attached to personal.xlsb and refer to it as eg =personal.xlsb!TickFnDiff(price1,price2)

This code is pretty ancient and i think originated from nigel before a few tweaks.

Sorry about the horrible lack of indenting in some parts but i couldn't be bothered to tidy it up at this time of day.

TickFnAdd and TickFnMinus should really be combined into something like TickFnOffset and it would handle either a +ve or -ve offset.

Code: Select all

Public Function TickFnAdd(odds As Currency, ticks As Integer) As Currency
    odds = TickFnValid(odds)
    For i = 1 To ticks
        odds = getNextOdds(odds)
    Next
    TickFnAdd = odds
End Function

Public Function TickFnMinus(odds As Currency, ticks As Integer) As Currency
    
    odds = TickFnValid(odds)
    For i = 1 To ticks
        odds = getPrevOdds(odds)
    Next
     TickFnMinus = odds
End Function


Public Function TickFnDiff(odds1 As Currency, odds2 As Currency) As Single
    Dim i As Double
    odds1 = TickFnValid(odds1)
    odds2 = TickFnValid(odds2)
    
    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
           DoEvents
        Loop
         TickFnDiff = tickCount
     
     Case Is > odds2
        tickCount = 0
        i = odds1
        Do While i <> odds2
            thisStep = getOddsStepDown(i)
            i = i - thisStep
            tickCount = tickCount + 1
        Loop
        TickFnDiff = tickCount - (tickCount * 2)
    
    Case Is = odds2
        TickFnDiff = 0
        
    End Select
    
Xit:
End Function
Public Function TickFnValid(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
     TickFnValid = Round(odds / oddsInc, 0) * oddsInc
    Else
     TickFnValid = 1000
    End If
End Function

Public 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

Public 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

Public Function getOddsStepUp(ByVal odds As Currency) 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

Public Function getOddsStepDown(ByVal odds As Currency) 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 = T
    Case 55 To 100
    oddsInc = 5
    Case 110 To 1000
    oddsInc = 10
    End Select
    
    getOddsStepDown = oddsInc
End Function

[ShaunWhite]

The error message variables will need editing.

Error trapping is for wimps

[sa7med]

I'm not a 100% on this but I'm pretty sure using a lookup is faster.

[ShaunWhite]

I'm not a 100% on this but I'm pretty sure using a lookup is faster.

Faster maybe but messier? But like all excel/coding there's always more than one way to do it so you takes your pick. No doubt someone with time on their hands will setup a vlookup vs function drag race.... Simon maybe?

[jamesg46]

Oh wow, thanks guys, I never noticed all the replies on this thread.

[jamesg46]

This kinda thing?

C3: Avg low
D3: Avg high

C4 & D4 round it to the nearest tick

E3 is the range size in ticks

Perfect! Thanks Simon

[Derek27]

I'm not a 100% on this but I'm pretty sure using a lookup is faster.

Faster maybe but messier? But like all excel/coding there's always more than one way to do it so you takes your pick. No doubt someone with time on their hands will setup a vlookup vs function drag race.... Simon maybe?

You get some surprising results when doing VBA speed tests. I once spent hours rewriting code to remove references to cells in a massively used loop. It was faster, but only just, which made me wish I hadn't bothered.

[ShaunWhite]

Unless something is a dog then speed is a poor second to being robust, reliable and easy to maintain. No point saving a min if you have to spend longer wondering why massive vlookup/match/index formulas all over the place aren't working as they should. Everyone uses Average & Sum etc so making common tick manipulation just as easy seems like a no brainer, especially compared to typing a 40 character statement to do the same thing.... yet again.

[alonzoharris]

This kinda thing?

C3: Avg low
D3: Avg high

C4 & D4 round it to the nearest tick

E3 is the range size in ticks

Thanks, do you have one where it shows the profit/loss with amount staked?

[ruthlessimon]

Thanks, do you have one where it shows the profit/loss with amount staked?

G3: Back stake
H3: Back entry price
I3: Lay exit price (hedge)

L3: Lay stake
M3: Lay entry price
N3: Back exit price (hedge)

Hth