amibroker

HomeKnowledge Base

How to import huge ASCII files quickly

From time to time we are asked how to import large text (ASCII) files quickly. Normally speed is non-issue for ASCII import as it is blazing fast. That kind of question typically comes from person who wants to import hundreds of megabytes of data.

ASCII importer is optimized for adding new data to the existing database, so the most efficient operation is adding current quote (the newest one).

If you want to import huge amount of data in ASCII format in most efficient manner you need to make sure that the file you are importing is sorted

  1. in ascending symbol order (so “AAPL” before “INTC”), and within symbol
  2. in ascending chronological order (so oldest records first, newest records last)

In SQL query talk it would be “ORDER BY Symbol ASC, Date ASC”.

Doing so ensures that no sorting is required during import and symbol shuffling is reduced to minimum, so in-memory cache is used most efficiently.

If your file is not ordered or ordered in reverse then it takes long to import because AB must shuffle data. In worst case scenario (newest records first), every data insert involves sorting which makes it a killer. The difference can be hours vs seconds on properly sorted file.

How to backfill all symbols in RT database

Various data sources have different backfill capabilities, therefore the procedure to backfill all symbols varies. There are three categories of data sources, those that offer:

  1. unlimited backfills (eSignal, IQFeed),
  2. limited, 1-symbol at a time backfill (Interactive Brokers),
  3. no backfill at all (for example DDE)

As a general rule, all real-time data sources provide backfills on-demand, which means that backfill for each symbol has to be requested separately. If data source provides backfill and there are missing quotes in the database, AmiBroker will automatically request backfill on first access to given symbol. So, as soon as you display its chart fresh data will be requested and backfilled. Backfill is not immediate, because it is Internet-based process that involves request-response procedure, so data are requested from external server and arrive in a few seconds or so.
The same procedure is performed when doing any access including running Analysis, so backfill is requested as soon as given symbol is accessed, but by default Analysis window will not wait until backfill data arrive unless you turn on the Wait for backfill option (provided that data source supports it).

Wait for backfill option

It is worth to note that not all data sources support this feature. It needs to be handled by the data plugin, and many 3rd party plugins do not offer this.

If you are using eSignal or IQFeed or other data source with automatic, unlimited backfill you can use procedure described in How to use Real-Time data sources tutorial.

If you are using Interactive Brokers then the procedure is differnet since Interactive Brokers puts lots of limitations on backfills. To learn how to backfill all symbols using Interactive Brokers please read this dedicated tutorial on IB.

If you are using data source that does not offer any backfill, the only option is to use ASCII import to import the historical data from text files.

AmiQuote and free data from Yahoo

There are a couple of things you need to know about Yahoo Finance pages that AmiQuote uses to download “historical” and “current” quotes.

Current quotes are quotes for current day (or previous day if there is no trading session today). For example MSFT current quote page is here:
http://finance.yahoo.com/q?s=MSFT
AmiQuote uses rather “download data” link which is: http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=sl1d1t1c1ohgv&e=.csv
But it is not relevant because both show same current quote.

Now there is a second source. Historical quotes are downloaded from Historical Prices page. For example MSFT historical page is here: https://finance.yahoo.com/quote/MSFT/history?p=MSFT (again AmiQuote uses rather plain text link “Download data” (URL is dynamic, so you must click on “Download data” link on Yahoo page)

Why using two sources? That’s simple: current mode gives data during trading session, while historical is only updated many hours after markets close so both compliment each other. Current mode is also much faster as it downloads as many as 200 symbols at once, while historical must download one by one. So recommended usage is to use Yahoo Current mode everyday, and Yahoo Historical once a week.
Of course you may use historical everyday as well if you have time and fast internet connection.

It is important to understand that AmiQuote is just the downloader (like Internet Explorer) and it does nothing except downloading the data, so if you belive that there is a bad quote – it is not AmiQuote, but rather Yahoo problem. To verify always go to relevant page (see links above) and check the quote on Yahoo Finance site directly.

It may happen that quotes on Yahoo Current page and Yahoo Historical pages differ. It is so because Yahoo gets them from different data vendors. If this happens the only solution is to report data error to Yahoo.

There are however 2 things you need to know about importing of data:

  1. AmiBroker by default imports split adjusted data (“Open, High, Low, Close” on Yahoo Historical page). For more information on how data are adjusted see Yahoo Help page at: http://help.yahoo.com/l/us/yahoo/finance/quotes/quote-12.html
  2. You can change it to import split-and-dividend adjusted data (“Adj. Close” on Yahoo Historical Page) as described below

These things are adjustable, so if you don’t like them, you can change them.
The import process of historical quotes is controlled using aqh.format file that you will find inside “Formats” subfolder. By default it looks as follows (you can open it with Windows Notepad).

# AmiQuote historical quotes download format (.AQH extension)
$FORMAT Date_DMY,Open,High,Low,Close,Skip,Volume
$SKIPLINES 0
$BREAKONERR 0
$SEPARATOR ,
$DEBUG 1
$AUTOADD 1
$CONT 1
$GROUP 254

Lines marked with bold mark important areas.

$FORMAT line controls the import format.
Yahoo currently delivers only ADJUSTED data. But they are adjusted in two ways. First way is just adjusting for splits. And these data are downloaded by default.
Additionally Yahoo has “Adj. Close” column that provides close price adjusted for splits AND dividends.
You can download data adjusted for splits AND dividends by changing aqh.format file.
AdjClose field says that AmiBroker should use adjusted price. If you want prices adjusted for both splits and dividends simply replace $FORMAT line with:

$FORMAT Date_DMY,Open,High,Low,Close,AdjClose,Volume
(note AdjClose in place of Skip field)

$VOLFACTOR line controls the volume multiplier. If you want volume to be expressed in single shares instead of hundreds of shares replace $VOLFACTOR line with:

$VOLFACTOR 1

The same $VOLFACTOR change should be applied to aqd.format file that is responsible for importing data in Yahoo CURRENT mode (if you are using it).

Setting up with FXCM (forex broker)

I have just prepared instructions how to connect to FXCM (Forex broker) to get their real-time quotes.
Also included is small example database (1 day worth of 1 minute data) and all required programs.

For the details check: http://www.amibroker.com/fxcm.html

How to combine two databases into one

If you ever wondered how to combine two databases into one this short article will show you how, but let us start with some background first… (more…)

How to change property for multiple symbols at once.

In order to automatically change a property for many symbols – instead of manual action in Symbol -> Information dialog one can use OLE automation.
For example – to unmark “Use only local database” option for all the symbols, it’s enough to run such SCAN:

– Analysis -> Formula Editor:
– enter:

AB CreateObject("Broker.Application");
st AB.Stocks(Name());
st.DataSource 0;
Buy 0

– Tools -> Send to Auto-analysis
– Apply to: All Symbols, N-last quotations = 1
– press SCAN

The other example shows how to rename all the symbols and replace .TO suffix with -TC (this may be useful when we want to use the existing historical data with a new datasource which uses a different symbology).

– Analysis -> Commentary
– enter:
AB CreateObject("Broker.Application");
sts AB.Stocks();
Qty sts.Count;
for( 
Qty 1>= 0)
{
 
st sts.Item);
 
Ticker st.Ticker;
 
printf("changing " ticker "\n" );
 
Length StrLen(Ticker );
 if( 
StrFind(ticker".TO") )
      
st.Ticker StrLefttickerLength-3)+"-TC";

– press APPLY
– use: VIEW -> Refresh All to see the changes in the symbol tree.

CAVEAT: The commentary formula above iterates through ALL SYMBOLS in the database, therefore, if your database is big (has more than 100 symbols) it may be SLOW, and you may experience “not responding” message, but it is NORMAL. It does not mean that program has stopped working. It just means that it has not completed iteration yet. In that case just WAIT and don’t touch it.

AmiBroker for Forex

Here is an article that tells you everything you need to know about using AmiBroker for trading FOREX markets. (more…)

How does the intraday-to-daily compression work?

When you are using intraday database daily candles are usually constructed by time-compression of intraday (for example 1-minute data). AmiBroker offers lots of flexibility when it comes to defining intraday-to-daily time compression.
(more…)

How to export quotations from AmiBroker to CSV file ?

The easiest way to export quotes to CSV file is to use the below formula from Automatic Analysis window:
(Analysis -> Automatic Analysis)
(more…)

How to use your LiveCharts account with AmiQuote ?

UPDATE April 2, 2008: We received the following information from eSignal:
” It wasn’t our intention to have this product be made compatible with 3rd party software[…] The new version of LiveCharts uses a different method of exporting data and it will no longer work with AmiQuote starting the week of April 14th, 2008.”

This effectivelly means that the instructions below will become obsolete April 14th, 2008.

You can use your Livecharts ($9.95/month) subscription to feed AmiQuote with real time data from Lycos. However, there is one extra step involved in order to make it work.
(more…)

« Previous Page