Lab: Prove That Bad Data Breaks Analysis

Five lessons have argued that data quality decides whether analysis means anything. An argument is not evidence. In this lab you will produce the evidence yourself: take a real price series, damage a copy of it in four specific, documented ways, run one identical rule over both, and measure exactly how much the conclusion moves.

The instrument does not matter. The point is not what the rule concludes about it — it is that the rule concludes two different things about the same instrument depending on which copy of the data it was handed.

What you are going to do

1. 1Get one clean series250+ daily bars, free EOD data
2. 2Copy itIdentical, side by side
3. 3Damage the copyFour defects, specified exactly
4. 4Run one rule on bothSame rule, same parameters
5. 5Measure and attributeWhich defect caused which difference

Deliverables

By the end you should have four things. Keep them; the checklist in particular is used again in Part 12 and in the capstone.

1. A spreadsheet (or AmiBroker database) holding the clean series and the damaged series side by side.
2. A filled-in results table comparing six measurements across the two series.
3. A short written failure-mode note — five fields, about a paragraph in total.
4. Your own copy of the data-sanity checklist at the end of this lab, adapted to the data sources you actually use.

What you need

• A price file. At least 250 daily rows for one liquid instrument, with date, open, high, low, close and volume columns. Any free end-of-day source will do, including a CSV exported from a broker platform. Five hundred rows is more comfortable than 250.
• A spreadsheet. Any one. This is Track A and it is the main path — you do not need AmiBroker installed, and you do not need to have written a line of AFL.
• Optionally, AmiBroker. Track B at the end repeats the same experiment inside AmiBroker for readers who already have it, or who come back after Part 3 and Part 12.

▷Exercise

Do not skip getting real data. The whole value of this lab is that the clean series is genuine and the damage is yours, so that you know with certainty which differences you caused. Using invented prices for both sides would prove nothing.

Step 1 — Load the clean series

Open the file in your spreadsheet and arrange it so that row 1 holds headers and the data starts on row 2, oldest bar first, in these columns:

A: Date    B: Open    C: High    D: Low    E: Close    F: Volume

Sort by date ascending if it is not already. Delete any summary or footer rows. Count your data rows and write the number down — the recipe below refers to bar numbers, and bar n is on spreadsheet row n + 1.

Step 2 — Make the damaged copy

Select A1:F(last row), copy, and paste into N1 as values, so that the damaged block is independent of the clean one. You now have two identical blocks:

Clean:   A Date  B Open  C High  D Low  E Close  F Volume
Damaged: N Date  O Open  P High  Q Low  R Close  S Volume

Before damaging anything, confirm they are identical. A quick way: put =SUM(E:E)-SUM(R:R) in an empty cell and check it reads zero.

Step 3 — Apply four defects, in order

Each defect below is a real failure mode from the previous lessons. Apply them to the damaged block only, in the order given, and always to the values already there rather than to the clean originals.

The quickest way to scale a block of cells in place is a scratch cell and a paste operation: type the factor into an empty cell, copy it, select the target range, and use Paste Special with the Multiply or Divide operation. Every mainstream spreadsheet offers both. If yours does not, compute the new values in a spare column and paste them back as values.

Defect 1 — an unadjusted 2-for-1 split at bar 120

In rows 2 to 120, multiply O, P, Q and R by 2, and divide S by 2.

This simulates the most common corporate-action failure: a split that the data source never applied, so the historical prices are still quoted in pre-split terms. The series now contains a fifty per cent overnight fall on row 121 that never happened.

Defect 2 — two bad high prints

In row 41 and in row 211, multiply P by 1.15 — leaving O, Q, R and S in those two rows exactly as they are.

This is a single erroneous print inflating the day’s high by fifteen per cent, and nothing else. The open, low and close are untouched, which is the whole point of the defect.

Defect 3 — a stale patch of seven bars

In rows 151 to 157, set O, P, Q and R all equal to the value in R150, and set S to zero.

This is a feed that stopped delivering while the clock kept running: seven bars with no range, no volume, and a price frozen at the last value received.

Defect 4 — a volume unit error

In rows 61 to 101, divide S by 100 — again applied to whatever is already there, so these rows carry both this defect and the volume half of Defect 1.

This is the same series reported in a different volume unit for part of its history — the kind of thing that happens when two files from different vintages are concatenated.

⚠Warning

Write down exactly what you did, including the row numbers. In fifteen minutes you will be matching differences back to defects, and a defect you cannot locate is a difference you cannot explain.

Step 4 — Define the rule, once

One rule, applied identically to both series. It is deliberately ordinary; it is a measuring instrument, not a recommendation.

State: the close is above its 50-bar simple moving average. Event: the bar on which the state changes from false to true.

Add these formulas to the clean block. Enter each in the row indicated and fill down to the last data row.

H51: =AVERAGE(E2:E51)          50-bar average of the clean close
I51: =IF(E51>H51,1,0)          clean state: 1 when above the average
J52: =IF(AND(I52=1,I51=0),1,0) clean event: the bar the state turned on
K3:  =100*(E3-E2)/E2           clean daily percentage change
L2:  =C2-D2                    clean bar range

Then the identical formulas on the damaged block:

U51: =AVERAGE(R2:R51)
V51: =IF(R51>U51,1,0)
W52: =IF(AND(V52=1,V51=0),1,0)
X3:  =100*(R3-R2)/R2
Y2:  =P2-Q2

And one column that compares them directly:

AA51: =IF(I51<>V51,1,0)        1 on every bar where the two disagree

Nothing about the rule differs between the two blocks. Every difference that follows is caused by the data.

Step 5 — Measure

Fill this table in from your own spreadsheet. Six measurements, two columns.

Measurement	Formula	Clean	Damaged
Bars in state	=SUM(I:I) / =SUM(V:V)
Cross-up events	=SUM(J:J) / =SUM(W:W)
Largest single-bar rise, %	=MAX(K:K) / =MAX(X:X)
Largest single-bar fall, %	=MIN(K:K) / =MIN(X:X)
Mean bar range	=AVERAGE(L:L) / =AVERAGE(Y:Y)
Bars where the state disagrees	=SUM(AA:AA)	—

Step 6 — Attribute each difference to a defect

This is the part that turns a demonstration into an investigation. For each row of your table that differs, decide which defect caused it. Sort or filter column AA to find the dates where the state disagrees, and compare them with the row numbers you damaged.

What you should find, and why:

• The largest single-bar fall in the damaged series will be about minus fifty per cent, at row 121. That is Defect 1 and nothing else. The clean series’ worst fall will be far smaller.
• The disagreements cluster in the fifty rows after row 121. Defect 1 did not only create one bad bar; it left the moving average carrying inflated pre-split prices for a full fifty-bar window afterwards, so the state is computed against an average that is too high. One defect, fifty wrong answers.
• A second, smaller cluster around rows 151 to 157. That is Defect 3: with the close frozen, the state stops responding, and whether it agrees with the clean series is accidental.
• The mean bar range is larger in the damaged series, but the state count is unaffected by Defect 2. The bad highs never touch the close, so a close-based rule cannot see them at all — while any range, ATR, volatility or breakout calculation is contaminated.
• Nothing at all changed because of Defect 4. The rule does not use volume, so a hundredfold volume error is completely invisible to it.

!Important

The last two findings are the most valuable thing in this lab. A defect is not “bad” or “harmless” in the abstract — it is harmful relative to a rule. Defect 2 is invisible to today’s rule and fatal to tomorrow’s volatility filter. Defect 4 is invisible to both and fatal to the liquidity screen you will write in Part 12. This is why data checking cannot be delegated to “does the chart look right”: a chart is one rule, and you will write many.

Step 7 — Write the failure-mode note

A paragraph, in whatever you use for notes. Five fields, every time:

1. What the defect was, in one sentence, including where it was located.
2. How it entered — for this lab, you put it there; in real work, name the plausible source.
3. What it changed in the measurements, with the numbers from your table.
4. Which kinds of rule it would damage, and which it would not touch.
5. How you would detect it in a series you had not damaged yourself.

Field 5 is the one that pays. Write it for all four defects before moving on, because it is the raw material for the checklist below.

Step 8 — Your data-sanity checklist

Copy this into your own notes and adapt it. It is the permanent product of this lab; the spreadsheet is not.

Before trusting a new data source, once:

1. Where did this data come from, and on what date did I obtain it?
2. Are the prices adjusted? For splits only, or for dividends as well? What does the vendor say, and does the data agree with what the vendor says?
3. What units is volume in? Compare one bar against an independent source.
4. Does the file include delisted instruments, or only currently listed ones?
5. What is the earliest date I actually have, per symbol, rather than in the marketing copy?

After every update, mechanically:

6. Any impossible OHLC relationships? (Low > High, Open or Close outside the range.)
7. Any non-positive prices?
8. Any duplicate timestamps?
9. Any bars missing relative to a reference symbol that trades every session?
10. How many zero-volume bars, and are they explicable for these instruments?
11. How many stale bars — no range, and equal to the previous close?
12. What is the largest single-bar move in the update, and is it real?

Before believing any result:

13. Which of the above did I check, and when?
14. Would any defect I found have changed this particular result? Which measurements would it have touched?
15. Is my universe construction written down alongside the result?

✦Tip

Items 6 to 12 are exactly what the exploration from the Data Defects in Practice lesson computes, which is why it is worth keeping. Item 9 is the one it cannot do, and is what Database Purify is for.

Track B — the same experiment inside AmiBroker

Skip this if AmiBroker is not installed yet. Come back after Part 3 and Part 12.

Import both series

Save the clean block as CLEAN.csv and the damaged block as DIRTY.csv, each with columns in the order date, open, high, low, close, volume, and no ticker column. That last detail is deliberate: when no field in the format definition names the ticker, AmiBroker’s ASCII importer uses the file name without path or extension as the ticker. Your two files therefore become symbols CLEAN and DIRTY without any extra work. If you would rather not rely on that, add a first column containing the literal text CLEAN or DIRTY on every row and map it to Ticker in step 2 below — both routes end in the same place.

Then use File → Import Wizard, which is a three-step dialog:

1. Pick files — select both CSV files.
2. Define fields — set the column combos to YMD (or DMY/MDY, matching your file), Open, High, Low, Close, Volume; set the separator to a comma; set Skip lines to 1 if your files have a header row; and tick Automatically add new symbols.
3. Additional settings — tick Add current settings to ASCII importer definitions and give the format a description and a file name if you want to reuse it. Without that tick, the mapping you just built is thrown away after this import.

Put both symbols in a watch list.

Run the rule on both

Complete runnable AFL

same-rule-two-series.afl

// same-rule-two-series.afl
// Part 2 - Lab: Prove That Bad Data Breaks Analysis
//
// Runs one fixed rule over whichever symbols the Analysis window is pointed at
// and reports the numbers the lab asks you to compare. Point it at a watch list
// holding your clean series and your damaged series, and read the two rows
// against each other.
//
// The rule is deliberately ordinary: "the close is above its 50-bar simple
// moving average". Nothing about it is a recommendation. It is a measuring
// instrument, chosen because it is simple enough that any difference between
// the two rows has to have come from the data rather than from the rule.
//
// Assumptions:
//   - Analysis window, Exploration mode, Range: All quotations.
//   - Both series cover the same instrument over the same dates, and differ
//     only by the defects you injected on purpose.
//   - Daily bars.

_SECTION_BEGIN("Same rule, two series");

MaPeriod  = 50;
AtrPeriod = 20;

Average    = MA( Close, MaPeriod );
InState    = Close > Average;           // a state: true over spans of bars
EntryEvent = Cross( Close, Average );   // an event: the bar it became true

PreviousClose      = Ref( Close, -1 );
SafePreviousClose  = IIf( PreviousClose > 0, PreviousClose, Close );
CloseChangePercent = Nz( 100 * ( Close - PreviousClose ) / SafePreviousClose );

// Cum( 1 ) counts bars, so on the last bar it holds the total bar count.
TotalBars    = Cum( 1 );
BarsInState  = Cum( InState );

FinalState = WriteIf( LastValue( InState ), "above the average", "below the average" );

Filter = Status( "lastbarinrange" );

AddColumn(     TotalBars,                 "Bars",                 1.0 );
AddColumn(     Cum( EntryEvent ),         "Cross-up events",      1.0 );
AddColumn(     BarsInState,               "Bars in state",        1.0 );
AddColumn(     100 * BarsInState / TotalBars, "% of bars in state", 1.1 );
AddColumn(     ATR( AtrPeriod ),          "ATR(20) on last bar",  1.4 );
AddColumn(     Highest( CloseChangePercent ), "Largest up move %",   1.2 );
AddColumn(     Lowest( CloseChangePercent ),  "Largest down move %", 1.2 );
AddColumn(     Average,                   "Average on last bar",  1.4 );
AddTextColumn( FinalState,                "Final state" );

_SECTION_END();

Download same-rule-two-series.afl51 lines

Run it as an Exploration over that watch list, with the range set to all quotations. You get two rows and the same comparison as Step 5, without the spreadsheet.

Run the defect census on both

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

Run this one the same way. The DIRTY row should show a non-zero stale-bar count from Defect 3, a raised big-move count from Defect 1, and a raised wide-bar count from Defect 2. The CLEAN row should be quiet. If the census fails to flag a defect you know you injected, that is worth ten minutes of your time: either the threshold is wrong for this instrument or the check does not do what you assumed, and both are useful to discover on data whose faults you already know.

Finally, run Tools → Database Purify with CLEAN as the reference symbol and see what it says about DIRTY. It should find the split candidate. It will not find Defect 2, 3 or 4, because those bars exist and are internally consistent — which is a precise demonstration of what that tool is and is not for.

Expected result

You should end with a damaged series whose worst single-bar fall is about minus fifty per cent, a state count and cross-up count that differ from the clean series, dozens of bars where the two states disagree — concentrated in the fifty bars after the injected split — a larger mean bar range, and at least one defect that changed nothing measurable at all.

If your two series produce identical numbers on every measurement, something went wrong: most likely the damaged block still contains formulas referring to the clean columns rather than pasted values. Check that editing a clean cell does not change a damaged one.

Common errors

• Pasting formulas instead of values into the damaged block, so that every “damage” edit is immediately overwritten by the clean source.
• Off-by-one on rows. Bar 120 is row 121 when row 1 is a header. Verify one damaged cell by eye against the date column before applying the rest.
• Filling the moving-average column down from row 2 instead of row 51, which produces averages over incomplete windows and makes the first fifty comparisons meaningless in both series.
• Using a series shorter than 250 rows, which leaves too little history after the 50-bar warm-up for the differences to be visible.
• Concluding that Defect 4 is harmless. It is invisible to this rule. That is a different statement.

Extension

Repeat the experiment with one defect at a time — four damaged copies rather than one — and record which measurements each defect moves on its own. It is slower, and it is the proper experimental design: with all four applied at once you are inferring attribution from location, whereas with one at a time you are measuring it.

Then add a second rule that uses the high rather than the close — for example, “the close exceeds the highest high of the previous twenty bars” — and repeat the measurement. Defect 2, which was invisible to the first rule, should now dominate the difference. That single result is the most transferable thing in this part: the question “is my data good enough?” has no answer until you say what you are going to do with it.

Recap

You have now shown, on data you produced yourself, that a fixed rule applied to two series that differ only by four localised defects returns materially different answers, and that the size of the difference depends on which defect and which rule. One bad bar propagated into fifty wrong states through a moving average. One bad high was invisible to a close-based rule and would wreck a volatility-based one. One volume error changed nothing today and would silently disable a liquidity filter tomorrow.

That is the argument of this whole part, made concrete. Part 3 installs AmiBroker and builds the database this data will live in — and the base-interval and adjustment decisions from these lessons are the ones you will make there, on purpose rather than by default.

Check your understanding

Question 1. In the lab, one injected split at bar 120 caused disagreements in roughly the next fifty bars. Why fifty?

• Because the split affected fifty bars of data
• Because the 50-bar moving average carried the inflated pre-split prices for that long
• Because the spreadsheet recalculated fifty rows at a time
• Because volume was halved for fifty bars

Show the answer and why

Answer: Because the 50-bar moving average carried the inflated pre-split prices for that long

The bad bar itself is one row. The damage spreads because every 50-bar average computed in the following fifty bars still includes pre-split prices that are twice what they should be. Indicator windows determine how far a single defect travels.

Question 2. Defect 2 inflated two daily highs by fifteen per cent, yet the close-above-average rule produced identical state counts. What is the correct conclusion?

• The defect was too small to matter
• The rule is robust to data errors
• The defect is invisible to this rule and would still damage rules that use the high or the range
• The spreadsheet formulas were entered incorrectly

Show the answer and why

Answer: The defect is invisible to this rule and would still damage rules that use the high or the range

A close-based rule never reads the high, so a corrupted high cannot reach it. Any range, true range, volatility or breakout calculation reads the high directly and would be affected. Harm is relative to the rule, not a property of the defect alone.

Question 3. Database Purify found the injected split but not the stale patch or the volume error. Why?

• Its detection threshold was set too high
• Those bars exist and are internally consistent, so no rule it checks is broken
• It only examines the reference symbol
• Stale bars are automatically repaired on import

Show the answer and why

Answer: Those bars exist and are internally consistent, so no rule it checks is broken

Purify tests for missing or extra quotes relative to a reference, impossible OHLC relationships, and large gaps. A frozen bar with equal OHLC and zero volume violates none of those, and a volume unit error violates none either. Knowing what a tool does not check is part of using it.

Question 4. Which items belong in the failure-mode note for a defect you found in real data? Select all that apply.

• Where the defect was located
• Which measurements it changed, with numbers
• Which rules it would not affect
• How you would detect it in data you had not damaged yourself

Show the answer and why

Answer: Where the defect was located, Which measurements it changed, with numbers, Which rules it would not affect, How you would detect it in data you had not damaged yourself

All four. Location and measured effect make the note checkable; naming the rules it does not affect prevents over-reaction; and the detection method is what turns one incident into a permanent check you run every time.

Check my answersClear