AmiBroker Documentation Item No. ab101.html
Revised March 4th, 2000.

This is the part of AmiBroker documentation. Copyright 2000 Tomasz Janeczko. All rights reserved.


ASCII Import

This document covers enhanced functionality provided by AmiBroker version 3.22 and above. Earlier versions provide only small subset of features described below.

Introduction

AmiBroker has easy-to-use and flexible quotation import feature.
Since AmiBroker 3.22 the ASCII importer is greatly enhanced giving you even more flexibility by using custom format definitions.

How does it work?

Quotation data may come from various sources so the format of the ASCII (i.e. text based) file may be much different from one source to another. To handle all those differences AmiBroker uses format definition commands that define the way the text information is interpreted by the ASCII importer. The format definition commands are keywords that begin with a dollar sign '$'. These commands may be embeded in the data file itself or, may be stored in the separate format definition file for multiple use. Storing format definition commands in separate file avoids the need to include the commands in every data file. The default format definition file name is "default.format" and this file should be stored in AmiBroker's current working directory. The defaults are overriden by any commands included (embedded) in the data file itself.

So, when you use the "Import from ASCII" menu, AmiBroker first looks for the format definition stored in "default.format" file and then parses the file you have choosen. If there is no "default.format" file then it uses internal defaults (described below).

You can modify "default.format" file to suit your needs. Moreover using OLE Automation (Win32 version) or ARexx (Amiga) interface you can specify the name of the format definition file which will be used instead of "default.format" file.

Format definition commands

The command keywords begin with a dollar sign '$''. Every line starting with command is interpreted in special way. Here is the list of commands recognized by AmiBroker's built-in importer. Bold letters mark keywords.

 

Command $AUTOADD Switch new ticker add mode
Arguments <number> 0 - do not add , 1 - add a new stock when non-existing ticker detected (default = 0)
Alias  
Examples $AUTOADD 1

 

Command $BREAKONERR Define on-error behaviour
Arguments <number> 0 - to continue, 1 - to break import on error (default=0)
Alias  
Examples $BREAKONERR 1

 

Command $DATE_DMY Define date
Arguments <number> The date in Canadian format (DD-MM-YY)
Alias $DATE_CDN
Examples $DATE_DMY 12-05-99
$DATE_CDN 12-05-1999

 

Command $DATE_MDY Define date
Arguments <number> The date in US format (MM-DD-YY)
Alias $DATE_USA
Examples $DATE_MDY 05/12/99
$DATE_USA 05/12/99

 

Command $DATE_YMD Define date
Arguments <number> The date in International format (YY-MM-DD)
Alias $DATE_INT
Examples $DATE_INT 99-05-12
$DATE_CDN 1999.05.12

 

Command $DEBUG Switch logging (debug) mode
Arguments <number> 0 - no error logging, 1 - log errors to "import.log" file (default=0)
Alias  
Examples $DEBUG 1

 

Command $FORMAT Define line format (sequence and types of fields)
Arguments DATE_MDY date in US format: MM-DD-YY (alias: DATE_USA)
DATE_DMY date in Canadian format: DD-MM-YY (alias: DATE_CDN)
DATE_YMD date in International format: YY-MM-DD (alias: DATE_INT)
NAME ticker name (alias: TICKER)
OPEN open price
HIGH high price
LOW low price
CLOSE close price
VOLUME volume
VOL1000 volume in thousands shares
VOLMIL volume in millions shares
VOLFACTOR volume factor (number of shares in a block) default =1
TURNOVER turnover
SKIP skip (ignore) field
Alias  
Examples $FORMAT TICKER DATE_MDY OPEN HIGH LOW CLOSE VOLUME
$FORMAT TICKER, DATE_INT, CLOSE, VOLUME
$FORMAT SKIP, TICKER, SKIP, SKIP, DATE_INT, OPEN, HIGH, LOW, CLOSE, TURNOVER

 

Command $NAME Define ticker name
Arguments <ticker> ticker name (symbol) (default = file name without path and extension)
Alias $TICKER
Examples $NAME AAPL
$TICKER MSFT

 

Command $SEPARATOR Define field separator character
Arguments <separator char> the character used to separate data fields (default = space)
Alias  
Examples $SEPARATOR ,
$SEPARATOR ;

 

Command $SKIPLINES Define how many lines to skip (ignore)
Arguments <number> number of lines to skip (default = 0)
Alias  
Examples $SKIPLINES 1

 

Command $VOLFACTOR Define volume factor
Arguments <number> the factor by which volume data is multiplied (default = 1)
Alias  
Examples $VOLFACTOR 10

Notes:

AmiBroker is not limited to any kind of fraction, if you wish you can write even: 5 333/999

Comments

You can include comments in both format definition file and the data file(s). Each line starting with * (asterisk) or ; (semicolon) or # (hash) is treated as a comment and ignored by the ASCII importer.

Usage examples

What may look complicated from command list will become quite clear after some examples. So I will give you four examples of how to write format definition files. First example will show the defintion for CSV (comma separated values) quotes available from Yahoo's finances site. Second example will show definition for Metastock ASCII file format. Third example shows definition for Omega SuperCharts ASCII file format. And fourth example will show the definition for s-files used by DM BOS (Polish brokerage company).

Yahoo CSV

The data from Yahoo's site looks as follows:

Date,Open,High,Low,Close,Volume
1-Feb- 0,104,105,100,100.25,2839600
31-Jan- 0,101,103.875,94.50,103.75,6265000
28-Jan- 0,108.1875,110.875,100.625,101.625,3779900

The first line gives us a hint about the meaning of the comma separated fields. First field will hold the date. The remaining fields will hold open, high, low, close prices and volume. Importer should skip the first line and parse all the remaining lines that hold just comma-separated data. Appropriate format definition file would look like this:

$FORMAT Date_DMY,Open,High,Low,Close,Volume
$SKIPLINES 1
$SEPARATOR ,
$DEBUG 1
$AUTOADD 1
$BREAKONERR 1

$DEBUG switches on error logging to "import.log" file and $BREAKONERR will cause importer to stop after the first error found. $AUTOADD ensures that new ticker will be added to the database if it is missing. Well... you may ask: how does it know the ticker name? The answer is simple: if there is no field which defines the ticker name, the importer takes the file name (without path and extension) as a ticker. So if you are importing file "C:\My data\AAPL.CSV" AmiBroker will use "AAPL" as a ticker name.

Metastock ASCII

The data in Metastock ASCII format looks as follows:

<ticker>,<per>,<date>,<high>,<low>,<close>,<vol>
AAP,D,1/17/2000,5483.33,5332.01,5362.3,0
AKS,D,1/17/2000,9868.45,9638.03,9687.62,0
FET,D,1/17/2000,3741.3,3540.2,3570.81,0

First field will hold the ticker name, second - time period ("D" means daily data), third - quotation date. The rest will hold high, low, close prices and volume. The importer should then skip the first line and parse all the remaining lines that hold just comma-separated data. Appropriate format definition file would look like this:

$FORMAT Ticker,Skip,Date_MDY,High,Low,Close,Volume
$SKIPLINES 1
$SEPARATOR ,
$DEBUG 1
$AUTOADD 1
$BREAKONERR 1

Skip in $FORMAT defines a field which should be ignored by the importer.

Omega SuperCharts ASCII

The data in Omega SC ASCII format looks as follows:

ticker,date,open,high,low,close,vol
AAP,20000117,5333.01,5483.33,5332.01,5362.3,3433450

This format is similiar to previous ones, however the date is in YYYYMMDD format without separators between year, month and day part. AmiBroker, however, can handle such dates with ease. Appropriate format definition file would look like this:

$FORMAT Name,Date_Int,Open,High,Low,Close,Volume
$SEPARATOR ,
$DEBUG 1
$SKIPLINES 1
$AUTOADD 1
$BREAKONERR 1

Skip in $FORMAT defines a field which should be ignored by the importer.

DMBOS S-files

The data in this format looks as follows:

0,29-02-00,12:05,MIDWIG,1069.1,,,+1.2,336002000,
0,29-02-00,12:05,NIF,48.6,,,+0.8,1763000,
0,29-02-00,12:05,WIG20,2300.3,,,+1.1,336002000,
0,29-02-00,12:05,WIG,21536.8,,,+0.2,336002000,
0,29-02-00,12:05,WIRR,2732.8,,,+1.6,16373000,
1,29-02-00,12:05,AGORA,144.00,,,+4.7,15802000,
1,29-02-00,12:05,AGROS,40.00,nk,72,+5.0,840000,
1,29-02-00,12:05,AMERBANK,28.00,,,+3.7,22000,
1,29-02-00,12:05,AMICA,41.50,nk,99,+2.2,564000,

This format is a little bit more complicated. For us useful fields are: 2nd - date, 4th - ticker, 5th - close price, 9th - the turnover value (close * volume). The remaining fields holds other information that is not useful for us. Appropriate format definition file would look like this:

$FORMAT Skip,Date_DMY,Skip,Name,Close,Skip,Skip,Skip,Turnover
$SEPARATOR ,
$DEBUG 1

Default behaviour

When importing ASCII files, AmiBroker attempts to open "default.import" file (in the AmiBroker's directory) to obtain the format definition. If such file is missing the following default format is applied:

$FORMAT DATE_USA, OPEN, HIGH, LOW, CLOSE, VOLUME
$SEPARATOR

This means that by default ASCII importer will use space character as a separator and will parse the following fields: date, open, high, low, close, volume. The file name (without path and extenstion) will be used as a ticker name. All other import parameters ($DEBUG,$AUTOADD, etc.) are set to zero.

Compatibility

The new format definition commands introduced in AmiBroker 3.22 should not affect importing your old files. New features are backward-compatible with earlier AmiBroker versions. In case of any compatiblility problems please send me an e-mail support@amibroker.com .


Metastock is a trademark of Equis International, Inc. Omega SuperCharts is a trademark of Omega Research, Inc.