# Reconciliation: expense export cleanup (synthetic sample)

Source: `input.md` (CSV block, 14 data rows). The input was read only and not modified. SHA-256 was `0e235a93df338d6347c4f574f15e615203353e69773b516724fea3c4d1c2edd7` before and after the run.
Outputs: `cleaned.csv`, `exceptions.csv`, this note. Nothing was written to a live system, and no payment, accounting entry or external transmission took place.

## Row counts

| Measure | Count |
|---|---|
| Source data rows | 14 |
| Rows in `cleaned.csv` | 14 (same order, source_row 1-14) |
| Rows deleted or merged | 0 |
| Rows with no issue flag | 7 (1, 2, 3, 4, 5, 11, 12) |
| Rows flagged | 7 (6, 7, 8, 9, 10, 13, 14) |
| Exception log entries | 7 (EX01-EX07, one per flagged row) |

Check: 7 clear + 7 flagged = 14 = source rows.

## Rules applied (all from the brief)

- Every original column is kept unchanged. The normalized values go in new columns: `date_normalized`, `amount_normalized`, `currency_normalized`, `person_normalized` and `cost_center_normalized`.
- All fields are written as quoted text, so `expense_id` keeps its leading zeros (for example `001001`).
- Dates already in YYYY-MM-DD are copied over. `03/04/2026` (row 9) is not converted because its locale is unknown.
- USD amounts using comma thousands grouping and a dot decimal are normalized, so `1,250.00` becomes `1250.00`. A parenthesized amount is read as a negative refund, so `(45.00)` becomes `-45.00`. `1.234,50` (row 14) and `TBC` (row 13) are left blank in `amount_normalized`; neither is set to zero.
- A missing currency (row 10) stays blank and is not inferred.
- Surrounding whitespace is trimmed from names (row 1) and cost-center codes are uppercased (row 2, `ops` to `OPS`). People are not merged.
- Exact duplicate (row 6 repeats row 5): flagged in `duplicate_of_source_row` and kept.
- Conflict (rows 7 and 8: same ID 001006 with amounts 120.00 and 140.00): both kept, both flagged in `conflict_group`. This is logged separately from the duplicate.

## Scoped totals (not a final accounting total)

Included rows: the first occurrence of each fully clear record (`in_scoped_total = yes`). Each currency is summed separately and no conversion is applied.

| Currency | Rows included | Amounts | Scoped total |
|---|---|---|---|
| USD | 1, 2, 4, 5, 11 | 1250.00 + 85.50 + 19.99 + 65.00 + (-45.00) | **1375.49 USD** |
| EUR | 3, 12 | 240.00 + 180.00 | **420.00 EUR** |

Excluded rows (7):

| Row | ID | Reason excluded |
|---|---|---|
| 6 | 001005 | Exact duplicate of row 5; only the first occurrence is counted |
| 7 | 001006 | Conflicts with row 8; needs a decision |
| 8 | 001006 | Conflicts with row 7; needs a decision |
| 9 | 001007 | Date locale unknown (USD 49.00 is left out) |
| 10 | 001008 | Currency missing (amount 95.00 cannot be assigned to a currency) |
| 13 | 001011 | Amount unknown (TBC) |
| 14 | 001012 | EUR amount locale unknown (`1.234,50`) |

Once the decisions below are made, the totals will change. For example, the row 7/8 choice adds 120.00 or 140.00 USD, and row 9 would add 49.00 USD once its date is confirmed.

## Unresolved decisions

1. Row 6: is this a double export (can be dropped downstream) or a genuine second expense?
2. Rows 7/8 (ID 001006): which amount is correct, 120.00 or 140.00, or are these two separate expenses that need distinct IDs?
3. Row 9: is `03/04/2026` 3 April 2026 or 4 March 2026?
4. Row 10: what currency is the 95.00?
5. Row 13: what is the invoiced amount?
6. Row 14: what is the intended value of `1.234,50` EUR (comma-decimal would give 1234.50)?
7. Any combined cross-currency total would need an authorized conversion rule and rate. None was provided, so none was applied.