Data Alignment Pitfalls Across Symbols

This is the lesson that saves you from being confidently wrong. Every technique in this part rests on two symbols sharing a row of dates, and they very often do not. When they do not, AmiBroker reconciles them for you, silently, using rules you have now read — and the result is a chart that looks exactly like a correct one.

By the end you will have a procedure that produces a number: how many bars of your comparison were traded, and how many were manufactured.

Two symbols, two calendars

Nothing forces two instruments to have bars on the same days. Some of the reasons are structural and some are defects, and from inside AFL they are indistinguishable:

• Different exchanges keep different holidays. A national holiday in one market is an ordinary Tuesday in another.
• Different asset classes keep different hours and weeks. Futures and currencies trade on days when the cash equity market is shut.
• Thin issues simply do not print. A share with no trades on a given day has no bar, even though the exchange was open.
• Halts, suspensions and corporate events remove bars in the middle of a history.
• Instruments start and stop. A share listed in 2019 has no data before 2019; a company taken over in 2022 has none after 2022.
• Your database has gaps. Failed downloads, partial imports and a source that was offline that morning all look identical to a genuine non-trading day.

Three symbols in one comparison, three different histories

Broad index

quotes throughout

Sector fund

quotes from launch

The share

quotes from listing

1990200020102020now

A three-way relative strength chart over this data has only the rightmost quarter in which all three exist. Nothing on the chart says so.

What Foreign() actually does about it

Foreign() guarantees one thing: the array it returns has exactly as many elements as the current symbol’s arrays, and element n is the foreign symbol’s data on the date of the current symbol’s bar n. To keep that guarantee it must resolve two different mismatches, in two different ways.

Bars the current symbol does not have are dropped. The note on the Foreign page states this explicitly: if the current symbol has data holes, Foreign() removes bars that exist in the foreign symbol but do not exist in the current symbol. The foreign data is being fitted to your symbol’s calendar, so anything outside that calendar has nowhere to go.

Bars the foreign symbol does not have are filled, according to fixup. With the default of 1 the missing bar’s open, high, low and close all become the previous bar’s close, and its volume becomes zero.

Both operations are correct, both are necessary, and neither reports anything.

The same two symbols, viewed from each side

Tuesday and Thursday are handled in opposite ways depending on which symbol the formula is running on. The two directions do not produce the same comparison.

Bar	Mon	Tue	Wed	Thu	Fri
Thin share: has a bar?	1	0	1	1	1
Index: has a bar?	1	1	1	0	1
Foreign(index) from the share	ok	dropped	ok	padded	ok
Foreign(share) from the index	ok	padded	ok	dropped	ok

Tuesday and Thursday are handled in opposite ways depending on which symbol the formula is running on. The two directions do not produce the same comparison.

That last row is the point people miss. Foreign() is not symmetric. Charting the share and reading the index gives you a different set of bars from charting the index and reading the share. Any statistic computed from the pair — a ratio, a correlation, a spread, a beta — can therefore take two different values depending on which symbol you happened to be standing on.

Listing and delisting dates

The two ends of a history need separate attention, because they fail differently from a mid-history hole.

Before the foreign symbol was listed, there is no previous bar to pad from. The function reference does not document what Foreign() returns in that region, so do not assume — test it. The audit formula below reports the first bar on which the unpadded series carries a value, which tells you where the foreign symbol’s real history starts in your database, whatever the padding did before it.

After a foreign symbol stops quoting — delisted, taken over, renamed, or simply not updated by your data source since a failed download three months ago — the padded series carries its last close forward indefinitely. This is the worst of the three cases, because it does not look like missing data. It looks like an instrument that has gone completely flat, and a ratio against a flat denominator is a pure copy of the numerator. Relative strength against a stale benchmark rises exactly when the stock rises, and it will do so forever.

⌀Data quality warning

A benchmark that has not updated is the single most common cause of a relative strength line that suddenly starts looking excellent. Before believing any outperformance, check the date of the benchmark’s last real quote. The audit formula prints it as a column.

How misalignment turns into signals that were never real

None of this is abstract. Here is how each mechanism reaches your Buy array.

A cross against a bar that did not trade. A regime rule such as Cross( Close, Foreign("^GSPC","C") ) can fire on a bar where the index was padded — its “price” that day was yesterday’s close, held flat, because the index did not trade. The cross is arithmetically true and refers to an event that did not happen.

A volume filter that fires on the recovery. Padded bars carry zero volume, so a foreign symbol’s average volume is dragged down by exactly the number of days it did not trade. The first real bar after a run of holidays then sits far above that depressed average, and a “volume surge” filter fires on a perfectly ordinary day.

A volatility measure that is too small. A padded bar with fixup = 1 has its high equal to its low equal to its close, so its range is zero and its true range is just the gap from the previous close. ATR() computed on foreign data is therefore biased downward, and any stop, target or breakout threshold expressed in multiples of it is tighter than intended.

A correlation pulled toward zero. A padded bar produces a one-bar return of exactly zero for the foreign symbol while the current symbol moved. Enough of them and a real relationship measures as a weak one, as the previous lesson showed.

An indicator with two different values. This is the one to remember, because it is easy to reproduce and impossible to argue with. Compute a 200-bar moving average of an index on the index’s own chart. Then compute the same average on a thin share’s chart, through Foreign(). If the share has data holes, the index bars on those dates were dropped before the average was taken, so the two numbers differ — the same indicator, on the same symbol, on the same date, with two answers depending on where you stood.

Fragment — not a complete formula

// On the index's own chart
Plot( MA( Close, 200 ), "MA200, computed on the index itself", colorBlue );

Fragment — not a complete formula

// On a thin share's chart, same quantity, read through Foreign
BenchmarkClose = Foreign( "^GSPC", "C" );
Plot( MA( BenchmarkClose, 200 ), "MA200, computed through Foreign", colorRed );

Compare the two last values. On a liquid share they will agree to within rounding. On a thin one they will not, and the size of the disagreement is a measure of how much your comparison is being reshaped by the current symbol’s calendar.

Pad and align all data to reference symbol

AmiBroker provides one setting that addresses this directly. It lives in Analysis → Settings, in the dialog headed Backtester settings, on the General tab: a checkbox labelled Pad and align all data to reference symbol with an edit field beside it for the reference symbol itself, and a sub-caption noting that turning it on may slightly change indicators if you have data holes. The same tab also carries the Use QuickAFL checkbox.

What the manual says it does: when it is turned on, all symbols’ quotes are padded and aligned to the reference symbol. Every symbol in the run then shares one bar grid — the reference symbol’s — instead of each pair being reconciled separately.

Four documented facts about it, all of which matter:

1. It is off by default. The manual says so explicitly, and adds “Use responsibly.”
2. It has a cost. The documentation notes it may slow down backtest, exploration or scan, and that it may introduce slight changes to indicator values where your data has holes, because those holes are filled with previous bar data.
3. It has two documented intended uses, and they are narrow: when your system uses general market timing — global signals derived from a reference symbol read with Foreign() — and when you are creating composites out of unaligned data.
4. It fails silently. If the reference symbol does not exist, the data will not be padded. No error, no warning, just an unpadded run that looks exactly like a padded one.

⚠Warning

Point 4 deserves a habit rather than a note. Before relying on this setting, confirm the reference symbol is spelled exactly as your database spells it — open it in a chart, do not trust the field. A misspelling turns the whole feature off without telling you, and the results still look plausible.

!Important

Do not confuse this with Base indexes in Symbol → Categories. Those are a per-market setting that controls the index used by the Relative Strength indicator, by composites built through the Composite calculation option, and by Beta. They are a different setting, in a different dialog, doing a different job. Both involve a “reference index” and they are not interchangeable.

The related UI feature, Composite recalculation, solves the same problem in its own way: it uses a market’s base index quotation dates as the master calendar and looks for each stock’s matching quotes, precisely because not all stocks are quoted every business day. Its documentation carries a warning worth transferring to your own work — automatic composite recalculation only makes sense if you follow the whole exchange. Part 16 takes this up properly.

Formula: the alignment audit

Goal

Produce, for every symbol in a watch list, a count of how many bars of a comparison against one reference symbol are real and how many are manufactured, split by cause — and then let you drop into a bar-by-bar view of any symbol that looks wrong.

This formula does not draw anything or generate any signal. It exists so that the sentence “this comparison is sound” can be replaced by a number.

Complete formula

Complete runnable AFL

alignment-audit.afl

// ===========================================================================
//  Cross-symbol alignment audit
//
//  Answers one question for every symbol you run it over: on how many bars is
//  the reference symbol's price a real quote, and on how many is it something
//  AmiBroker manufactured so that the two series could share a bar grid?
//
//  HOW TO RUN
//    Analysis window. Apply to: the watch list you actually study.
//    Range: All quotations - a shorter range hides exactly the gaps you are
//    looking for. Ctrl+R (Parameters) sets the reference symbol and the mode.
//
//    Summary mode gives one row per symbol. Detail mode gives one row per
//    suspect bar for the selected symbol, which is what you read when the
//    summary says something is wrong and you want to see where.
//
//  THE TECHNIQUE
//    Foreign() is called twice on the same field. With fixup = 0 the holes stay
//    Null, so the array shows where the reference symbol genuinely has no bar.
//    With fixup = 1 the holes are filled from the previous Close. Comparing the
//    two arrays separates real quotes from manufactured ones, which nothing in
//    the chart itself will ever tell you.
//
//    Three different causes are reported separately, because the fixes differ:
//      before listing        - the reference symbol had not started quoting yet
//      after last quote      - the reference symbol stopped (delisted, renamed,
//                              or simply not updated in your database)
//      padded                - both were listed, but the reference did not
//                              trade that day: holiday, halt, or a thin issue
//
//  RUN IT BOTH WAYS
//    This formula sees holes in the REFERENCE symbol only. Foreign() aligns the
//    reference to the current symbol, so bars that exist in the reference but
//    not in the current symbol have already been dropped before any of this
//    code runs, and cannot be counted from here. To measure that direction,
//    run the audit again with the two symbols swapped: put the reference symbol
//    in the Analysis filter and name the stock as the reference. The two bar
//    counts should agree. When they do not, the difference is the number of
//    bars one side is silently discarding.
//
//  ASSUMPTIONS
//    - Data holes and non-trading days are indistinguishable from inside AFL.
//      A count of padded bars is evidence that something needs checking, not a
//      verdict about which of the two it was.
// ===========================================================================

ReferenceSymbol = ParamStr(    "Reference symbol", "^GSPC" );
DetailMode      = ParamToggle( "Report", "Summary per symbol|Detail: suspect bars", 0 );

// Two readings of the same field. The only difference is the fixup argument.
ReferenceRaw    = Foreign( ReferenceSymbol, "C", 0 );
ReferencePadded = Foreign( ReferenceSymbol, "C", 1 );
ReferenceVolume = Foreign( ReferenceSymbol, "V", 1 );

HasRealBar = NOT IsNull( ReferenceRaw );

// Cum() counts qualifying bars from the start of the array, so a running count
// of zero means the reference symbol has not produced its first quote yet.
RealSoFar     = Cum( HasRealBar );
BeforeListing = RealSoFar == 0;

FirstRealBar  = RealSoFar == 1;
LastRealIndex = LastValue( ValueWhen( HasRealBar, BarIndex() ) );
AfterLastQuote = BarIndex() > LastRealIndex;

// A padded bar is one where both symbols were live but only one of them traded.
PaddedBar = NOT HasRealBar AND NOT BeforeListing AND NOT AfterLastQuote;

SuspectBar = BeforeListing OR AfterLastQuote OR PaddedBar;

// -- per-symbol totals -----------------------------------------------------

HomeBars        = LastValue( Cum( 1 ) );
ReferenceBars   = LastValue( RealSoFar );
PaddedCount     = LastValue( Cum( PaddedBar ) );
PreListingCount = LastValue( Cum( BeforeListing ) );
PostQuoteCount  = LastValue( Cum( AfterLastQuote ) );
PaddedPercent   = 100 * PaddedCount / Max( HomeBars, 1 );

FirstRefDate = DateTimeToStr( LastValue( ValueWhen( FirstRealBar, DateTime() ) ) );
LastRefDate  = DateTimeToStr( LastValue( ValueWhen( HasRealBar,   DateTime() ) ) );

// The verdict is written in words rather than signalled by a colour, so the
// table stays readable when it is printed, exported or read aloud.
Verdict = WriteIf( ReferenceBars == 0,
                   "NO REFERENCE DATA - check the ticker spelling",
          WriteIf( PreListingCount > 0 AND PostQuoteCount > 0,
                   "reference missing at both ends of the history",
          WriteIf( PreListingCount > 0,
                   "reference starts later than this symbol",
          WriteIf( PostQuoteCount > 0,
                   "reference stops before this symbol",
          WriteIf( PaddedCount > 0,
                   "calendars differ on some bars",
                   "aligned on every bar in range" ) ) ) ) );

// -- output ----------------------------------------------------------------

Filter = IIf( DetailMode, SuspectBar, Status( "lastbarinrange" ) );

// The default Ticker and Date columns are switched off and replaced by explicit
// ones, so that the column numbers passed to SetSortColumns() below are
// unambiguous: what you count in this file is what the table shows.
SetOption( "NoDefaultColumns", True );

AddTextColumn( Name(), "Symbol", 1.0, colorDefault, colorDefault, 80 );   // column 1
AddColumn( DateTime(), "Date", formatDateTime );                          // column 2
AddTextColumn( ReferenceSymbol, "Reference", 1.0, colorDefault, colorDefault, 90 ); // column 3

if ( DetailMode )
{
    BarVerdict = WriteIf( BeforeListing, "reference not listed yet",
                 WriteIf( AfterLastQuote, "reference stopped quoting",
                 WriteIf( PaddedBar,      "padded - reference did not trade",
                                          "real bar on both sides" ) ) );

    AddColumn( Close, "This symbol close", 1.2 );
    AddColumn( ReferencePadded, "Reference close used", 1.2 );
    AddColumn( ReferenceVolume, "Reference volume", 1.0 );
    AddTextColumn( BarVerdict, "What this bar is", 1.0, colorDefault, colorDefault, 210 );
}
else
{
    AddColumn( HomeBars,        "Bars, this symbol", 1.0 );            // column 4
    AddColumn( ReferenceBars,   "Bars, real reference quotes", 1.0 );  // column 5
    AddColumn( PaddedCount,     "Padded bars", 1.0 );                  // column 6
    AddColumn( PaddedPercent,   "Padded %", 1.2 );                     // column 7
    AddColumn( PreListingCount, "Bars before reference listed", 1.0 ); // column 8
    AddColumn( PostQuoteCount,  "Bars after reference stopped", 1.0 ); // column 9
    AddTextColumn( FirstRefDate, "First reference quote", 1.0, colorDefault, colorDefault, 120 );
    AddTextColumn( LastRefDate,  "Last reference quote",  1.0, colorDefault, colorDefault, 120 );
    AddTextColumn( Verdict,      "Verdict",               1.0, colorDefault, colorDefault, 260 );

    // Worst offenders first: sort by the padded-bar count, descending.
    SetSortColumns( -6 );
}

Download alignment-audit.afl136 lines

How it works

The technique is one idea used four times: call Foreign() twice on the same field, once with fixup = 0 and once with fixup = 1. The unpadded copy shows where the reference symbol genuinely has no bar. Everything else follows from comparing them.

The three causes are separated because their fixes differ. Cum( HasRealBar ) counts real bars from the beginning of the array, so a running count of zero means the reference had not started quoting yet. ValueWhen( HasRealBar, BarIndex() ) holds the bar number of the most recent real bar, so LastValue() of it is the last one in the whole array; anything beyond that is the stale-benchmark case. What is left — no real bar, but inside the reference symbol’s lifetime — is an ordinary padded bar, and that is the number you compare across symbols.

Summary mode reports one row per symbol on the last bar in range. Detail mode reports one row per suspect bar for whichever symbol you are looking at, with a text column saying which of the three cases each bar is. The default Ticker and Date columns are switched off and replaced with explicit ones so that the column numbers passed to SetSortColumns() mean what the file says they mean.

The verdict column is written in words. That is deliberate: an exploration result gets exported, pasted into a document and read by people who did not write it, and a colour-coded row loses its meaning on the way.

Key functions

• Foreign( ticker, field, fixup ) — called with both 0 and 1 to separate real data from filled data.
• ValueWhen( condition, array ) — used here on BarIndex() to find the position of the most recent bar satisfying a condition.
• DateTimeToStr( number ) — turns a DateTime value into a printable string for a text column.
• Status( "lastbarinrange" ) — true on the final bar of the analysis range, which is how a per-bar formula produces one row per symbol.
• SetOption( "NoDefaultColumns", True ) — removes the automatic Ticker and Date/Time columns so the numbering is explicit.

Expected result

In summary mode, one row per symbol with bar counts, a padded percentage, the first and last real reference quotes, and a verdict. Sorted so the worst offenders are at the top.

=Expected result

On a domestic watch list against a domestic index, most rows should read aligned on every bar in range or show a padded percentage well under one per cent. A row with a double-digit padded percentage, or a reference stops before this symbol verdict, is telling you that any comparison you have drawn against that pair is partly fiction.

Test it

1. Set the reference symbol to something that does not exist. Every row should read NO REFERENCE DATA, and the bar counts for the reference should be zero. That confirms the audit detects the failure it is most likely to meet.
2. Set the reference symbol to the symbol being scanned, with Apply to set to one symbol. Padded bars must be zero and the verdict must be aligned on every bar in range. If a symbol is not perfectly aligned with itself, the fault is in the formula, not the data.
3. Switch to Detail mode on the worst offender and look at the dates. Do they fall on recognisable public holidays? Then it is a calendar difference. Are they scattered at random? Then it is more likely a data defect worth fixing at source.
4. Run it with Range set to a recent period, then again with Range set to all quotations. Short ranges hide listing-date problems entirely.

Common errors

Symptom	Cause
Every row reads NO REFERENCE DATA	Ticker spelling. Open the reference in a chart before blaming the formula
Padded percentages far higher than expected	The reference trades on a different calendar from the watch list — check whether it should be the reference at all
The audit shows perfect alignment but a comparison still looks wrong	You have only checked one direction. See below
No rows at all in summary mode	The analysis range excludes the last bar, or Filter is being suppressed by an empty range
Detail mode returns thousands of rows	That is the answer. The symbol has thousands of suspect bars

Extension

Add a column reporting the largest run of consecutive padded bars, not just the total. Ten scattered holidays and one ten-day suspension have the same total and very different implications. BarsSince() on the real-bar condition gets you most of the way there.

The verification technique, as a procedure

Run this before any cross-symbol result goes into a decision or a document. It takes a few minutes and it is the difference between a claim and a checked claim.

1. Count, in both directions. Run the audit in summary mode over your watch list with the benchmark as the reference. Then run it again the other way round: put the benchmark in the Analysis filter and name one of your symbols as the reference. The bar counts from the two runs should reconcile. Where they do not, the difference is the number of bars one side is discarding, and it is invisible from the first run alone.
2. Check the ends before the middle. Look at the first reference quote and last reference quote columns before anything else. A benchmark that starts late shortens your study; a benchmark that stopped makes everything after that date meaningless.
3. Spot-check the dates. Detail mode on the worst symbol. Named holidays are a calendar difference you can decide to live with. Random gaps are a data problem you should fix rather than pad over.
4. Recompute one quantity from two places. Take a single indicator on the benchmark — a long moving average is ideal — and compute it on the benchmark’s own chart and through Foreign() from your symbol. If the two last values disagree, the current symbol’s calendar is reshaping the benchmark, and by how much.
5. Decide, and write the decision down. The options are: restrict the date range to the overlap, choose a different benchmark whose calendar matches, switch on Pad and align all data to reference symbol if your work is one of its two documented use cases, or handle the holes yourself with fixup = 0 and explicit IsNull() tests. All four are legitimate. Doing nothing, having not looked, is not.

▷Exercise

Do step 4 now, on the pair you are most likely to use in real work. Note both numbers. If they agree, you have earned the right to stop thinking about alignment for that pair. If they do not, you have just found out something about every comparison you have already drawn with it.

Recap

Two symbols rarely share a calendar, and Foreign() reconciles them by dropping bars the current symbol lacks and padding bars the foreign symbol lacks. The operation is asymmetric, so the same pair can produce two different answers depending on which symbol the formula runs on. Padded bars carry the previous close with zero volume and zero range, which quietly biases volume filters, volatility measures and correlations, and can manufacture crossings on days that did not trade.

Listing and delisting are the two ends of the same problem, and a stale benchmark is the most dangerous case because it makes relative strength look permanently good.

Pad and align all data to reference symbol on the Backtester settings General tab addresses this for Analysis runs. It is off by default, it has a documented cost, it is intended for market-timing systems and composite building, and it fails silently if the reference symbol name is wrong.

The habit that matters is the audit: two Foreign() calls with different fixup values, run in both directions, producing a number you can quote.

Check your understanding

Question 1. A thin share has no bar on a Tuesday when the index traded. What happens to the index's Tuesday bar when you call Foreign("^GSPC","C") from the share's chart?

• It is padded with the previous close
• It is dropped, because the current symbol has no bar on that date
• It becomes Null and the fixup argument decides what replaces it
• AmiBroker inserts a bar into the share's data

Show the answer and why

Answer: It is dropped, because the current symbol has no bar on that date

Foreign() fits the foreign symbol to the CURRENT symbol's calendar. Bars that exist only in the foreign symbol have nowhere to go and are removed. Padding applies to the opposite case: bars the foreign symbol is missing.

Question 2. A relative strength line against a benchmark starts rising smoothly and never falls again. What should you check first?

• The rebasing anchor date
• The date of the benchmark's last real quote
• Whether the benchmark includes dividends
• The moving average length

Show the answer and why

Answer: The date of the benchmark's last real quote

A benchmark that stopped updating is padded forward at its last close forever. The ratio then becomes a copy of the numerator, so it rises exactly when the stock rises. The audit formula prints the last real quote date as a column for this reason.

Question 3. Which statements about "Pad and align all data to reference symbol" are correct? Select all that apply.

• It is on by default
• If the reference symbol does not exist, data is not padded and no error is shown
• The documentation lists market-timing systems and composite building as its intended uses
• It may change indicator values where the data has holes

Show the answer and why

Answer: If the reference symbol does not exist, data is not padded and no error is shown, The documentation lists market-timing systems and composite building as its intended uses, It may change indicator values where the data has holes

The manual states plainly that the setting is off by default and asks you to use it responsibly. The silent failure on a missing reference symbol is the trap worth remembering.

Question 4. Why can a 200-bar moving average of an index have two different values on the same date?

• Because moving averages are path-dependent
• Because QuickAFL truncates the array differently in each context
• Because computed through Foreign() from a symbol with data holes, the index bars on those dates have already been dropped
• It cannot; the two values are always identical

Show the answer and why

Answer: Because computed through Foreign() from a symbol with data holes, the index bars on those dates have already been dropped

The average is taken over whatever bars survived synchronisation. A current symbol with holes removes index bars before the average is computed, so the same indicator on the same symbol on the same date gives a different answer depending on where you stand.

Question 5. The audit reports zero padded bars for a pair. What has that established?

• That the comparison is fully aligned in both directions
• That the reference symbol has a bar on every date the current symbol has a bar
• That the two symbols have the same number of bars
• That neither symbol has data defects

Show the answer and why

Answer: That the reference symbol has a bar on every date the current symbol has a bar

The audit sees holes in the reference symbol only, because bars the current symbol lacks were removed before the formula ran. Establishing the other direction requires running it again with the roles swapped.

Check my answersClear