#!/usr/bin/env python3 """Compare two CSV exports record by record, never total against total. Usage: python3 scripts/reconcile_exports.py A.csv B.csv --key COLS --measures COLS [--currency-col COL] [--label-a NAME] [--label-b NAME] [--scale-a MEASURE=FACTOR] [--scale-b MEASURE=FACTOR] [--rel-tol 0.01] [--abs-tol 1.00] [--rollup COLS] [--month-from DATECOL] [--pass-pct 0.5] [--fail-pct 2.0] [--mature-through YYYY-MM-DD] [--date-col COL] [--thousands ,] [--exceptions OUT.csv] [--json] A is the reference side (platform export, form log, source of a copy). B is the side under test (warehouse query, CRM extract, dashboard export, second implementation of a metric). Both files are UTF-8 CSV (a BOM is fine) and share the key and measure column names. What it separates: - keys matched, missing in B, unexpected in B, and duplicate keys per side (excess rows); duplicate values are summed for comparison and flagged - per matched key and measure: diff = B minus A, flagged when abs(diff) > max(--abs-tol, --rel-tol x abs(A)) - with --currency-col: a currency difference on a matched key is its own issue (KEY-08). Its values are never subtracted from each other: the key is left out of the value comparison and the matched-only totals, and is listed apart with each side's total in its own currency - totals per currency: A, B, signed difference (B minus A) and absolute difference (abs diffs on matched keys + A values of missing keys + B values of unexpected keys + both sides of currency-mismatched keys, each in its own side's currency) - matched-only agreement, so missing slices (FRS-03) and value disagreement (REC-02) are scored apart - rollups over --rollup columns: PASS when abs(percent) <= --pass-pct, WARN when <= --fail-pct, else FAIL; a group whose currencies differ is FAIL and its difference is printed as not comparable Empty cells are missing values, not zero. --scale applies a factor to one side before comparing (Google Ads cost_micros: --scale-a spend=0.000001). --month-from derives a "month" column (YYYY-MM) from a date column. --mature-through drops rows dated after that day (from --date-col, else the --month-from column, else a column named "date"). It prints keys and measures only, never whole rows. It cannot tell why a key is missing; the comparison contract and the source behaviour do. Standard library only. Exit status: 0 when every rollup passes and no missing, unexpected, duplicate, currency or value issue exists; 1 otherwise, also when no matched key holds a value for a measure, so nothing was compared; 2 on input errors, including a file that is not UTF-8 CSV, a row with more cells than the header, and two files with no rows. """ import argparse import csv import json import sys from collections import OrderedDict from datetime import date from decimal import ROUND_HALF_UP, Decimal, InvalidOperation LIST_LIMIT = 20 class InputError(Exception): pass def split_cols(text): return [c.strip() for c in (text or "").split(",") if c.strip()] def parse_scale(items, measures): scales = {} for item in items or []: if "=" not in item: raise InputError("--scale expects MEASURE=FACTOR, got %r" % item) name, factor = item.split("=", 1) name = name.strip() if name not in measures: raise InputError("--scale names %r, which is not in --measures" % name) try: scales[name] = Decimal(factor.strip()) except InvalidOperation: raise InputError("--scale factor %r is not a number" % factor) return scales def parse_number(text, thousands, where): raw = text.strip() if raw == "": return None if thousands: raw = raw.replace(thousands, "") try: value = Decimal(raw) except InvalidOperation: hint = "" if thousands else "; pass --thousands , if the export uses a thousands separator" raise InputError("%s: %r is not a number%s" % (where, text, hint)) if not value.is_finite(): raise InputError("%s: %r is not a finite number" % (where, text)) return value def parse_day(text, where): try: return date.fromisoformat(text.strip()[:10]) except ValueError: raise InputError("%s: %r is not a YYYY-MM-DD date" % (where, text)) def read_side(path, label, args, scales): try: with open(path, newline="", encoding="utf-8-sig") as handle: reader = csv.DictReader(handle) header = [h.strip() for h in (reader.fieldnames or [])] raw_rows = list(reader) except OSError as exc: raise InputError("cannot read %s: %s" % (path, exc)) except (UnicodeDecodeError, csv.Error): raise InputError("%s (%s) is not a UTF-8 CSV file; save a workbook or UTF-16 export as CSV UTF-8" % (label, path)) needed = [c for c in args.key_cols if c != "month" or not args.month_from] needed += args.measure_cols if args.currency_col: needed.append(args.currency_col) if args.month_from: needed.append(args.month_from) if args.mature_through: needed.append(args.date_col) needed += [c for c in args.rollup_cols if c != "month" or not args.month_from] missing = sorted(set(c for c in needed if c not in header)) if missing: raise InputError("%s (%s) lacks columns: %s" % (label, path, ", ".join(missing))) keys = OrderedDict() dropped = 0 rows_read = 0 for number, raw in enumerate(raw_rows, start=2): if None in raw: raise InputError("%s line %d has more cells than the header; quote any value that holds a comma" % ( label, number)) row = {(k or "").strip(): (v or "").strip() for k, v in raw.items()} rows_read += 1 where = "%s line %d" % (label, number) if args.month_from: row["month"] = parse_day(row[args.month_from], where).strftime("%Y-%m") if args.mature_through: if parse_day(row[args.date_col], where) > args.mature_through: dropped += 1 continue key = tuple(row[c] for c in args.key_cols) values = {} for m in args.measure_cols: v = parse_number(row[m], args.thousands, "%s column %s" % (where, m)) if v is not None and m in scales: v = v * scales[m] values[m] = v entry = keys.get(key) if entry is None: entry = {"rows": 0, "values": dict((m, None) for m in args.measure_cols), "currencies": [], "group": tuple(row[c] for c in args.rollup_cols)} keys[key] = entry entry["rows"] += 1 for m, v in values.items(): if v is not None: entry["values"][m] = v if entry["values"][m] is None else entry["values"][m] + v if args.currency_col: cur = row[args.currency_col] if cur not in entry["currencies"]: entry["currencies"].append(cur) return {"keys": keys, "rows_read": rows_read, "dropped": dropped} def money(value): if value is None: return "" return format(value.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP), ",") def signed(value): text = money(value) return "+" + text if value is not None and value > 0 else text def pct(part, whole): if whole is None or whole == 0: return None return (Decimal(100) * part / whole).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP) def pct_text(value): if value is None: return "n/a" return ("+%s%%" % value) if value > 0 else ("%s%%" % value) def currency_of(entry): return "/".join(sorted(entry["currencies"])) if entry["currencies"] else "all" def currency_list(totals): return ", ".join("%s %s" % (money(v), cur) for cur, v in totals.items()) or "no values" def key_text(key): return ", ".join(key) def reconcile(args): scales_a = parse_scale(args.scale_a, args.measure_cols) scales_b = parse_scale(args.scale_b, args.measure_cols) side_a = read_side(args.a, args.label_a, args, scales_a) side_b = read_side(args.b, args.label_b, args, scales_b) ka, kb = side_a["keys"], side_b["keys"] if not ka and not kb: raise InputError("neither file has a row to compare; check the exports and any --mature-through date") matched = [k for k in ka if k in kb] missing = [k for k in ka if k not in kb] unexpected = [k for k in kb if k not in ka] dup_a = [k for k, e in ka.items() if e["rows"] > 1] dup_b = [k for k, e in kb.items() if e["rows"] > 1] exceptions = [] def add_exc(key, issue, measure="", a_value=None, b_value=None, diff=None, a_cur="", b_cur=""): exceptions.append(OrderedDict( [(c, key[i]) for i, c in enumerate(args.key_cols)] + [("issue", issue), ("measure", measure), ("a_value", "" if a_value is None else str(a_value)), ("b_value", "" if b_value is None else str(b_value)), ("diff", "" if diff is None else str(diff)), ("a_currency", a_cur), ("b_currency", b_cur)])) currency_mismatch = [] if args.currency_col: for k in matched: if sorted(ka[k]["currencies"]) != sorted(kb[k]["currencies"]): currency_mismatch.append(k) cm_set = set(currency_mismatch) mixed_a = [k for k, e in ka.items() if len(e["currencies"]) > 1] mixed_b = [k for k, e in kb.items() if len(e["currencies"]) > 1] for k in dup_a: add_exc(k, "duplicate_a", "(rows)", a_value=ka[k]["rows"], a_cur=currency_of(ka[k])) for k in dup_b: add_exc(k, "duplicate_b", "(rows)", b_value=kb[k]["rows"], b_cur=currency_of(kb[k])) measures = OrderedDict() for m in args.measure_cols: flagged, empty, unvalued = [], [], [] totals = OrderedDict() def bucket(cur): if cur not in totals: totals[cur] = {"a": Decimal(0), "b": Decimal(0), "abs": Decimal(0), "matched_a": Decimal(0), "matched_b": Decimal(0)} return totals[cur] for k, e in ka.items(): if e["values"][m] is not None: bucket(currency_of(e))["a"] += e["values"][m] for k, e in kb.items(): if e["values"][m] is not None: bucket(currency_of(e))["b"] += e["values"][m] cm_totals = {"a": OrderedDict(), "b": OrderedDict()} for k in matched: a_val, b_val = ka[k]["values"][m], kb[k]["values"][m] if k in cm_set: # Different currencies: never subtract one from the other. Each side's # value counts as disagreement in its own currency (KEY-08 scores it). a_cur, b_cur = currency_of(ka[k]), currency_of(kb[k]) if a_val is not None: bucket(a_cur)["abs"] += abs(a_val) cm_totals["a"][a_cur] = cm_totals["a"].get(a_cur, Decimal(0)) + a_val if b_val is not None: bucket(b_cur)["abs"] += abs(b_val) cm_totals["b"][b_cur] = cm_totals["b"].get(b_cur, Decimal(0)) + b_val add_exc(k, "currency_mismatch", m, a_val, b_val, None, a_cur, b_cur) continue t = bucket(currency_of(ka[k])) if a_val is not None: t["matched_a"] += a_val if b_val is not None: t["matched_b"] += b_val if a_val is None and b_val is None: unvalued.append(k) continue if a_val is None or b_val is None: empty.append(k) t["abs"] += abs(a_val if a_val is not None else b_val) add_exc(k, "value_mismatch", m, a_val, b_val, None, currency_of(ka[k]), currency_of(kb[k])) continue diff = b_val - a_val t["abs"] += abs(diff) tolerance = max(args.abs_tol, args.rel_tol * abs(a_val)) if abs(diff) > tolerance: flagged.append((k, a_val, b_val, diff)) add_exc(k, "value_mismatch", m, a_val, b_val, diff, currency_of(ka[k]), currency_of(kb[k])) for k in missing: v = ka[k]["values"][m] if v is not None: bucket(currency_of(ka[k]))["abs"] += abs(v) add_exc(k, "missing_in_b", m, v, None, None, currency_of(ka[k]), "") for k in unexpected: v = kb[k]["values"][m] if v is not None: bucket(currency_of(kb[k]))["abs"] += abs(v) add_exc(k, "unexpected_in_b", m, None, v, None, "", currency_of(kb[k])) rollups = [] if args.rollup_cols: groups = OrderedDict() def group(g): if g not in groups: groups[g] = {"a": Decimal(0), "b": Decimal(0), "ma": Decimal(0), "mb": Decimal(0), "missing": 0, "unexpected": 0, "flagged": 0, "currency": 0, "cur_a": [], "cur_b": []} return groups[g] flagged_keys = set(x[0] for x in flagged) | set(empty) for k, e in ka.items(): g = group(e["group"]) if e["values"][m] is not None: g["a"] += e["values"][m] for cur in e["currencies"]: if cur not in g["cur_a"]: g["cur_a"].append(cur) if k not in kb: g["missing"] += 1 for k, e in kb.items(): g = group(ka[k]["group"] if k in ka else e["group"]) if e["values"][m] is not None: g["b"] += e["values"][m] for cur in e["currencies"]: if cur not in g["cur_b"]: g["cur_b"].append(cur) if k not in ka: g["unexpected"] += 1 for k in matched: g = group(ka[k]["group"]) if k in cm_set: g["currency"] += 1 continue if ka[k]["values"][m] is not None: g["ma"] += ka[k]["values"][m] if kb[k]["values"][m] is not None: g["mb"] += kb[k]["values"][m] if k in flagged_keys: g["flagged"] += 1 for gkey in sorted(groups): g = groups[gkey] comparable = not (g["currency"] or len(g["cur_a"]) > 1 or len(g["cur_b"]) > 1 or (g["cur_a"] and g["cur_b"] and g["cur_a"] != g["cur_b"])) if comparable: diff = g["b"] - g["a"] p = pct(diff, g["a"]) mp = pct(g["mb"] - g["ma"], g["ma"]) else: diff = p = mp = None if not comparable: status, reason = "FAIL", "currency" elif g["a"] == 0: status, reason = ("PASS", "") if g["b"] == 0 else ("FAIL", "reference total is zero") elif abs(p) <= args.pass_pct: status, reason = "PASS", "" elif abs(p) <= args.fail_pct: status, reason = "WARN", "" else: status, reason = "FAIL", "" rollups.append(OrderedDict([ ("group", list(gkey)), ("a_total", g["a"]), ("b_total", g["b"]), ("comparable", comparable), ("difference", diff), ("percent", p), ("matched_only_percent", mp), ("missing_keys", g["missing"]), ("unexpected_keys", g["unexpected"]), ("flagged_keys", g["flagged"]), ("currency_mismatch_keys", g["currency"]), ("a_currencies", g["cur_a"]), ("b_currencies", g["cur_b"]), ("status", status), ("reason", reason)])) compared = len(matched) - len(cm_set) - len(unvalued) measures[m] = {"flagged": flagged, "empty": empty, "totals": totals, "rollups": rollups, "cm_totals": cm_totals, "unvalued": unvalued, "compared": compared} return {"side_a": side_a, "side_b": side_b, "matched": matched, "missing": missing, "unexpected": unexpected, "dup_a": dup_a, "dup_b": dup_b, "currency_mismatch": currency_mismatch, "mixed_a": mixed_a, "mixed_b": mixed_b, "measures": measures, "exceptions": exceptions} def date_index(args): col = args.date_col if args.date_col in args.key_cols else None if col is None and args.month_from in args.key_cols: col = args.month_from return args.key_cols.index(col) if col else None def print_report(args, r): la, lb = args.label_a, args.label_b a, b = r["side_a"], r["side_b"] out = ["Reconciliation: %s (%s) against %s (%s)" % (la, args.a, lb, args.b)] out.append("Key: %s. Measures: %s.%s Tolerance per key: max(%s, %s%% of the %s value)." % ( ", ".join(args.key_cols), ", ".join(args.measure_cols), (" Currency column: %s." % args.currency_col) if args.currency_col else "", args.abs_tol, (args.rel_tol * 100).normalize(), la)) line = "Rows read: %s %d, %s %d." % (la, a["rows_read"], lb, b["rows_read"]) if args.mature_through: line += " Dropped as later than %s: %s %d, %s %d." % ( args.mature_through.isoformat(), la, a["dropped"], lb, b["dropped"]) out.append(line) out.append("Keys: %s %d, %s %d, matched %d, missing in %s %d, unexpected in %s %d." % ( la, len(a["keys"]), lb, len(b["keys"]), len(r["matched"]), lb, len(r["missing"]), lb, len(r["unexpected"]))) excess_a = sum(a["keys"][k]["rows"] - 1 for k in r["dup_a"]) excess_b = sum(b["keys"][k]["rows"] - 1 for k in r["dup_b"]) out.append("Duplicates: %s %d keys (%d excess rows), %s %d keys (%d excess rows)." % ( la, len(r["dup_a"]), excess_a, lb, len(r["dup_b"]), excess_b)) for label, keys, side in ((la, r["dup_a"], a), (lb, r["dup_b"], b)): for k in keys[:LIST_LIMIT]: out.append(" - duplicate in %s: %s (%d rows)" % (label, key_text(k), side["keys"][k]["rows"])) if args.currency_col: out.append("Currency mismatches: %d keys" % len(r["currency_mismatch"])) di = date_index(args) summary = OrderedDict() for k in r["currency_mismatch"]: short = tuple(v for i, v in enumerate(k) if i != di) pair = (currency_of(a["keys"][k]), currency_of(b["keys"][k])) summary.setdefault((short, pair), 0) summary[(short, pair)] += 1 for (short, pair), count in list(summary.items())[:LIST_LIMIT]: out.append(" - %s: %d keys, %s %s, %s %s" % (key_text(short), count, la, pair[0], lb, pair[1])) for label, keys in ((la, r["mixed_a"]), (lb, r["mixed_b"])): if keys: out.append(" - %d keys in %s carry more than one currency" % (len(keys), label)) for m, info in r["measures"].items(): out.append("Value mismatches (%s): %d of %d matched keys%s" % ( m, len(info["flagged"]), len(r["matched"]), (", plus %d with an empty value on one side" % len(info["empty"])) if info["empty"] else "")) if info["unvalued"]: out.append(" - %d matched keys have no %s value on either side, so nothing was compared for them" % ( len(info["unvalued"]), m)) for k, av, bv, diff in info["flagged"][:LIST_LIMIT]: out.append(" - %s: %s %s, %s %s, difference %s (%s)" % ( key_text(k), la, money(av), lb, money(bv), signed(diff), pct_text(pct(diff, av)))) if len(info["flagged"]) > LIST_LIMIT: out.append(" - and %d more (see --exceptions)" % (len(info["flagged"]) - LIST_LIMIT)) first = args.measure_cols[0] if a["keys"] and b["keys"] and not r["matched"]: out.append("No key matched. Compare the key formats before scoring anything: hyphens in Google Ads IDs, " "the act_ prefix on Meta accounts, date formats.") out.append("Missing in %s: %d keys" % (lb, len(r["missing"]))) for k in r["missing"][:LIST_LIMIT]: out.append(" - %s: %s %s %s" % (key_text(k), la, first, money(a["keys"][k]["values"][first]))) if len(r["missing"]) > LIST_LIMIT: out.append(" - and %d more (see --exceptions)" % (len(r["missing"]) - LIST_LIMIT)) out.append("Unexpected in %s: %d keys" % (lb, len(r["unexpected"]))) for k in r["unexpected"][:LIST_LIMIT]: out.append(" - %s: %s %s %s" % (key_text(k), lb, first, money(b["keys"][k]["values"][first]))) if len(r["unexpected"]) > LIST_LIMIT: out.append(" - and %d more (see --exceptions)" % (len(r["unexpected"]) - LIST_LIMIT)) for m, info in r["measures"].items(): out.append("Totals (%s), by currency:" % m) for cur, t in info["totals"].items(): out.append(" - %s: %s %s, %s %s, signed difference %s, absolute difference %s" % ( cur, la, money(t["a"]), lb, money(t["b"]), signed(t["b"] - t["a"]), money(t["abs"]))) out.append("Matched keys only (%s), by %s currency:" % (m, la)) for cur, t in info["totals"].items(): if t["matched_a"] or t["matched_b"]: out.append(" - %s: %s %s, %s %s, difference %s (%s)" % ( cur, la, money(t["matched_a"]), lb, money(t["matched_b"]), signed(t["matched_b"] - t["matched_a"]), pct_text(pct(t["matched_b"] - t["matched_a"], t["matched_a"])))) if r["currency_mismatch"]: out.append(" - currency-mismatched keys, not compared: %d keys; %s %s; %s %s" % ( len(r["currency_mismatch"]), la, currency_list(info["cm_totals"]["a"]), lb, currency_list(info["cm_totals"]["b"]))) if info["rollups"]: out.append("Rollups (%s) by %s: PASS at %s%% or less, WARN up to %s%%, else FAIL" % ( m, ", ".join(args.rollup_cols), args.pass_pct, args.fail_pct)) for g in info["rollups"]: extra = [] if g["missing_keys"]: extra.append("missing keys %d" % g["missing_keys"]) if g["unexpected_keys"]: extra.append("unexpected keys %d" % g["unexpected_keys"]) if g["flagged_keys"]: extra.append("flagged keys %d" % g["flagged_keys"]) if g["currency_mismatch_keys"]: extra.append("currency mismatch keys %d" % g["currency_mismatch_keys"]) cur_a = (" " + "/".join(g["a_currencies"])) if g["a_currencies"] else "" cur_b = (" " + "/".join(g["b_currencies"])) if g["b_currencies"] else "" if g["comparable"]: diff_text = "difference %s (%s), matched-only %s" % ( signed(g["difference"]), pct_text(g["percent"]), pct_text(g["matched_only_percent"])) else: diff_text = "difference not comparable (currencies differ)" out.append(" - %s: %s %s%s, %s %s%s, %s%s: %s%s" % ( key_text(g["group"]), la, money(g["a_total"]), cur_a, lb, money(g["b_total"]), cur_b, diff_text, (", " + ", ".join(extra)) if extra else "", g["status"], (" (%s)" % g["reason"]) if g["reason"] else "")) statuses = [g["status"] for info in r["measures"].values() for g in info["rollups"]] flagged_total = sum(len(i["flagged"]) + len(i["empty"]) for i in r["measures"].values()) out.append("Result: %d missing, %d unexpected, %d duplicate keys, %d currency mismatches, " "%d value mismatches; rollups %d PASS, %d WARN, %d FAIL." % ( len(r["missing"]), len(r["unexpected"]), len(r["dup_a"]) + len(r["dup_b"]), len(r["currency_mismatch"]), flagged_total, statuses.count("PASS"), statuses.count("WARN"), statuses.count("FAIL"))) if args.exceptions: out.append("Exceptions: %d rows written to %s" % (len(r["exceptions"]), args.exceptions)) else: out.append("Exceptions: not written (add --exceptions FILE.csv for the full list).") out.append("Nothing was changed.") print("\n".join(out)) def write_exceptions(path, args, rows): columns = args.key_cols + ["issue", "measure", "a_value", "b_value", "diff", "a_currency", "b_currency"] with open(path, "w", newline="", encoding="utf-8") as handle: writer = csv.DictWriter(handle, fieldnames=columns, lineterminator="\n") writer.writeheader() for row in rows: writer.writerow(row) def is_clean(r): if r["missing"] or r["unexpected"] or r["dup_a"] or r["dup_b"] or r["currency_mismatch"]: return False if r["mixed_a"] or r["mixed_b"]: return False for info in r["measures"].values(): if info["flagged"] or info["empty"]: return False if r["matched"] and not info["compared"]: return False if any(g["status"] != "PASS" for g in info["rollups"]): return False return True def to_jsonable(value): if isinstance(value, Decimal): return str(value) if isinstance(value, tuple): return list(value) if isinstance(value, dict): return OrderedDict((str(k), to_jsonable(v)) for k, v in value.items()) if isinstance(value, list): return [to_jsonable(v) for v in value] return value def main(argv=None): p = argparse.ArgumentParser(description="Compare two CSV exports record by record.") p.add_argument("a", help="reference CSV (platform, form log, source)") p.add_argument("b", help="CSV under test (warehouse, CRM, dashboard)") p.add_argument("--key", required=True, help="comma-separated key columns") p.add_argument("--measures", required=True, help="comma-separated measure columns") p.add_argument("--currency-col") p.add_argument("--label-a", default="A") p.add_argument("--label-b", default="B") p.add_argument("--scale-a", action="append", help="MEASURE=FACTOR applied to side A") p.add_argument("--scale-b", action="append", help="MEASURE=FACTOR applied to side B") p.add_argument("--rel-tol", default="0.01", help="relative tolerance per key (default 0.01 = 1%%)") p.add_argument("--abs-tol", default="1.00", help="absolute tolerance per key (default 1.00)") p.add_argument("--rollup", default="", help="comma-separated rollup columns (may include month)") p.add_argument("--month-from", help="date column to derive a month column from") p.add_argument("--pass-pct", default="0.5") p.add_argument("--fail-pct", default="2.0") p.add_argument("--mature-through", help="drop rows dated after YYYY-MM-DD") p.add_argument("--date-col", help="date column for --mature-through") p.add_argument("--thousands", help="thousands separator to strip, for example ,") p.add_argument("--exceptions", help="write every exception to this CSV") p.add_argument("--json", action="store_true") args = p.parse_args(argv) try: args.key_cols = split_cols(args.key) args.measure_cols = split_cols(args.measures) args.rollup_cols = split_cols(args.rollup) if not args.key_cols or not args.measure_cols: raise InputError("--key and --measures need at least one column each") if "month" in args.rollup_cols + args.key_cols and not args.month_from: raise InputError("a month column needs --month-from DATECOL") try: args.rel_tol = Decimal(args.rel_tol) args.abs_tol = Decimal(args.abs_tol) args.pass_pct = Decimal(args.pass_pct) args.fail_pct = Decimal(args.fail_pct) except InvalidOperation: raise InputError("tolerances and percentages must be numbers") if args.mature_through: args.mature_through = parse_day(args.mature_through, "--mature-through") if not args.date_col: args.date_col = args.month_from or "date" result = reconcile(args) except InputError as exc: print("error: %s" % exc, file=sys.stderr) return 2 if args.exceptions: try: write_exceptions(args.exceptions, args, result["exceptions"]) except OSError as exc: print("error: cannot write %s: %s" % (args.exceptions, exc), file=sys.stderr) return 2 if args.json: data = { "keys_a": len(result["side_a"]["keys"]), "keys_b": len(result["side_b"]["keys"]), "matched": len(result["matched"]), "missing_in_b": [list(k) for k in result["missing"]], "unexpected_in_b": [list(k) for k in result["unexpected"]], "duplicates_a": [list(k) for k in result["dup_a"]], "duplicates_b": [list(k) for k in result["dup_b"]], "currency_mismatches": len(result["currency_mismatch"]), "measures": OrderedDict(), "clean": is_clean(result)} for m, info in result["measures"].items(): data["measures"][m] = { "value_mismatches": [[list(k), str(a), str(b), str(d)] for k, a, b, d in info["flagged"]], "empty_values": [list(k) for k in info["empty"]], "no_value_on_either_side": len(info["unvalued"]), "currency_mismatch_totals": to_jsonable(info["cm_totals"]), "totals": to_jsonable(info["totals"]), "rollups": to_jsonable(info["rollups"])} print(json.dumps(data, indent=2)) else: print_report(args, result) return 0 if is_clean(result) else 1 if __name__ == "__main__": sys.exit(main())