"Vibe-Trading: Your Personal Trading Agent"
Python ai-agentalgorithmic-tradingbacktestingfintechllmmcpmulti-agentpythonquantitative-financetrading
"""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", ""),
)