finsight / src /mcp_sql /server.py
margotbtn's picture
Configured HF space
12a6efa
Raw History Blame Contribute Delete
9.27 kB
#!/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()