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

#1
Dear Friends

In my early days of TA, I used to make my EOD file for cash, by downloading file from NSE,BSE and make it into format say Ticker,name,Date, OHLCV.

This method is self rely method to get data, without have to depend on any one. Now I am paid subscriber for Data.

But still I believe for new TA entrants getting free data is much helpful, at least in initial days.

creating cash EOD file is easy, But Future & Option Eod file require some excel function to use to create Ticker & Name by considering its expiry date.

My question, Is here anyone display How to make EOD file for Future & option as their own, in excel only, without depending on anyone, by using some excel function...

for this I am doing some work in excel, and learning some function.........
But if someone already done this, please reply to me ...
 
#2
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
 
#3
Thanks. Appears pretty complicated and since we are dealing with numbers, a mistake or two can easily go un-noticed but finally gets noticed when your trade goes awry.

I believe that if a investor cannot even invest in a good data feed, he has no business being in the stock investment / trading business and is better off investing in Mutual Funds / ETF's.
 

umeshmandal

Well-Known Member
#4
Thanks. Appears pretty complicated and since we are dealing with numbers, a mistake or two can easily go un-noticed but finally gets noticed when your trade goes awry.

I believe that if a investor cannot even invest in a good data feed, he has no business being in the stock investment / trading business and is better off investing in Mutual Funds / ETF's.
May sound bitter to ppl who use data service but Buddy, they also use similar things to collate the data and vend it....
Once you can make something of your own and as it is said good "initially" , yes it is really helpful.
There are people arond who are just learning the ropes of TA and the wud do well by creating their own data base rather than paying "precious" money allocated to trading/investing as they hv started off with bare minimum capital to test the waters.... just my view for what it is worth.
 
#5
May sound bitter to ppl who use data service but Buddy, they also use similar things to collate the data and vend it....
Once you can make something of your own and as it is said good "initially" , yes it is really helpful.
There are people arond who are just learning the ropes of TA and the wud do well by creating their own data base rather than paying "precious" money allocated to trading/investing as they hv started off with bare minimum capital to test the waters.... just my view for what it is worth.
Am not disputing that, but question is if you have purchased a car, would you use Adulterated fuel to save a few bucks. Will you switch it off at every decline to save on fuel? Will you try to save on maintenance bills by servicing yourself?

As far as I know, answer is No. Then why try to save a few bucks in a area where one bad day costs more than what is saved over months or years. The question is, Are you better off spending your time building better systems to make money or spending time trying to get data for free.
 
#6
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
 
#7
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
 
#8
Dear Friends

In my early days of TA, I used to make my EOD file for cash, by downloading file from NSE,BSE and make it into format say Ticker,name,Date, OHLCV.

This method is self rely method to get data, without have to depend on any one. Now I am paid subscriber for Data.

But still I believe for new TA entrants getting free data is much helpful, at least in initial days.

creating cash EOD file is easy, But Future & Option Eod file require some excel function to use to create Ticker & Name by considering its expiry date.

My question, Is here anyone display How to make EOD file for Future & option as their own, in excel only, without depending on anyone, by using some excel function...

for this I am doing some work in excel, and learning some function.........
But if someone already done this, please reply to me ...
Hi,

Thnx for valuable info.

But if we want column like Value and Trades which are available from exchanges...How to update the same in Amibroker.

From value/volume we get avg.traded price of the day which is more useful then Weighted Avg.Price.
 
#9
bhattsr said:
Hi,

Thnx for valuable info.

But if we want column like Value and Trades which are available from exchanges...How to update the same in Amibroker.

From value/volume we get avg.traded price of the day which is more useful then Weighted Avg.Price.
No Idea .....
 
#10
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.

Suhas
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


Original FO bhav file

fo27DEC2010bhav.csv


Modified file --

fo27DEC2010bhav -- in Excel 97-2003 format.xls
 

Attachments

Similar threads