bank-flow-reconciliation

Author：nigo (Tu Jiabing) | WeChat Official Account：Retrograde Dog

Audit practice + digital tools, sharing practical experience in improving audit efficiency.

Bank Statement Merging and Reconciliation

Overview

Three scripts, in workflow order:

Pre-check (scripts/bankflow_precheck.py): Read files, output column names, sample rows, amount statistics, and account distribution. Only explore, do not judge.
Statement merging (scripts/bankflow_merge.py): Multi-bank multi-format statements → unified 12-column standardized Excel
Statement reconciliation (scripts/bankflow_reconcile.py): Bank statements vs journal, 9-layer progressive matching, output reconciliation working papers

AI's work: run script → look at output → fill in configuration → run script → ask the user whether they want AI review. The matching logic is all in the code; AI does not need to understand the internals of the engine.

Data Preparation

Reconciliation requires two sets of data:Journal(general ledger/detailed ledger) andbank statements。

Required fields for the journal

Element	Description
Bank deposit account line	The account code usually starts with 1002. If there is no account code column, the account name "Bank deposit" can be used instead
Date column	Posting date or voucher date
Debit/credit amount column	Two separate columns; there cannot be only one column with signed amounts
Voucher number column	Identify multiple journal lines of the same voucher
Description column	Key information source for the L2 matching layer
Customer/supplier information	⭐ Decisive factor. With customer/supplier → matching rate 90%+, without customer/supplier → 30-50%

⭐ Customer/supplier information (two sources, choose one)

Source	Configuration	Scenario
① Customer/supplier auxiliary accounting column	`客商辅助项列名: "客户,供应商"`	Yonyou/Kingdee standard account set, the customer/supplier is in the auxiliary accounting item of the offset account line (split mode）
② Counterparty column	`交易对手列名: "往来单位名称"`	The bank deposit line has an independent "business partner"/"counterparty" column (direct mode, higher matching rate)

Direct mode has an empirical rate of 92.6%, split mode 78.5%. Prefer direct mode, and use split mode only when the bank deposit line has no customer/supplier value.

Required fields for bank statements

Element	Description
Transaction date	Transaction date of each transaction
Amount (debit/credit or receipt/payment)	Two separate columns
Counterparty account name	Strongly recommended, cross-match with the customer/supplier in the journal
Description	L2 matching clue

File format .xlsx/.xls/.csv Both are fine; headers not on the first row or summary rows at the end can both be handled.

Workflow

Step 1: Determine the task type

Merge only: multiple bank statements → one standardized Excel
Reconcile only: existing standardized statements → reconcile with the journal
Merge + reconcile: merge first, then reconcile

The user says "reconcile" but provides bank statement files from multiple banks → usually they need to be merged first.

Step 2: Run the pre-check

python SKILL_DIR/scripts/bankflow_precheck.py \
  --gl "序时账.xlsx" \
  --bank "银行流水.xls" \
  --bank-subject "1002"

Output: file format, column names, first 3 sample rows, amount statistics,candidate counterparty column diagnostic table(column name + non-null rate + sample values), bank account distribution, entity column distribution. The AI looks at this information to make the judgments in Step 3.

Common parameters:--gl-subject-col(account code column name),--bank-subject-name(filter by name),--bank-header-row(skip leading rows). See the full table at references/配置参数详解.md。

Step 3: Make judgments

Look at the pre-check output and make 4 decisions:

Decision 1: Column mapping

Based on the column names and sample values, determine which column corresponds to each field in the configuration. Look at the actual column names, do not guess—"account number"/"account code"/"account encoding" are named differently in different software.

Pitfall reminders：

No account code column: use the account name column instead, configure 科目代码列名: "科目名称", 银行科目代码: "银行存款"
Account code mixed in the encoding column(e.g., "1002/8111001012600894851"): use that column directly, configure 银行科目代码: "1002", the engine's startswith handles it naturally
Fake counterparty pitfall：交易对手column is 100% non-null but the sample values are bank account names (e.g., "Agricultural Bank of China Mojiang County Branch 007316") → not a real counterparty. Judge by the sample values, do not be misled by the non-null rate. The real counterparty may be in the composite field of the counterpart account row

Decision 2: Is the counterparty information sufficient?

Look at the diagnostic table from the pre-check: the candidate counterparty column has values on the bank deposit rows → direct mode (交易对手列名). Only has values on the counterpart account rows → split mode (客商辅助项列名）。

⓾ Counterparty missing confirmation (STOP)

If all candidate columns have no values on either side, or the sample values are non-counterparty information such as bank account names—stop and use the question tool to ask the user.

Why stop: the match rate will plummet from 90%+ to 30-50%, too big a drop. The user needs informed consent—they may prefer to organize the counterparty data first rather than accept a working paper where more than half cannot be matched.

[A] Continue: match using only amount + date, accept the low match rate

[B] Add fields: I will specify the correct counterparty column

[C] Cancel: improve the data first

Decision 3: Are multiple accounts covered?

Look at the account distribution output by the pre-check.

Single account or accounts consistent on both sides → continue directly.

⓾ Multiple accounts not fully covered confirmation (STOP)

The journal has 5 bank accounts but the statements only have 2—continuing silently would make all the other 3 accounts unmatchable, and the user would think everything was reconciled when in fact only part was. In audit scenarios this cognitive discrepancy is a risk.

[A] Reconcile only the matched accounts: filter the journal to keep only those with statements

[B] Reconcile all: do not set an account column name, reconcile as a whole (lower match rate)

[C] Add statements: I will supply the missing bank statement files

Multiple account identifiers inconsistent: The journal uses "China Merchants Bank", while the statement uses account number "3960xxx" → grouping keys do not match → 0%. In this case, do not set the account column name (choose [B]) and reconcile as a whole.

Company columns must be symmetric: The journal has a company column but the statement does not → 0%. Do not configure 公司列名, instead use 主体过滤。

Performance: When >5000 rows, it is strongly recommended to set the account column name for grouping (16s vs 129s).

Decision 4: Is the scope aligned? (Primary cause of low match rate)

Look at the bank account distribution and entity distribution in the pre-check. When the journal scope is significantly larger than the statement, a large number of records cannot be matched.

Type	Typical scenario	Solution	Evidence
Account code range	The journal contains 32 bank accounts, the statement has only 1	Precisely limit `银行科目代码: "100201"`	16% → 99%
Entity range	The journal contains 11 entities, the statement has only 1	`主体过滤: "主体,西安德诺"`	4% → 93%
Date range	The journal covers the full year, the statement covers half a year	`日期范围: "2025-01-01,2025-06-30"`	Low → normal

⓾ Scope limitation confirmation (STOP)

Configure 主体过滤/日期范围 or narrow 银行科目代码 before — filtered records do not participate in reconciliation at all, and users may mistakenly think everything has been reconciled. Omitting records from audit working papers without disclosure is a major risk.

[A] Confirm limitation: accept the high match rate after scope alignment

[B] Keep everything: no limitation, reconcile as a whole (lower match rate but no omissions)

Only after choosing [A] should you configure filter parameters. When reporting results, disclose the filter conditions actually used.

Step 4: Write the configuration

Based on the judgment result, write reconcile_config.json。

Complete parameter table and JSON exampleSee references/配置参数详解.md(all fields of gl_config + bank_config, customer/vendor mode selection, fixed mapping for reconciliation linked from merged results).

Step 5: Run reconciliation

python SKILL_DIR/scripts/bankflow_reconcile.py \
  reconcile_config.json \
  --output "输入文件所在目录/核对底稿.xlsx"

If --output is not passed, it is saved to CWD by default, with filename 银行流水核对底稿_YYYYMMDD_HHMMSS.xlsx. It is recommended to explicitly pass --output。

Step 6: AI review (optional)

STOP — after running Step 5, stop first. Do not automatically add --ai-review and rerun.

Why stop and ask: AI review reads unmatched records one by one (possibly hundreds), taking 1-2 minutes and considerable tokens. But when the match rate is 90%+, it is actually more efficient to leave the remaining dozen or so records to an accountant for manual review (they have business context). Automatic review = wasting time and money when the user does not need it. This is the gate most often skipped in actual use — once you see the match rate, you want to keep running, but review is optional, and the choice belongs to the user.

Read the match rate output by the engine, and use the question tool to ask:

Current match rate: amount 99.2% / count 98.4%, 112 unmatched records.

[A] Perform AI review: Review unmatched records one by one, and write matchable ones into the working papers (estimated 1-2 minutes)

[B] Do not review: Produce the working papers directly, leave the rest to manual review (recommended: the match rate is already high)

[C] Fix scope/data first: When the match rate is low, return to Decision 4

Only after choosing [A] do you enter AI review (see references/AI复核流程.mdfor details). When unmatched records > 500, the engine automatically skips — this means the scope is not aligned, and the scope should be fixed rather than forcing a review.

Expected match rate

Scenario	Expected amount match rate
Range aligned + has counterparty	90%~99% (normal)
Range aligned + no counterparty + has summary	70%~90%
No counterparty + no summary	30%~50%
Range not aligned	<50% (fix range first)

An amount match rate ≥ 90% is considered a good result; no need to analyze the reasons. The record count match rate is usually lower than the amount match rate—it is normal for the journal to aggregate multiple small expense reimbursements into a single voucher (e.g., 50 bank transactions corresponding to 1 GL entry), which lowers the record count match rate but does not affect the reconciliation conclusion. As long as the totals balance (bank transaction income/expenses ≈ journal debits/credits), the working paper can be delivered directly, and unmatched records can be handed to accounting for manual review.Do not proactively provide lengthy analysis of why the match rate is not high—the user wants the working paper, not an analysis report.

Dependency installation

pip install pandas openpyxl xlrd rapidfuzz

xlrd Only needed when reading .xls.rapidfuzz Optional (falls back to difflib).

Reference documents

Document	When to consult
`references/配置参数详解.md`	When writing configuration—parameter tables and JSON examples for all three modules
`references/AI复核流程.md`	When performing AI review—monthly batching, subagent prompts, three levels of judgment