""" Phase 3 — Performance Engine Reads all history JSON files, calculates BUY signal returns vs. Nifty 50, and computes a composite AI Score. """ import os import json import re import pandas as pd from datetime import datetime try: from modules.price_fetcher import fetch_current_prices, fetch_nifty50_current except ImportError: from price_fetcher import fetch_current_prices, fetch_nifty50_current # ───────────────────────────────────────────────────────────────────────────── # Helpers # ───────────────────────────────────────────────────────────────────────────── def _parse_nifty_from_market_summary(market_summary: list) -> float | None: """ Extract a numeric Nifty 50 value from the market_summary list. Example entry: {"label": "Nifty 50", "value": "23,050.20 (+0.25%)"} """ for item in market_summary: label = item.get("label", "").lower() if "nifty 50" in label or "nifty50" in label: raw = item.get("value", "") # Strip commas, extract first numeric value match = re.search(r"[\d,]+\.?\d*", raw.replace(",", "")) if match: try: return float(match.group()) except ValueError: pass return None def _load_signal_prices_from_snapshot(data: dict) -> dict: """ Build _signal_prices by cross-referencing portfolio signals with _portfolio_snapshot LTPs. Used for backfilling old history files that lack _signal_prices. Returns dict: { symbol: { signal, price_on_day, qty, nifty_on_day } } """ snapshot = data.get("_portfolio_snapshot", []) portfolio_signals = data.get("portfolio", []) market_summary = data.get("market_summary", []) ltp_map = {} qty_map = {} for item in snapshot: sym = item.get("symbol") if sym: ltp_map[sym] = float(item.get("ltp") or 0) qty_map[sym] = float(item.get("qty") or 0) nifty_on_day = _parse_nifty_from_market_summary(market_summary) result = {} for entry in portfolio_signals: sym = entry.get("symbol") sig = entry.get("signal", "") if sym and sig == "BUY" and sym in ltp_map and ltp_map[sym] > 0: result[sym] = { "signal": sig, "price_on_day": ltp_map[sym], "qty": qty_map.get(sym, 1.0), "nifty_on_day": nifty_on_day, } return result # ───────────────────────────────────────────────────────────────────────────── # Core Loader # ───────────────────────────────────────────────────────────────────────────── def load_all_buy_signals(history_dir: str = "history", limit_days: int = 1095) -> list[dict]: """ Read every history JSON or database record and collect all BUY signals with entry prices (default 1095 days, ~3 years). Returns list of dicts: { date, symbol, signal, price_on_day, qty, nifty_on_day } """ # Try DB load first try: from modules.db import load_all_buy_signals_from_db db_rows = load_all_buy_signals_from_db(limit_days=limit_days) if db_rows: return db_rows except Exception as e: print(f"[performance] DB load failed, falling back to files: {e}") if not os.path.exists(history_dir): return [] files = sorted([f for f in os.listdir(history_dir) if f.endswith(".json")]) if len(files) > limit_days: files = files[-limit_days:] rows = [] for filename in files: date_str = filename.replace(".json", "") path = os.path.join(history_dir, filename) try: with open(path, "r", encoding="utf-8") as fp: data = json.load(fp) except Exception as e: print(f"[performance] Could not load {filename}: {e}") continue # Use stored _signal_prices if present, else derive from snapshot signal_prices = data.get("_signal_prices") if not signal_prices: signal_prices = _load_signal_prices_from_snapshot(data) for sym, sp in signal_prices.items(): if sp.get("signal") == "BUY" and sp.get("price_on_day", 0) > 0: rows.append({ "date": date_str, "symbol": sym, "signal": "BUY", "price_on_day": float(sp["price_on_day"]), "qty": float(sp.get("qty", 1.0)), "nifty_on_day": sp.get("nifty_on_day"), # may be None }) return rows # ───────────────────────────────────────────────────────────────────────────── # Main Computation # ───────────────────────────────────────────────────────────────────────────── def compute_performance(history_dir: str = "history") -> tuple[pd.DataFrame, dict]: """ Full performance computation. Returns: (signals_df, summary_dict) signals_df columns: date, symbol, signal, price_on_day, qty, nifty_on_day, current_price, current_nifty, stock_return_pct, nifty_return_pct, alpha_pct, beat_market, invested_value, current_value summary_dict keys: ai_score, buy_win_rate, avg_alpha, total_signals, total_invested, total_current_value, total_gain_loss, portfolio_return_pct, nifty_return_pct """ rows = load_all_buy_signals(history_dir) if not rows: return pd.DataFrame(), {} df = pd.DataFrame(rows) # ── Fetch current prices ────────────────────────────────────────────────── all_symbols = df["symbol"].unique().tolist() current_prices = fetch_current_prices(all_symbols) current_nifty = fetch_nifty50_current() df["current_price"] = df["symbol"].map(current_prices) df["current_nifty"] = current_nifty # ── Drop rows where we couldn't get live prices ─────────────────────────── df = df.dropna(subset=["current_price"]) df = df[df["current_price"] > 0] if df.empty: return pd.DataFrame(), {} # ── Return calculations ─────────────────────────────────────────────────── df["stock_return_pct"] = ((df["current_price"] - df["price_on_day"]) / df["price_on_day"]) * 100 # Nifty return: use stored nifty_on_day if available and realistic (>= 5000), else default to None df["nifty_return_pct"] = df.apply( lambda r: ((current_nifty - r["nifty_on_day"]) / r["nifty_on_day"]) * 100 if (r["nifty_on_day"] and current_nifty and r["nifty_on_day"] >= 5000 and current_nifty >= 5000) else None, axis=1 ) df["alpha_pct"] = df.apply( lambda r: r["stock_return_pct"] - r["nifty_return_pct"] if r["nifty_return_pct"] is not None else None, axis=1 ) df["beat_market"] = df.apply( lambda r: r["alpha_pct"] > 0 if r["alpha_pct"] is not None else None, axis=1 ) # ── Portfolio value tracking (qty-weighted) ─────────────────────────────── df["invested_value"] = df["qty"] * df["price_on_day"] df["current_value"] = df["qty"] * df["current_price"] # ── Summary metrics ─────────────────────────────────────────────────────── valid_alpha = df.dropna(subset=["alpha_pct"]) beats = valid_alpha[valid_alpha["beat_market"] == True] buy_win_rate = (len(beats) / len(valid_alpha) * 100) if len(valid_alpha) > 0 else 0 avg_alpha = float(valid_alpha["alpha_pct"].mean()) if len(valid_alpha) > 0 else 0 total_invested = float(df["invested_value"].sum()) total_current_val = float(df["current_value"].sum()) total_gain_loss = total_current_val - total_invested portfolio_return = ((total_current_val - total_invested) / total_invested * 100) if total_invested > 0 else 0 # Nifty return — use average nifty_on_day across signals weighted by invested value (only valid >= 5000) weighted_nifty_rows = df[(df["nifty_on_day"].notna()) & (df["nifty_on_day"] >= 5000)] if current_nifty and not weighted_nifty_rows.empty: total_w = weighted_nifty_rows["invested_value"].sum() weighted_nifty_base = ( (weighted_nifty_rows["nifty_on_day"] * weighted_nifty_rows["invested_value"]).sum() / total_w if total_w > 0 else weighted_nifty_rows["nifty_on_day"].mean() ) nifty_return = ((current_nifty - weighted_nifty_base) / weighted_nifty_base) * 100 else: nifty_return = None # AI Score: buy win rate (60%) + avg alpha clamped (40%) alpha_score = min(max((avg_alpha + 5) / 10 * 100, 0), 100) # +5% alpha → 100, -5% → 0 ai_score = (buy_win_rate * 0.6) + (alpha_score * 0.4) ai_score = round(min(max(ai_score, 0), 100), 1) summary = { "ai_score": ai_score, "buy_win_rate": round(buy_win_rate, 1), "avg_alpha": round(avg_alpha, 2), "total_signals": int(len(df)), "signals_beating": int(len(beats)), "total_invested": round(total_invested, 2), "total_current_value": round(total_current_val, 2), "total_gain_loss": round(total_gain_loss, 2), "portfolio_return_pct": round(portfolio_return, 2), "nifty_return_pct": round(float(nifty_return), 2) if nifty_return is not None else None, "current_nifty": round(float(current_nifty), 2) if current_nifty else None, "last_updated": datetime.now().isoformat(), } return df, summary # ───────────────────────────────────────────────────────────────────────────── # Cumulative Chart Data # ───────────────────────────────────────────────────────────────────────────── def build_cumulative_chart_data(df: pd.DataFrame, summary: dict) -> pd.DataFrame: """ Build a DataFrame for the dual-line cumulative return chart. Each row = one unique signal date, with: date, portiq_cumulative_pct, nifty_cumulative_pct Strategy: equal-time-point comparison — for each date, compute the average stock return of BUYs issued on or before that date. """ if df.empty: return pd.DataFrame() dates = sorted(df["date"].unique()) chart_rows = [] for d in dates: # All BUY signals issued on or before this date subset = df[df["date"] <= d] if subset.empty: continue avg_stock_ret = float(subset["stock_return_pct"].mean()) nifty_vals = subset.dropna(subset=["nifty_return_pct"]) avg_nifty_ret = float(nifty_vals["nifty_return_pct"].mean()) if not nifty_vals.empty else None chart_rows.append({ "date": d, "portiq_return_pct": round(avg_stock_ret, 2), "nifty_return_pct": round(avg_nifty_ret, 2) if avg_nifty_ret is not None else None, }) return pd.DataFrame(chart_rows) # ───────────────────────────────────────────────────────────────────────────── # Cache persistence # ───────────────────────────────────────────────────────────────────────────── def save_performance_cache(df: pd.DataFrame, summary: dict): """Persist performance results to PostgreSQL database for fast re-loads.""" from modules.db import get_db_connection, get_owner_user_id, clean_nans from psycopg2.extras import Json payload = { "summary": summary, "signals": df.to_dict(orient="records") if not df.empty else [], } payload = clean_nans(payload) owner_id = get_owner_user_id() conn = get_db_connection() try: with conn.cursor() as cur: cur.execute( """ INSERT INTO performance_cache (user_id, payload, updated_at) VALUES (%s, %s, CURRENT_TIMESTAMP) ON CONFLICT (user_id) DO UPDATE SET payload = EXCLUDED.payload, updated_at = CURRENT_TIMESTAMP; """, (owner_id, Json(payload)) ) conn.commit() print("[performance] Saved performance metrics cache to database.") except Exception as e: conn.rollback() print(f"[performance] Failed to save DB cache: {e}") finally: conn.close() def load_performance_cache() -> tuple[pd.DataFrame, dict]: """Load cached performance data from PostgreSQL if it exists and is fresh (< 4 hours old).""" from modules.db import get_db_connection, get_owner_user_id from datetime import datetime, timezone owner_id = get_owner_user_id() conn = get_db_connection() try: with conn.cursor() as cur: cur.execute( "SELECT payload, updated_at FROM performance_cache WHERE user_id = %s;", (owner_id,) ) row = cur.fetchone() if not row: return pd.DataFrame(), {} payload, updated_at = row # Calculate cache age from timezone-aware updated_at age = (datetime.now(timezone.utc) - updated_at).total_seconds() if age > 4 * 3600: # Stale after 4 hours return pd.DataFrame(), {} summary = payload.get("summary", {}) signals = payload.get("signals", []) df = pd.DataFrame(signals) if signals else pd.DataFrame() return df, summary except Exception as e: print(f"[performance] Database cache load failed: {e}") return pd.DataFrame(), {} finally: conn.close() def compute_actual_trades_performance(owner_id) -> dict: """ Computes performance benchmarking for all actual trades. Compares trade return vs nifty return, gets comparison list of skipped AI suggestions, and calculates portfolio metrics. """ from modules.db import load_actual_trades_from_db, get_all_ai_suggestions_for_dates trades = load_actual_trades_from_db(owner_id) if not trades: return { "trades": [], "comparison": [], "summary": { "total_trades": 0, "total_invested": 0.0, "total_current_value": 0.0, "total_gain_loss": 0.0, "portfolio_return_pct": 0.0, "nifty_return_pct": 0.0, "alpha_pct": 0.0, "win_rate": 0.0, "ai_suggested_count": 0, "you_bought_count": 0, "coverage_pct": 0.0, } } # Get current prices for all trade symbols trade_symbols = list(set(t["symbol"] for t in trades)) # Also find all AI suggestions on trade dates to include in comparison & coverage unique_dates = list(set(t["trade_date"] for t in trades)) all_comparison_symbols = set(trade_symbols) # Fetch suggestions for all dates in a single batch query all_sugs = get_all_ai_suggestions_for_dates(owner_id, unique_dates) ai_suggestions_by_date = {} for d in unique_dates: sugs = all_sugs.get(d, []) # Keep only BUY signals buy_sugs = [s for s in sugs if s["signal"] == "BUY"] ai_suggestions_by_date[d] = buy_sugs for s in buy_sugs: all_comparison_symbols.add(s["symbol"]) # Fetch live prices for all comparison symbols all_symbols_list = list(all_comparison_symbols) current_prices = fetch_current_prices(all_symbols_list) current_nifty = fetch_nifty50_current() or 23000.0 # Calculate returns for actual trades processed_trades = [] total_invested = 0.0 total_current_value = 0.0 winning_trades_count = 0 trades_with_nifty_count = 0 sum_alpha = 0.0 # Map trade_date and symbol to the trade for easy checking trade_map = {(t["trade_date"], t["symbol"]): t for t in trades} for t in trades: sym = t["symbol"] qty = t["qty_bought"] buy_price = t["buy_price"] nifty_entry = t["nifty_on_trade_day"] curr_price = current_prices.get(sym) if curr_price is None or curr_price <= 0: curr_price = buy_price stock_ret = ((curr_price - buy_price) / buy_price) * 100 if nifty_entry and nifty_entry > 0: nifty_ret = ((current_nifty - nifty_entry) / nifty_entry) * 100 alpha = stock_ret - nifty_ret beat_market = alpha > 0 trades_with_nifty_count += 1 sum_alpha += alpha else: nifty_ret = None alpha = None beat_market = None invested = qty * buy_price curr_val = qty * curr_price gain_loss = curr_val - invested total_invested += invested total_current_value += curr_val if beat_market: winning_trades_count += 1 processed_trades.append({ "trade_date": t["trade_date"], "symbol": sym, "qty_bought": qty, "buy_price": buy_price, "current_price": curr_price, "nifty_on_trade_day": nifty_entry, "stock_return_pct": round(stock_ret, 2), "nifty_return_pct": round(nifty_ret, 2) if nifty_ret is not None else None, "alpha_pct": round(alpha, 2) if alpha is not None else None, "beat_market": beat_market, "invested": round(invested, 2), "current_value": round(curr_val, 2), "gain_loss": round(gain_loss, 2) }) # Calculate comparison table comparison_list = [] ai_suggested_count = 0 you_bought_count = 0 for d in unique_dates: buy_sugs = ai_suggestions_by_date.get(d, []) for sug in buy_sugs: sym = sug["symbol"] price_on_day = sug["price_on_day"] ai_suggested_count += 1 trade_key = (d, sym) bought = trade_key in trade_map curr_price = current_prices.get(sym) or price_on_day # Fetch nifty_on_day for the date from the trade (if any trade has it) nifty_entry = None trades_on_date = [tr for tr in trades if tr["trade_date"] == d] if trades_on_date: nifty_entry = trades_on_date[0]["nifty_on_trade_day"] if bought: you_bought_count += 1 tr = trade_map[trade_key] qty = tr["qty_bought"] buy_price = tr["buy_price"] nifty_entry = tr["nifty_on_trade_day"] or nifty_entry stock_ret = ((curr_price - buy_price) / buy_price) * 100 invested = qty * buy_price curr_val = qty * curr_price gain_loss = curr_val - invested else: qty = 0.0 buy_price = None stock_ret = ((curr_price - price_on_day) / price_on_day) * 100 invested = 0.0 curr_val = 0.0 gain_loss = 0.0 if nifty_entry and nifty_entry >= 5000 and current_nifty and current_nifty >= 5000: nifty_ret = ((current_nifty - nifty_entry) / nifty_entry) * 100 alpha = stock_ret - nifty_ret else: nifty_ret = None alpha = None comparison_list.append({ "date": d, "symbol": sym, "signal": "BUY", "price_on_day": price_on_day, "nifty_on_day": nifty_entry, "bought": bought, "qty_bought": qty, "buy_price": buy_price, "current_price": curr_price, "stock_return_pct": round(stock_ret, 2), "nifty_return_pct": round(nifty_ret, 2) if nifty_ret is not None else None, "alpha_pct": round(alpha, 2) if alpha is not None else None, "invested": round(invested, 2), "current_value": round(curr_val, 2), "gain_loss": round(gain_loss, 2) }) total_gain_loss = total_current_value - total_invested portfolio_return = (total_gain_loss / total_invested * 100) if total_invested > 0 else 0.0 nifty_return = None trades_with_nifty = [pt for pt in processed_trades if pt["nifty_on_trade_day"] is not None and pt["nifty_on_trade_day"] >= 5000] if current_nifty and trades_with_nifty: total_w = sum(pt["invested"] for pt in trades_with_nifty) if total_w > 0: weighted_nifty_base = sum(pt["nifty_on_trade_day"] * pt["invested"] for pt in trades_with_nifty) / total_w else: weighted_nifty_base = sum(pt["nifty_on_trade_day"] for pt in trades_with_nifty) / len(trades_with_nifty) nifty_return = ((current_nifty - weighted_nifty_base) / weighted_nifty_base) * 100 win_rate = (winning_trades_count / len(processed_trades) * 100) if processed_trades else 0.0 coverage_pct = (you_bought_count / ai_suggested_count * 100) if ai_suggested_count > 0 else 0.0 avg_alpha = (sum_alpha / trades_with_nifty_count) if trades_with_nifty_count > 0 else 0.0 summary = { "total_trades": len(processed_trades), "total_invested": round(total_invested, 2), "total_current_value": round(total_current_value, 2), "total_gain_loss": round(total_gain_loss, 2), "portfolio_return_pct": round(portfolio_return, 2), "nifty_return_pct": round(nifty_return, 2) if nifty_return is not None else None, "alpha_pct": round(avg_alpha, 2), "win_rate": round(win_rate, 1), "ai_suggested_count": ai_suggested_count, "you_bought_count": you_bought_count, "coverage_pct": round(coverage_pct, 1), } return { "trades": processed_trades, "comparison": comparison_list, "summary": summary } # ───────────────────────────────────────────────────────────────────────────── # Rebalanced Composite Stock Scoring Engine # ───────────────────────────────────────────────────────────────────────────── MIN_BUY_SCORE_THRESHOLD = 25 def calculate_stock_composite_score( symbol: str, pct_diff: float, fundamentals: dict | None = None, is_financial: bool = False, has_earnings_pressure: bool = False, has_commodity_trough: bool = False ) -> dict: """ Computes institutional composite score (0 to 100 points) based on rebalanced weights: - 20% Price Dip % (Max 20 pts): Reduced from 50% to 20% - 50% Fundamental Quality (Max 50 pts): ROE > 15%, low D/E, steady quarterly operating margins - 30% Valuation Safety (Max 30 pts): P/E relative to median and 52-week support floor Enforces MIN_BUY_SCORE_THRESHOLD = 25. Returns: { "total_score": int, "dip_score": float, "quality_score": float, "valuation_score": float, "qualifies_buy": bool } """ fund = fundamentals or {} # 1. Price Dip % (20% -> Max 20 points) if pct_diff < 0: dip_score = min(max(abs(pct_diff), 0.0), 20.0) else: dip_score = 0.0 # 2. Fundamental Quality (50% -> Max 50 points) tier1_banks = {"HDFCBANK", "ICICIBANK", "SBIN", "KOTAKBANK", "AXISBANK"} # a) ROE > 15% (Max 20 points) roe = fund.get("roe") if is_financial and symbol in tier1_banks: # Tier-1 systemically important deposit franchise with sustainable core banking ROE roe_score = 20.0 elif roe is not None: if roe >= 15.0: roe_score = 20.0 elif roe > 0: roe_score = (roe / 15.0) * 15.0 else: roe_score = 0.0 else: roe_score = 10.0 if not is_financial else 12.0 # b) Low Debt / Equity (Max 15 points) de = fund.get("debt_to_equity") if is_financial: if symbol in tier1_banks or de is None: # Low-cost CASA deposit franchise — highest balance sheet safety de_score = 15.0 elif de <= 3.0: de_score = 8.0 elif de <= 4.5: # NBFCs like Muthoot (D/E 3.9) with wholesale borrowing de_score = 4.0 else: # High leverage lenders like IRFC (D/E 7.15) get 0 pts de_score = 0.0 else: if de is not None: if de <= 0.3: de_score = 15.0 elif de <= 0.8: de_score = 10.0 elif de <= 1.5: de_score = 5.0 else: de_score = 0.0 else: de_score = 10.0 # c) Steady Quarterly Operating Margins (Max 15 points) if has_earnings_pressure or has_commodity_trough: margin_score = 0.0 else: margin_score = 15.0 quality_score = roe_score + de_score + margin_score # 3. Valuation Safety (30% -> Max 30 points) # a) P/E relative to median (Max 15 points) pe = fund.get("trailing_pe") if symbol in tier1_banks: # Quality Tier-1 banks trading at attractive historical median valuation (PE 15-18) pe_score = 15.0 elif pe is not None: if 12.0 <= pe <= 22.0: pe_score = 15.0 elif 8.0 <= pe < 12.0: pe_score = 12.0 elif 22.0 < pe <= 32.0: pe_score = 8.0 elif 0 < pe < 8.0: pe_score = 6.0 else: pe_score = 0.0 else: pe_score = 8.0 # b) Distance from 52-week low support / tactical dip entry (Max 15 points) h52 = fund.get("week_52_high") l52 = fund.get("week_52_low") cmp_price = fund.get("cmp") if h52 and l52 and cmp_price and h52 > l52: pos = (cmp_price - l52) / (h52 - l52) if pos <= 0.35: support_score = 15.0 elif pos <= 0.65: support_score = 10.0 else: support_score = 4.0 else: support_score = 15.0 if (symbol in tier1_banks and pct_diff < 0) else (12.0 if pct_diff <= -3.0 else 8.0) valuation_score = pe_score + support_score total_score = round(dip_score + quality_score + valuation_score) total_score = min(max(total_score, 0), 100) qualifies_buy = (total_score >= MIN_BUY_SCORE_THRESHOLD) and not (has_earnings_pressure or has_commodity_trough) return { "total_score": total_score, "dip_score": round(dip_score, 1), "quality_score": round(quality_score, 1), "valuation_score": round(valuation_score, 1), "qualifies_buy": qualifies_buy, }