Brand test

/brand-test — on-brand charts from a spreadsheet

Renders a chart from tabular data in the Recon brand (dark canvas, single blue data line, status colours only where they mean something) and hands back a PNG plus an HTML card that matches the app's "Portfolio performance" panel.

The brand rules are listed below and built into the script, so the skill is self-contained.

Steps

1. Find the data. Use the file the user attached or names (.csv, .tsv, .xlsx, .xls). If they gave a chart image instead of data, read the values off it, write them to a CSV (one column per series, first column the x-axis) and say the numbers were read by eye. No file at all: ask for one in one sentence.
2. Install the script if brand_chart.py isn't already in the working directory: write the code block at the end of this skill to brand_chart.py exactly as given.
3. Profile: python3 brand_chart.py <file> --profile (add --sheet NAME for Excel). It lists column kinds (date / number / category) and suggests commands.

4. Choose the chart. If the user said what they want, map it to flags. Otherwise pick the most useful view from the profile and say which you chose:

   • something over time → --x <date col> (+ --y <number col> --agg sum, or --agg count), --freq D|W|M
   • level series (equity, price, balance) → --agg last so the headline shows latest value and change
   • breakdown → --x <category col> --agg count (ranked horizontal bars, top 12 + Other)
   • split over time → add --by <category col> and --kind bar (stacked) or line
   • share of a whole with ≤5 parts → --kind donut
   • filter rows → --where "status == 'filled'"
   • money columns are detected by name (price, notional, pnl, equity, amount…); force with --money Always pass a human --title in sentence case ("Daily orders by status"), never a column name.
5. Render: python3 brand_chart.py <file> [flags] --out out/<slug>. It writes out/<slug>.png (2×, for decks/Slack) and out/<slug>.html (self-contained card).
6. Check it: open the PNG with Read. Fix and re-run if labels overlap, a single giant "Other" bar hides the story (raise --top), dates are unreadable (coarser --freq), or the colours don't match meaning.
7. Deliver: send the PNG and the HTML with SendUserFile (HTML with display "render"). One line on what the chart shows and the single most notable thing in it. Offer one alternative view.

Brand rules the script already applies (keep them if you hand-edit)

• Canvas #0B0E13, card #11161E, border #222B37, gridlines #1C2430 horizontal only, no axis lines.
• Text #F2F4F7, labels #A3ABB8, ticks/timestamps #7D8695. Font Plus Jakarta Sans (Inter fallback), tabular numbers.
• Data: #7BA4FA line, 2px, with a 16% area fill; second series #B5CCFC, then greys.
• Status words map to status colours: filled/running → #5DCB7E; held/paused/pending → #F2B84B; canceled/stopped/failed → #F07070; expired/new → greys. P&L bars green above zero, red below.
• Headline value large and bold; changes always signed (+/−) and coloured.
• Never: 3D, gradients beyond the area fill, rainbow palettes, dual axes, labels on pie slices.

brand_chart.py

#!/usr/bin/env python3
"""brand_chart.py — turn a CSV / TSV / Excel file into a Recon-branded chart.

Outputs <out>.png (2x, for decks/Slack) and <out>.html (a self-contained card
styled like the app's "Portfolio performance" panel).

Usage:
  python3 brand_chart.py FILE --profile                 # list columns + suggestions
  python3 brand_chart.py FILE --x created_at --agg count --freq D
  python3 brand_chart.py FILE --x symbol --y filled_qty --agg sum --kind hbar
  python3 brand_chart.py FILE --x created_at --by status --agg count --freq W
Options: --sheet NAME  --y COL  --by COL  --agg sum|count|mean|last
         --kind auto|area|line|bar|hbar|donut|scatter  --freq D|W|M|H
         --top N  --title TEXT  --subtitle TEXT  --out PATH  --where "pandas query"
"""
import argparse, base64, html, io, re, sys
from pathlib import Path
import pandas as pd
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
from matplotlib import font_manager

# ── Recon tokens (dark-only brand) ──────────────────────────────────────────
T = {
    "bg": "#0B0E13", "surface": "#11161E", "raised": "#1A2230", "border": "#222B37",
    "grid": "#1C2430", "track": "#2A3342",
    "text": "#F2F4F7", "text2": "#A3ABB8", "muted": "#7D8695",
    "accent": "#7BA4FA", "accent_soft": "#B5CCFC",
    "pos": "#5DCB7E", "warn": "#F2B84B", "neg": "#F07070",
}
SERIES = [T["accent"], T["accent_soft"], "#5B6B85", "#3E5A99", "#8E97A6", "#C9D6F2"]
SEMANTIC = {  # status words get the app's status colours; extra words in a role get its tints
    "pos": {"filled", "running", "open", "active", "done", "success", "complete", "completed", "win", "profit"},
    "warn": {"paused", "held", "pending", "waiting", "partially_filled", "accepted", "paper"},
    "neg": {"canceled", "cancelled", "stopped", "failed", "rejected", "error", "loss", "killed"},
    "neutral": {"expired", "new", "queued", "replaced", "done_for_day", "inactive"},
}
TINTS = {"pos": ["#5DCB7E", "#2F8A4C"], "warn": ["#F2B84B", "#A9772A"],
         "neg": ["#F07070", "#A84848"], "neutral": ["#8E97A6", "#5B6B85", "#C9D0DB"]}
FONT = "Inter" if any("Inter" in f.name for f in font_manager.fontManager.ttflist) else "DejaVu Sans"
WEB_FONT = "'Plus Jakarta Sans', Inter, system-ui, sans-serif"


def semantic_role(label):
    k = str(label).strip().lower().replace(" ", "_")
    for role, words in SEMANTIC.items():
        if k in words:
            return role
    return None


def palette_for(labels, fallback=None):
    """Status-like labels -> app status colours (tinted when a role repeats);
    everything else -> the blue series palette. Returns None if no label is status-like."""
    labels = [str(l) for l in labels]
    if not any(semantic_role(l) for l in labels):
        return None
    used, out, i = {}, [], 0
    for l in labels:
        r = semantic_role(l)
        if r:
            n = used.get(r, 0); used[r] = n + 1
            out.append(TINTS[r][min(n, len(TINTS[r]) - 1)])
        else:
            out.append(SERIES[i % len(SERIES)]); i += 1
    return out


def load(path, sheet=None):
    p = Path(path)
    ext = p.suffix.lower()
    if ext in (".xlsx", ".xlsm", ".xls"):
        df = pd.read_excel(p, sheet_name=sheet or 0)
    elif ext == ".tsv":
        df = pd.read_csv(p, sep="\t")
    else:
        df = pd.read_csv(p, sep=None, engine="python")
    df.columns = [str(c).strip() for c in df.columns]
    for c in df.columns:  # parse date-like text columns
        if (df[c].dtype == object or pd.api.types.is_string_dtype(df[c])) and re.search(r"(date|time|_at$|day|month|period)", c, re.I):
            parsed = pd.to_datetime(df[c], errors="coerce", utc=True, format="ISO8601")
            if parsed.notna().mean() < 0.8:
                parsed = pd.to_datetime(df[c], errors="coerce", utc=True, format="mixed")
            if parsed.notna().mean() > 0.8 or (df[c].notna().mean() < 0.8 and parsed.notna().sum() == df[c].notna().sum()):
                df[c] = parsed.dt.tz_convert(None)
    return df


def profile(df):
    rows = []
    for c in df.columns:
        s = df[c]
        nn = s.notna().mean()
        if nn == 0:
            continue
        kind = ("date" if pd.api.types.is_datetime64_any_dtype(s) else
                "number" if pd.api.types.is_numeric_dtype(s) and s.nunique() > 2 else
                "category" if s.nunique() <= 40 else "id/text")
        rows.append((c, kind, s.nunique(), f"{nn:.0%}"))
    print(f"{len(df)} rows × {len(df.columns)} cols (empty columns hidden)\n")
    print(f"{'column':28} {'kind':9} {'unique':>7} {'filled':>7}")
    for r in rows:
        print(f"{r[0]:28} {r[1]:9} {r[2]:>7} {r[3]:>7}")
    dates = [r[0] for r in rows if r[1] == "date"]
    nums = [r[0] for r in rows if r[1] == "number"]
    cats = sorted([r for r in rows if r[1] == "category" and 1 < r[2] <= 25], key=lambda r: r[2])
    cats = [r[0] for r in cats if not r[0].endswith("_id")]
    print("\nSuggestions:")
    if dates:
        print(f"  --x {dates[0]} --agg count --freq D            # activity over time")
        if nums: print(f"  --x {dates[0]} --y {nums[0]} --agg sum --freq D   # value over time")
        if cats: print(f"  --x {dates[0]} --by {cats[0]} --agg count --freq W  # split by {cats[0]}")
    for c in cats[:3]:
        print(f"  --x {c} --agg count                          # breakdown by {c}")


def fmt_num(v, money=False):
    if pd.isna(v):
        return "–"
    sign = "-" if v < 0 else ""
    a = abs(v)
    pre = "$" if money else ""
    if a >= 1e6: return f"{sign}{pre}{a/1e6:.2f}M"
    if a >= 1e4: return f"{sign}{pre}{a/1e3:.1f}k"
    if money or a % 1: return f"{sign}{pre}{a:,.2f}"
    return f"{sign}{pre}{a:,.0f}"


def aggregate(df, x, y, by, agg, freq, top):
    d = df.copy()
    is_time = pd.api.types.is_datetime64_any_dtype(d[x])
    if is_time:
        d[x] = d[x].dt.to_period(freq).dt.to_timestamp() if freq != "H" else d[x].dt.floor("h")
    keys = [x] + ([by] if by else [])
    if agg == "count" or y is None:
        g = d.groupby(keys).size()
    else:
        d[y] = pd.to_numeric(d[y], errors="coerce")
        g = getattr(d.groupby(keys)[y], {"sum": "sum", "mean": "mean", "last": "last"}[agg])()
    if by:
        g = g.unstack(by).fillna(0)
        if len(g.columns) > top:
            keep = g.sum().sort_values(ascending=False).index[:top - 1]
            g = pd.concat([g[keep], g.drop(columns=keep).sum(axis=1).rename("Other")], axis=1)
    else:
        g = g.to_frame(y or "count")
        if not is_time:
            g = g.sort_values(g.columns[0], ascending=False)
            if len(g) > top:
                rest = g.iloc[top - 1:].sum()
                g = pd.concat([g.iloc[:top - 1], pd.DataFrame([rest], index=["Other"])])
    return g, is_time


def style_axes(ax):
    ax.set_facecolor(T["surface"])
    for s in ax.spines.values():
        s.set_visible(False)
    ax.tick_params(colors=T["muted"], labelsize=10, length=0, pad=8)
    ax.grid(axis="y", color=T["grid"], linewidth=1)
    ax.set_axisbelow(True)


def draw(g, is_time, kind, money, x_label):
    plt.rcParams.update({"font.family": FONT, "svg.fonttype": "none"})
    fig, ax = plt.subplots(figsize=(10, 4.2), dpi=200)
    fig.patch.set_facecolor(T["surface"])
    style_axes(ax)
    cols = list(g.columns)
    colors = palette_for(cols) or [SERIES[i % len(SERIES)] for i in range(len(cols))]
    if kind == "auto":
        kind = ("area" if len(cols) == 1 else "line") if is_time else "hbar"

    if kind in ("area", "line"):
        for c, col in zip(cols, colors):
            ax.plot(g.index, g[c], color=col, linewidth=2.2, solid_capstyle="round", label=str(c))
            if kind == "area":
                base = min(0, g[c].min())
                ax.fill_between(g.index, g[c], base, color=col, alpha=0.16, linewidth=0)
        ax.margins(x=0)
        if is_time:
            import matplotlib.dates as mdates
            loc = mdates.AutoDateLocator(maxticks=6)
            ax.xaxis.set_major_locator(loc)
            ax.xaxis.set_major_formatter(mdates.ConciseDateFormatter(loc, show_offset=False))
    elif kind == "bar":
        labels = [f"{i:%b %d}" if hasattr(i, "strftime") else str(i) for i in g.index]
        if len(cols) == 1:
            v = g[cols[0]]
            bc = palette_for(labels) or [T["neg"] if val < 0 else T["accent"] for val in v]
            ax.bar(labels, v, color=bc, width=0.62)
        else:
            bottom = pd.Series(0.0, index=g.index)
            for c, col in zip(cols, colors):
                ax.bar(labels, g[c], bottom=bottom.values, color=col, width=0.62, label=str(c))
                bottom += g[c]
        step = max(1, len(labels) // 8)
        ax.set_xticks(range(0, len(labels), step), [labels[k] for k in range(0, len(labels), step)])
        ax.margins(x=0.01)
    elif kind == "hbar":
        g = g.iloc[::-1]
        labels = [str(i) for i in g.index]
        v = g[cols[0]]
        has_neg = (v < 0).any()
        bc = palette_for(labels) or [(T["neg"] if val < 0 else T["pos"]) if has_neg else T["accent"] for val in v]
        bc = ["#3A4454" if l == "Other" else c for l, c in zip(labels, bc)]  # remainder stays quiet
        ax.barh(labels, v, color=bc, height=0.56)
        ax.grid(axis="y", visible=False)
        ax.grid(axis="x", color=T["grid"], linewidth=1)
        ax.tick_params(axis="y", colors=T["text2"], labelsize=11)
        for i, val in enumerate(v):
            ax.text(val, i, "  " + fmt_num(val, money), va="center",
                    ha="left" if val >= 0 else "right", color=T["text"], fontsize=10, fontweight="bold")
        ax.margins(x=0.12)
    elif kind == "donut":
        v = g[cols[0]].clip(lower=0)
        dc = palette_for(g.index) or [SERIES[i % len(SERIES)] for i in range(len(g))]
        ax.pie(v, colors=dc, startangle=90, counterclock=False,
               wedgeprops={"width": 0.16, "edgecolor": T["surface"], "linewidth": 2})
        ax.text(0, 0.06, fmt_num(v.sum(), money), ha="center", va="center", color=T["text"], fontsize=26, fontweight="bold")
        ax.text(0, -0.2, "Total", ha="center", color=T["text2"], fontsize=11)
        ax.legend([str(i) for i in g.index], loc="center left", bbox_to_anchor=(1.02, 0.5), frameon=False,
                  labelcolor=T["text2"], fontsize=10)
        ax.set_aspect("equal")
    elif kind == "scatter":
        ax.scatter(g.index, g[cols[0]], s=22, color=T["accent"], alpha=0.85, linewidths=0)

    if len(cols) > 1 and kind != "donut":
        ax.legend(loc="upper left", bbox_to_anchor=(0, 1.12), ncol=min(len(cols), 6), frameon=False,
                  labelcolor=T["text2"], fontsize=10, handlelength=1.0, handleheight=0.6, columnspacing=1.4)
    if kind not in ("hbar", "donut"):
        ax.yaxis.set_major_formatter(matplotlib.ticker.FuncFormatter(lambda v, _: fmt_num(v, money)))
    if kind == "hbar":
        ax.xaxis.set_major_formatter(matplotlib.ticker.FuncFormatter(lambda v, _: fmt_num(v, money)))
    fig.tight_layout(pad=1.2)
    return fig, kind


def headline(g, is_time, money):
    total = g.sum(axis=1)
    if is_time and len(total) > 1:
        last, first = total.iloc[-1], total.iloc[0]
        delta = last - first
        pct = (delta / abs(first) * 100) if first else None
        return fmt_num(last, money), delta, pct, "latest"
    return fmt_num(total.sum(), money), None, None, "total"


def card_html(title, subtitle, big, delta, pct, big_label, svg, money, n_rows):
    dcls = "pos" if (delta or 0) >= 0 else "neg"
    delta_html = ""
    if delta is not None:
        sign = "+" if delta >= 0 else ""
        p = f" ({sign}{pct:.2f}%)" if pct is not None else ""
        delta_html = f'<span class="delta {dcls}">{sign}{fmt_num(delta, money)}{p}</span>'
    return f"""<!doctype html><html lang="en"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1"><title>{html.escape(title)}</title>
<link rel="preconnect" href="https://fonts.googleapis.com"><link href="https://fonts.googleapis.com/css2?family=Plus+Jakarta+Sans:wght@400;500;600;700&display=swap" rel="stylesheet">
<style>
:root{{--bg:{T['bg']};--surface:{T['surface']};--border:{T['border']};--text:{T['text']};--text2:{T['text2']};--muted:{T['muted']};--accent:{T['accent']};--pos:{T['pos']};--neg:{T['neg']}}}
*{{box-sizing:border-box}}body{{margin:0;background:var(--bg);color:var(--text);font-family:{WEB_FONT};padding:32px 16px;font-variant-numeric:tabular-nums}}
.card{{max-width:1040px;margin:0 auto;background:var(--surface);border:1px solid var(--border);border-radius:14px;padding:32px 36px}}
.head{{display:flex;justify-content:space-between;align-items:baseline;gap:16px;flex-wrap:wrap}}
h1{{font-size:24px;font-weight:600;margin:0;letter-spacing:-.01em}}.meta{{color:var(--muted);font-size:14px}}
.value{{display:flex;align-items:baseline;gap:16px;margin:28px 0 8px;flex-wrap:wrap}}
.big{{font-size:44px;font-weight:700;letter-spacing:-.02em}}.big small{{font-size:14px;color:var(--text2);font-weight:500;margin-left:10px;letter-spacing:0}}
.delta{{font-size:20px;font-weight:500}}.pos{{color:var(--pos)}}.neg{{color:var(--neg)}}
.chart svg{{width:100%;height:auto;display:block}}.chart svg text{{font-family:{WEB_FONT}!important}}
.foot{{margin-top:12px;color:var(--muted);font-size:13px;border-top:1px solid var(--border);padding-top:14px}}
@media (max-width:600px){{.card{{padding:20px}}.big{{font-size:32px}}}}
</style></head><body><div class="card">
<div class="head"><h1>{html.escape(title)}</h1><span class="meta">{html.escape(subtitle)}</span></div>
<div class="value"><span class="big">{big}<small>{big_label}</small></span>{delta_html}</div>
<div class="chart">{svg}</div>
<div class="foot">Source: {n_rows:,} rows · generated with /brand-test</div>
</div></body></html>"""


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("file"); ap.add_argument("--sheet"); ap.add_argument("--profile", action="store_true")
    ap.add_argument("--x"); ap.add_argument("--y"); ap.add_argument("--by")
    ap.add_argument("--agg", default="sum", choices=["sum", "count", "mean", "last"])
    ap.add_argument("--kind", default="auto", choices=["auto", "area", "line", "bar", "hbar", "donut", "scatter"])
    ap.add_argument("--freq", default="D"); ap.add_argument("--top", type=int, default=12)
    ap.add_argument("--where"); ap.add_argument("--title"); ap.add_argument("--subtitle")
    ap.add_argument("--money", action="store_true", help="format values as $")
    ap.add_argument("--out", default="brand_chart")
    a = ap.parse_args()

    df = load(a.file, a.sheet)
    if a.profile or not a.x:
        profile(df); return
    if a.where:
        df = df.query(a.where)
    for c in [a.x, a.y, a.by]:
        if c and c not in df.columns:
            sys.exit(f"Column not found: {c}. Run with --profile to list columns.")
    agg = "count" if a.y is None else a.agg
    money = a.money or bool(a.y and re.search(r"(price|notional|pnl|p&l|equity|amount|value|cost|usd|\$)", a.y, re.I))
    g, is_time = aggregate(df, a.x, a.y, a.by, agg, a.freq, a.top)
    fig, kind = draw(g, is_time, a.kind, money, a.x)

    out = Path(a.out)
    out.parent.mkdir(parents=True, exist_ok=True)
    fig.savefig(out.with_suffix(".png"), facecolor=T["surface"])
    buf = io.StringIO(); fig.savefig(buf, format="svg", facecolor=T["surface"])
    svg = re.sub(r"^.*?(<svg)", r"\1", buf.getvalue(), flags=re.S)
    svg = re.sub(r'(<svg[^>]*?) width="[^"]*" height="[^"]*"', r"\1", svg, count=1)

    what = f"{a.agg} of {a.y}" if a.y and agg != "count" else "count"
    title = a.title or (f"{a.y or 'Rows'} by {a.x}" + (f" · split by {a.by}" if a.by else ""))
    if is_time:
        idx = g.index
        sub = a.subtitle or f"{idx.min():%b %d, %Y} — {idx.max():%b %d, %Y} · {what}"
    else:
        sub = a.subtitle or what
    # levels (equity, price) show latest + change; flows (sum/count per period) show the total
    big, delta, pct, lab = headline(g, is_time and agg in ("last", "mean") and kind in ("area", "line", "bar"), money)
    out.with_suffix(".html").write_text(card_html(title, sub, big, delta, pct, lab, svg, money, len(df)))
    print(f"chart: {kind}\nwrote {out.with_suffix('.png')}\nwrote {out.with_suffix('.html')}")


if __name__ == "__main__":
    main()