S$1,000 has bought a record amount of yen, won, ringgit and baht since 2024

"""Prep for the SGD to yen, won, ringgit and baht insight (demo).

Reads MAS's monthly exchange rates (SingStat Table Builder, table M700051, saved as an .xlsx on 16 Oct 2026), checks the file,
writes data/monthly_rates.csv (one row per month and currency, the rate exactly as published) and prep/data.json (every figure the page uses).

Usage: python3 -I prep/prep_fx.py        (run from the build folder; needs openpyxl for the .xlsx; the page build itself needs only the standard library)
"""
import csv, json, os, sys
from decimal import Decimal, ROUND_HALF_UP

HERE = os.path.dirname(os.path.abspath(__file__))
ROOT = os.path.dirname(HERE)
SRC = os.path.join(ROOT, "data", "M700051.xlsx")
CSV = os.path.join(ROOT, "data", "monthly_rates.csv")
OUT = os.path.join(HERE, "data.json")

MON = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
# code: (series label in the file, units of foreign currency per published rate, name, country, symbol)
CUR = {
    "JPY": ("Japanese Yen", 100, "Yen", "Japan", "¥"),
    "KRW": ("Korean Won", 100, "Won", "South Korea", "₩"),
    "MYR": ("Malaysian Ringgit", 1, "Ringgit", "Malaysia", "RM"),
    "THB": ("Thai Baht", 100, "Baht", "Thailand", "฿"),
}
GRID_START = (2015, 1)


def half_up(x, places=0):
    return Decimal(x).quantize(Decimal(1).scaleb(-places), rounding=ROUND_HALF_UP)


def ym(label):
    s = str(label).strip()
    y, m = int(s[:4]), MON.index(s[5:8]) + 1
    assert s[4] == " " and 1988 <= y <= 2100, label
    return (y, m)


def read_xlsx():
    import openpyxl
    ws = openpyxl.load_workbook(SRC, data_only=True).active
    rows = list(ws.iter_rows(values_only=True))
    hi = [i for i, r in enumerate(rows) if r and str(r[0]).strip() == "Data Series"]
    assert len(hi) == 1, "header row not found"
    hdr = rows[hi[0]]
    assert "MONETARY AUTHORITY OF SINGAPORE" in " ".join(str(r[0]) for r in rows[:hi[0]] if r and r[0]), "source line missing"
    months = [ym(h) for h in hdr[1:] if h not in (None, "")]
    assert months == sorted(months, reverse=True), "months should run newest first"
    assert len(set(months)) == len(months), "duplicate months"
    out = []
    for code, (label, unit, *_r) in CUR.items():
        hit = [r for r in rows[hi[0] + 1:] if r and r[0] and str(r[0]).startswith(label + " (")]
        assert len(hit) == 1, (code, "series not found exactly once")
        r = hit[0]
        assert ("Per %d " % unit if unit > 1 else "Per Malaysian") in str(r[0]) or unit == 1, (code, r[0])
        for m, v in zip(months, r[1:]):
            if v in (None, "", "na", "-"):
                continue
            out.append((m, code, str(v).strip()))
    return out, months


def main():
    recs, months = read_xlsx()
    with open(CSV, "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["year", "month", "currency", "sgd_per_unit_as_published"])
        for (y, m), code, v in sorted(recs):
            w.writerow([y, m, code, v])
    rows = list(csv.DictReader(open(CSV)))
    data = {}
    for code, (label, unit, name, country, sym) in CUR.items():
        s = sorted(((int(r["year"]), int(r["month"])), Decimal(r["sgd_per_unit_as_published"])) for r in rows if r["currency"] == code)
        keys = [k for k, _ in s]
        # no gaps: every month between the first and the last is present
        n = (keys[-1][0] - keys[0][0]) * 12 + keys[-1][1] - keys[0][1] + 1
        assert n == len(keys), (code, "gap in months", n, len(keys))
        assert all(v > 0 for _, v in s)
        # what S$1,000 buys, whole units, half up. rate = SGD per `unit` units
        amt = [(k, half_up(Decimal(1000) * unit / v)) for k, v in s]
        full = dict(amt)
        grid = [(k, a) for k, a in amt if k >= GRID_START]
        last = amt[-1]
        rec = max(amt, key=lambda x: (x[1], x[0]))
        assert sum(1 for k, a in amt if a == rec[1]) == 1, (code, "tie for the record")
        g_rec = max(grid, key=lambda x: x[1])
        g_low = min(grid, key=lambda x: x[1])
        assert sum(1 for k, a in grid if a == g_low[1]) == 1 and g_rec == rec, (code, "grid extremes")
        low_all = min(amt, key=lambda x: x[1])
        jan15 = full[GRID_START]
        above = [k for k, a in grid if a > jan15]
        below_or_eq = [k for k, a in grid if a <= jan15]
        last_below = max(below_or_eq) if below_or_eq else None
        # biggest rise over 12 months (same month a year earlier)
        climb = fall = None
        for k, a in amt:
            p = (k[0] - 1, k[1])
            if p in full and k >= (GRID_START[0] + 1, 1):
                r = a / full[p] - 1
                if climb is None or r > climb[0]:
                    climb = (r, p, k, full[p], a)
                if fall is None or r < fall[0]:
                    fall = (r, p, k, full[p], a)
        # the middle month: line the grid months up by amount; with an odd count the middle one is exact
        assert len(grid) % 2 == 1
        by_amt = sorted(grid, key=lambda x: (x[1], x[0]))
        mid = by_amt[len(grid) // 2]
        # ranking within the grid: 1 + the number of months that bought strictly more; the top five, most first (later month first on a tie)
        top = sorted(grid, key=lambda x: (-x[1], [-x[0][0], -x[0][1]]))
        latest_rank = 1 + sum(1 for k, a in grid if a > last[1])
        before_rec_year = max((x for x in amt if x[0][0] < rec[0][0]), key=lambda x: x[1])
        data[code] = {
            "name": name, "country": country, "symbol": sym, "unit": unit,
            "first_month": list(keys[0]), "last_month": list(keys[-1]), "n_full": len(keys), "n_grid": len(grid),
            "grid": [[k[0], k[1], int(a)] for k, a in grid],
            "jan15": int(jan15), "latest": [last[0][0], last[0][1], int(last[1])],
            "record": [rec[0][0], rec[0][1], int(rec[1])], "grid_low": [g_low[0][0], g_low[0][1], int(g_low[1])],
            "full_low": [low_all[0][0], low_all[0][1], int(low_all[1])],
            "before_record_year": [before_rec_year[0][0], before_rec_year[0][1], int(before_rec_year[1])],
            "months_above_jan15": len(above), "last_at_or_below_jan15": list(last_below) if last_below else None,
            "months_in_2026": sum(1 for k, _ in grid if k[0] == 2026),
            "climb12": {"from": [climb[1][0], climb[1][1]], "to": [climb[2][0], climb[2][1]], "a": int(climb[3]), "b": int(climb[4]), "pct": float(half_up(climb[0] * 100, 0))},
            "fall12": ({"from": [fall[1][0], fall[1][1]], "to": [fall[2][0], fall[2][1]], "a": int(fall[3]), "b": int(fall[4]), "pct": float(half_up(-fall[0] * 100, 0))} if fall[0] < 0 else None),
            "median": [mid[0][0], mid[0][1], int(mid[1])],
            "latest_rank": latest_rank, "n_more_than_latest": latest_rank - 1,
            "top5": [[k[0], k[1], int(a)] for k, a in top[:5]],
            "gain_record_pct": float(half_up((rec[1] / jan15 - 1) * 100, 0)),
            "gain_latest_pct": float(half_up((last[1] / jan15 - 1) * 100, 0)),
            "gap_record_low_pct": float(half_up((g_rec[1] / g_low[1] - 1) * 100, 0)),
            "latest_vs_record_pct": float(half_up((1 - last[1] / rec[1]) * 100, 0)),
        }
        print(code, "months", len(keys), keys[0], "to", keys[-1], "| record", rec, "| grid low", g_low, "| jan15", jan15, "| latest", last,
              "| above jan15", len(above), "of", len(grid), "| last at/below jan15", last_below)
    D = {"asof": "16 Oct 2026", "src_updated": "14 Oct 2026", "cur": data, "order": ["JPY", "KRW", "MYR", "THB"],
         "n_rows": len(rows), "grid_start": list(GRID_START)}
    json.dump(D, open(OUT, "w"), indent=1)
    print("wrote", CSV, len(rows), "rows;", OUT)


if __name__ == "__main__":
    main()