Skip to content

"Vibe-Trading: Your Personal Trading Agent"

33.1 KB
Raw Blame
"""Trade Journal Analyzer tool.

Parses a broker CSV/Excel export and produces:
  - profile: holding days, trade frequency, win rate, PnL ratio, cumulative
    PnL, max drawdown, top symbols, market/hourly distribution
  - behavior (Phase 4b): disposition effect, overtrading, chasing momentum,
    anchoring — each with severity + numeric evidence

Strategy extraction → backtest bridge still pending (Phase 4c).
"""

from __future__ import annotations

import json
import logging
import math
import re
from collections import defaultdict, deque
from collections.abc import Mapping
from typing import Any

import pandas as pd

from backtest.engines._market_hooks import code_currency
from src.agent.tools import BaseTool
from src.tools.path_utils import safe_user_path
from src.tools.trade_journal_parsers import (
    parse_file,
    records_to_dataframe,
)

logger = logging.getLogger(__name__)

# (symbol, from_dt, to_dt) -> factor turning a from_dt price into to_dt's
# caliber, or None when the symbol has no coverage. See pair_trades_fifo.
CaliberAdjust = Any


def build_frame_adjust(frames: dict[str, pd.DataFrame]):
    """Build a :data:`CaliberAdjust` from OHLCV frames with adjusted closes.

    The factor between two dates is ``close(to_dt) / close(from_dt)`` on the
    frame's (adjusted) close, using the last bar on or before each date when
    the exact date has no bar. Returns ``None`` for symbols or dates the
    frames do not cover, which leaves that leg raw in the pairing. The frames
    the shadow run writes are already split/dividend-adjusted on every loader
    path, so this restates both legs of a roundtrip to one caliber (#15).
    """

    def _close_on_or_before(frame: pd.DataFrame, dt: pd.Timestamp) -> float | None:
        if frame is None or frame.empty or "close" not in frame.columns:
            return None
        idx = frame.index
        pos = idx.searchsorted(dt, side="right") - 1
        if pos < 0:
            return None
        value = frame["close"].iloc[pos]
        return float(value) if pd.notna(value) and float(value) > 0 else None

    def adjust(symbol: str, from_dt, to_dt) -> float | None:
        frame = frames.get(symbol)
        if frame is None:
            return None
        start = _close_on_or_before(frame, pd.Timestamp(from_dt))
        end = _close_on_or_before(frame, pd.Timestamp(to_dt))
        if start is None or end is None:
            return None
        return end / start

    return adjust


_ALLOWED_EXT = {".csv", ".xlsx", ".xls"}


def pair_trades_fifo(
    df: pd.DataFrame,
    adjust: "CaliberAdjust | None" = None,
) -> list[dict[str, Any]]:
    """Pair buys and sells per symbol using FIFO to compute per-roundtrip PnL.

    Reversal fees are shared proportionally between the closing quantity and
    the remaining opening quantity, so each fee is counted exactly once.

    Long lots and short lots are both modelled: a sell that finds no open
    long lot opens a short lot, and a buy first covers open shorts before
    queueing as a long (#16). Short PnL is (entry - cover) x qty.

    When ``adjust`` is given, each matched leg is restated to the sell (or
    cover) date's price caliber before PnL, so a split or dividend between
    the two dates no longer books as a fake loss or vanishes (#15). The
    callback returns the factor that turns a price at the earlier date into
    the later date's caliber, or ``None`` when no data covers the symbol,
    which leaves that leg raw. Without ``adjust`` the pairing is byte-for-byte
    the legacy raw behavior.

    Args:
        df: Standardized DataFrame (datetime-sorted).
        adjust: Optional ``(symbol, from_dt, to_dt) -> factor | None``.

    Returns:
        List of dicts: symbol, buy_dt, sell_dt, qty, buy_price, sell_price,
        hold_days, pnl, pnl_pct, side, currency. ``currency`` is the symbol's
        settlement currency via :func:`backtest.engines._market_hooks.code_currency`,
        so downstream sums never mix denominations (#1808). Unmatched
        positions are ignored.
    """
    longs: dict[str, deque] = defaultdict(deque)
    shorts: dict[str, deque] = defaultdict(deque)
    roundtrips: list[dict[str, Any]] = []

    def _factor(symbol: str, from_dt: Any, to_dt: Any) -> float | None:
        if adjust is None:
            return None
        try:
            factor = adjust(symbol, from_dt, to_dt)
        except Exception:  # an adjuster miss must not break pairing
            return None
        if factor is None or factor <= 0:
            return None
        return float(factor)

    for row in df.itertuples(index=False):
        # Dividend cash rows and other non-trade events carry no position change.
        if row.side not in ("buy", "sell"):
            continue
        if row.side == "buy":
            # Cover open shorts first, then queue any remainder as a long lot.
            remaining = row.quantity
            sq = shorts[row.symbol]
            while remaining > 1e-9 and sq:
                lot = sq[0]
                cover_dt = pd.to_datetime(row.datetime)
                entry_dt = pd.to_datetime(lot["dt"])
                hold = (cover_dt - entry_dt).total_seconds() / 86400.0 if pd.notna(cover_dt) and pd.notna(entry_dt) else 0.0
                # Restate the short lot into the cover date's caliber exactly as
                # the long branch below does: the factor turns an entry-date
                # price into the cover date's, so price is scaled BY it and the
                # share count by its inverse. Dividing the price instead would
                # book a 1:2 split as a large fake short profit.
                factor = _factor(row.symbol, entry_dt, cover_dt) or 1.0
                lot_qty_here = lot["qty"] / factor
                entry_price = lot["price"] * factor
                take = min(lot_qty_here, remaining)
                gross = (entry_price - row.price) * take
                # Fees were paid in original-caliber shares.
                consumed = take * factor
                entry_fee = lot["fee"] * (consumed / lot["qty"]) if lot["qty"] else 0.0
                cover_fee = row.fee * (take / row.quantity) if row.quantity else 0.0
                pnl = gross - entry_fee - cover_fee
                cost = entry_price * take
                pnl_pct = pnl / cost if cost else 0.0
                roundtrips.append({
                    "symbol": row.symbol,
                    "buy_dt": lot["dt"],
                    "sell_dt": row.datetime,
                    "qty": take,
                    "buy_price": lot["price"],
                    "sell_price": row.price,
                    "hold_days": round(hold, 2),
                    "pnl": round(pnl, 2),
                    "pnl_pct": round(pnl_pct, 4),
                    "side": "short",
                    "currency": code_currency(row.symbol),
                })
                lot["fee"] -= entry_fee
                lot["qty"] -= consumed
                remaining -= take
                if lot["qty"] <= 1e-9:
                    sq.popleft()
            if remaining > 1e-9:
                longs[row.symbol].append({
                    "dt": row.datetime,
                    "qty": remaining,
                    "price": row.price,
                    "fee": row.fee * (remaining / row.quantity),
                })
            continue

        # sell: match against oldest longs; anything left opens a short lot.
        remaining = row.quantity
        q = longs[row.symbol]
        while remaining > 1e-9 and q:
            lot = q[0]
            sell_dt = pd.to_datetime(row.datetime)
            buy_dt = pd.to_datetime(lot["dt"])
            factor = _factor(row.symbol, buy_dt, sell_dt)
            # Restate the lot to the sell date's caliber: a 1:2 split turns a
            # pre-split lot of 10@100 into 20@50, so the post-split sell
            # matches the whole position instead of half plus a phantom
            # short (#15).
            factor = factor or 1.0
            lot_qty_here = lot["qty"] / factor
            lot_price_here = lot["price"] * factor
            take = min(lot_qty_here, remaining)
            hold = (sell_dt - buy_dt).total_seconds() / 86400.0 if pd.notna(sell_dt) and pd.notna(buy_dt) else 0.0
            gross = (row.price - lot_price_here) * take
            # Proportional fee allocation (in original-caliber shares)
            consumed = take * factor
            buy_fee = lot["fee"] * (consumed / lot["qty"]) if lot["qty"] else 0.0
            sell_fee = row.fee * (take / row.quantity) if row.quantity else 0.0
            pnl = gross - buy_fee - sell_fee
            cost = lot_price_here * take
            pnl_pct = pnl / cost if cost else 0.0
            roundtrips.append({
                "symbol": row.symbol,
                "buy_dt": lot["dt"],
                "sell_dt": row.datetime,
                "qty": take,
                "buy_price": lot["price"],
                "sell_price": row.price,
                "hold_days": round(hold, 2),
                "pnl": round(pnl, 2),
                "pnl_pct": round(pnl_pct, 4),
                "side": "long",
                "currency": code_currency(row.symbol),
            })
            lot["fee"] -= buy_fee
            lot["qty"] -= consumed
            remaining -= take
            if lot["qty"] <= 1e-9:
                q.popleft()
        if remaining > 1e-9:
            shorts[row.symbol].append({
                "dt": row.datetime,
                "qty": remaining,
                "price": row.price,
                "fee": row.fee * (remaining / row.quantity),
            })
    return roundtrips


def _safe_div(a: float, b: float) -> float:
    return float(a) / float(b) if b else 0.0


def _compute_profile(df: pd.DataFrame) -> dict[str, Any]:
    """Build the trading profile dict.

    Args:
        df: Standardized DataFrame (datetime parsed, sorted). May carry
            dividend cash rows; those are summed into ``total_dividends`` and
            ``total_pnl`` but never counted as trades.

    Monetary metrics and the average-win/average-loss ratio are computed
    within each settlement currency. Single-currency journals retain scalar
    values; mixed journals return currency-keyed mappings. Win rate and
    holding time remain sample-wide because they do not add money amounts.

    Returns:
        Dict with avg_holding_days, trade_frequency_per_week, win_rate,
        profit_loss_ratio, total_pnl, total_dividends,
        dividend_rows_missing_cash, max_drawdown,
        top_symbols, market_distribution, hourly_distribution,
        roundtrips_sample, and currency_warning when mixed.
    """
    if df.empty:
        return {"error": "empty trade journal"}

    rts = pair_trades_fifo(df)
    rts_df = pd.DataFrame(rts)

    trades_df = df[df["side"].isin(("buy", "sell"))]
    # Per-currency dividend split: a dividend settles in its symbol's
    # currency, so it joins that currency's pool even when the market has no
    # round-trip in this journal — the cash is real, dropping it would be a
    # silent omission (#1808).
    div_rows = df[df["side"] == "dividend"]
    dividend_amounts = div_rows["amount"]
    dividends_by_currency: dict[str, float] = {}
    for symbol, amount in div_rows.groupby("symbol")["amount"].sum().items():
        cur = code_currency(symbol)
        dividends_by_currency[cur] = (
            dividends_by_currency.get(cur, 0.0) + float(amount)
        )
    # A dividend row that parsed with no cash is almost never a real zero
    # payout — it means the export named its cash column something the parser
    # did not recognise, and the payout would otherwise be booked as 0 with
    # nothing to show for it. Surface the count so the caller can see that the
    # journal was read but the money was not, instead of trusting a total that
    # silently omits it.
    dividend_rows_missing_cash = int((dividend_amounts == 0).sum())

    total_trades = len(trades_df)
    span_days = max(1, (df["datetime"].max() - df["datetime"].min()).days)
    freq_per_week = round(total_trades / span_days * 7, 2)

    currencies = sorted(
        {rt.get("currency") or "UNKNOWN" for rt in rts}
        | set(dividends_by_currency)
    )
    mixed = len(currencies) > 1

    win_rate = round(sum(rt["pnl"] > 0 for rt in rts) / len(rts), 4) if rts else 0.0
    avg_hold = round(float(rts_df["hold_days"].mean()), 2) if rts else 0.0
    pnl_by_currency = {}
    drawdown_by_currency = {}
    ratio_by_currency = {}
    for currency in currencies:
        pool = sorted((rt for rt in rts if rt["currency"] == currency),
                      key=lambda rt: rt["sell_dt"])
        pnl = pd.Series([rt["pnl"] for rt in pool], dtype=float)
        wins, losses = pnl[pnl > 0], pnl[pnl < 0]
        avg_win = float(wins.mean()) if len(wins) else 0.0
        avg_loss = float(losses.mean()) if len(losses) else 0.0
        ratio_by_currency[currency] = (
            round(avg_win / abs(avg_loss), 2) if avg_loss
            else float("inf") if avg_win else 0.0
        )
        pnl_by_currency[currency] = round(float(pnl.sum()) + dividends_by_currency.get(currency, 0.0), 2)
        # The realized-PnL curve starts at zero, before the first close.
        # Otherwise a losing first trade becomes the peak and disappears.
        cumulative = pnl.cumsum()
        drawdown = (cumulative - cumulative.cummax().clip(lower=0)).min()
        drawdown_by_currency[currency] = round(float(drawdown), 2) if pd.notna(drawdown) else 0.0

    dividend_totals = {cur: round(dividends_by_currency.get(cur, 0.0), 2) for cur in currencies}
    total_pnl = pnl_by_currency if mixed else next(iter(pnl_by_currency.values()), 0.0)
    max_drawdown = drawdown_by_currency if mixed else next(iter(drawdown_by_currency.values()), 0.0)
    pnl_ratio = ratio_by_currency if mixed else next(iter(ratio_by_currency.values()), 0.0)
    total_dividends = dividend_totals if mixed else next(iter(dividend_totals.values()), 0.0)

    mixed_trade_currencies = len({code_currency(symbol) for symbol in trades_df["symbol"]}) > 1
    top_symbols = (
        trades_df.groupby("symbol")
        .agg(trades=("symbol", "count"), total_amount=("amount", "sum"))
        .sort_values("trades" if mixed_trade_currencies else "total_amount", ascending=False)
        .head(10)
        .round(2)
        .reset_index()
        .to_dict(orient="records")
    )

    if mixed_trade_currencies:
        for entry in top_symbols:
            entry["currency"] = code_currency(entry["symbol"])

    market_dist = trades_df["market"].value_counts().to_dict()
    hourly_dist = trades_df["datetime"].dt.hour.value_counts().sort_index().to_dict()
    hourly_dist = {int(h): int(c) for h, c in hourly_dist.items()}

    sample = rts_df.head(5).copy()
    if not sample.empty:
        sample["buy_dt"] = sample["buy_dt"].astype(str)
        sample["sell_dt"] = sample["sell_dt"].astype(str)
        roundtrips_sample = sample.to_dict(orient="records")
    else:
        roundtrips_sample = []

    return {
        "total_trades": total_trades,
        "total_roundtrips": len(rts_df),
        "avg_holding_days": avg_hold,
        "trade_frequency_per_week": freq_per_week,
        "win_rate": win_rate,
        "profit_loss_ratio": pnl_ratio,
        "total_pnl": total_pnl,
        "total_dividends": total_dividends,
        "dividend_rows_missing_cash": dividend_rows_missing_cash,
        "max_drawdown": max_drawdown,
        "top_symbols": top_symbols,
        "market_distribution": market_dist,
        "hourly_distribution": hourly_dist,
        "roundtrips_sample": roundtrips_sample,
        **({
            "currencies": currencies,
            "total_dividends_by_currency": {
                cur: round(dividends_by_currency.get(cur, 0.0), 2)
                for cur in currencies
            },
            "currency_warning": (
                "Journal cash flows span multiple settlement currencies; monetary metrics are "
                "reported per currency with no FX conversion."
            ),
        } if mixed else {}),
    }


def _severity(score: float, thresholds: tuple[float, float]) -> str:
    """Map a numeric score to low/medium/high given (med_cutoff, high_cutoff)."""
    med, high = thresholds
    if score >= high:
        return "high"
    if score >= med:
        return "medium"
    return "low"


def _disposition_effect(rts_df: pd.DataFrame) -> dict[str, Any]:
    """Detect disposition effect: holding losers longer than winners.

    Metric = avg_loss_hold / avg_win_hold. A ratio > 1 means the user holds
    losing positions longer than winning ones — the classic disposition bias.
    """
    if rts_df.empty:
        return {"severity": "low", "evidence": "no closed roundtrips"}
    wins = rts_df[rts_df["pnl"] > 0]
    losses = rts_df[rts_df["pnl"] < 0]
    if wins.empty or losses.empty:
        return {
            "severity": "low",
            "evidence": "not enough winners and losers to compare holding times",
        }
    win_hold = float(wins["hold_days"].mean())
    loss_hold = float(losses["hold_days"].mean())
    if win_hold == 0 and loss_hold == 0:
        return {
            "severity": "low",
            "ratio_loss_to_win_hold": None,
            "avg_winner_hold_days": 0.0,
            "avg_loser_hold_days": 0.0,
            "evidence": "Both winning and losing roundtrips have zero holding time; "
            "the ratio is undefined and does not establish a hold-losers-longer tendency.",
        }
    ratio = loss_hold / win_hold if win_hold > 0 else float("inf")
    severity = _severity(ratio, (1.2, 1.5))
    ratio_text = f"ratio {ratio:.2f}" if math.isfinite(ratio) else "ratio unbounded because winner holding time is zero"
    return {
        "severity": severity,
        "ratio_loss_to_win_hold": round(ratio, 2),
        "avg_winner_hold_days": round(win_hold, 2),
        "avg_loser_hold_days": round(loss_hold, 2),
        "evidence": (
            f"Losing roundtrips held {loss_hold:.1f}d vs winning "
            f"{win_hold:.1f}d ({ratio_text}). "
            + ("Classic disposition pattern." if severity == "high"
               else "Mild hold-losers-longer tendency." if severity == "medium"
               else "Hold times roughly symmetric.")
        ),
    }


def _overtrading(df: pd.DataFrame, rts_df: pd.DataFrame) -> dict[str, Any]:
    """Detect overtrading: high-activity days produce worse PnL.

    Buckets trading days into top-quartile (busy) and bottom-quartile (quiet)
    by trade count, then compares the realized PnL of roundtrips whose sell
    lands on each bucket.
    """
    if df.empty or rts_df.empty:
        return {"severity": "low", "evidence": "insufficient data"}

    daily_trades = df.groupby(df["datetime"].dt.date).size()
    if len(daily_trades) < 4:
        return {"severity": "low", "evidence": "fewer than 4 trading days"}

    busy_cut = daily_trades.quantile(0.75)
    quiet_cut = daily_trades.quantile(0.25)
    busy_days = set(daily_trades[daily_trades >= busy_cut].index)
    quiet_days = set(daily_trades[daily_trades <= quiet_cut].index)

    rts_df = rts_df.copy()
    rts_df["sell_date"] = pd.to_datetime(rts_df["sell_dt"]).dt.date
    busy_pnl = rts_df[rts_df["sell_date"].isin(busy_days)]["pnl"]
    quiet_pnl = rts_df[rts_df["sell_date"].isin(quiet_days)]["pnl"]
    if busy_pnl.empty or quiet_pnl.empty:
        return {"severity": "low", "evidence": "roundtrips not spread across busy/quiet days"}

    busy_avg = float(busy_pnl.mean())
    quiet_avg = float(quiet_pnl.mean())

    # severity rule: busy-day PnL must be meaningfully worse than quiet-day
    gap = quiet_avg - busy_avg
    base = abs(quiet_avg) if quiet_avg != 0 else 1.0
    severity = _severity(gap / base, (0.3, 1.0)) if busy_avg < quiet_avg else "low"

    return {
        "severity": severity,
        "busy_day_avg_pnl": round(busy_avg, 2),
        "quiet_day_avg_pnl": round(quiet_avg, 2),
        "busy_day_trade_threshold": round(float(busy_cut), 1),
        "evidence": (
            f"On busy days (≥{busy_cut:.0f} trades) avg PnL {busy_avg:+.0f}; "
            f"on quiet days (≤{quiet_cut:.0f}) avg PnL {quiet_avg:+.0f}. "
            + ("High activity hurts returns." if severity == "high"
               else "Some drag from busy-day trading." if severity == "medium"
               else "Activity level does not materially hurt PnL.")
        ),
    }


def _chasing_momentum(df: pd.DataFrame) -> dict[str, Any]:
    """Detect chasing: buys concentrated after recent price rises in the same symbol.

    For each BUY, look at the user's own last 3 trades of that symbol. If the
    price trended upward (last trade price > 3rd-prior by > 3%), count the buy
    as a chase. Ratio of chasing buys → severity.
    """
    buys = df[df["side"] == "buy"].sort_values(["symbol", "datetime"]).copy()
    if buys.empty:
        return {"severity": "low", "evidence": "no buys"}

    buys["prev3_price"] = buys.groupby("symbol")["price"].shift(3)
    matured = buys.dropna(subset=["prev3_price"])
    if matured.empty:
        return {
            "severity": "low",
            "evidence": "not enough repeat buys per symbol to evaluate chasing",
        }
    chased = matured[matured["price"] > matured["prev3_price"] * 1.03]
    ratio = len(chased) / len(matured)
    severity = _severity(ratio, (0.4, 0.6))
    return {
        "severity": severity,
        "chase_ratio": round(ratio, 3),
        "buys_evaluated": int(len(matured)),
        "evidence": (
            f"{len(chased)}/{len(matured)} buys ({ratio:.0%}) came after a >3% "
            "price run-up in the same symbol. "
            + ("Strong chasing pattern." if severity == "high"
               else "Some chasing tendency." if severity == "medium"
               else "No clear chasing bias.")
        ),
    }


def _anchoring(df: pd.DataFrame) -> dict[str, Any]:
    """Detect price anchoring: repeated trades cluster in a narrow price band.

    For each symbol with ≥5 trades, compute σ(price)/mean(price). A low ratio
    (<0.05) means the user consistently trades the same price area, suggesting
    they are anchored to a reference price rather than reacting to moves.
    """
    grouped = df.groupby("symbol")
    rows: list[dict[str, Any]] = []
    for sym, sub in grouped:
        if len(sub) < 5:
            continue
        mean = float(sub["price"].mean())
        std = float(sub["price"].std())
        if mean == 0:
            continue
        cv = std / mean
        rows.append({"symbol": sym, "trades": len(sub), "mean_price": round(mean, 2), "cv": round(cv, 4)})

    if not rows:
        return {"severity": "low", "evidence": "no symbol has ≥5 trades to evaluate anchoring"}

    anchored = [r for r in rows if r["cv"] < 0.05]
    ratio = len(anchored) / len(rows)
    severity = _severity(ratio, (0.33, 0.66))
    return {
        "severity": severity,
        "anchored_symbol_ratio": round(ratio, 3),
        "symbols_evaluated": len(rows),
        "anchored_symbols": anchored[:5],
        "evidence": (
            f"{len(anchored)}/{len(rows)} frequently-traded symbols stayed in a "
            "narrow price band (CV<5%). "
            + ("Strong anchoring — repeated trades at the same price." if severity == "high"
               else "Some anchoring on select symbols." if severity == "medium"
               else "Prices vary naturally across repeat trades.")
        ),
    }


def _compute_behavior(df: pd.DataFrame) -> dict[str, Any]:
    """Run all 4 behavior diagnostics.

    Args:
        df: Standardized DataFrame (datetime-sorted). Diagnostics run on the
            buy/sell rows only; dividend cash rows are not trading behavior.

    Returns:
        Dict with disposition_effect / overtrading / chasing_momentum /
        anchoring keys, each {severity, evidence, ...metrics}.
    """
    if df.empty:
        return {"error": "empty trade journal"}
    trades_df = df[df["side"].isin(("buy", "sell"))]
    rts_df = pd.DataFrame(pair_trades_fifo(df))
    currencies = sorted({code_currency(symbol) for symbol in trades_df["symbol"]})
    if len(currencies) > 1:
        by_currency = {
            currency: _overtrading(
                trades_df[trades_df["symbol"].map(code_currency) == currency],
                rts_df[rts_df["currency"] == currency] if not rts_df.empty else rts_df,
            )
            for currency in currencies
        }
        overtrading = {
            "severity": max((result["severity"] for result in by_currency.values()),
                            key=("low", "medium", "high").index),
            "by_currency": by_currency,
            "evidence": "Activity and realized PnL compared separately by settlement currency; no FX conversion.",
        }
    else:
        overtrading = _overtrading(trades_df, rts_df)
    return {
        "disposition_effect": _disposition_effect(rts_df),
        "overtrading": overtrading,
        "chasing_momentum": _chasing_momentum(trades_df),
        "anchoring": _anchoring(trades_df),
    }


def _apply_filter(df: pd.DataFrame, expr: str) -> pd.DataFrame:
    """Filter DataFrame by a simple expression.

    Supports:
        - "YYYY-MM to YYYY-MM" or "YYYY-MM-DD to YYYY-MM-DD" (date range)
        - "symbol=XXX" (exact match)
        - "market=china_a|us|hk|crypto"

    Args:
        df: Standardized DataFrame.
        expr: Filter expression.

    Returns:
        Filtered DataFrame (may be empty).

    Raises:
        ValueError: For an inverted date range, a field that is not a journal
            column, or an expression in none of the supported forms.
    """
    expr = expr.strip()
    if not expr:
        return df

    if " to " in expr:
        try:
            lo_raw, hi_raw = (p.strip() for p in expr.split(" to ", 1))
            lo_month = re.fullmatch(r"\d{4}-\d{2}", lo_raw) is not None
            hi_month = re.fullmatch(r"\d{4}-\d{2}", hi_raw) is not None
            lo_format = "%Y-%m" if lo_month else "%Y-%m-%d"
            hi_format = "%Y-%m" if hi_month else "%Y-%m-%d"
            lo = pd.to_datetime(lo_raw, format=lo_format, errors="raise")
            hi_base = pd.to_datetime(hi_raw, format=hi_format, errors="raise")
            hi = (
                hi_base + pd.offsets.MonthBegin(1)
                if hi_month
                else hi_base + pd.Timedelta(days=1)
            )
        except ValueError:
            raise
        except Exception as exc:
            logger.warning("filter date parse failed: %s", exc)
            return df
        # Mirror alpha_bench _parse_period: reject inverted ranges.
        if lo >= hi:
            raise ValueError(f"inverted date filter: {expr!r}")
        return df[(df["datetime"] >= lo) & (df["datetime"] < hi)]

    if "=" in expr:
        key, val = (p.strip() for p in expr.split("=", 1))
        if key in df.columns:
            return df[df[key].astype(str).str.upper() == val.upper()]
        raise ValueError(f"unsupported filter field {key!r}; use symbol= or market=")
    # Returning the full journal here would report an unapplied filter as applied.
    raise ValueError(
        f"unsupported filter {expr!r}; use 'YYYY-MM[-DD] to YYYY-MM[-DD]', "
        "'symbol=...' or 'market=...'"
    )


def _json_safe(value: Any) -> Any:
    """Recursively replace non-finite floats with ``None``.

    A journal with no losing round-trip yields an infinite profit/loss ratio,
    and ``json.dumps`` would emit the non-standard ``Infinity`` literal that
    strict JSON parsers reject.
    """
    if isinstance(value, float):
        return value if math.isfinite(value) else None
    if isinstance(value, Mapping):
        return {key: _json_safe(item) for key, item in value.items()}
    if isinstance(value, (list, tuple)):
        return [_json_safe(item) for item in value]
    return value


_ANALYSIS_TYPES = frozenset({"full", "profile", "behavior", "strategy"})


def analyze_trade_journal(file_path: str, analysis_type: str = "full", filter_expr: str = "") -> str:
    """Parse a trade journal and return a JSON analysis.

    Args:
        file_path: Path to CSV/Excel file.
        analysis_type: "full" | "profile" | "behavior" | "strategy".
            "profile" and "behavior" are fully implemented; "strategy" still
            returns a Phase 4c placeholder.
        filter_expr: Optional filter. Examples: "2026-01 to 2026-03",
            "symbol=600519.SH", "market=china_a".

    Returns:
        JSON string. Keys: status, file, format_detected, total_records,
        date_range, market, profile / behavior (when applicable).
    """
    if not isinstance(analysis_type, str) or analysis_type not in _ANALYSIS_TYPES:
        return json.dumps(
            {
                "status": "error",
                "error": (
                    f"Unknown analysis_type {analysis_type!r}. "
                    f"Expected one of: {', '.join(sorted(_ANALYSIS_TYPES))}"
                ),
            },
            ensure_ascii=False,
        )
    try:
        path = safe_user_path(file_path)
    except ValueError as exc:
        return json.dumps({"status": "error", "error": str(exc)}, ensure_ascii=False)
    if not path.exists():
        return json.dumps({"status": "error", "error": f"File not found: {file_path}"}, ensure_ascii=False)
    if path.suffix.lower() not in _ALLOWED_EXT:
        return json.dumps(
            {"status": "error", "error": f"Unsupported extension {path.suffix}. Expected .csv/.xlsx/.xls"},
            ensure_ascii=False,
        )

    try:
        fmt, records = parse_file(path)
    except (ValueError, FileNotFoundError) as exc:
        return json.dumps({"status": "error", "error": str(exc)}, ensure_ascii=False)

    if not records:
        return json.dumps(
            {"status": "error", "error": "No trade records parsed"}, ensure_ascii=False
        )

    df = records_to_dataframe(records)
    if df["datetime"].isna().any():
        return json.dumps(
            {"status": "error", "error": (
                "Trade journal contains missing or invalid dates. "
                "Correct the datetime/date values before analysis."
            )},
            ensure_ascii=False,
        )
    quantities = df.loc[df["side"].isin(("buy", "sell")), "quantity"]
    if not (quantities.map(math.isfinite) & (quantities > 0)).all():
        return json.dumps(
            {"status": "error", "error": (
                "Trade journal contains missing or invalid buy/sell quantities. "
                "Correct quantity values to finite positive numbers before analysis."
            )},
            ensure_ascii=False,
        )
    try:
        filtered = _apply_filter(df, filter_expr)
    except ValueError as exc:
        return json.dumps({"status": "error", "error": str(exc)}, ensure_ascii=False)

    result: dict[str, Any] = {
        "status": "ok",
        "file": path.name,
        "format_detected": fmt,
        "total_records": len(filtered),
        "date_range": _format_range(filtered),
        "symbols_count": int(filtered["symbol"].nunique()) if not filtered.empty else 0,
        "market": _pick_dominant_market(filtered),
    }
    if filter_expr:
        result["filter_applied"] = filter_expr

    if analysis_type in {"full", "profile"}:
        result["profile"] = _compute_profile(filtered)

    if analysis_type in {"full", "behavior"}:
        result["behavior"] = _compute_behavior(filtered)

    if analysis_type in {"full", "strategy"}:
        result["strategy_features"] = {
            "status": "pending",
            "note": "Strategy extraction → backtest bridging lands in Phase 4c.",
        }

    return json.dumps(_json_safe(result), ensure_ascii=False, default=str, allow_nan=False)


def _format_range(df: pd.DataFrame) -> str:
    """Return 'YYYY-MM-DD ~ YYYY-MM-DD' or empty."""
    if df.empty:
        return ""
    return f"{df['datetime'].min().date()} ~ {df['datetime'].max().date()}"


def _pick_dominant_market(df: pd.DataFrame) -> str:
    """Return the most-traded market; empty when df is empty."""
    if df.empty:
        return ""
    return df["market"].value_counts().idxmax()


class TradeJournalTool(BaseTool):
    """Trade journal analyzer tool (registered via auto-discovery)."""

    name = "analyze_trade_journal"
    description = (
        "Analyze a user's trade journal (CSV/Excel broker export). "
        "Parses 同花顺/东方财富/富途/generic formats. Returns: "
        "(1) trading profile — holding days, frequency, win rate, PnL ratio, "
        "top symbols, market/hourly distribution; "
        "(2) behavior diagnostics — disposition effect, overtrading, chasing "
        "momentum, anchoring (each with severity + numeric evidence)."
    )
    parameters = {
        "type": "object",
        "properties": {
            "file_path": {
                "type": "string",
                "description": "Path to the uploaded CSV/Excel file.",
            },
            "analysis_type": {
                "type": "string",
                "enum": ["full", "profile", "behavior", "strategy"],
                "description": "Which analysis to run. 'full' = profile (behavior/strategy are Phase 4b placeholders).",
                "default": "full",
            },
            "filter_expr": {
                "type": "string",
                "description": "Optional filter, e.g. '2026-01 to 2026-03', 'symbol=600519.SH', 'market=china_a'.",
                "default": "",
            },
        },
        "required": ["file_path"],
    }
    repeatable = True

    def execute(self, **kwargs: Any) -> str:
        return analyze_trade_journal(
            file_path=kwargs["file_path"],
            analysis_type=kwargs.get("analysis_type") or "full",
            filter_expr=kwargs.get("filter_expr", ""),
        )