iBanFirst MCP with Claude on Excel.

---
name: ibanfirst-excel-sync
description: Sync iBanFirst live data (wallets, balances, IBANs, payments, FX rates,
  transactions) into any Excel workbook. Trigger this skill when the user asks to sync,
  refresh, or update iBanFirst data into a workbook; pull live balances, IBANs, or wallet
  details from iBanFirst; import iBanFirst transactions or financial movements; update FX
  rates from iBanFirst; check payment status; generate a bulk payments CSV; or any request
  mentioning "iBanFirst MCP", "sync iBanFirst", or "refresh from iBanFirst". Also trigger
  when the user asks about planned payments, pending operations, or wants to export
  operations to CSV for iBanFirst.
---

## When to use
Trigger this skill when the user asks to:
- Sync, refresh, or update iBanFirst data into the workbook
- Pull live balances, IBANs, or wallet details from iBanFirst
- Import iBanFirst transactions / financial movements
- Update FX rates from iBanFirst
- Check payment status from iBanFirst
- Any request mentioning "iBanFirst MCP", "sync iBanFirst", or "refresh from iBanFirst"

---

## Core principle: always discover, never assume

**Before writing anything to the workbook, always run the Discovery phase.**
Never hardcode sheet names, column positions, or row offsets — these vary per client.
Every workflow below starts with Discovery; skip it only if already done in the same session.

---

## Phase 0 — Workbook Discovery

Run once at the start of each session. Results are reused by all subsequent workflows.

### 0.1 Sheet identification
List all sheet names in the workbook. Then map each sheet to its role by looking for:

| Role | Identification signals |
|---|---|
| **Accounts** | Contains columns for IBAN / account number, currency, balance. May be named "Accounts", "Comptes", "Wallets", or similar. |
| **Transactions** | Contains columns for date, amount, debit/credit type, description. Historical data. May be named "Transactions", "Mouvements", "History". |
| **Planned Operations** | Same structure as Transactions but forward-looking. May be named "Planned", "Opérations prévues", "Pending". |
| **FX Rates** | Contains currency codes and exchange rates. May be named "FX", "FX Rates", "Taux", "Exchange Rates". |

If a sheet cannot be confidently identified, ask the user before proceeding.

### 0.2 Column discovery (per sheet)
For each identified sheet, read the header row (scan the first 5 rows to find it — it may not be row 1). Map column letters/indices to semantic roles:

**Accounts sheet — locate columns for:**
- Bank / institution name
- Account type / label
- Account number / IBAN
- Currency
- Native balance (numeric, point-in-time)
- EUR equivalent (formula-driven)
- Notes / last updated

**Transactions & Planned Operations sheets — locate columns for:**
- Date
- Bank / institution
- Account type
- Currency
- Amount (absolute value)
- Type (Credit / Debit)
- Category
- Description / label
- EUR equivalent (formula)
- Status
- Notes / dedup ID (look for `iBanFirst:` prefix values to confirm)

**FX Rates sheet — locate:**
- Currency code column
- Rate-to-EUR column
- Last updated column / cell
- Source note cell

### 0.3 iBanFirst rows discovery (Accounts sheet)
Scan the Bank/institution column for rows containing "iBanFirst" (case-insensitive). Record their row indices — these are the rows to update. If none exist, ask the user where to insert iBanFirst rows.

### 0.4 Dedup column identification (Transactions / Planned Operations)
Confirm which column holds dedup IDs by scanning for cells containing the pattern `iBanFirst:`. If found, that column is the dedup column. If not found, use the Notes column identified in 0.2.

### 0.5 EUR Equivalent formula template
Read an existing EUR Equivalent cell (if any) to capture the exact formula pattern used (e.g. VLOOKUP, INDEX/MATCH, direct rate reference). Replicate this exact formula structure when inserting new rows — never invent a new formula shape.

### 0.6 Wallet ID mapping
Call `get_wallets` once. For each wallet returned, attempt to match it to an existing Accounts row by currency or label. Record the mapping `{ walletId → rowIndex, accountType label }`. If a wallet has no matching row, flag it to the user.

---

## Available iBanFirst MCP Tools

| Tool | Purpose |
|---|---|
| `get_wallets` | List all wallets (id, tag, currency, booking balance, last movement date) |
| `get_wallet_details` | Full wallet detail: IBAN (`accountNumber`), BIC, holder, correspondent bank |
| `get_wallet_balance` | Balance on a specific date (params: `wallet_id`, `date` YYYY-MM-DD) |
| `get_financial_movements` | All transaction history across all wallets |
| `get_payments_by_status` | Payments filtered by status: `all`, `planified`, `finalized`, `rejected`, `canceled`, `refused`, `blocked`, `waitingconfirmation` |
| `get_payment_details` | Full detail of a single payment by ID |
| `get_fx_rates` | Live FX rate for a currency pair (e.g. `EURUSD`, `EURGBP`) |
| `get_external_bank_accounts` | List registered external/beneficiary accounts |
| `get_external_bank_account_by_id` | Detail of a specific external account |

---

## Workflows

### 1. Refresh Accounts sheet (balances + IBANs)
**Trigger:** "update my iBanFirst accounts", "refresh balances", "sync account numbers"

1. Run Phase 0 if not already done.
2. Call `get_wallet_details` for each wallet to retrieve IBAN, BIC, holder.
3. For each wallet, write to its discovered row:
   - **IBAN column**: IBAN from `accountNumber`
   - **Currency column**: wallet currency
   - **Native Balance column**: `bookingAmount.value` — hardcoded value, never a formula
   - **Notes column**: `"Updated via MCP iBanFirst {DD/MM/YYYY HH:MM:SS}"`
4. Verify the EUR Equivalent column still contains a formula (do not overwrite).
5. Done when all iBanFirst rows have a non-empty IBAN, non-zero balance, and a dated note.

---

### 2. Import financial movements into Transactions sheet
**Trigger:** "import iBanFirst transactions", "sync movements", "add iBanFirst transactions"

1. Run Phase 0 if not already done.
2. Call `get_financial_movements` to retrieve the full movement list.
3. Read the dedup column (discovered in 0.4) to build the existing ID set.
4. Filter to movements whose `id` does NOT already appear as `iBanFirst:{id}` in the dedup column.
5. Sort new movements by `bookingDate` ascending (then `id` for same-date stability).
6. For each new movement, append a row using the discovered column positions:
   - **Date**: `bookingDate` converted to the workbook's date serial format
   - **Bank**: `"iBanFirst"`
   - **Account Type**: label from wallet mapping (Phase 0.6)
   - **Currency**: `amount.currency`
   - **Amount**: `Math.abs(amount.value)`
   - **Type**: `"Credit"` if `amount.value >= 0`, else `"Debit"`
   - **Category**: map description keywords → category (e.g. `"Achat / vente de devises"` → `"FX"`, `"Virement"` → `"Transfer"`)
   - **Description**: `description` field
   - **EUR Equivalent**: replicate the formula template discovered in Phase 0.5, adjusted for the new row number
   - **Status**: `"Completed"`
   - **Dedup / Notes**: `"iBanFirst:{id}"`
7. Sort the full data range by the Date column ascending.
8. Apply the same formatting style (colors, fonts, borders, alternating rows) already present in the sheet — match existing rows exactly, do not invent new styles.
9. Done when new movements appear sorted by date, with `iBanFirst:` IDs in the dedup column, no duplicates.

---

### 3. Import planned/pending payments into Planned Operations sheet
**Trigger:** "sync planned payments", "import pending iBanFirst payments"

1. Run Phase 0 if not already done.
2. Call `get_payments_by_status` with `"planified"` and `"waitingconfirmation"`.
3. Call `get_payment_details` for each payment returned.
4. Read the dedup column in Planned Operations to filter out already-imported payments.
5. Map new payments to the discovered column positions (same logic as Workflow 2).
   - **Status column**: `"Confirmed"` for planified, `"Planned"` for waitingconfirmation
6. Sort full range by Date column ascending. Match existing formatting style.
7. Done when planned payments appear with correct dates and dedup IDs.

---

### 4. Refresh FX Rates sheet
**Trigger:** "update FX rates", "refresh exchange rates", "sync FX from iBanFirst"

1. Run Phase 0 if not already done.
2. Read all currency codes from the FX Rates sheet (currency code column, discovered in 0.2).
3. Add any currencies from active wallets not already in the sheet.
4. For each non-EUR currency, call `get_fx_rates` with pair `"EUR{CODE}"`. Fetch all in parallel.
5. Convert returned `midMarket` rate to Rate to EUR: `1 / midMarket`.
6. Write to each currency's row:
   - **Rate column**: updated rate (4 decimal places)
   - **Last Updated column**: `=TODAY()` formula
   - **Timestamp cell** (if present): `"{DD/MM/YYYY HH:MM:SS}"` as text
   - **EUR row**: never overwrite — EUR rate is always 1.
7. Update the source note cell with: `"Last updated via MCP iBanFirst {DD/MM/YYYY HH:MM:SS}"`.
8. Done when all non-EUR rate cells show live rates and the timestamp is current.

---

### 5. Check payment status
**Trigger:** "status of my payments", "show rejected payments", "any blocked payments?"

1. Call `get_payments_by_status` with the relevant status.
2. Present a summary table in chat: payment ID, date, amount, currency, status, description.
3. Call `get_payment_details` for any payment the user wants to drill into.
4. Do NOT write to the spreadsheet unless explicitly asked.

---

### 6. Generate iBanFirst bulk payments CSV
**Trigger:** "generate payments CSV", "create ibanfirst CSV", "bulk payment file", "export planned operations to CSV"

1. Run Phase 0 if not already done.
2. Read the Planned Operations sheet using the discovered column positions.
3. Filter to Debit rows only (Credit = incoming, cannot be initiated).
4. Build an IBAN lookup from the Accounts sheet (institution + account type → IBAN).
5. Convert date serials to DD/MM/YYYY.
6. Map each Debit row to CSV columns:

   | CSV column | Source (semantic) |
   |---|---|
   | Beneficiary name | Bank / institution column |
   | Account | IBAN from Accounts lookup |
   | Amount | Amount column |
   | Currency | Currency column |
   | Reference | Description column |
   | Execution Date | Date column → DD/MM/YYYY |
   | Status | Status column |

7. Output as semicolon-separated text in chat (`;` delimiter — required by iBanFirst). Include header row.
8. Instruct user to save as UTF-8 CSV and upload to platform.ibanfirst.com → Payments.
9. Exclude rows where Amount is 0 or empty.

---

## Notes & Guards
- **Always run Discovery first** — never assume column positions or sheet names.
- **Match existing style** — when inserting rows, replicate the formatting of adjacent rows. Never apply arbitrary styles.
- **Replicate existing formulas** — read the EUR Equivalent formula from an existing row and copy its structure. Never invent a new formula shape.
- **Hardcode balances** — balances are point-in-time snapshots, always written as values.
- **EUR Equivalent column is always a formula** — never hardcode it.
- **EUR rate is always 1** — never overwrite it in the FX Rates sheet.
- **Deduplication is mandatory** — scan the dedup column before every import. Never insert a row whose `iBanFirst:{id}` already exists.
- **New wallets** — if `get_wallets` returns a wallet with no matching Accounts row, flag it to the user before writing anything.
- **Sort after every import** — always re-sort the full data range by Date ascending after appending rows.
- **CSV output only in chat** — never create a separate sheet for the CSV.
- **Semicolons only** — iBanFirst requires `;` delimiters. Never use commas.
- **UTF-8 encoding** — always warn the user explicitly when saving the CSV file.
- **`get_external_bank_accounts` errors** — skip silently if this tool returns an error.