Dear All
Here is how you can Make on your own EOD data file (BHAVCOPY) from authenticate sources i.e. directly from exchange.
Download each Day Bhavcopy from exchange site.
we Need data in csv file and format is as follows
Ticker,Name,Date,Open,High,Low,Close,Volume,OpenInterest
In case of F&O of NSE , MCX & NCDEX the above format getting all data except Ticker is easy, because all other data is given in separate Column
In my opinion Name is not important for Future & Option if you Know the Meaning of Ticker and its Contract information.
Method to create Ticker Name for Future & option in case of MCX, NCDEX, NSE is
near month expiry we end ticker with Roman No. -I
next Month expity we end ticker with Roman No. -II
then –III then -IV.........so on till various contracts available with different Expiry Date.
i.e. the Logic is we add Roman No. on the basis of Expiry Date.
In all Bhavcopy of NSE F&O, MCX & NCDEX, all contract which are available to trade are mention in Bhavcopy, even though contract is not traded on particular day, the information is given with OHL values as Zero.
For NSE, :
1st sort all data of Bhavcopy in ascending order of 1st preference B column & 2nd C Column from Data>Sort>select Colum B & C
We create Ticker
1) By using following formula in the cell Q2 (copy –paste following formula)
=IF($B2&"@"&$C2=$B1&"@"&$C1,$Q1,1+$Q1*($B2=$B1))
copied down for all rows
2) Next in cell R2 (copy –paste following formula)
=TRIM($B2)&"-"&ROMAN($Q2)&REPT("-"&$D2&" "&$E2,OR($A2="OPTIDX",$A2="OPTSTK"))
copied down for all rows
Here B = Ticker information column
C = Expiry Date
Your Ticker is created in column R
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 MCX :
1st sort all data of Bhavcopy in ascending order of 1st preference B column & 2nd C Column from Data>Sort>select Colum B & C
We create Ticker
1) By using following formula in the cell N2 (copy –paste following formula)
=IF($B2&"@"&$C2=$B1&"@"&$C1,$N1,1+$N1*($B2=$B1))
copied down for all rows
2) Next in cell O2 (copy –paste following formula)
=TRIM($B2)&"-"&ROMAN($N2)
copied down for all rows
Here B = Ticker information column
C = Expiry Date
Your Ticker is created in column O
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 NCDEX :
1st sort all data of Bhavcopy in ascending order of 1st preference A column & 2nd B Column from Data>Sort>select Colum A & B
We create Ticker
1) By using following formula in the cell Q2 (copy –paste following formula)
=IF($A2&"@"&$B2=$A1&"@"&$B1,$Q1,1+$Q1*($A2=$A1))
copied down for all rows
2) Next in cell R2 (copy –paste following formula)
=TRIM($A2)&"-"&ROMAN($Q2)
copied down for all rows
Here A = Ticker information column
B = Expiry Date
Your Ticker is created in column R
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.
Now you can create Your Bhavcopy without depending on anyone.
Regards
Suhas