// 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();