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.
- Accept either a bank statement
.xlsx file or a bank statement .pdf file. - 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.
- Confirm the reconciliation threshold. Default to
0.00 unless the user asks for a tolerance. - Run
scripts/recon_logic.py with the bank file, GL file, output file, and threshold. - 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 - INLINECODE9 : matched groupings with match basis and variance notes
- INLINECODE10 : bank rows not matched to the GL
- INLINECODE11 : GL rows not matched to the bank
Command
CODEBLOCK0
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.
- 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.
- Match by shared extracted keys such as batch IDs, invoice IDs, vendor IDs, customer IDs, and tax/payment references.
- Allow one-to-one, one-to-many, many-to-one, and grouped many-to-many matches when totals fall within threshold.
- For remaining items, use semantic name grouping plus summed-amount comparison.
- 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.
银行对账技能
将银行对账单行与总账行进行对账，生成一个可供会计师立即审阅的.xlsx工作簿。
工作流程
- 1. 识别银行对账单路径和总账工作簿路径。
- 接受银行对账单.xlsx文件或银行对账单.pdf文件。
- 如果银行对账单是PDF格式，则运行工作流程，先提取银行对账单行至结构化工作簿，然后将提取的工作簿与总账进行对账。
- 确认对账阈值。除非用户要求容差，否则默认为0.00。
- 使用银行文件、总账文件、输出文件和阈值运行scripts/recon_logic.py。
- 返回生成的工作簿并汇总：
- 匹配的银行行数
- 匹配的总账行数
- 未对账的银行行数
- 未对账的总账行数
- 5. 如果用户要求后续分析，请先使用摘要、未对账银行和未对账总账选项卡。
输出工作簿
生成的工作簿应包含以下选项卡：
- - 摘要：阈值、匹配数量、未对账数量及基本总计
- 对账结果：匹配分组及匹配依据和差异说明
- 未对账银行：未与总账匹配的银行行
- 未对账总账：未与银行匹配的总账行
命令
bash
python3 scripts/reconlogic.py <银行xlsx或pdf> <总账xlsx> <输出xlsx> [阈值]
当银行输入为PDF时，脚本会在运行对账前，在PDF旁边创建一个配套的提取工作簿（相同基本名称，后缀为_extracted.xlsx）。
匹配逻辑
采用分层方法：
- 1. 在输出中保留两个源文件的原始符号。
- 使用绝对值比较银行和总账金额进行匹配，以便银行正负性和会计借贷方正负性能够对账，而无需重写显示的源金额。
- 通过共享的提取键（如批次ID、发票ID、供应商ID、客户ID以及税务/付款参考）进行匹配。
- 当总计在阈值范围内时，允许一对一、一对多、多对一和分组多对多匹配。
- 对于剩余项目，使用语义名称分组加汇总金额比较。
- 将未匹配的行保留在专用选项卡中，而不是从交付物中删除。
备注
- - 从每个输入工作簿中读取第一个工作表。
- 期望简单的三列输入：日期、金额、描述/备注。
- 对于基于文本的银行对账单PDF，脚本通过读取PDF内容流提取交易行，并将交易表重构为工作簿。
- PDF路径最适合具有可选文本的数字对账单；扫描图像PDF仍需要OCR或多模态提取路径。
- 保持工作簿生成的依赖项轻量化，以便在最小Python环境中运行。
