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 statement path and GL workbook path.
2. Accept either a bank statement .xlsx file or a bank statement .pdf file.
3. If the bank statement is a PDF, run the workflow so it first extracts the bank statement lines into a structured workbook, then reconciles that extracted workbook to the GL.
4. Confirm the reconciliation threshold. Default to 0.00 unless the user asks for a tolerance.
5. Run scripts/recon_logic.py with the bank file, GL file, output file, and threshold.
6. Return the generated workbook and summarize:
   • matched bank row count
   • matched GL row count
   • unreconciled bank row count
   • unreconciled GL row count
7. 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#

bash

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

When the bank input is a PDF, the script also creates a companion extracted workbook beside the PDF (same basename with _extracted.xlsx) before running reconciliation.

Matching Logic#

Use a layered approach:

1. Preserve the original signs from both source files in the output.
2. Compare bank and GL amounts using absolute values for matching so bank polarity and accounting debit/credit polarity can reconcile without rewriting displayed source amounts.
3. Match by shared extracted keys such as batch IDs, invoice IDs, vendor IDs, customer IDs, and tax/payment references.
4. Allow one-to-one, one-to-many, many-to-one, and grouped many-to-many matches when totals fall within threshold.
5. For remaining items, use semantic name grouping plus summed-amount comparison.
6. 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.
• For text-based bank statement PDFs, the script extracts transaction rows by reading the PDF content streams and reconstructing the transaction table into a workbook.
• The PDF path is best for digital statements with selectable text; scanned-image PDFs would still need OCR or a multimodal extraction path.
• Keep the workbook generation dependency-light so it can run in minimal Python environments.