Import bank statements and reconcile transactions against receipts

[ndomako10]

Summary

Add the ability to import a downloaded bank statement (CSV) and compare its transactions against receipts in the workbook, surfacing missing receipts, unmatched transactions, and amount discrepancies.

Proposed behaviour

• A new script or -Reconcile mode accepts a statement CSV and a bank account name
• Matches statement transactions to receipt rows by date and amount
• Produces a reconciliation report identifying:
  • Missing receipts — statement transactions with no corresponding receipt file
  • Unmatched receipts — receipt rows with no matching statement transaction (possible duplicate or wrong amount in filename)
  • Amount mismatches — date/vendor match found but amounts differ
• Vendor name matching is fuzzy (date + amount is the primary key; vendor name is a secondary signal)

Design notes

• CSV column mapping (date, amount, description) will vary by bank — a configurable profile per bank account is likely needed
• Statement import is scoped to one bank account per run; the bank account name maps to card numbers via Accounts.xlsx

Prerequisites

• Restructure Accounts.xlsx: add Status and Bank Account columns #57 — Status column (Accounts.xlsx restructure)
• Add Bank Account column to Accounts.xlsx to group card numbers by account #59 — Bank Account column (card-to-account mapping, required to associate a statement with the right receipts)

[ndomako10]

Active accounts only

Reconciliation should only run for Active accounts. When a card is marked Inactive in Accounts.xlsx, the script should not prompt for or expect a statement for that account.

This is a consequence of the workflow decision documented in #57: a card stays Active until its final statement has been reconciled, at which point it is marked Inactive. Once Inactive, no further statements will exist for that account and reconciliation can skip it entirely.

[ndomako10]

Apple Pay tokenization and alias handling (see ADR-009)

Apple Pay generates a unique Device Account Number (DAN) per device per card. A physical debit card added to a phone and a watch produces three distinct account numbers in receipt filenames, all appearing as the original card number on bank statements.

This means receipt-side account numbers and statement-side account numbers can differ for Apple Pay transactions. Reconciliation will produce unmatched entries for any Apple Pay transaction until alias handling is implemented.

Design decision

Rather than adding a Source Account column to Accounts.xlsx (which would add schema complexity for all users to solve an edge case most won't encounter), Apple Pay alias mapping should be handled via a separate opt-in config at reconciliation time -- e.g. a Config/Aliases.json or a section in Config.env that maps DANs back to their physical card numbers.

This was explicitly considered and rejected as a core schema concern in ADR-009.

Implication for implementation

When #60 is built:

• Match statement transactions to receipts by date + amount as the primary key
• For unmatched statement entries, check the alias config before flagging as unreconciled
• Document the alias config clearly so users with Apple Pay can populate it