HomeKnowledge Base

Time compression of data retrieved from another symbol

AmiBroker’s Time-Frame functions ( allow to use multiple intervals within a single formula and combine them together. Another set of functions in AFL (Foreign and SetForeign) allow us to retrieve data of another symbol from the database, so we can implement strategies where rules are based on multiple symbols.

This article shows how to combine these two features together and properly use Time-Frame functions on data retrieved from another symbol. Let us consider an example of a strategy, which works on daily data, but uses an additional filter based on weekly readings of S&P500 index.

The following sequence is required to code such conditions properly:

  1. switch to the other symbol with SetForeign
  2. compress data into higher interval with TimeFrameSet
  3. store the weekly values / conditions in custom variables
  4. with TimeFrameRestore() or RestorePriceArrays() functions restore the original arrays of the tested symbol (in the original time-frame)
  5. use custom variables assigned in step (3) expanded to original time-frame using TimeFrameExpand()

Here is the AFL formula, which implements the above conditions:

// first switch to ^GSPC symbol
SetForeign"^GSPC" );
// compress data to weekly interval
TimeFrameSetinWeekly );
// assign weekly values to custom variables
indexWeeklyClose Close;
indexWeeklyMA =  MAClose52 );
indexWeeklyFilter Close MAClose52 );
// restore original arrays (back to the primary symbol)
// RestorePriceArrays() function is an equivalent
// align data back to original interval
indexFilterExpanded TimeFrameExpandindexWeeklyFilterinWeekly );
// exploration shows the results, note that all weekly values
// need to be expanded if we haven't done it yet
Filter 1;
AddColumnClose"Close AAPL" );
AddColumnTimeFrameExpandindexWeeklyCloseinWeekly ), "Weekly close ^GSPC" );
AddColumnTimeFrameExpandindexWeeklyMAinWeekly ), "Weekly MA ^GSPC" );
AddColumnindexFilterExpanded"Weekly index filter")

Let us compare the readings obtained from the code with a sample chart – both ^GSPC raw reading and 52-week MA values match the chart and the condition is properly aligned to the bars starting on 2011-10-28 and extends until new weekly bar is formed.

TimeFrame + Foreign

There is also an alternative method we can use:

  1. retrieve values from ^GSPC using Foreign() function
  2. compress these readings into weekly interval using TimeFrameCompress
  3. perform calculations on weekly compressed array
  4. expand the compressed data back to the original timeframe using timeFrameExpand
indexClose Foreign("^GSPC","C");
indexWeeklyClose2 TimeFrameCompressindexCloseinWeekly );
indexWeeklyMA2 MAindexWeeklyClose252 );
indexWeeklyFilter2 indexWeeklyClose2 indexWeeklyMA2;
Filter 1;
AddColumnClose"Close AAPL" );
AddColumnTimeFrameExpandindexWeeklyClose2inWeekly ), "Weekly close ^GSPC" );
AddColumnTimeFrameExpandindexWeeklyMA2inWeekly ), "Weekly MA ^GSPC" );
AddColumnTimeFrameExpandindexWeeklyFilter2inWeekly ), "Weekly index filter")

How to combine data from multiple input files

Sometimes it is useful to update existing database with quotes from other source and sometimes we just want to update one data field, leaving other unaffected. For example we may want to import data into Aux1 and Aux2 fields leaving regular OHLC prices untouched.

This post will show how to use $HYBRID mode of ASCII importer to combine data from multiple input files.

When we use Import Wizard / Import ASCII features to import quotations, by default the importer expects Close prices to be present in the imported data. There are some situations however when it would be convenient to import data just to one field (e.g. Aux1) if we have some additional values that we want to store in the database.

Of course, one way would be to combine all these quotes in a single file before the import and process in one run – but ASCII importer allows also to use special $HYBRID mode to import such partial data.

As an example, let us consider a situation where we already have adjusted OHLC quotes imported into the database and we have a file containing unadjusted quotes in the following format:


In order to import those quotes into Aux1 field and combine with existing data, go to File->Import Wizard menu, pick the file and define format the following way:

ASCII import wizard

First we need to define columns so they match the format of input data (in this case set first three columns to Ticker, DMY, Aux1 and the rest to Skip).

To activate special hybrid mode we need to type:


into Additional Commands box and since we are not importing regular prices that include Close array, we also need to mark Allow negative prices.

If instead of using the wizard we create import definition file manually, then it should contain:


Now we can verify the results in Symbol->Quote Editor. The OHLC fields still contain adjusted values, while additional data has been properly stored in Aux1 field without affecting the other fields.

Quote Editor

For more information about Import Wizard and ASCII importer please check the following parts of the User’s Guide:

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:
AmiQuote uses rather “download data” link which is:
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: (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:
  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
$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:


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:

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;
Qty 1>= 0)
st sts.Item);
Ticker st.Ticker;
printf("changing " ticker "\n" );
Length StrLen(Ticker );
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.

« Previous PageNext Page »