Bank Statement vs Internal Records — Reconciliation Skill

Triggers

• "reconcile" or "run reconciliation" → ask for 2 file inputs, then produce Excel report

---

Step 0 — Collect inputs (ALWAYS do this first)

Before writing any code, confirm both files are available. Check uploaded_files in context.

If fewer than 2 files are uploaded, ask the user:

"กรุณาอัปโหลด 2 ไฟล์ครับ:

1. Bank Statement — ไฟล์ที่ได้จากธนาคาร (.xlsx หรือ .csv)
2. Internal Records — ไฟล์บันทึกภายในของบริษัท (.xlsx หรือ .csv)"

Do not proceed until both files are present.

If exactly 2 files are uploaded, confirm which is which:

"ยืนยันก่อนนะครับ:

• [filename_1] = Bank Statement ✓
• [filename_2] = Internal Records ✓ ถูกต้องไหมครับ หรือสลับกัน?"

Proceed only after user confirms.

---

Step 1 — Read both files

Use bash_tool to read each file. Always read /mnt/skills/public/xlsx/SKILL.md first.

# Detect file type and read accordingly
python3 << 'EOF'
import pandas as pd, sys, os

bank_path = "/mnt/user-data/uploads/<bank_filename>"
internal_path = "/mnt/user-data/uploads/<internal_filename>"

def read_file(path):
    ext = os.path.splitext(path)[1].lower()
    if ext == ".csv":
        return pd.read_csv(path)
    else:
        return pd.read_excel(path)

df_bank = read_file(bank_path)
df_internal = read_file(internal_path)

print("=== BANK COLUMNS ===")
print(df_bank.columns.tolist())
print(df_bank.dtypes)
print(df_bank.head(3).to_string())

print("\n=== INTERNAL COLUMNS ===")
print(df_internal.columns.tolist())
print(df_internal.dtypes)
print(df_internal.head(3).to_string())
EOF

After reading, identify and map these key columns in each file:

Logical field	Bank column (detected)	Internal column (detected)
Transaction date	e.g. วันที่ / Date	e.g. date / txn_date
Reference / Doc no	e.g. เลขที่ / Ref	e.g. ref_no / doc_no
Description	e.g. รายการ / Desc	e.g. description
Amount	e.g. จำนวนเงิน	e.g. amount
Debit / Credit	e.g. ถอน / ฝาก	e.g. debit / credit

If columns are ambiguous, print a mapping summary and ask the user to confirm before continuing.

---

Step 2 — Clean & normalise both dataframes

# Standardise column names to lowercase snake_case
# Parse dates → datetime, coerce errors
# Strip whitespace from string columns
# Round amounts to 2 decimal places
# Unify debit/credit into a single signed `amount` column:
#   credit = positive, debit = negative
# Drop fully empty rows
# Create match_key = str(date.date()) + "|" + str(round(abs(amount), 2))
#   (used for fuzzy matching — date + amount, ignoring description)

---

Step 3 — Run reconciliation logic (Python)

Match strategy (in order)

1. Exact match — same match_key (date + amount) AND same reference/doc number → MATCHED
2. Amount+Date match — same match_key but different/missing reference → MATCHED (no ref)
3. Amount match ±1 day — amount matches, date differs by ≤ 1 calendar day → NEAR MATCH
4. Unmatched bank — bank rows with no match in internal → BANK ONLY
5. Unmatched internal — internal rows with no match in bank → INTERNAL ONLY

# Use pandas merge for exact + amount+date matches
# For ±1 day near matches: iterate unmatched rows, check if amount exists
#   in opposite dataframe within date ± 1 day window
# Track which rows are consumed — no double-matching
# Assign match_status column to every row

Computed summary KPIs

After matching, compute:

KPI	Formula
bank_total	SUM of all bank amounts (absolute)
internal_total	SUM of all internal amounts (absolute)
difference	bank_total − internal_total
matched_count	rows with MATCHED or MATCHED (no ref)
matched_amount	SUM of matched bank amounts
near_match_count	rows with NEAR MATCH
unmatched_bank_count	BANK ONLY rows
unmatched_bank_amount	SUM of BANK ONLY amounts
unmatched_internal_count	INTERNAL ONLY rows
unmatched_internal_amount	SUM of INTERNAL ONLY amounts
match_rate	matched_count / total_bank_rows

---

Step 4 — Build Excel report with openpyxl

Font: Arial 10. Do NOT use pandas to_excel (no formatting control).

General formatting rules

• Row 1 = header: bold, white text (FFFFFF), dark blue fill (1F3864), center-aligned, frozen
• Data rows: alternating white / light gray (F2F2F2)
• Auto-fit column widths (min 10, max 55)
• Currency columns: #,##0.00
• Date columns: YYYY-MM-DD
• Percentage columns: 0.00%
• Thai text: UTF-8 — openpyxl handles natively

---

Sheet 1 — "Summary" (Dashboard)

Layout: key-value table in columns A–B, no tabular data.

Section A — File info

Label	Value
Bank File	<filename>
Internal File	<filename>
Report Generated	today's date
Date Range (Bank)	min–max date in bank file
Date Range (Internal)	min–max date in internal

Section B — Reconciliation result

Label	Value	Format
Bank Total	bank_total	#,##0.00
Internal Total	internal_total	#,##0.00
Difference	difference	#,##0.00 — red if ≠ 0, green if 0
Total Bank Rows	total bank rows	#,##0
Total Internal Rows	total int rows	#,##0

Section C — Match breakdown

Label	Count	Amount
✅ Matched	matched_count	matched_amount
⚠️ Near Match (±1 day)	near_match_count	near_match_amount
❌ Bank Only	unmatched_bank_count	unmatched_bank_amount
❌ Internal Only	unmatched_internal_count	unmatched_internal_amount
Match Rate	—	match_rate (0.00% format)

• Section headers: bold, 1F3864 fill, white text
• "Difference" value cell: green fill EAF3DE if 0, red fill FCEBEB if non-zero
• Match Rate: green if ≥ 95%, amber if 80–94%, red if < 80%

---

Sheet 2 — "Matched"

All rows with match_status IN (MATCHED, MATCHED (no ref), NEAR MATCH).

Columns: match_status | bank_date | bank_ref | bank_description | bank_amount | internal_date | internal_ref | internal_description | internal_amount | amount_diff

• amount_diff = bank_amount − internal_amount (should be 0 for exact matches)
• match_status cell fill:

  MATCHED          → #EAF3DE (green)
  MATCHED (no ref) → #E6F1FB (blue)
  NEAR MATCH       → #FAEEDA (amber)
  

• Sort: bank_date ASC, then bank_amount DESC

---

Sheet 3 — "Bank Only (Unmatched)"

All bank rows with match_status = 'BANK ONLY'.

Columns: bank_date | bank_ref | bank_description | bank_amount | remark

• All rows: light red fill FCEBEB
• remark column: pre-fill with "ไม่พบในระบบภายใน" for every row
• Last row: Total row — bold, FAEEDA fill, SUM of bank_amount
• Sort: bank_date ASC

---

Sheet 4 — "Internal Only (Unmatched)"

All internal rows with match_status = 'INTERNAL ONLY'.

Columns: internal_date | internal_ref | internal_description | internal_amount | remark

• All rows: light red fill FCEBEB
• remark column: pre-fill with "ไม่พบใน Bank Statement" for every row
• Last row: Total row — bold, FAEEDA fill, SUM of internal_amount
• Sort: internal_date ASC

---

Step 5 — File naming & output

Save to:

/mnt/user-data/outputs/Reconcile_Bank_vs_Internal_YYYYMMDD.xlsx

where YYYYMMDD = today's date (datetime.date.today().strftime('%Y%m%d')).

Then call present_files with the output path.

After presenting, print a plain-text summary in chat:

✅ Reconciliation complete

Bank Total      : ฿ X,XXX,XXX.XX
Internal Total  : ฿ X,XXX,XXX.XX
Difference      : ฿ X,XXX.XX  ← 0 = balanced

Matched         : XX rows  (XX.X%)
Near Match      : XX rows
Bank Only       : XX rows  ← requires follow-up
Internal Only   : XX rows  ← requires follow-up

---

Edge cases to handle

Situation	Handling
One file has header on row 2+	Detect by checking if row 1 is all strings with no numbers; skip rows accordingly
Amount column has currency symbols (฿, $, ,)	Strip with str.replace before casting to float
Date format is DD/MM/YYYY or MM/DD/YYYY	Try multiple formats with pd.to_datetime(dayfirst=True)
Debit/Credit in separate columns	Combine: amount = credit − debit
Duplicate rows in either file	Flag duplicates in a duplicate_flag column; still attempt matching
Empty file or < 2 rows of data	Abort and tell user the file appears empty

---

KPI reference (for context only)

KPI	Target
Match Rate	≥ 95%
Difference	= 0 (fully balanced)
Bank Only	= 0 rows
Internal Only	= 0 rows

Key notes

• Match key = date + amount (not description — descriptions often differ between bank and internal)
• Near match window = ±1 calendar day (accounts for value-date vs posting-date differences)
• All amounts normalised to signed float before matching (positive = inflow, negative = outflow)
• No row is matched more than once (first-match-wins to avoid double-counting)