User Defined Function (UDF) to get Trade Symbol for Options

Below UDF can be used to get trade symbol for weekly and monthly options. Click here to know more about Trade Symbol format for various segments.

Syntax: GetOptionTradeSymbol(Symbol,Strike,OptType,Expiry)

Example usage:
=GetOptionTradeSymbol(“BANKNIFTY”,26200,”CE”,”30-May-2019″) OR
=GetOptionTradeSymbol(“BANKNIFTY”,26200,”CE”,DATE(2019,5,30))


Public Function GetOptionTradeSymbol(ByVal Symbol As String, ByVal Strike As Double, ByVal OptType As String, ByVal Expiry As Date) As String
    On Error GoTo ErrHandler:
    'Symbol  = Symbol name of the option. Example NIFTY, BANKNIFTY, ACC etc
    'Strike = Strike price of the option. Example 12600, 26700, 205 etc
    'OptType  =  CE or PE

    If Symbol = "" Then GetOptionTradeSymbol = "NULLSYMBOL": Exit Function
    If Strike <= 0 Then GetOptionTradeSymbol = "NULLSTRIKE": Exit Function
    If OptType = "" Then GetOptionTradeSymbol = "NULLOPTTYPE": Exit Function

    Dim IntMonth As Integer
    Dim StrYear As String
    Dim StrMonth As String
    Dim StrDay As String

    Dim NextMonth As Date
    Dim IntNextMonth As Integer
    Dim IsWeeklyOption As Boolean

    IntMonth = Month(Expiry)
    StrYear = Format$(Expiry, "yy")
    StrMonth = UCase$(Format(Expiry, "mmm"))
    StrDay = Format$(Expiry, "dd")

    NextMonth = DateAdd("d", 7, Expiry)
    IntNextMonth = Month(NextMonth)
    IsWeeklyOption = (IntMonth = IntNextMonth)

    If IsWeeklyOption Then
        If IntMonth = 10 Then
            StrMonth = "O"
        ElseIf IntMonth = 11 Then
            StrMonth = "N"
        ElseIf IntMonth = 12 Then
            StrMonth = "D"
        Else
            StrMonth = UCase$(Format(Expiry, "m"))
        End If
        GetOptionTradeSymbol = Symbol & StrYear & StrMonth & StrDay & Strike & OptType
    Else
        GetOptionTradeSymbol = Symbol & StrYear & StrMonth & Strike & OptType
    End If
    Exit Function
ErrHandler:
    GetOptionTradeSymbol = "ERROR"
End Function

Was this article helpful?

Related Articles

Leave A Comment?