Bank Reconciliation Skill

Reconcile bank statement rows against GL rows and produce an .xlsx workbook that is immediately reviewable by an accountant.

Workflow

1. Identify the bank workbook and GL workbook paths.
2. Confirm the reconciliation threshold. Default to 0.00 unless the user asks for a tolerance.
3. Run scripts/recon_logic.py with the bank file, GL file, output file, and threshold.
4. Return the generated workbook and summarize:
   • matched bank row count
   • matched GL row count
   • unreconciled bank row count
   • unreconciled GL row count
5. If the user asks for follow-up analysis, use the Summary, Unreconciled Bank, and Unreconciled GL tabs first.

Output Workbook

The generated workbook should contain these tabs:

• Summary: threshold, matched counts, unreconciled counts, and basic totals
• Recon Results: matched groupings with match basis and variance notes
• Unreconciled Bank: bank rows not matched to the GL
• Unreconciled GL: GL rows not matched to the bank

Command

python3 scripts/recon_logic.py <bank_xlsx> <gl_xlsx> <output_xlsx> [threshold]

Matching Logic

Use a layered approach:

1. Match by shared extracted keys such as batch IDs, invoice IDs, vendor IDs, customer IDs, and tax/payment references.
2. Allow one-to-one, one-to-many, many-to-one, and grouped many-to-many matches when totals fall within threshold.
3. For remaining items, use semantic name grouping plus summed-amount comparison.
4. Preserve unmatched rows in dedicated tabs instead of dropping them from the deliverable.

Notes

• Read the first worksheet from each input workbook.
• Expect simple three-column inputs: date, amount, description/memo.
• Keep the workbook generation dependency-light so it can run in minimal Python environments.