FOR CURRENCY - OPTION

Presently option traded only in USD-INR, But in near future it will start in others.

Being NSE provide all in one column A say ticker-date-ce/pe-strike, so we need to separate all these in different column. Say in column O,P,Q,R as Ticker,date,StrikePrice,CE/PE. Respectively so copy following formula in

O2 :

=TRIM(MID($A2,1,12))

P2 :

=TRIM(MID($A2,13,11))

Q2 :

=TRIM(MID($A2,26,10))

R2 :

=TRIM(MID($A2,24,2))

copied down for all rows.

Now sort all data of Bhavcopy in ascending order of 1st preference O column & 2nd P Column from Data>Sort>select Colum O & P

Select – sort anything that looks like a number, as a number.

We create Ticker

1) By using following formula in the cell S2 (copy –paste following formula)

=IF($O2&"@"&$P2=$O1&"@"&$P1,$S1,1+$S1*($O2=$O1))

copied down for all rows

2) Next in cell T2 (copy –paste following formula)

=$O2&"-"&ROMAN($S2)&REPT("-"&$Q2&"-"&$R2,1)

copied down for all rows

Here O = Ticker information column

P = Expiry Date

Your Ticker is created in column T

You can use this whole column copy – paste special – Values to other ExcelSheet and take other figures Like O H L C V OI etc to the corresponding columns.

FOR CURRENCY – FUTURE

Being NSE provide all in one column A say ticker-date, so we need to separate these in 2 different column. Say in column O,P as Ticker,date respectively so copy following formula in

O2 :

=TRIM(MID($A2,1,12))

P2 :

=TRIM(MID($A2,13,11))

copied down for all rows.

Now sort all data of Bhavcopy in ascending order of 1st preference O column & 2nd P Column from Data>Sort>select Colum O & P

Select – sort anything that looks like a number, as a number.

We create Ticker

1) By using following formula in the cell S2 (copy –paste following formula)

=IF($O2&"@"&$P2=$O1&"@"&$P1,$S1,1+$S1*($O2=$O1))

copied down for all rows

2) Next in cell T2 (copy –paste following formula)

=$O2&"-"&ROMAN($S2)

copied down for all rows

Here O = Ticker information column

P = Expiry Date

Your Ticker is created in column T

You can use this whole column copy – paste special – Values to other ExcelSheet and take other figures Like O H L C V OI etc to the corresponding columns.

Regards

Suhas