amibroker

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.

Filed by Tomasz Janeczko at 3:32 pm under Data
Comments Off on How to import huge ASCII files quickly

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).

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.

Filed by Tomasz Janeczko at 4:29 am under Data
Comments Off on How to backfill all symbols in RT database

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).

Filed by Tomasz Janeczko at 2:19 pm under AmiQuote,Data
Comments Off on AmiQuote and free data from Yahoo

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

Filed by Tomasz Janeczko at 3:23 pm under Data
Comments Off on Setting up with FXCM (forex broker)

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…)

Filed by AmiBroker Support at 5:54 am under Data
Comments Off on How to combine two databases into one

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:

– 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( i = Qty - 1; i >= 0; i = i - 1 )
{
 st = sts.Item( i );
 Ticker = st.Ticker;
 printf("changing " + ticker + "\n" );
 Length = StrLen(Ticker );
 if( StrFind(ticker, ".TO") )
      st.Ticker = StrLeft( ticker, Length-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.

Filed by AmiBroker Support at 10:03 am under Data
3 Comments

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

Filed by AmiBroker Support at 2:56 am under Data
3 Comments

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…)

Filed by AmiBroker Support at 7:31 pm under Data
2 Comments

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

Filed by Tomasz Janeczko at 8:56 pm under Data,Exploration
6 Comments

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…)

Filed by Tomasz Janeczko at 8:41 pm under AmiQuote
Comments Off on How to use your LiveCharts account with AmiQuote ?