Make EOD Data file from Excel for all Cash, Future & option.....

[P.SUHAS T&I]

Thank you for this explanation Suhas.

I tried to follow the steps given for the NSE on the bhav copy downloaded from NSE. But somehow it is not working for me. It seems I have made some silly mistake somewhere.

Can anyone please take a look at the screen shots and the attached file, and tell me how could I do it correctly so that the symbol names gets changed according to the contract months, like ABAN-I, ABAN-II and ABAN-III .

I will be very thankful for any help in this regard.

Thanks a lot

If you paste wrong formula how it will give you correct result !!!
just see the formula which you paste in R column Roman(Q2) & not (R2)
Paste formula properly in R2 cell and copy to all below rows

whenever you are in doubt check formula thoroughly.

Suhas

 

[franktrader]

If you paste wrong formula how it will give you correct result !!!
just see the formula which you paste in R column Roman(Q2) & not (R2)
Paste formula properly in R2 cell and copy to all below rows

whenever you are in doubt check formula thoroughly.

Suhas

Thank you so much Suhas for pointing out my mistake.
When I made the change as you said, it is working great.

Thanks a lot for sharing this method with us.

 

[ManuAdam]

Myself is a paid subscriber for data. if you notice I have mention this in my 1st post,

This is one of the method to get data as your own & generate Bhavcopy for yourself

even though I am paid subscriber, Some time I have to wait & having data issues some time......

I have tested it and after only circulate, if you done whole thing carefully there is no question of error.

I didnt just paste formula, I actually disclose the logic behind it. if someone know the formula function he can understand it very well.

even I have not seen any convertor / software to get f&o,MCX,NCDEX bhavcopy free here, if any one know please share the link.
Regards
Suhas Patkar

Dear Sir, I am a complete noob in excel. You seem to be quite an Excel expert and very good in putting things together nicely. I am also using excel to get RIGHT security-wise EOD data to feed in my ninjatrader 7. I do it by importing Security wise data from http://www.nseindia.com/content/equities/eq_scriphistdata.htm . and then formatting them according to my need one by one. But that is time taking as I need to delete columns (not required for me): Symbol ; Series ; Prev. Close ; Last Price ; Average Price ; TurnOver in Lakhs ... After deleting these columns I format date column : YYYYmmdd and then save it as csv file.
I am wondering whether this can be done prgrammatically in Excel so that it fetches Security wise EOD data minus those columns which are not required and also format the date column in YYYYmmdd format automatically. I request you and other fellow members to Kindly Help.

 

[THETRADER]

Free EOD data downloader software (GOOD) for NSE Cash, NSE FNO and MCX is available from

www.volumedigger.com

1. This software downloads and puts it in a text file - apparently useable directly by Metastock downloader.
2. This software lets you specify the dates for which the data is to be downloaded. Which means you can even download historical data.
3. It is multithreaded, which allows you to download data simultaneously.
4. For FNO -I, -II, etc., are added for running contracts - You know what I mean.
5. This software is licenced till 2050!

Please make a small contribution if you like it to encourage them - It is not compulsory.

Pleae note that I am not connected with them in anyway.

 

[gupta_pramendra]

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

Thanks Suhas thanks a lot

 

[gannglobal]

repeat post............