Dear K S Kachhwaha,
I trade daily using PIB quotes imported into Amibroker. Like i mentioned in my earlier mail i have not faced too much of delay in updating charts in AMI. So obviously PIB is writting to its folder quickly. I do not know if it has to do anything with net connection or system configurations. Anyway for your information i use broadband (256Kbps), a lap top celeron with 756 MB RAM, Windows Xp ( updated to the latest) , and of course for PIB i am using the latest version of Java software from sun micro systems. i can give you a link for downloading latest code for importing data into Amibroker released by AMI support. May be you can have a look at it.
ODBC/SQL Universal Data/AFL plugins (v.1.0.1)
Copyright (C)2006 AmiBroker.com.
INTRODUCTION.
There are two plugins:
ODBC.dll - data plugin
ODBCA.dll - AFL plugin
Data plugin handles retrieving quotation data, while AFL plugin allows to access other data from AFL formula level.
In fact these two plugins are almost identical and will be merged in the future into one as soon as AmiBroker will have support for dual-mode plugins.
Both plugin files should be copied to " Plugins" subfolder inside AmiBroker directory.
Once properly loaded they will appear under Tools->Plugins in AmiBroker as:
ODBC/SQL Universal Data Plugin
ODBC/SQL Universal AFL Plugin
DOWNLOADS
ODBC Data and AFL data plugins and their source codes are available from the following locations:
ODBC/SQL Universal Data plugin:
http://www.amibroker.com/bin/odbc/ODBC.dll
ODBC/SQL Universal AFL plugin:
http://www.amibroker.com/bin/odbc/ODBCA.dll
ODBC/SQL Data/AFL plugin C++ source code (for Visual C++ 6.0)
http://www.amibroker.com/bin/odbc/ODBCSrc.zip
DATA PLUGIN
Data plugin can connect to any database that has corresponding ODBC driver. In order to allow to access data, the table holding quotations needs to have the following columns:
SYMBOL - holding ticker symbol (text - varchar)
DATE - quotation date / time (DATETIME type - for Microsoft SQL Server, or TIMESTAMP type for mySQL (open source))
OPEN - open (recommended FLOAT type)
HIGH - high (recommended FLOAT type)
LOW - low (recommended FLOAT type)
CLOSE - close (recommended FLOAT type)
VOLUME - volume (recommended FLOAT or INT type)
OPENINT (optional) - open interest - (recommended FLOAT or INT type)
Types of fields are not that important as plugin has ability to auto-convert if types differ. Recommended types are just as a guideline for best performance.
To connect to external database please follow these steps:
1. Go to File->New database
2. Enter new database path and click "Create" button
3. Select "ODBC/SQL Universal Data plugin" from Data source combo
4. Click on "Configure" button
5. Click on "Pick database button" and create new ODBC source or pick existing one then click OK
6. Select desired table name from the "Table name" combo
7. Pick correct column names for Open/High/Low/Close/Symbo/Date fields or accept defaults
8. (Optional) You may press "Retrieve symbols" button to get the list of symbols transferred from the external database to AmiBroker
9. Click OK
10. Click OK to close Database Settings dialog
Configuration Dialog
Data plugin configuration dialog is available from File->Database Settings->Configure
Controls:
Pick ODBC Data source... - brings up standard Windows ODBC data source selector that allows you to pick existing source or create new one. After picking the data source appropriate connection string is copied automatically to Database (ODBC connection string). Note that in case of some servers (for example Microsoft SQL Express 2005) it may be required to add manually the following two items to the connection string: SERVER=.\SQLEXPRESS;DRIVER={SQL Server};
If picked data source is available, then plugin connects to it and fills Table name combo box with the list of available tables.
Database (ODBC connection string) - standard ODBC connection string required to connect to the database. This includes the data source name as well as other optional information, such as a user ID and password. For example, "ODBC;DSN=SQLServer_Source;DATABASE=MyDatabase;UID=SA;PWD=abc123" is a possible connection string. For more information about Microsoft SQL Server connection strings see this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_d_4x4k.asp
Table name - combo box field that is filled with all tables present in the database currently connected. You should pick the table which holds quotation data (i.e. SYMBOL, DATA, OPEN, HIGH, LOW, CLOSE, VOLUME columns)
Symbol - the name of the field in selected table that holds ticker symbols. By default it is set to SYMBOL but you can pick any other column name that you have in your database
Retrieve All Symbols - pressing this button queries the database with the following SQL :
SELECT Symbol FROM <selectedtable> GROUP BY Symbol
- this gives the list of all symbols available in the table and this list is then copied to AmiBroker's internal symbol list (so you don't need to add symbols manually)
Date/Time, Open, High, Low, Close, Volume, Open Int - these fields define column names for subsequent price data fields. You may use them to change the names of the columns if your database does not follow default schema of SYMBOL, DATE, OPEN, HIGH, LOW, CLOSE, VOLUME. Open Int field is optional
Requery database every 5 seconds - if you check this box, the plugin will trigger automatic refreshes of all charts causing re-query of database every 5 seconds. This is useful if your database is fed with real time data and you want changes to be reflected (almost) immediatelly on the charts. Please note that if you are using EOD database you should NOT use this feature as it just adds extra load to your database and to your computer.
FUNCTIONS (AFL)
odbcOpenDatabase( "connectstring" )
- opens database using ODBC driver
connectstring - specifies an ODBC connect string. This includes the data source name as well as other optional information, such as a user ID and password. For example, "DSN=SQLServer_Source;UID=SA;PWD=abc123" is a possible connect string.
If you do not know what exactly connection string is, provided that you use ODBC Data plugin as your quotation source as well, you may go to File->Database Settings->Configure and copy connection string from "Data source" field (you need to press "Pick data source" button first if database was not configured).
Example:
odbcOpenDatabase("ODBC;DATABASE=amitest;DSN=mySQL AmiTest;OPTION=0;PORT=0;UID=root");
odbcSetFieldNames( "symbolfield", "datefield" )
- allows to set names of symbol and date/time columns.
By default the plugin uses SYMBOL and DATE as names of those two columns to produce internally statements like this:
SELECT FIELD, DATE FROM tablename WHERE SYMBOL = 'MSFT' ORDER BY DATE ASC
This function is provided to allow you to read data from tables that have different column names than defaults.
Example:
odbcSetFieldNames("ticker", "datetime" );
odbcGetArray( "tablename", "symbol", "fieldname" )
- retrieves array from database table
"tablename" - is the name of table to retrieve data from
"symbol" is the ticker symbol to request data for, if you specify empty string then current symbol is used
"fieldname" is the column name with custom data
The plugin internally executes the following query
SELECT fieldname, DATE FROM tablename WHERE SYMBOL = 'symbol' ORDER BY DATE ASC
and returns data held in fieldname column.
Example:
Graph0=odbcGetArray("mytable", "", "userval1");
odbcGetValue( "tablename", "symbol", "fieldname" )
- retrieves single numerical or text value from database table
"tablename" - is the name of table to retrieve data from
"symbol" is the ticker symbol to request data for, if you specify empty string then current symbol is used
"fieldname" is the column name with custom data
The plugin internally executes the following query
SELECT fieldname FROM tablename WHERE SYMBOL = 'symbol'
and returns first data record held in fieldname column.
The function is intended to be used on arrays WITHOUT date/time column (static data, no history).
Example:
Graph0=odbcGetValue("mytable", "", "userval1");
One more thing i do is that at the end of day i do not delete the data folder contents or the files created by the utility convertor when closing it. This i feel helps me in continuing next days updates easily. I delete these files only if its size increases and is creating problems while updating to AMI.I also would suggest that in the utility you have created keep it as a stand alone one only for the purpose of importing data from PIB to AMI. Some of the other utilities i have used has EOD downloaders, Fetching data from yahoo etc.
Regards
Saji