AmiBroker ASCII Import

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

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:

for DATE_xxx you can use ‘-‘, ‘/’ or ‘\’ as day/month/year separators. You can even omit separators at all if only you give a date in a 6 digit (YYMMDD, MMDDYY, DDMMYY) or 8 digit format (YYYYMMDD, MMDDYYYY, DDMMYYYY).

AmiBroker recognises decimal as well as true fractions in price data. True fractions must follow the whole value after at least single space. For example you can specify: 5.33 or 5 1/3

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.

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