Excel formula to get Weekly Options Trade Symbol

To know more about the trade symbol format for various market segments, please refer this link.

Below excel formula can be used to get weekly options trade symbol.

=[SYMBOL]&TEXT([EXPIRY],"yy")&IF(MONTH([EXPIRY])=10,"O",IF(MONTH([EXPIRY])=11,"N",IF(MONTH([EXPIRY])=12,"D",MONTH([EXPIRY]))))&TEXT([EXPIRY],"dd")&[STRIKE]&[OPT]

 

Example:

A3 = BANKNIFTY
B3 = 21-Apr-2019
C3 = 25000

‘CE Options
D3 = A3&TEXT(B3,”yy”) &IF(MONTH(B3)=10,”O”,IF(MONTH(B3)=11,”N”,IF(MONTH(B3)=12,”D”,MONTH(B3)))) &TEXT(B3,”dd”)&C3&”CE”

‘PE Options
E3 = A3&TEXT(B3,”yy”) &IF(MONTH(B3)=10,”O”,IF(MONTH(B3)=11,”N”,IF(MONTH(B3)=12,”D”,MONTH(B3)))) &TEXT(B3,”dd”)&C3&”PE”

Download the excel sheet used in this article from here

Was this article helpful?

Related Articles

1 Comment

  1. Avatar siva reddy

    sir

    from nest Iam unable to connect to excel pls. guide me

Leave A Comment?