Case 04472

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

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.