# Total_Fees_Calculation

> Solve dabstep questions that ask for the total payment processing fees a merchant should pay over a specific time period (a day, month, or year). Use this skill whenever the question involves computing total fees for a named merchant over a date range using the dabstep dataset. Trigger on phrases like "total fees", "fees that [merchant] should pay", "fees paid in [month/year]".

- Skill: `zjunlp/total-fees-calculation-8` (Agent Skill)
- Install (CLI): `npx skillmds@latest add zjunlp/total-fees-calculation-8`
- Raw SKILL.md: https://api.skillmd.com/api/skills/zjunlp/total-fees-calculation-8/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: zjunlp (https://skillmd.com/u/zjunlp)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/zjunlp/total-fees-calculation-8

---


# Total Fees Calculation

These questions ask: *"What are the total fees (in euros) that [Merchant] paid/should pay in [time period]?"*

The answer is computed by: filtering transactions → matching each to exactly one fee rule → summing `fee = fixed_amount + rate * eur_amount / 10000`.

**Do NOT read manual.md or payments-readme.md** — all required knowledge is in this skill. Skip directly to the algorithm.

## Dataset Files

| File | Purpose |
|------|---------|
| `payments.csv` | One row per transaction; key columns: `merchant`, `year`, `day_of_year`, `card_scheme`, `is_credit`, `eur_amount`, `issuing_country`, `acquirer_country`, `aci`, `has_fraudulent_dispute`, `is_refused_by_adyen` |
| `merchant_data.json` | Merchant objects: `merchant`, `account_type`, `capture_delay`, `merchant_category_code` |
| `fees.json` | ~1000 fee rule objects (see matching logic below) |

## Complete Algorithm

Run this in one code block — adapt the merchant name, year, and time filter for the question:

```python
import json, pandas as pd
from datetime import date, timedelta

# ── Load data ──────────────────────────────────────────────────────────
with open('fees.json') as f: fees = json.load(f)
with open('merchant_data.json') as f: merchant_data = json.load(f)
merchant_map = {m['merchant']: m for m in merchant_data}
payments = pd.read_csv('payments.csv')

def doy_to_month(doy, year):
    return (date(year, 1, 1) + timedelta(days=doy - 1)).month

payments['month'] = payments.apply(lambda r: doy_to_month(r['day_of_year'], r['year']), axis=1)

# ── Merchant attributes ────────────────────────────────────────────────
merchant_name = 'MERCHANT_NAME'   # ← fill in
year = 2023                        # ← fill in

m = merchant_map[merchant_name]
merchant_at  = m['account_type']
merchant_mcc = m['merchant_category_code']

def map_capture_delay(cd):
    if cd in ('immediate', 'manual'): return cd
    n = int(cd)
    return '<3' if n < 3 else ('3-5' if n <= 5 else '>5')

merchant_cd = map_capture_delay(m['capture_delay'])

# ── Helper: parse range strings ────────────────────────────────────────
def parse_range(s, pct=False):
    def val(v):
        v = v.strip().replace('%', '')
        if v.endswith('m'): return float(v[:-1]) * 1e6
        if v.endswith('k'): return float(v[:-1]) * 1e3
        return float(v) / 100.0 if pct else float(v)
    if s.startswith('<'): return (0.0, val(s[1:]))
    if s.startswith('>'): return (val(s[1:]), float('inf'))
    a, b = s.split('-')
    return (val(a), val(b))

# ── Helper: find the best matching fee rule for one transaction ─────────
def find_rule(tx, monthly_volume, monthly_fraud_rate):
    ic = (tx['issuing_country'] == tx['acquirer_country'])
    candidates = []
    for rule in fees:
        if rule['card_scheme'] != tx['card_scheme']: continue
        # Empty list [] means "applies to all"
        if rule['account_type'] and merchant_at not in rule['account_type']: continue
        if rule['merchant_category_code'] and merchant_mcc not in rule['merchant_category_code']: continue
        if rule['aci'] and tx['aci'] not in rule['aci']: continue
        # None means "applies to all"
        if rule['capture_delay'] is not None and rule['capture_delay'] != merchant_cd: continue
        if rule['is_credit'] is not None and rule['is_credit'] != tx['is_credit']: continue
        if rule['intracountry'] is not None:
            if rule['intracountry'] != (1.0 if ic else 0.0): continue
        if rule['monthly_fraud_level'] is not None:
            lo, hi = parse_range(rule['monthly_fraud_level'], pct=True)
            if not (lo <= monthly_fraud_rate < hi): continue
        if rule['monthly_volume'] is not None:
            lo, hi = parse_range(rule['monthly_volume'])
            if not (lo <= monthly_volume < hi): continue
        candidates.append(rule)
    if not candidates: return None
    # Pick the most specific rule (most non-null / non-empty fields)
    return max(candidates, key=lambda r: sum([
        bool(r['account_type']), r['capture_delay'] is not None,
        bool(r['merchant_category_code']), r['is_credit'] is not None,
        bool(r['aci']), r['intracountry'] is not None,
        r['monthly_fraud_level'] is not None, r['monthly_volume'] is not None
    ]))

# ── Filter target transactions (exclude refused) ───────────────────────
# CHOOSE ONE of the three patterns below:

# Pattern A — specific day (e.g. "For the 10th of 2023"):
target_day = 10   # ← fill in
txs = payments[(payments['merchant'] == merchant_name) &
               (payments['year'] == year) &
               (payments['day_of_year'] == target_day) &
               (payments['is_refused_by_adyen'] == False)]
# Compute monthly stats for the calendar month that contains this day
target_month = doy_to_month(target_day, year)

# Pattern B — specific month (e.g. "In April 2023"):
# target_month = 4   # ← fill in
# txs = payments[(payments['merchant'] == merchant_name) &
#                (payments['year'] == year) &
#                (payments['month'] == target_month) &
#                (payments['is_refused_by_adyen'] == False)]

# Pattern C — full year (e.g. "In 2023"):
# txs = payments[(payments['merchant'] == merchant_name) &
#                (payments['year'] == year) &
#                (payments['is_refused_by_adyen'] == False)]

# ── Monthly stats (use ALL merchant transactions, including refused) ───
# For Pattern A or B: compute stats for the single month
all_merchant = payments[(payments['merchant'] == merchant_name) & (payments['year'] == year)]

monthly_txs = all_merchant[all_merchant['month'] == target_month]
monthly_volume     = monthly_txs['eur_amount'].sum()
fraud_volume       = monthly_txs[monthly_txs['has_fraudulent_dispute'] == True]['eur_amount'].sum()
monthly_fraud_rate = fraud_volume / monthly_volume if monthly_volume > 0 else 0

# ── Calculate and sum fees ─────────────────────────────────────────────
# For Pattern A or B (single month stats):
total_fee = 0.0
for _, row in txs.iterrows():
    rule = find_rule(row.to_dict(), monthly_volume, monthly_fraud_rate)
    if rule:
        total_fee += rule['fixed_amount'] + rule['rate'] * row['eur_amount'] / 10000

print(round(total_fee, 2))

# For Pattern C (full year) — replace the block above with:
# total_fee = 0.0
# for month in range(1, 13):
#     m_txs = all_merchant[all_merchant['month'] == month]
#     mv = m_txs['eur_amount'].sum()
#     fv = m_txs[m_txs['has_fraudulent_dispute'] == True]['eur_amount'].sum()
#     mfr = fv / mv if mv > 0 else 0
#     month_txs = txs[txs['month'] == month]
#     for _, row in month_txs.iterrows():
#         rule = find_rule(row.to_dict(), mv, mfr)
#         if rule:
#             total_fee += rule['fixed_amount'] + rule['rate'] * row['eur_amount'] / 10000
# print(round(total_fee, 2))
```

## Key Rules

**Rule selection** — when multiple rules match, pick the **most specific** one (the rule with the most non-null / non-empty fields). Never use the first/lowest-ID rule.

**Empty list `[]`** in `account_type`, `merchant_category_code`, `aci` means the rule applies to ALL values (same as `null`).

**No matching rule → fee = 0** for that transaction. This is expected; many transaction-type combinations are not covered by the fee schedule.

**Monthly stats scope** — always compute `monthly_volume` and `monthly_fraud_rate` over the **full natural calendar month** using **all** merchant transactions (including refused ones), even when the question asks about a single day within that month.

**intracountry** — compute directly as `issuing_country == acquirer_country` from `payments.csv`; do not look up the acquirer's country from `merchant_data.json`.

**Refused transactions** — exclude `is_refused_by_adyen == True` from the fee-paying transactions, but include them when computing monthly stats.

## Expected Output

A single number rounded to 2 decimal places. If no applicable answer exists, output `Not Applicable`.