amibroker

When we use File->Import ASCII to import data, we can choose import file format using one pre-defined import format definitions. As it is explained in the manual (http://www.amibroker.com/guide/d_ascii.html) it is also possible to create our custom import definitions to match data we are trying to import. This article explains all the required steps.

The easiest method to create import definition is to use File->Import Wizard. In the first page, select at least one file in the format we want to import and on the second page configure columns:

This all easy when we are importing quotation data, but when we are importing non-quotation data such as category assignments, we can not select appropriate columns using Import Wizard. In such case we need to type-in appropriate $FORMAT command in the “Additional commands” field of Import Wizard.

For example if we have file with categories like this:

"DDD","3D Systems Corporation","Technology","Computer Software: Prepackaged Software",1
"MMM","3M Company","Health Care","Medical/Dental Instruments",1
"WBAI","500.com Limited","Consumer Services","Services-Misc. Amusement & Recreation",1
"WUBA","58.com Inc.","Technology","Computer Software: Programming, Data Processing",1
"AHC","A.H. Belo Corporation","Consumer Services","Newspapers/Magazines",1
"ATEN","A10 Networks, Inc.","Technology","Computer Communications Equipment",1
"AIR","AAR Corp.","Capital Goods","Aerospace",1
"AAN","Aaron's,  Inc.","Technology","Diversified Commercial Services",1

We need to add the following commands in the “Additional commands” field of Import Wizard

$FORMAT Ticker,FullName,SectorName,IndustryName,Group
$OVERWRITE 1
$CLEANSECTORS 1
$SORTSECTORS 1

First line tells AmiBroker the column meaning, second line tells it to overwrite existing data. Last two lines tell AmiBroker to wipe existing category structure and sort imported sectors alphabetically. Be sure NOT to specify $CLEANSECTORS command when you do NOT want to wipe existing category structure.

We also need to mark “No quotation data” box in the second page of Import wizard to tell AmiBroker that the file that we are importing does not contain quotes and it should switch off all price checking.

Finally, in the last step of the wizard save the format definition:

Once we do this, next time we use File->Import ASCII a new selection My own format will appear in the Files of type combo box in the file selector dialog.

It is worth noting that import definitions are plain text files that are stored in “Formats” subfolder of AmiBroker directory, and the list of available import definitions that appears in “Files of type” combo box, is also a plain text file called “import.types” that is located in the same subfolder. So, advanced users may also modify those files directly using plain text editor such as Notepad. It is all explained in great detail in the manual http://www.amibroker.com/guide/d_ascii.html

Filed by Tomasz Janeczko at 8:11 am under Data
Comments Off on How to create custom import definition for ASCII importer

In order to combine data stored in two separate databases within a single database we may consider one of the following options:

ASCII IMPORT/EXPORT METHOD

First of the possible ways is to export data from one database into CSV files using the procedure presented here:

http://www.amibroker.com/kb/2006/03/04/how-to-export-quotations-from-amibroker-to-csv-file/

Once we have our quotations exported into text files, we can load the other database and use built in ASCII importer to import data. The detailed procedure is outlined in the following Knowledge Base Article:

http://www.amibroker.com/kb/2014/12/29/importing-data-using-multiple-column-separators/

FILE COPY METHOD

Another way of combining the databases is to copy the individual symbols files. Each database stores individual data files within 0-9,a-z,”_” subfolders and it is possible to copy the individual data files between databases. When copying, we need to maintain the same folder pattern and copy symbols from “a” subfolder of one database into “a” subfolder of the other database (the same for other folders), so each of the symbols would land in its respective folder.

After we copy the data files, we also need to delete broker.master file from the target database folder. This is because this file stores pre-generated symbol list used for fast loading. When we delete the file, it will be reconstructed based on information from individual data files.

More information about the database structure can be found in the manual:
http://www.amibroker.com/guide/h_workspace.html

Filed by Tomasz Janeczko at 3:09 pm under Data
Comments Off on How to combine multiple databases into one

When we import data from ASCII (plain text) files, sometimes the data in the input files are arranged in columns separated by different characters. This article shows how to configure Import Wizard / ASCII Importer to import such data correctly.

Let us consider data file using the following format

Ticker,Date/Time,Open,High,Low,Close,Volume
EURUSD,2011-06-13 20:19:00,1.4421,1.4421,1.4419,1.4419,332
EURUSD,2011-06-13 20:20:00,1.4419,1.4419,1.4418,1.4418,298
EURUSD,2011-06-13 20:21:00,1.4418,1.4418,1.4416,1.4417,192


In this sample data file columns are separated with a comma, with one exception – date and time columns are combined together, with a space in between. ASCII importer requires us to specify Date and Time columns separately. Fortunately – there is a way to treat space and comma both as separators at the same time.

To configure Import Wizard, we need to specify columns to match the input data, treating Date and Time as two separate columns (Column 2 and Column 3 in this case), additionally we need to set Separator field as Comma or Space, so the importer would properly recognize space as a character delimiting the new column.

This way our data will be imported properly. There are also other multiple separator choices available in the Import Wizard, such as Tab or Space and Semicolon or Space that can be used if your data file uses tabs or semicolons as primary column separator.

If we build our import definition file manually for ASCII importer, we can also specify multiple separators, by enclosing the required characters in quotation marks in $SEPARATOR command. The equivalent format definition for the above input data would look like this:

$FORMAT Ticker, Date_YMD, Time, Open, High, Low, Close, Volume
$SKIPLINES 1
$SEPARATOR ", "
$CONT 1
$GROUP 255
$AUTOADD 1
$DEBUG 1


More information about ASCII importer and Import Wizard functionalities can be found in the manual:
http://www.amibroker.com/guide/w_impwizard.html
http://www.amibroker.com/guide/d_ascii.html

Filed by Tomasz Janeczko at 3:56 am under Data
Comments Off on Importing data using multiple column separators