#!/usr/bin/env python3 """ MCP server — structured financial data (DuckDB). Tools exposed: - get_price_history(ticker, days) → recent OHLCV data - get_indicators(ticker) → RSI, MACD, SMAs + fundamentals - compare_stocks(tickers) → side-by-side comparison """ import json import os import duckdb from mcp.server.fastmcp import FastMCP DB_PATH = os.path.join( os.path.dirname(os.path.abspath(__file__)), "..", "data", "finsight.db" ) mcp = FastMCP("FinSight SQL") def _conn() -> duckdb.DuckDBPyConnection: return duckdb.connect(DB_PATH, read_only=True) # --------------------------------------------------------------------------- # Tool: get_price_history # --------------------------------------------------------------------------- @mcp.tool() def get_price_history(ticker: str, days: int = 30) -> str: """ Return the last N trading days of OHLCV price data for a stock ticker. Use this to examine recent price action, trend direction, and volume. Returns: JSON list of {date, open, high, low, close, volume}. """ ticker = ticker.upper().strip() try: con = _conn() rows = con.execute( """ SELECT date, open, high, low, close, volume FROM prices WHERE ticker = ? ORDER BY date DESC LIMIT ? """, [ticker, days], ).fetchall() con.close() except Exception as e: return json.dumps({"error": str(e)}) if not rows: return json.dumps({"error": f"No price data found for {ticker}"}) return json.dumps( [ { "date": str(r[0]), "open": round(r[1], 2), "high": round(r[2], 2), "low": round(r[3], 2), "close": round(r[4], 2), "volume": r[5], } for r in rows ], indent=2, ) # --------------------------------------------------------------------------- # Tool: get_indicators # --------------------------------------------------------------------------- @mcp.tool() def get_indicators(ticker: str) -> str: """ Return the latest technical indicators and fundamental data for a ticker. Includes: RSI-14 (with overbought/oversold signal), MACD line/signal/histogram and trend direction, SMA-20/50/200 with price-relative positioning, golden cross flag, plus P/E, forward P/E, EPS, market cap, sector, beta, 52-week range. Use this as the primary tool when analysing any individual stock. """ ticker = ticker.upper().strip() try: con = _conn() ind = con.execute( """ SELECT date, rsi_14, macd, macd_signal, macd_hist, sma_20, sma_50, sma_200 FROM indicators WHERE ticker = ? ORDER BY date DESC LIMIT 1 """, [ticker], ).fetchone() price_row = con.execute( "SELECT close FROM prices WHERE ticker = ? ORDER BY date DESC LIMIT 1", [ticker], ).fetchone() fund = con.execute( """ SELECT name, sector, industry, market_cap, pe_ratio, forward_pe, eps, dividend_yield, beta, week_52_high, week_52_low FROM fundamentals WHERE ticker = ? """, [ticker], ).fetchone() con.close() except Exception as e: return json.dumps({"error": str(e)}) if not ind: return json.dumps({"error": f"No data found for {ticker}"}) price = price_row[0] if price_row else None rsi = round(ind[1], 2) if ind[1] is not None else None result = { "ticker": ticker, "as_of": str(ind[0]), "price": round(price, 2) if price else None, "indicators": { "rsi_14": rsi, "rsi_signal": ( "overbought (>70)" if rsi and rsi > 70 else "oversold (<30)" if rsi and rsi < 30 else "neutral" ), "macd": round(ind[2], 4) if ind[2] is not None else None, "macd_signal": round(ind[3], 4) if ind[3] is not None else None, "macd_hist": round(ind[4], 4) if ind[4] is not None else None, "macd_trend": "bullish" if ind[4] and ind[4] > 0 else "bearish", "sma_20": round(ind[5], 2) if ind[5] is not None else None, "sma_50": round(ind[6], 2) if ind[6] is not None else None, "sma_200": round(ind[7], 2) if ind[7] is not None else None, "above_sma_20": bool(price and ind[5] and price > ind[5]), "above_sma_50": bool(price and ind[6] and price > ind[6]), "above_sma_200": bool(price and ind[7] and price > ind[7]), "golden_cross": bool(ind[6] and ind[7] and ind[6] > ind[7]), }, "fundamentals": { "name": fund[0] if fund else None, "sector": fund[1] if fund else None, "industry": fund[2] if fund else None, "market_cap_B": round(fund[3] / 1e9, 2) if fund and fund[3] else None, "pe_ratio": round(fund[4], 2) if fund and fund[4] else None, "forward_pe": round(fund[5], 2) if fund and fund[5] else None, "eps": round(fund[6], 2) if fund and fund[6] else None, "dividend_yield_pct": round(fund[7] * 100, 2) if fund and fund[7] else None, "beta": round(fund[8], 2) if fund and fund[8] else None, "week_52_high": round(fund[9], 2) if fund and fund[9] else None, "week_52_low": round(fund[10], 2) if fund and fund[10] else None, } if fund else None, } return json.dumps(result, indent=2) # --------------------------------------------------------------------------- # Tool: compare_stocks # --------------------------------------------------------------------------- @mcp.tool() def compare_stocks(tickers: str) -> str: """ Compare multiple stocks side-by-side on price, 30-day return, RSI, MACD trend, SMA positioning, golden cross, P/E ratio, forward P/E, and market cap. Pass tickers as a comma-separated string, e.g. 'AAPL,MSFT,NVDA'. Use this to rank stocks or identify the strongest momentum plays in a group. """ ticker_list = [t.strip().upper() for t in tickers.split(",") if t.strip()] if not ticker_list: return json.dumps({"error": "No tickers provided"}) results = [] try: con = _conn() for ticker in ticker_list: ind = con.execute( """ SELECT date, rsi_14, macd_hist, sma_50, sma_200 FROM indicators WHERE ticker = ? ORDER BY date DESC LIMIT 1 """, [ticker], ).fetchone() price_row = con.execute( "SELECT close FROM prices WHERE ticker = ? ORDER BY date DESC LIMIT 1", [ticker], ).fetchone() fund = con.execute( "SELECT name, sector, market_cap, pe_ratio, forward_pe FROM fundamentals WHERE ticker = ?", [ticker], ).fetchone() # 30-day return: latest close / close ~30 trading days ago ret_row = con.execute( """ WITH ranked AS ( SELECT close, ROW_NUMBER() OVER (ORDER BY date DESC) AS rn FROM prices WHERE ticker = ? ) SELECT (SELECT close FROM ranked WHERE rn = 1) / NULLIF((SELECT close FROM ranked WHERE rn = 31), 0) - 1 """, [ticker], ).fetchone() price = price_row[0] if price_row else None results.append({ "ticker": ticker, "name": fund[0] if fund else ticker, "sector": fund[1] if fund else None, "price": round(price, 2) if price else None, "return_30d_pct": round(ret_row[0] * 100, 2) if ret_row and ret_row[0] else None, "rsi_14": round(ind[1], 1) if ind and ind[1] is not None else None, "macd_trend": "bullish" if ind and ind[2] and ind[2] > 0 else "bearish", "above_sma_50": bool(price and ind and ind[3] and price > ind[3]), "above_sma_200": bool(price and ind and ind[4] and price > ind[4]), "golden_cross": bool(ind and ind[3] and ind[4] and ind[3] > ind[4]), "market_cap_B": round(fund[2] / 1e9, 1) if fund and fund[2] else None, "pe_ratio": round(fund[3], 1) if fund and fund[3] else None, "forward_pe": round(fund[4], 1) if fund and fund[4] else None, }) con.close() except Exception as e: return json.dumps({"error": str(e)}) return json.dumps(results, indent=2) if __name__ == "__main__": mcp.run()