Free data from NSENOW to AmiBroker via Excel

[josh1]

Here I am going to explain how the whole thing works.

1. I will not explain how to get realtime data into into excel. That is explained adequately by Raju here-
http://www.traderji.com/intraday/60576-realtime_now_excel_amibroker.html

The exception in my case is that I am interested in FnO hence I want the following columns-
Symbol,Last Trade Time,Last Traded Price,Last Trade Qty,Option Type,Strike Price
The order of the columns in NSENOW is irrelevant. So just start NSENOW and get these columns.

2. We create a csv file(C:\MyCSV.csv) containing stock quotations from Sheet1 of the excel workbook. Just click on the Sheet called "Start".
The order of cloumns should be strictly the same as given in Sheet1. There is also one more column for date containing formula Today(). This is must in front of every share since AmiBroker requires date for importing quotations.
3. We create AmiBroker as an activeX object in excel VBA and refer to it.
4. We call AmiBroker and tell it to import the csv file we created.
5. AmiBroker imports the quotations from MyCSV.csv by referring to the NSENOW.format file that we put into its format folder.
6. We tell it to RefreshAll and it refreshes the charts.
7. This is repeatd automatically every 2 seconds with 9 scrips. I believe we can use this for upto 20 scrips easily. For more scrips, refresh interval has to be increased.
8. In AmiBroker Database Settings, we set the database as Local and Base Time interval as Tick.

We do not have to worry about naming of each and every cell in excel sheet.
This is much simpler than the DDE option isn't it?

This trick can be used to -
-import data from any other trading application that allows link to excel and get Realtime charts.
-We can also import backfil data with this

 

[josh1]

Here I am gonna explain the Excel Workbook.

There are three sheets in the workbook. Sheet1, Start and Stop.

First Sheet1 is used to capture data from NESNOW.
You can see that the Symbol column shows NIFTY in case of Nifty future as well as options. Hence I have added the columns Option Type and Strike Price to distinguish between them. Date column gives todays' date. These columns are used in the format file to import data into correct scrips. We will see that later.

You can replace the scrips by scrips of your own choice.Do not increase/decrease columns until you understand what you are doing If you want to observe more scrips, add them in NSENOW - Link to Excel - Paste into the Sheet1. Copy the formula in date column "Today()" in front of them.

The other two sheets are used to start and stop import of data into AmiBroker.
That is, Click on Start will activate the VB Macro and start pushing data into AmiBroker. Click on Stop will stop pushing.

 

[josh1]

Here I explain the NSENOW.format file. This file is to be kept in the format folder below your AmiBroker folder. It is used by AmiBroker to recognise the format of the MyCSV.csv file we generated.

Here are the contents of the file.
# Format definition file generated automatically
# by AmiBroker's ASCII Import Wizard
$FORMAT Ticker, Time, Close, Volume, Appendticker, Appendticker, Date_MDY
$SKIPLINES 1 This tells AmiBroker to ignore the first line in csv file
$SEPARATOR , This tells that we are using comma (,) as field seperator
$CONT 1
$GROUP 255
$AUTOADD 1 This tell to automatically add new scripts into its database
$DEBUG 1

Keep the other three lines as it is. Now let us see the $FORMAT line.
This line tell Amibroker the name of each field in which data is to be imported. Hence order of columns in excel sheet is very important.
The first four fields upto Volume are easy to understand. The next two fields "AppendTicker" tell Amibroker that the contents of these fields are to be concatenated with "Ticker" Field.
Thus the fields Symbol, Option Type and Strike Price will be combined to give us NIFTYCE5300 in case of Nifty Call of Strike price 5300

Explanation of other lines is available in AmiB

 

[josh1]

Here I explain the NSENOW.format file. This file is to be kept in the format folder below your AmiBroker folder. It is used by AmiBroker to recognise the format of the MyCSV.csv file we generated.

Here are the contents of the file.
# Format definition file generated automatically
# by AmiBroker's ASCII Import Wizard
$FORMAT Ticker, Time, Close, Volume, Appendticker, Appendticker, Date_MDY
$SKIPLINES 1 This tells AmiBroker to ignore the first line in csv file
$SEPARATOR , This tells that we are using comma (,) as field seperator
$CONT 1
$GROUP 255
$AUTOADD 1 This tell to automatically add new scripts into its database
$DEBUG 1

Now let us see the $FORMAT line. This line tell Amibroker the name of each field in which data is to be imported. Hence order of columns in excel sheet is very important.
The first four fields upto Volume are easy to understand. The next two fields "AppendTicker" tell Amibroker that the contents of these fields are to be concatenated with "Ticker" Field.
Thus the fields Symbol, Option Type and Strike Price will be combined to give us NIFTYCE5300 in case of Nifty Call of Strike price 5300

Explanation of other lines is available in AmiBroker's UserGuide

Below is snapshot of MyCSV.csv file generated
Symbol,Last Trade Time,Last Traded Price,Last Trade Qty,Option Type,Strike Price,Date,,,,
NIFTY,15:19:18,5223.5,50,,,4/24/2012,,,,
TATAMOTORS,15:19:18,311.85,50,,,4/24/2012,,,,
CIPLA,15:19:12,313.45,2,,,4/24/2012,,,,
CAIRN,15:19:14,342.45,7,,,4/24/2012,,,,
MINIFTY,15:19:18,5224.8,20,,,4/24/2012,,,,
NIFTY,15:19:19,9,50,CE,5300,4/24/2012,,,,
NIFTY,15:19:19,48.1,50,CE,5200,4/24/2012,,,,
NIFTY,15:19:19,26.45,100,PE,5200,4/24/2012,,,,
NIFTY,15:19:18,83.8,50,PE,5300,4/24/2012,,,,
,,,,,,,,,,
,,,,,,,,,,

 

[josh1]

Known Issues to be addressed asap.

1. I am not able to open another instance of excel or another workbook. The new worksheet becomes active workbook and the application hangs till it is closed.
2. I am not able to select database through excel to for updating. AmiBroker starts importing quotations in its default database. If two instances of AmiBroker are open, it probably imports into both or the one that was opened first.

 

[Raju]

Josh1,

Could the following be used so that quotes would get saved in specific DB.I am using such jsscript to backfill...Realtime.. ?

/* create AB object */
AB = new ActiveXObject("Broker.Application")
/* select data base */
AB.LoadDatabase("G:\\Databases\\RTDB")

Regards,
Raju

 

[josh1]

Josh1,

Could the following be used so that quotes would get saved in specific DB.I am using such jsscript to backfill...Realtime.. ?

/* create AB object */
AB = new ActiveXObject("Broker.Application")
/* select data base */
AB.LoadDatabase("G:\\Databases\\RTDB")

Regards
Raju

Yes. I am using the same but with single backslash "\" . You can open the worksheet and see the code. Let us check by using "\\" instead.

I was trying to set the databasepath property but I am not able to get the syntax correct.
/* select data base */
set AB.DatabasePath = ("G:\\Databases\\RTDB")

 

[sayantan2u9]

deleted post

 

[josh1]

Dear Raju,

I have read your thread on similar subject fully. You were stuck because you are using DDE Plugin. I started this different thread because I believe that the OLE Automation method is much better,simpler and we can use it for back fill of FnO data also.

I guess that you have read the chapter on OLE Automation in AmiBroker Reference Guide so I shall explain this further.

Currently I am using .csv file to update data in AmiBroker. I am overwriting the .csv file every 2 seconds. This is fine for may be upto 20 scrips. For more scrips, we will have to increase time interval to 3 seconds or may be 5,10,20 seconds depending upon number of scrips, bandwidth etc. This method of programming is primitive in my opinion since it involves read/write to disk every few seconds.

The command /* create AB object */
AB = new ActiveXObject("Broker.Application")
gives access to other objects below AmiBroker. Some of these are - Stocks-Stock-Quotations-Quotation.
If we are able to create a loop to pick all the quotations from excel Sheet1 (maybe into an array) every few seconds and then push it into these objects, we can do away with the .csv part. In that case, we will be able to update 200 or more scrips at a time. However, I find it difficult because I am not a programmer.

 

[Raju]

Dear Raju,

I have read your thread on similar subject fully. You were stuck because you are using DDE Plugin. I started this different thread because I believe that the OLE Automation method is much better,simpler and we can use it for back fill of FnO data also.

I guess that you have read the chapter on OLE Automation in AmiBroker Reference Guide so I shall explain this further.

Currently I am using .csv file to update data in AmiBroker. I am overwriting the .csv file every 2 seconds. This is fine for may be upto 20 scrips. For more scrips, we will have to increase time interval to 3 seconds or may be 5,10,20 seconds depending upon number of scrips, bandwidth etc. This method of programming is primitive in my opinion since it involves read/write to disk every few seconds.

The command /* create AB object */
AB = new ActiveXObject("Broker.Application")
gives access to other objects below AmiBroker. Some of these are - Stocks-Stock-Quotations-Quotation.
If we are able to create a loop to pick all the quotations from excel Sheet1 (maybe into an array) every few seconds and then push it into these objects, we can do away with the .csv part. In that case, we will be able to update 200 or more scrips at a time. However, I find it difficult because I am not a programmer.

Yes Josh1...even the mixing of issue gets solved when csv is used ..I found it late , with some help we should able to workout a soln for updating 55 scrips .Will let you know..Due to office timming am not able to fully concentrate ..

Regards,
Raju