Spreadsheet QA

Business exports lie quietly. A "Grand Total" row doubles every SUM. A customer listed twice multiplies their orders through a join. EUR and USD sit in one amount column. A UTC timestamp at 02:30 on Aug 1 is a July order in New York. None of these raise an error, so an answer computed by eye or by one quick query looks right and is wrong. This skill makes every number reproducible: profile, define, compute in code, reconcile, show the evidence.

Scope: questions about the data in the file. To combine or dedupe several files into one first, use csv-excel-merger. To check whether a financial model's formulas are sound, use spreadsheet-model-auditor.

Workflow

1. Profile before answering anything

python3 scripts/profile.py path/to/*.csv path/to/book.xlsx --out sqa_out

Read sqa_out/profile.md. It reports, per file and sheet: header row, row count, candidate keys, duplicate keys, each column's type and parse rate, null %, distinct count, min/max, top values, currency and timezone hints, date ranges, embedded total/footer rows, hidden rows/columns, autofilters, merged cells, and a join-risk section predicting fan-out between files. It also drafts data_dictionary.md with open questions.

Why first: the traps above are visible only in the profile. Once a query has run, nobody rereads the raw rows. Do this even for "just give me the total": the total is the question most exposed to embedded total rows.

State the grain in one line per table ("one row = one order; key order_id") before writing a query. If no key is unique, say what one row is before counting anything.