Bank Statement Reconcile

SKILL.md

---
name: bank-statement-reconcile
description: "Bank Statement Reconciler — Upload bank statements (CSV/Excel/PDF) + orders/invoices → AI auto-matching → Reconciliation results (matched/difference/unclaimed/unmatched). Supports Chinese banks (BOC/ICBC/CCB), Alipay/WeChat Pay, PayPal/Stripe, Amazon/Shopify/Temu. Trigger: bank reconciliation, statement matching, bank statement reconcile."
---

# Bank Statement Reconciler

AI-powered bank statement reconciliation — upload statements + orders → get matched/difference/unclaimed/unmatched results.

## AI Agent Full Flow

```python
from scripts import reconcile_bank_statements, TierConfig

result = reconcile_bank_statements(
    statement_file="bank.csv",
    order_file="orders.csv",
    statement_type="auto",
    order_type="auto",
    match_mode="smart",
    amount_tolerance=0.01,
    date_range_days=3,
    tier=TierConfig(is_pro=True),
)

# Result keys: matched, differences, unclaimed, unmatched_orders, summary, excel_path
```

## Supported Statement Formats

### Chinese Banks (CSV/Excel/PDF)
| Bank | Format | Key Columns |
|------|--------|-------------|
| BOC | CSV/Excel | Transaction Date, Amount, Counterparty, Balance, Summary |
| ICBC | CSV/Excel | Date, Amount, Counterparty Name, Balance, Summary |
| CCB | CSV/Excel | Transaction Time, Amount, Counterparty, Balance, Remark |
| ABC | CSV/Excel | Transaction Date, Amount, Counterparty Name, Balance, Usage |

### Payment Platforms
| Platform | Format | Key Columns |
|----------|--------|-------------|
| Alipay | CSV | Transaction Time, Counterparty, Amount, Status, Description |
| WeChat Pay | CSV | Transaction Time, Transaction Type, Amount, Counterparty, Remark |
| PayPal | CSV/JSON | Date, Amount, Item, Status, Counterparty |
| Stripe | CSV/JSON | Date, Amount, Description, Customer, Currency |

### E-commerce
| Platform | Format | Key Columns |
|----------|--------|-------------|
| Amazon | CSV/Excel | Order Date, Order ID, Order Status, Item Total, Payment |
| Shopify | CSV/Excel | Created, Name, Financial Status, Total, Source |
| Temu | CSV | Date, Order ID, Amount, Status, Payment Method |

## Matching Modes

### 1. Exact Matching
Same date + same amount. Best for real-time payments, bank transfers.

### 2. Fuzzy Matching
Date within ±N days + amount within ±X tolerance. Best for delayed settlements, batch payments.

### 3. Semantic Matching (PRO only)
AI-powered counterparty name similarity matching. Handles: "Alibaba" ↔ "Alibaba Cloud", "Zhang San" ↔ "Zhang San (Personal)".

## Tiered Features

| Feature | FREE | PRO |
|---------|:----:|:---:|
| Monthly statements | 50 | Unlimited |
| Bank accounts | 1 | Unlimited |
| Output format | Text | Excel + JSON |
| Alipay/WeChat | — | Yes |
| PayPal/Stripe | — | Yes |
| Semantic matching | — | Yes |
| Feishu card | — | Yes |
| Price | Free | $0.01/call |

## Excel Export Format

Exported Excel (`reconciliation_YYYYMMDD_HHMMSS.xlsx`) contains:
- **Sheet: Matched** — Matched transactions
- **Sheet: Differences** — Amount differences
- **Sheet: Unclaimed** — Money without order (unclaimed)
- **Sheet: Unmatched** — Order without payment (unmatched)
- **Sheet: Summary** — Summary statistics

## Feishu Card Output

PRO tier supports Feishu interactive cards with match rate, amounts, and action buttons.

## Billing

- Billing via `skillpay.me/api/v1/billing/charge`
- User data transmitted to SkillPay for billing identification
- $0.01 USD per reconciliation call (PRO tier)

## Required Environment Variables

| Variable | Description |
|----------|-------------|
| `FEISHU_USER_ID` | User ID for billing |
| `SKILL_BILLING_API_KEY` | SkillPay Builder API Key |
| `SKILL_BILLING_SKILL_ID` | SkillPay Skill ID (default: bank-statement-reconcile) |
| `OPENAI_API_KEY` | AI model API key (for semantic matching in PRO) |

## Common Errors

| Error | Cause | Solution |
|-------|-------|----------|
| `UNSUPPORTED_FORMAT` | File format not supported | Convert to CSV/Excel |
| `COLUMN_NOT_FOUND` | Required column missing | Check statement format |
| `AMOUNT_MISMATCH` | Amount parsing failed | Verify currency/decimal |
| `TIER_LIMIT_EXCEEDED` | Statement count exceeds tier | Upgrade or split files |