48-Hour Field Notes: Every Record Parsed Cleanly and the Reconciliation Was Off by a Cent

Parse success is a terrible health signal, and I keep relearning that lesson the expensive way. My extraction step reported a clean parse for every record in the batch, yet the reconciliation job refused to close the day. The totals were off by exactly one cent, which is the least dramatic and most irritating failure a pipeline can hand you. What do you actually do when your schema gate says yes and your ledger says no?

The 48-hour log, in order

1. Hours 0-4 - I blamed the model response and diffed raw output against parsed rows, which showed nothing because both looked identical when printed with print().
2. Hours 4-12 - The bug vanished locally: my fixture file stored amounts as strings, while the live payload used JSON numbers that Python happily turned into floats.
3. Hours 12-20 - I finally looked at the shapes I had been ignoring: thousands separators, accounting negatives in parentheses, a comma used as a decimal point, and a value with three decimals.
4. Hours 20-36 - I stopped hunting individual rows and wrote a fixture table that states the expected outcome for every shape I had seen in the wild.
5. Hours 36-48 - I replaced "did it parse" with a gate that refuses any value it cannot represent exactly in cents.

Three ways a number stops being a number

1. The float that arrived before you looked

If you call json.loads() on a payload with "amount": 19.99, you get a float, and the precision damage happens before your validation code runs. My old gate accepted floats because isinstance(x, (int, float)) felt generous. Generous is exactly the wrong instinct for money.

2. Separator chaos dressed as a string

Models and spreadsheets both love 1,234.50, 1.234,50, 1,234, and (12.34) for negative. Every one of those is parseable, and at least one of them is ambiguous without a declared locale. A parser that guesses silently will guess wrong on some input you never wrote a test for.

3. Rounding that nobody signed off on

A value like 1.005 is not representable in cents, so somebody has to decide what happens. If the rounding decision lives inside a library default, you have made a business rule without writing it down. My gate now rejects more than two decimals unless a caller explicitly opts in.

The gate I run now

import re from decimal import Decimal, InvalidOperation, ROUND_HALF_UP CENTS = Decimal("0.01") PLAIN = re.compile(r"^[+-]?(\d+(\.\d*)?|\.\d+)$") CURRENCY = re.compile(r"^(?:USD|EUR|GBP|\$|€|£)\s*|\s*(?:USD|EUR|GBP)$") class AmountError(ValueError): """Raised when a value is not a trustworthy 2-decimal amount.""" def parse_amount(raw, field="amount", decimal_comma=False): if raw is None: raise AmountError(f"{field}: missing value") if isinstance(raw, bool): raise AmountError(f"{field}: bool is not an amount") if isinstance(raw, float): raise AmountError(f"{field}: float already lost precision upstream") if isinstance(raw, int): text = str(raw) elif isinstance(raw, str): text = raw.replace("\u00a0", " ").replace("\u2212", "-").strip() if not text: raise AmountError(f"{field}: empty string") if text.startswith("(") and text.endswith(")"): text = "-" + text[1:-1].strip() text = CURRENCY.sub("", text).strip() text = _fold_separators(text, field, decimal_comma) else: raise AmountError(f"{field}: unsupported type {type(raw).__name__}") if not PLAIN.match(text): raise AmountError(f"{field}: not a plain decimal: {raw!r}") try: value = Decimal(text) except InvalidOperation as exc: raise AmountError(f"{field}: unparsable: {raw!r}") from exc if value.normalize().as_tuple().exponent < -2: raise AmountError(f"{field}: more than two decimal places: {raw!r}") return value.quantize(CENTS, rounding=ROUND_HALF_UP) def _fold_separators(text, field, decimal_comma): if "," in text and "." in text: if text.rfind(",") > text.rfind("."): return text.replace(".", "").replace(",", ".") return text.replace(",", "") if "," not in text: return text parts = text.split(",") if decimal_comma and len(parts) == 2 and len(parts[1]) == 2: return text.replace(",", ".") if all(len(p) == 3 for p in parts[1:]) and 1 <= len(parts[0].lstrip("+-")) <= 3: return text.replace(",", "") raise AmountError(f"{field}: ambiguous comma in {text!r}") 

Notice what the function refuses to do. It does not strip arbitrary junk characters, it does not accept scientific notation, and it does not silently round a third decimal place. Refusing is cheap at ingest time and extremely expensive at reconciliation time, which is the whole trade I kept getting backwards.

A fixture table that fails loudly

Opinions in code are worthless without a table that pins them down. This is the exact list I keep next to the parser, and each row is a decision rather than a sample.

REJECT = object() CASES = [ ("12.34", False, Decimal("12.34")), ("12.340", False, Decimal("12.34")), ("$1,234.50", False, Decimal("1234.50")), ("1.234,50", False, Decimal("1234.50")), ("-1,234", False, Decimal("-1234.00")), ("1234,50", True, Decimal("1234.50")), ("(12.34)", False, Decimal("-12.34")), ("\u221212.34", False, Decimal("-12.34")), (12, False, Decimal("12.00")), ("1,23", False, REJECT), ("1.005", False, REJECT), ("1e3", False, REJECT), (12.34, False, REJECT), (True, False, REJECT), (None, False, REJECT), ] def run_fixtures(): failed = 0 for raw, decimal_comma, expected in CASES: try: outcome = parse_amount(raw, decimal_comma=decimal_comma) except AmountError: outcome = REJECT if expected is REJECT: ok = outcome is REJECT else: ok = outcome is not REJECT and outcome == expected failed += 0 if ok else 1 print(f"{'PASS' if ok else 'FAIL'} {raw!r:>16} -> {outcome}") print(f"{len(CASES) - failed} passed, {failed} failed") return failed if __name__ == "__main__": raise SystemExit(1 if run_fixtures() else 0) 

The run should end with 15 passed, 0 failed, and it exits non-zero when someone loosens the gate for a "quick fix". That exit code is the part that survives code review, because the next person cannot quietly relax the float rule without breaking CI.

Decision table: accept, normalize, or reject

For the JSON case, the fix belongs at the boundary: use json.loads(payload, parse_float=str) so the original text survives, then let the gate decide. That one keyword argument eliminated the entire class of bug that cost me the first twelve hours.

Where the free model access and the free server fit

I used MonkeyCode to re-run the extraction prompt over the same fixture inputs while I tightened the gate, and the free model access meant I did not have to ration calls to reproduce a shape. The free server option also let the fixture sweep run somewhere other than my laptop, which mattered because I was restarting the parser constantly. Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The important design constraint is that the gate never trusts the model. Swapping tiers should change how often a value arrives in a weird shape, never whether a weird shape reaches your ledger. If your validation logic is coupled to whichever model answered last, you built a coin flip, not a contract.

Limitations, and who should skip this

• Single-comma strings are still ambiguous. "1.234" can mean one point two three four or one thousand two hundred thirty four, and no parser can resolve that without a declared locale.
• This is not a compliance-grade money library. It is an ingest gate; double-entry accounting, FX conversion, and tax rules are different disciplines with different review requirements.
• A free tier is not a durability guarantee. I would not treat any trial environment as the system of record for financial data, and neither should you.
• Skip this if your volumes are tiny and human-reviewed. A spreadsheet and a second pair of eyes beats a parser nobody maintains.

What I would repeat, and what I would change

• Repeat: writing the fixture table before the third hour of debugging, because a table forces you to enumerate the decisions you were making implicitly.
• Repeat: rejecting floats at the boundary instead of coercing them, since a coerced float is a lie with extra decimal places.
• Change: I would log the raw string of every rejected value from day one, because "it parsed" hid the shapes that mattered.
• Change: I would put the reconciliation sum behind the same gate using sum(..., Decimal("0.00")), so float leakage cannot reappear in the total.

If you want to reproduce the sweep, a local shell is honestly enough, and I ran mine on the free server mainly because I kept killing my own terminal. The fixture table is the artifact worth stealing, and if you find a row my parser gets wrong, I would genuinely like to hear about it.