Excel 自動對帳 Python Script - 財務必備工具

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
AI智慧職場 - Excel 自動對帳工具
作者：AI智慧職場（IT 經理 30 年實戰分享）
"""

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
from datetime import datetime

# ===== 設定區（依你的 Excel 欄位名稱修改）=====
CONFIG = {
    "source_a_file": "bank_statement.xlsx",   # 來源A（如銀行對帳單）
    "source_b_file": "company_ledger.xlsx",   # 來源B（如公司帳本）
    "source_a_date_col": "交易日期",
    "source_a_amount_col": "交易金額",
    "source_a_desc_col": "說明",
    "source_b_date_col": "日期",
    "source_b_amount_col": "金額",
    "source_b_desc_col": "摘要",
    "match_by": ["date", "amount"],           # 對帳依據欄位
    "output_file": "reconciliation_result.xlsx",
}

RED_FILL   = PatternFill("solid", fgColor="FFCCCC")
GREEN_FILL = PatternFill("solid", fgColor="CCFFCC")
BOLD_FONT  = Font(bold=True)


def load_and_standardize(filepath, date_col, amount_col, desc_col, prefix):
    df = pd.read_excel(filepath)
    df = df.rename(columns={
        date_col:   "date",
        amount_col: "amount",
        desc_col:   f"{prefix}_desc",
    })
    df["amount"] = pd.to_numeric(df["amount"], errors="coerce").round(2)
    df["date"]   = pd.to_datetime(df["date"], errors="coerce").dt.date
    return df


def highlight_sheet(ws, diff_rows):
    for row_idx in range(2, ws.max_row + 1):
        fill = RED_FILL if row_idx in diff_rows else None
        if fill:
            for cell in ws[row_idx]:
                cell.fill = fill


def reconcile():
    cfg = CONFIG
    ts = datetime.now().strftime("%Y%m%d_%H%M%S")
    output = cfg["output_file"].replace(".xlsx", f"_{ts}.xlsx")

    print("? 讀取資料中...")
    source_a = load_and_standardize(
        cfg["source_a_file"],
        cfg["source_a_date_col"], cfg["source_a_amount_col"], cfg["source_a_desc_col"],
        "a"
    )
    source_b = load_and_standardize(
        cfg["source_b_file"],
        cfg["source_b_date_col"], cfg["source_b_amount_col"], cfg["source_b_desc_col"],
        "b"
    )

    print("? 交叉比對中...")
    merged = pd.merge(
        source_a, source_b,
        on=cfg["match_by"],
        how="outer",
        indicator=True
    )

    matched     = merged[merged["_merge"] == "both"].drop("_merge", axis=1)
    a_only      = merged[merged["_merge"] == "left_only"].drop("_merge", axis=1)
    b_only      = merged[merged["_merge"] == "right_only"].drop("_merge", axis=1)

    print(f"✅ 對帳完成！")
    print(f"   已配對 ：{len(matched):>5} 筆")
    print(f"   僅來源A：{len(a_only):>5} 筆  ← 需人工確認")
    print(f"   僅來源B：{len(b_only):>5} 筆  ← 需人工確認")

    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        matched.to_excel(writer, sheet_name="✅ 已對帳",   index=False)
        a_only.to_excel(writer,  sheet_name="⚠️ 僅銀行有", index=False)
        b_only.to_excel(writer,  sheet_name="⚠️ 僅帳本有", index=False)

    # 為差異分頁標紅色
    wb = load_workbook(output)
    for sheet_name in ["⚠️ 僅銀行有", "⚠️ 僅帳本有"]:
        ws = wb[sheet_name]
        for row in ws.iter_rows(min_row=2):
            for cell in row:
                cell.fill = RED_FILL
        for cell in ws[1]:
            cell.font = BOLD_FONT
    wb.save(output)

    print(f"? 結果已儲存至：{output}")


if __name__ == "__main__":
    reconcile()