finance-reconcile（财务协调）

Finance Audit

Overview

Comprehensive financial audit tool that validates balance sheets and income statements. Performs four core audits: balance sheet equilibrium, income statement verification, account change tracking, and cross-statement relationship validation.

Quick Start

Basic Balance Sheet Audit

Verify balance sheet with account changes:

scripts/reconcile.py \
    --balance-sheet balance_sheet.xlsx \
    --account-changes account_changes.xlsx \
    --output audit_report.xlsx

Complete Financial Audit (Balance Sheet + Income Statement)

Full audit including profit statement:

scripts/reconcile.py \
    --balance-sheet balance_sheet.xlsx \
    --account-changes account_changes.xlsx \
    --income-statement income_statement.xlsx \
    --income-details income_details.xlsx \
    --transactions transactions.xlsx \
    --output complete_audit.xlsx \
    --period "2024年1月"

Using Custom Column Names

Use JSON config file for custom column names:

scripts/reconcile.py \
    --balance-sheet bs.xlsx \
    --account-changes ac.xlsx \
    --config my_config.json

Command Parameters

Required File Formats

1. Balance Sheet (资产负债表) - Required

2. Account Changes (科目变动明细表) - Required

3. Income Statement (利润表) - Optional

4. Income Details (利润明细表) - Optional

5. Transactions (交易明细表) - Optional

See references/file_formats.md for detailed format specifications and custom column configuration.

Audit Objectives

The script performs four core audit objectives:

1. Balance Sheet Equilibrium Verification

Formula: 资产总计 = 负债总计 + 所有者权益总计

Verifies that the balance sheet is balanced overall.

2. Income Statement Verification

Logic: Income statement items = Corresponding detail summary

Validates each income statement item (revenue, expenses, etc.) against detail records.

3. Account Change Tracking

Logic: Opening Balance + Changes = Closing Balance

For each account:

• Asset accounts: Closing = Opening + Debit - Credit
• Liability/Equity accounts: Closing = Opening + Credit - Debit

4. Cross-Statement Relationship Validation

Logic: Retained earnings change ≈ Net profit

Validates relationships between balance sheet and income statement.

See references/verification_rules.md for detailed audit logic and troubleshooting.

Audit Outputs

Console Report

================================================================================
                              财务审计报告 - 本期
================================================================================

一、审计概述
--------------------------------------------------------------------------------
审计结果: 通过 ✓

二、资产负债表审计
--------------------------------------------------------------------------------
资产总计: ¥1,000,000.00
负债总计: ¥600,000.00
所有者权益总计: ¥400,000.00
资产负债平衡: 是 ✓

三、利润表审计
--------------------------------------------------------------------------------
利润项目匹配: 5/5

四、报表勾稽关系
--------------------------------------------------------------------------------
净利润: ¥100,000.00
未分配利润变动: ¥100,000.00
勾稽关系验证: 通过 ✓

五、不平衡项目清单
--------------------------------------------------------------------------------
  无不平衡项目

Excel Audit Report

When --output is specified, generates an Excel file with these sheets:

Account Change Analysis Table

Using the Script as a Module

from scripts.reconcile import (
    audit_financial_statements,
    generate_audit_report,
    export_audit_report
)

result, accounts = audit_financial_statements(
    balance_sheet_file='balance_sheet.xlsx',
    account_changes_file='account_changes.xlsx',
    income_statement_file='income_statement.xlsx',
    income_details_file='income_details.xlsx',
    transactions_file='transactions.xlsx',
    tolerance=0.01,
    period='2024年1月'
)

# Print console report
print(generate_audit_report(result, period='2024年1月'))

# Export Excel report
export_audit_report(result, 'audit_report.xlsx', accounts, period='2024年1月')

# Check audit result
if result.is_passed:
    print("Audit passed!")
else:
    print(f"Found {len(result.unbalanced_items)} unbalanced items")

Tolerance Setting

Default tolerance is ±0.01 yuan. Adjust with --tolerance:

scripts/reconcile.py \
    --balance-sheet bs.xlsx \
    --account-changes ac.xlsx \
    --tolerance 1.0

Consider these factors when setting tolerance:

• Exchange rate fluctuations (for foreign currency)
• System precision differences
• Rounding errors

Troubleshooting

Balance Sheet Not Balanced

Possible causes:

• Incorrect balance entries
• Incorrect totals calculation
• Opening/closing mismatch

Solutions:

1. Check balance sheet total rows
2. Verify account balances
3. Check account type classification

Account Changes Unbalanced

Possible causes:

• Missing change records
• Incorrect debit/credit direction
• Account name mismatch

Solutions:

1. Ensure account names match exactly
2. Verify debit/credit records
3. Check for missing change records

Income Statement Not Matching

Possible causes:

• Missing detail items
• Inconsistent item names
• Incorrect summary calculation

Solutions:

1. Ensure detail table includes all items
2. Check item name consistency
3. Verify summary calculations

Cross-Statement Relationship Failed

Possible causes:

• Profit distribution (dividends, retained earnings)
• Mismatched accounting periods
• Incorrect net profit calculation

Solutions:

1. Consider profit distribution events
2. Verify income statement net profit
3. Confirm period consistency

Resources

scripts/reconcile.py

Core audit script supporting:

• Balance sheet equilibrium verification
• Income statement validation
• Account change tracking
• Cross-statement relationship validation
• Transaction impact tracing
• Comprehensive audit report generation

references/file_formats.md

Detailed file format specifications, column name mapping, and configuration options.

references/verification_rules.md

Comprehensive audit rules, validation logic, output examples, and troubleshooting guide.