# Reconcile Bank vs Internal

**Creator:** Wasu
**Version:** 1
**Version date:** 2026-06-18
**Exported from:** Skill Hub

---

# 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.

```bash
# 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

```python
# 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`

```python
# 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)