Data Defects in Practice

A corrupt file announces itself. A corrupt price series does not. It loads, it charts, it computes, and it returns a number with two decimal places that looks exactly like a correct one. By the end of this lesson you will recognise the six defect classes that account for most of that damage, know roughly how each one gets in, and have a mechanical check that surfaces all of them in one pass.

Why a defect costs more than it looks

Consider a single bad print that inflates one day’s high by fifteen per cent on an otherwise ordinary instrument. That one number is now inside:

• the true range for that bar, and therefore inside ATR for the next fourteen or twenty bars, depending on your period;
• any Bollinger-style band, because the standard deviation of the last twenty closes moved;
• any breakout rule referencing the highest high of the last N bars, for the next N bars;
• any normalisation that divides by volatility, which quietly shrinks every position size for as long as the contaminated ATR persists;
• any percentile or ranking computed across a universe, because this symbol is now an outlier.

One wrong number, dozens of wrong downstream values, none of them flagged. This is why data checking is not a chore you do once — it is a step in the research loop.

Where a defect enters and what it touches

1. 1Bad printOne erroneous trade, or a vendor error
2. 2BarHigh, low or close is wrong
3. 3IndicatorsATR, bands, extremes — for N bars
4. 4RuleTriggers or fails to trigger
5. 5ConclusionA number that looks fine

Nothing in this chain raises an error. The defect is only visible if you look for it.

The six classes

Missing bars and holidays

A bar that should exist does not. Holidays are the benign version: an exchange was closed, so there is nothing to record, and every symbol on that exchange is missing the same day. The malignant version is a bar missing from one symbol and present in its neighbours — a failed download, a symbol that was halted, or a vendor gap.

Distinguishing the two needs a reference. That is exactly the design of AmiBroker’s Tools → Database Purify tool: you nominate a reference symbol that trades on every session — an index is the usual choice — and it reports, for each symbol you check, quotes present in the reference but missing from the symbol. It can also report the reverse: “extra quotes” present in the symbol but absent from the reference, which usually means your reference symbol was a poor choice rather than that the symbol is wrong.

Missing bars distort anything counted in bars. A twenty-bar moving average computed over a series with three missing days spans twenty-three calendar days for that stretch, and nothing tells you.

⌀Data quality warning

There is a second, subtler source of manufactured bars. When AFL reads another symbol with Foreign(), the default behaviour — the fixup parameter set to 1 — fills missing bars by setting all four OHLC fields to the previous bar’s close and volume to zero. That is a sensible default for aligning two calendars, but it means a cross-symbol study can contain bars that were invented at read time. They are recognisable: flat, zero-volume, and equal to the preceding close. Part 15 covers the alignment problem properly.

Bad ticks and outliers

A single erroneous print — a fat-finger order, a mis-keyed decimal point, a trade reported against the wrong symbol — enters the bar as a new high or low, or occasionally as the close. Some feeds are filtered by the vendor and some are documented as unfiltered; the official data-source listing marks at least one major feed as unfiltered explicitly. Do not assume filtering.

The official troubleshooting advice for a bad tick in a plugin-fed database is worth knowing: force a backfill for that symbol and hope the vendor has since corrected its own records. That works for feeds that clean up after themselves and does nothing for feeds that do not.

Detection is statistical rather than definitional. Two independent tests catch most cases:

• a close-to-close change far larger than the instrument’s normal daily move;
• a bar range far larger than the recent average true range, which catches a bad high or low even when the close is fine.

Both need a threshold, and any threshold you pick will produce false positives on genuine news days. That is acceptable: the output is a list of bars to look at, not a list of bars to delete.

Duplicate bars

Two bars carrying the same timestamp. The common causes are importing the same file twice, importing overlapping files, and tick data in which several trades share a timestamp. AmiBroker’s importer has commands aimed squarely at the last case — $TICKMODE for importing duplicate-timestamped ticks, and $ALLOW99SECONDS for files whose seconds field is out of range.

⚠Warning

$TICKMODE 1 deletes every quote already present in the database for the symbols in the import file and re-imports from scratch. The official documentation states this warning twice. Never run an import you have not read the format definition for, and never run one against a database you have not backed up.

Duplicates are cheap to detect — compare each bar’s timestamp with the previous one — and they corrupt any bar-counting logic, because the same moment now consumes two positions in every array.

Zero-volume bars

A bar reporting no trades at all. There are at least three different things this can mean, and they need different responses:

1. Genuinely no trading. Common on illiquid instruments and on intraday bars in quiet periods. The bar is correct.
2. A padded non-trading day. Some vendors deliberately insert filler bars so that every symbol shares one calendar. It is a named, configurable feature rather than an accident: one commercial data plugin exposes it as a Date Padding setting in its own configuration dialog. The bar is a convenience, not an observation.
3. A manufactured bar, as produced by the Foreign() fixup described above.

All three look identical in the data. Only the first is a real observation, and a liquidity filter that treats zero as a low number rather than as “no information” will happily let case 2 or 3 through.

Stale and repeated prices

A stale bar is one where nothing moved because nothing was received: open, high, low and close are all the same number, and that number equals the previous close. It is the signature of a feed that stopped while the clock kept running.

Distinguish it from a genuinely flat bar, where high equals low because one price traded repeatedly — real on very illiquid instruments and on some intraday bars. The discriminator is the comparison with the previous close, plus the run length: a single flat bar is unremarkable, whereas nine consecutive identical bars is a feed that failed.

Stale bars are quietly destructive because they look like low volatility. Every volatility-based position-sizing rule will size up into them, which is precisely the wrong direction: the market did not become calm, your data became blind.

Impossible OHLC relationships

Some defects are not statistical at all — they are arithmetically impossible. AmiBroker’s Database Purify tool checks for exactly five of them:

• Open > High
• Close > High
• Open < Low
• Close < Low
• Low > High

Any of these means the bar was assembled wrongly somewhere upstream. They occur more often than you would expect, usually when a vendor adjusts different fields with different factors — an adjusted close computed on a different basis from the OHLC can land below the low.

The repair that hides the problem

Here is a genuinely surprising piece of AmiBroker behaviour, and one that would be irresponsible to leave out of a data-quality lesson.

Unless the ASCII import format explicitly sets $ALLOWNEG 1, the importer performs its own range checking and fix-up on incoming prices, documented as:

if( open == 0 ) open = close;
if( high < max( open, close ) ) high = max( open, close );
if( low == 0 )  low  = min( open, close );

Read that carefully. A file arriving with a zero open, or with a high below the close, is silently repaired on the way in. The database ends up self-consistent, and the underlying problem — a broken vendor file — leaves no trace.

✕Common mistake

Do not read that fix-up as protection. It corrects three specific cases and leaves the others: Low > High and Close < Low pass through untouched. And a zero open replaced by the close is not the real open — it is a plausible substitute. If your Database Purify run comes back clean, that is evidence about the database, not about the file it came from. Check the file too, at least once, when you adopt a new source.

Related switches worth knowing: $STRICT 1 enforces that open, high and low are all greater than zero, and $ALLOWNEG 1 disables the OHLC checking entirely — which you need for spreads and some derived series that legitimately go negative, and which you should never set on ordinary equity data.

AmiBroker’s own detector

Tools → Database Purify is the built-in first pass. Its controls, in the order you meet them:

• Reference symbol — the instrument assumed to trade every session.
• Apply to — all symbols, the current symbol, or a category filter.
• Quotes — all, or the last n quotes of the reference symbol.
• Report invalid OHLC relationship — the five cases above. On by default.
• Report missing quotes — present in the reference, absent here. On by default.
• Report missing quotes at the beginning — symbols with a shorter history than the reference. Off by default, and the guide advises using it sparingly, since a later listing date is normal rather than a defect.
• Report possible splits — gaps beyond a detection threshold, with an estimated ratio.
• Report extra quotes — present here, absent from the reference.

Results appear in a list. Right-click to add the flagged symbols to a watch list or to copy the list to the clipboard, and double-click any entry to jump to that bar on the chart — which is how you decide whether a flag is a defect or a genuine event.

A check you can run over the whole database

Database Purify answers “does this bar break a rule?”. The exploration below answers a different question: “how much of each defect class does each symbol contain?” — a per-symbol census you can sort, compare and re-run after every data update.

Goal

Produce one row per symbol summarising bar count, coverage dates, and counts for each defect class, so that the symbols worth investigating sort to the top instead of having to be noticed by eye.

Complete formula

Complete runnable AFL

data-sanity-check.afl

// data-sanity-check.afl
// Part 2 - Data Defects in Practice
//
// One row per symbol, summarising the data defects that are cheap to detect
// mechanically. It repairs nothing. It tells you where to look.
//
// Assumptions:
//   - Analysis window, Exploration mode, Range: All quotations, applied to a
//     watch list or to the whole database.
//   - Daily bars or higher. On intraday data the flat-bar and stale-bar counts
//     will be far larger and mean something different, because a quiet minute
//     genuinely has no trades in it.
//   - The thresholds below are triage, not truth. Every count this produces is
//     a question to investigate, not a verdict.

_SECTION_BEGIN("Data sanity check");

// ---- Thresholds -----------------------------------------------------------
ExtremeMovePercent = 25;   // close-to-close move, in percent, that deserves a look
RangeAtrMultiple   = 5;    // bar range this many times the recent average true range
AtrPeriod          = 20;

// ---- Per-bar defect flags -------------------------------------------------

// 1. Impossible OHLC relationships. These are the five cases AmiBroker's own
//    Database Purify tool reports, restated in AFL so you can count them.
BadOhlc = Low   > High
       OR Open  > High
       OR Close > High
       OR Open  < Low
       OR Close < Low;

// 2. Non-positive prices. A zero close is not a low price, it is a missing one.
BadPrice = Open <= 0 OR High <= 0 OR Low <= 0 OR Close <= 0;

// 3. Zero-volume bars. Sometimes real, sometimes a padded non-trading day,
//    sometimes a bar that was manufactured to line two calendars up.
ZeroVolume = Volume <= 0;

// 4. Flat bars: the bar has no range at all.
FlatBar = High == Low;

// 5. Stale bars: flat AND identical to the previous close. That is the
//    signature of a feed that stopped while the clock kept running.
PreviousClose = Ref( Close, -1 );
StaleBar      = FlatBar AND Open == Close AND Close == PreviousClose;

// 6. Duplicate timestamps: two bars claiming the same moment in time.
DuplicateStamp = DateTime() == Ref( DateTime(), -1 );

// 7. Outliers, tested two independent ways, because a bad print can sit in the
//    close or only in the high or the low.
SafePreviousClose  = IIf( PreviousClose > 0, PreviousClose, Close );
CloseChangePercent = Nz( 100 * abs( Close - PreviousClose ) / SafePreviousClose );
ExtremeClose       = CloseChangePercent > ExtremeMovePercent;

AverageTrueRange   = ATR( AtrPeriod );
ExtremeRange       = Nz( ( High - Low ) > RangeAtrMultiple * AverageTrueRange );

// ---- One row per symbol ---------------------------------------------------
Filter = Status( "lastbarinrange" );

AddColumn( Cum( 1 ),              "Bars",        1.0 );
AddColumn( ValueWhen( Status( "firstbarinrange" ), DateTime() ),
                                  "First bar",   formatDateTime );
AddColumn( DateTime(),            "Last bar",    formatDateTime );
AddColumn( Cum( BadOhlc ),        "Bad OHLC",    1.0 );
AddColumn( Cum( BadPrice ),       "Bad price",   1.0 );
AddColumn( Cum( DuplicateStamp ), "Dup stamp",   1.0 );
AddColumn( Cum( ZeroVolume ),     "Zero volume", 1.0 );
AddColumn( Cum( FlatBar ),        "Flat bars",   1.0 );
AddColumn( Cum( StaleBar ),       "Stale bars",  1.0 );
AddColumn( Cum( ExtremeClose ),   "Big moves",   1.0 );
AddColumn( Cum( ExtremeRange ),   "Wide bars",   1.0 );
AddColumn( Highest( CloseChangePercent ), "Worst move %", 1.1 );

_SECTION_END();

Download data-sanity-check.afl77 lines

✦Tip

This is an AmiBroker Exploration. Running it needs a database (Part 3) and the Analysis window (Part 12). Read it now for the checks it encodes — every one of them is a translation of something in this lesson — and run it when you get there.

How it works

The formula is in three parts. The first sets thresholds as named constants at the top, where they can be changed without hunting through expressions. The second builds one Boolean array per defect class, each of which is true on the bars where that defect is present. The third collapses those arrays into per-symbol totals.

The collapse is the interesting trick. Cum() accumulates an array from the first bar, so Cum( BadOhlc ) holds a running count of impossible bars. Setting Filter = Status( "lastbarinrange" ) reports only the final bar of each symbol, at which point every running count has finished counting. One row per symbol, with totals.

Key functions

• Ref( array, -1 ) returns the previous bar’s value, which is how the stale-bar and duplicate-timestamp tests compare a bar with its predecessor.
• Nz( x ) converts Null, NaN and infinity to zero. It appears wherever a warm-up bar or a division could otherwise poison a total.
• Cum( array ) accumulates. On a Boolean array it counts occurrences.
• Status( "lastbarinrange" ) and Status( "firstbarinrange" ) return 1 on the last and first bar of the analysis range respectively.
• ValueWhen( condition, array ) carries forward the value the array had when the condition was last true — here, the timestamp of the first bar.
• DateTime() returns encoded date/time values, formatted for display with the formatDateTime constant.

Expected result

One row per symbol. On well-maintained end-of-day equity data, Bad OHLC, Bad price and Dup stamp should all be zero, and you should treat any non-zero value in those three columns as something to explain rather than something to accept. Zero volume, Flat bars and Stale bars will be small but non-zero on thinly traded instruments. Big moves will be non-zero for almost everything — genuine large moves exist.

Test it

Take the symbol with the largest Worst move % and look at that bar on a chart, or in the Quote Editor, which shows every bar regardless of any display filtering. Either the move is real, in which case you have learned something about the instrument, or it is not, in which case you have found a defect and the check works. Both outcomes validate the tool.

Common errors

• Running it on intraday data and being alarmed by the flat-bar count. A quiet minute legitimately has no range; the thresholds in this formula are calibrated for daily bars.
• Reading Zero volume as a defect count. It is a count of bars needing explanation, and on an illiquid instrument the explanation is usually “nothing traded”.
• Running it before updating the database and concluding that every symbol is stale.
• Expecting it to find missing bars. It cannot: a bar that is absent is not in the array to be counted. Missing bars need a reference symbol, which is why Database Purify exists.

Extension

Add a column that expresses each count as a percentage of the bar count, so that a twenty-year series and a six-month series can be compared honestly. A symbol with forty stale bars out of five thousand is in a different condition from one with forty out of ninety.

What to do with what you find

A defect list is only useful if it ends in a decision. In rough order of preference:

1. Re-download the affected symbol. The cheapest fix, and the only one that recovers the true value rather than a plausible one. For plugin-fed databases this means forcing a backfill.
2. Correct the bar by hand in the Quote Editor (Symbol → Quote Editor), which can add, edit and delete quotes. Record what you changed and why.
3. Delete the bar, via Edit → Delete Quotation for one symbol or Edit → Delete Session for that date across the whole database. Deletion is not reversible, so back up first.
4. Exclude the symbol from the study and say so in your write-up.
5. Accept it and document it. Sometimes the defect is small, bounded, and cheaper to describe than to fix. That is a legitimate choice as long as it is written down.

What is never acceptable is option zero: noticing the defect and proceeding without recording it. Six months later, neither you nor anyone reading your results can tell whether the conclusion survived it.

Recap

Data defects are silent by construction: they produce values, not errors. Six classes cover most of the damage — missing bars, outliers, duplicates, zero-volume bars, stale prints and impossible OHLC relationships — and each needs a different detection method and a different response. AmiBroker gives you a built-in cross-symbol detector in Database Purify and a Quote Editor for repairs, and the importer performs a partial silent fix-up that can hide the evidence of a bad source file. Detection is mechanical and cheap. The decision about what to do afterwards is yours, and belongs in your notes.

The next lesson deals with a defect that no amount of checking a symbol can find, because the problem is which symbols are there at all.

Check your understanding

Question 1. A daily bar has Open = High = Low = Close = 41.20, Volume = 0, and the previous close was also 41.20. What is the most likely explanation on a liquid large-cap stock?

• The stock traded flat all day
• A stale or padded bar where no data was received
• A stock split occurred that day
• The exchange rounded all trades to the same price

Show the answer and why

Answer: A stale or padded bar where no data was received

On a liquid instrument, a day with genuinely zero range and zero volume is implausible. The combination of no range, no volume and equality with the previous close is the standard signature of a padded non-trading day or a feed that stopped updating.

Question 2. Your ASCII file contains a bar with High below Close. You import it without $ALLOWNEG. What does the database contain afterwards?

• The bar exactly as it was in the file
• No bar at all, because the import rejected it
• A bar whose High has been raised to the greater of Open and Close
• A bar with all four prices set to the close

Show the answer and why

Answer: A bar whose High has been raised to the greater of Open and Close

The documented fix-up sets high = max(open, close) whenever high is below that maximum. The bar becomes self-consistent, no error is reported, and the fact that the source file was wrong leaves no trace in the database.

Question 3. Which defects can a per-symbol exploration of stored bars detect on its own? Select all that apply.

• Impossible OHLC relationships
• Bars missing relative to the rest of the market
• Duplicate timestamps
• Stale bars repeating the previous close

Show the answer and why

Answer: Impossible OHLC relationships, Duplicate timestamps, Stale bars repeating the previous close

Everything except missing bars can be found by examining the bars that are present. A missing bar is not in the array at all, so detecting it requires comparison with a reference symbol that does have it — which is what Database Purify does.

Question 4. A single bad print inflates one day’s high by 15 per cent. You use ATR(20) to size positions. What is the effect?

• One position is sized wrongly, on that day only
• Position sizes are reduced for roughly the next twenty bars
• Nothing, because ATR uses the close
• All positions are reduced permanently

Show the answer and why

Answer: Position sizes are reduced for roughly the next twenty bars

True range for the affected bar includes the inflated high, and a twenty-period average carries that bar for about twenty bars. Volatility-normalised sizing then reduces size for that whole stretch, from a single wrong number.

Check my answersClear