Free data from NSENOW to AmiBroker via Excel

[newbie12345]

Josh1 & Newbie12345

i mainly backfill commodity datas. in nest plus the interday data for 15 of commodities contains lot of zeroes in the o/h/l/c so nest plus is not suitable.

i take the backfill data from DIET ODIN - Market Movement and written a macro which does the necessary part like stripping unnecessary column, inserting today's date and import thru import wizard of Ami. the format file i use is the default 1 supplied with the utility.

if the backfill data are clean then nowbackfill.xls is the best choice.

if there is a wayso that i get clean data from nest plus i am with nowbackfill.xlm

sr114

Hi sr114,
Thanks for your quick reply.
Well, I was going through the backfill data in NEST and I noticed it fills zeroes in the O/H/L columns of the minute record if the price has remained unchanged during that minute. I might have to write a script that replaces the zeroes with the open/close price for that particular minute and import the same data manually. Btw, I could get a whole months data for Crude in NEST from Zerodha.

 

[sr114]

Newbie12345

the problem is that if there are zeroes in the data, of which commodity may be the chart gets distorted. so it is better to concentrate on the clean data.

script for over riding zeroes are ok but actually we don't know the price range at that time in that commodity. thiese happens mainly in mini lots otherwise crude, gold silver nat gas are ok.

thats why i prefer this route

sr

 

[newbie12345]

Newbie12345

the problem is that if there are zeroes in the data, of which commodity may be the chart gets distorted. so it is better to concentrate on the clean data.

script for over riding zeroes are ok but actually we don't know the price range at that time in that commodity. thiese happens mainly in mini lots otherwise crude, gold silver nat gas are ok.

thats why i prefer this route

sr

Am talking about Crude June Futures. I manually edited an entire months data yesterday and it took me a good 15 minutes to do just that. What I noticed was, the zeroes were in the price columns though the volumes column showed some traded quantity which could possibly mean that trades did take place but either at the Open price or at the Close price, hence leaving the other columns with zeroes. Now, this bug has to be fixed by Omnesys or we have to find a jugaad for the same. Am fine with the same OHLC price as the one minute candle will show a dash instead of an Eiffel tower from Zero to 4990 and mess up the whole chart.

 

[josh1]

Hi Josh1, am pasting the first few lines from the automatically generated backfill for Cipla -

CIPLA-EQ 14-05-2012 09:16:00 14-05-2012 09:16:00 318 318.7 317.1 317.5 4675
CIPLA-EQ 14-05-2012 09:17:00 14-05-2012 09:17:00 317.5 317.75 317.1 317.1 3511
CIPLA-EQ 14-05-2012 09:18:00 14-05-2012 09:18:00 317.1 317.45 317 317.2 2384
CIPLA-EQ 14-05-2012 09:19:00 14-05-2012 09:19:00 317.2 317.2 316.3 316.5 2933

Can you look at the data and tell where I might possibly be going wrong?

Much thanks!

Firstly, there should be a comma (,) between each field.
Secondly, date is not splitting but it is getting copied in right column.
Third, Date is in DMY format Nest.format file requires Date_MDY format. Change it to DATE_DMY.

Open the file in notepad and check first. You seem to be opening it in excel.

 

[sr114]

Niwbie12345

why so bothered about manually editing the OHLCV data when backfilling. we mainly do backfill if our system get halted due to some reason. at that time it is better to get the clean data and backfill it and concentrate on trading.

manually editing is ok in the weekends when we are not trading and doing the house cleaning jobs. so it it better for me to go via this route. also i track a smaal list of scrips so it is not so time comsuming for me.

sr

 

[newbie12345]

Firstly, there should be a comma (,) between each field.
Secondly, date is not splitting but it is getting copied in right column.
Third, Date is in DMY format Nest.format file requires Date_MDY format. Change it to DATE_DMY.

Open the file in notepad and check first. You seem to be opening it in excel.

This is what it looks like when I opened it in Notepad++.
CIPLA-EQ,14-05-2012 09:16:00,14-05-2012 09:16:00,318,318.7,317.1,317.5,4675
CIPLA-EQ,14-05-2012 09:17:00,14-05-2012 09:17:00,317.5,317.75,317.1,317.1,3511
CIPLA-EQ,14-05-2012 09:18:00,14-05-2012 09:18:00,317.1,317.45,317,317.2,2384
CIPLA-EQ,14-05-2012 09:19:00,14-05-2012 09:19:00,317.2,317.2,316.3,316.5,2933
CIPLA-EQ,14-05-2012 09:20:00,14-05-2012 09:20:00,316.5,316.5,315.95,316.5,2099

Exactly. Date is getting copied twice. I don't know why.

Should I use Nest.format or NSENOW2.format for importing data from NOW to Amibroker?

 

[josh1]

This is what it looks like when I opened it in Notepad++.
CIPLA-EQ,14-05-2012 09:16:00,14-05-2012 09:16:00,318,318.7,317.1,317.5,4675
CIPLA-EQ,14-05-2012 09:17:00,14-05-2012 09:17:00,317.5,317.75,317.1,317.1,3511
CIPLA-EQ,14-05-2012 09:18:00,14-05-2012 09:18:00,317.1,317.45,317,317.2,2384
CIPLA-EQ,14-05-2012 09:19:00,14-05-2012 09:19:00,317.2,317.2,316.3,316.5,2933
CIPLA-EQ,14-05-2012 09:20:00,14-05-2012 09:20:00,316.5,316.5,315.95,316.5,2099

Exactly. Date is getting copied twice. I don't know why.

Should I use Nest.format or NSENOW2.format for importing data from NOW to Amibroker?

Use Nest.format. In Developer tab click Record Macro. Click yes. Do the splitting manually. Stop recording. Check the macro generated and replace the macro given in NOWBackfil.xlsm.

 

[josh1]

Newbie12345

the problem is that if there are zeroes in the data, of which commodity may be the chart gets distorted. so it is better to concentrate on the clean data.

script for over riding zeroes are ok but actually we don't know the price range at that time in that commodity. thiese happens mainly in mini lots otherwise crude, gold silver nat gas are ok.

thats why i prefer this route

sr

Columns C, D, E and F will contain prices O,H,L,C in that order. Try somthing like this. I have not tested it. Place this just above the splitting code.


Dim High as variant, Low as Variant, i as integer, c as integer

For i = 1 To .Range("A65536").End(xlUp).Row

If Cells(i, "C").Value >= Cells(i, "F") then
High = Cells(i, "C").Value
Low = Cells(i, "F").value
else
High = Cells(i, "F").Value
Low = Cells(i, "C").value
End If

If Cells(i, "D").Value = 0 Then
Cells(i, "D") = High
End If

If Cells(i, "E").Value = 0 Then
Cells(i, "E") = Low
End If
Next i

 

[newbie12345]

Dear Josh1,
Thank you for your patience. What are the changes one has to make to NSE-Now-RT2.xls for it to work with NEST commodities platform provided by Zerodha? Any help in this regard would be immensely appreciated.
I have a solution to the backfill issue as well as cleaning the zeros in the OHLC one minute tick data. Only issue is its a command line tool and is still quite crude. I will fine tune it and post it over the weekend.

 

[sansarva]

Hi Josh1, am pasting the first few lines from the automatically generated backfill for Cipla -

CIPLA-EQ 14-05-2012 09:16:00 14-05-2012 09:16:00 318 318.7 317.1 317.5 4675
CIPLA-EQ 14-05-2012 09:17:00 14-05-2012 09:17:00 317.5 317.75 317.1 317.1 3511
CIPLA-EQ 14-05-2012 09:18:00 14-05-2012 09:18:00 317.1 317.45 317 317.2 2384
CIPLA-EQ 14-05-2012 09:19:00 14-05-2012 09:19:00 317.2 317.2 316.3 316.5 2933

Can you look at the data and tell where I might possibly be going wrong?

Much thanks!

DO a simple test and revert back,
after you paste the data from nest data table in backfill.xlms file in the sheet one ,widen the datetime column and see if it is left aligned or right aligned.

It should be right aligned. If it is left aligned, you will get the above problem

The requirement is the date format in the nest data table should match, the date format in nest format file, the now format file, the macro code for generating CSV file in backfill. xlms and your computer system OS setting.

What happens is the movement you paste the data from the nest data table into the backfill.xlms, the excell checks the data format of the data in the column with computer system data.

for example if the nest data is in the form of dd-mm-yyyy and and your computer regional setting is mm-dd-yyyy

the data CIPLA-EQ 14-05-2012 09:16:00 14-05-2012 09:16:00 318 318.7 317.1 317.5 4675

14-05-2012 , is not interpreted as number (ie. date) but as general format or text,

Excel by default aligns general format left and number format as right.

when macro is run when you click on the backfill sheet in backfill. xlms, the spliting take place.

Actually how does the macro split, it simply inserts a duplicate column, the first column it formats as date the second as time (both column contain the same data).

In your case the duplication of data takes place but the formatting of first data as date and other as time did not occur because excel failed in the first place to recognized the data as a number ( it just recognized it as text)

Now that you hopefully understood the working, please change you computer date setting to the one I explained in an earlier post, later check all the date format in nest format files and in macro

Regards