live_market_query

Execute a live market query against Indian stock data using Groww API + screener DB. This tool runs Python code in a SANDBOX with pre-loaded market data. The LLM generates code on-the-fly based on the user's natural language query. ┌──────────────────────────────────────────────────────────────────────┐ │ SANDBOX VARIABLES (pre-loaded, use directly — no import needed): │ │ │ │ history — pd.DataFrame of daily EOD snapshots (up to 30 days) │ │ Columns: ticker, date, open, high, low, close, last_price, │ │ volume, upper_circuit_limit, lower_circuit_limit, day_change, │ │ day_change_perc, total_buy_quantity, total_sell_quantity, │ │ week_52_high, week_52_low, exchange │ │ │ │ screener — pd.DataFrame of current market_screener data (81 cols) │ │ Key columns: ticker, price, volume, rsi_14, macd, macd_hist, │ │ pe_ttm, pb_ratio, roe, piotroski_score, sector, industry, │ │ sma_50, sma_200, market_cap, etc. │ │ │ │ tickers — list[str] of ~2900 Indian stock tickers │ │ │ │ groww — Authenticated Groww API client (max 100 calls/run) │ │ groww.get_quote(exchange="NSE", segment="CASH", │ │ trading_symbol="RELIANCE") │ │ Returns: last_price, ohlc, volume, upper_circuit_limit, │ │ lower_circuit_limit, day_change, day_change_perc, │ │ total_buy_quantity, total_sell_quantity, depth, week_52_high/low │ │ │ │ pd, np — pandas and numpy │ │ │ │ results — Empty DataFrame. YOUR CODE MUST assign results to a │ │ non-empty DataFrame. │ │ │ │ ❌ FORBIDDEN: import, open(), exec(), eval(), file I/O │ └──────────────────────────────────────────────────────────────────────┘ ╔══════════════════════════════════════════════════════════════════════╗ ║ MANDATORY: CONFIRM FILTERS WITH USER BEFORE WRITING CODE ║ ║ ║ ║ Before generating code, you MUST ask the user: ║ ║ ║ ║ 1. WHICH PRICE to filter on — different prices give different ║ ║ results. Present choices in PLAIN ENGLISH (never column names): ║ ║ • "Closing price" (official exchange closing price) ║ ║ • "Last traded price" (final trade — can differ from close ║ ║ in illiquid or circuit-locked stocks) ║ ║ • "Day's high" / "Day's low" (intraday extremes) ║ ║ • "Opening price" (day's first trade) ║ ║ ║ ║ 2. WHICH DATA POINTS matter for the query — examples: ║ ║ • "Should I check if the closing price or last traded price ║ ║ hit the upper circuit? In illiquid stocks these can differ." ║ ║ • "Should I use trading volume, or also look at pending buy ║ ║ and sell orders to gauge demand pressure?" ║ ║ • "For gap detection — do you want opening price vs previous ║ ║ day's closing price, or previous day's high/low?" ║ ║ ║ ║ 3. Present a PLAIN-ENGLISH summary of your filter plan: ║ ║ ✅ "I'll find stocks where the last traded price equals the ║ ║ upper circuit limit for 5 straight days, with pending buy ║ ║ orders > 0 to confirm real demand." ║ ║ ❌ "I'll filter close >= upper_circuit_limit * 0.999" ║ ║ ║ ║ WHY: In trading, closing price vs last traded price vs high/low ║ ║ can change results significantly. A stock frozen at upper circuit ║ ║ may have last_traded = UCL but close ≠ UCL (exchange auction). ║ ║ The user knows which matters for their strategy — always ask. ║ ╚══════════════════════════════════════════════════════════════════════╝ ┌──────────────────────────────────────────────────────────────────────┐ │ HOW TO THINK ABOUT THE DATA │ │ │ │ 1. ALWAYS START with a diagnostic step: │ │ dates = history['date'].dt.date.nunique() │ │ print(f"History has {dates} unique trading days, " │ │ f"{len(history)} total rows") │ │ → This tells you if multi-day analysis is possible. │ │ If dates < N for an N-day query, say so honestly. │ │ │ │ 2. 'history' has ONE ROW PER TICKER PER DAY. Each row is an │ │ EOD snapshot captured at 3:35 PM IST. The table accumulates │ │ over time — after 5 trading days, each ticker has 5 rows. │ │ After 30 days, each ticker has ~22 rows (trading days only). │ │ │ │ 3. Multi-day pattern queries (streaks, consecutive days, moving │ │ averages) REQUIRE multiple days of history. If history has │ │ fewer days than the query needs, tell the user: │ │ "History currently has X days. This query needs Y days. │ │ Results will be available after [date]." │ │ │ │ 4. 'screener' is a CURRENT SNAPSHOT (81 cols, 1 row per ticker). │ │ Use it for: RSI, MACD, PE, sector, industry, Piotroski, etc. │ │ It has NO time dimension — don't try to compute streaks from it.│ │ │ │ 5. JOIN strategy: Use history for time-series patterns, then │ │ merge with screener for fundamental context: │ │ results = time_series_result.merge( │ │ screener[['ticker','rsi_14','pe_ttm','sector']], on='ticker')│ │ │ │ 6. groww.get_quote() is REAL-TIME but limited to 100 calls/run. │ │ Use it only for small subsets (e.g., top 50 from a filter). │ │ For bulk analysis, always use history + screener DataFrames. │ └──────────────────────────────────────────────────────────────────────┘ HISTORY SCHEMA (live_quotes_history table — 1 row per ticker per day): ticker — str, e.g. "RELIANCE.NS", "ELITECON.BO" date — datetime (EOD snapshot timestamp) open, high, low, close — float (OHLC prices in ₹) last_price — float (last traded price — may differ from close!) volume — int (total day volume) upper_circuit_limit — float (exchange-imposed upper limit) lower_circuit_limit — float (exchange-imposed lower limit) day_change — float (₹ change from prev close) day_change_perc — float (% change from prev close) total_buy_quantity — int (pending buy orders at EOD) total_sell_quantity — int (pending sell orders at EOD) week_52_high — float week_52_low — float exchange — str, "NSE" or "BSE" ┌──────────────────────────────────────────────────────────────────────┐ │ COLUMN SELECTION GUIDE — pick the RIGHT columns for each query │ │ │ │ Don't blindly use OHLC for everything. Each column has a purpose: │ │ │ │ PRICE COLUMNS (choose wisely): │ │ • close — official closing price (auction-determined). │ │ Use for: daily returns, moving averages, trend lines. │ │ • last_price — last traded price (can differ from close in │ │ illiquid stocks or during circuit freezes). │ │ Use for: current valuation, real-time comparisons. │ │ • high/low — intraday range. │ │ Use for: volatility, breakout detection, ATR. │ │ • open — day's opening price. │ │ Use for: gap-up/gap-down detection (compare with │ │ previous day's close). │ │ │ │ CIRCUIT LIMIT COLUMNS: │ │ • upper_circuit_limit — if close >= UCL * 0.999, stock hit UC. │ │ • lower_circuit_limit — if close <= LCL * 1.001, stock hit LC. │ │ • Compare last_price (not just close) to UCL/LCL — a stock can │ │ be frozen at circuit with last_price = UCL but close might be │ │ set differently by exchange in auction. │ │ │ │ VOLUME & ORDER BOOK: │ │ • volume — total shares traded. Use for: liquidity, │ │ volume spikes, accumulation/distribution. │ │ • total_buy_quantity — pending buy orders at snapshot time. │ │ • total_sell_quantity — pending sell orders at snapshot time. │ │ • buy_qty >> sell_qty = demand pressure (bullish). │ │ • sell_qty >> buy_qty = supply pressure (bearish). │ │ • For UC-locked stocks: buy_qty is huge, sell_qty ≈ 0. │ │ • For LC-locked stocks: sell_qty is huge, buy_qty ≈ 0. │ │ │ │ CHANGE COLUMNS: │ │ • day_change — ₹ absolute change from prev close. │ │ • day_change_perc — % change from prev close. │ │ • Use these for: top gainers/losers, momentum sorting. │ │ • Don't recompute manually — these are exchange-provided values. │ │ │ │ 52-WEEK COLUMNS: │ │ • week_52_high/low — use for: proximity-to-52W-high/low, │ │ breakout detection, value-at-bottom screening. │ │ • close / week_52_high = % from 52W high (nearness ratio). │ │ • close / week_52_low = % above 52W low. │ │ │ │ QUERY → BEST COLUMNS mapping: │ │ ┌────────────────────────────────┬───────────────────────────────┐ │ │ │ Query │ Columns to use │ │ │ ├────────────────────────────────┼───────────────────────────────┤ │ │ │ Upper/lower circuit detection │ last_price + UCL/LCL + │ │ │ │ │ total_buy/sell_quantity │ │ │ │ Gap up/down │ open vs prev day's close │ │ │ │ Volume spike │ volume (compare across days) │ │ │ │ Top gainers/losers │ day_change_perc │ │ │ │ Near 52W high/low │ close + week_52_high/low │ │ │ │ Demand/supply pressure │ total_buy_qty/total_sell_qty │ │ │ │ Price trend / returns │ close (across multiple days) │ │ │ │ Intraday volatility │ high - low (daily range) │ │ │ │ Frozen/illiquid stocks │ volume=0 or last_price≠close │ │ │ │ Accumulation (smart money) │ volume↑ + price↑ + buy>>sell │ │ │ │ Distribution (selling) │ volume↑ + price↓ + sell>>buy │ │ │ └────────────────────────────────┴───────────────────────────────┘ │ └──────────────────────────────────────────────────────────────────────┘ SCREENER SCHEMA (market_screener table — 1 row per ticker, current snapshot): ticker, price, volume, market_cap, sector, industry, country, exchange, rsi_14, macd, macd_signal, macd_hist, stoch_k, stoch_d, adx, sma_20, sma_50, sma_200, ema_9, ema_20, ema_50, ema_200, bb_upper, bb_middle, bb_lower, bb_position, atr, volatility, pe_ttm, pb_ratio, ps_ratio, ev_to_ebitda, earnings_yield, dividend_yield, roe, roa, net_profit_margin, operating_margin, gross_margin, current_ratio, quick_ratio, debt_to_equity, piotroski_score, altman_z_score, roic, fcf_yield, price_above_sma50, price_above_sma200, is_uptrend_sma200, above_ichimoku_cloud, has_golden_cross, macd_bullish, ... (81 columns total — use screener.columns to see all) GROWW API REFERENCE (real-time, use sparingly): groww.get_quote(exchange="NSE"|"BSE", segment="CASH", trading_symbol="RELIANCE") Returns: last_price, ohlc{open,high,low,close}, volume, upper_circuit_limit, lower_circuit_limit, day_change, day_change_perc, total_buy_quantity, total_sell_quantity, week_52_high, week_52_low, depth{buy[5],sell[5]} — order book depth (5 levels each) CODE PATTERNS (copy and adapt these): ── Pattern A: Multi-day streak detection (e.g., UC for 5 days) ── # Step 1: Check data availability days_available = history['date'].dt.date.nunique() STREAK_DAYS = 5 if days_available < STREAK_DAYS: results = pd.DataFrame([{'error': f'Need {STREAK_DAYS} days, have {days_available}'}]) else: grouped = history.groupby('ticker') hits = [] for ticker, g in grouped: g = g.sort_values('date').reset_index(drop=True) streak = 0 for _, r in g.iterrows(): close = r.get('close') ucl = r.get('upper_circuit_limit') if close and ucl and ucl > 0 and close >= ucl * 0.999: streak += 1 else: streak = 0 if streak >= STREAK_DAYS: last = g.iloc[-1] hits.append({ 'ticker': ticker, 'streak': streak, 'close': round(last['close'], 2), 'ucl': round(last['upper_circuit_limit'], 2), 'volume': int(last['volume'] or 0), }) results = pd.DataFrame(hits).sort_values('streak', ascending=False) if hits else \ pd.DataFrame([{'message': f'No stocks with {STREAK_DAYS}-day UC streak'}]) ── Pattern B: Volume spike detection (today vs N-day average) ── avg_vol = history.groupby('ticker')['volume'].mean().reset_index() avg_vol.columns = ['ticker', 'avg_vol'] cur = screener[['ticker', 'price', 'volume', 'sector']].copy() m = cur.merge(avg_vol, on='ticker') m['vol_x'] = np.where(m['avg_vol'] > 0, m['volume'] / m['avg_vol'], 0) results = m[m['vol_x'] >= 10].sort_values('vol_x', ascending=False) ── Pattern C: Cross-reference history + screener (e.g., near 52W low + strong fundamentals) ── latest = history.sort_values('date').groupby('ticker').last().reset_index() near_low = latest[latest['close'] <= latest['week_52_low'] * 1.05][['ticker', 'close', 'week_52_low']] strong = screener[screener['piotroski_score'] >= 7][['ticker', 'piotroski_score', 'roe', 'pe_ttm', 'sector']] results = near_low.merge(strong, on='ticker') ── Pattern D: Real-time spot check (small subset via Groww API) ── # Use ONLY after filtering to a small set (<100 tickers) filtered_tickers = screener[screener['rsi_14'] < 25]['ticker'].tolist()[:50] rows = [] for t in filtered_tickers: sym = t.replace('.NS','').replace('.BO','') exc = 'NSE' if t.endswith('.NS') else 'BSE' try: q = groww.get_quote(exchange=exc, segment='CASH', trading_symbol=sym) if q and q.get('last_price'): rows.append({ 'ticker': t, 'live_price': q['last_price'], 'change_pct': q.get('day_change_perc'), 'buy_qty': q.get('total_buy_quantity'), 'sell_qty': q.get('total_sell_quantity'), }) except: pass results = pd.DataFrame(rows).sort_values('change_pct', ascending=False) if rows else \ pd.DataFrame([{'message': 'No live data returned'}]) ── Pattern E: Day-over-day comparison (e.g., price gap up/down) ── pivot = history.pivot_table(index='date', columns='ticker', values='close') pivot = pivot.sort_index() if len(pivot) >= 2: pct_change = pivot.pct_change().iloc[-1] # latest day vs prev day gap_up = pct_change[pct_change > 0.05].reset_index() gap_up.columns = ['ticker', 'gap_pct'] gap_up['gap_pct'] = (gap_up['gap_pct'] * 100).round(2) results = gap_up.merge(screener[['ticker','price','sector','volume']], on='ticker') results = results.sort_values('gap_pct', ascending=False) else: results = pd.DataFrame([{'error': f'Need 2+ days, have {len(pivot)}'}]) DECISION GUIDE — which data source to use: ┌─────────────────────────────────┬──────────────────────────────────┐ │ Question type │ Use this │ ├─────────────────────────────────┼──────────────────────────────────┤ │ UC/LC streaks, multi-day trends │ history (group by ticker+date) │ │ Volume spikes vs historical avg │ history (avg) + screener (today) │ │ RSI, MACD, PE, sector filters │ screener (81 columns) │ │ Near 52W high/low │ history (has week_52_high/low) │ │ Real-time price right now │ groww.get_quote() (max 100) │ │ Order book / buy-sell pressure │ groww.get_quote() depth field │ │ Fundamentals + technicals │ screener only │ │ Cross-reference patterns+fundas │ history JOIN screener on ticker │ └─────────────────────────────────┴──────────────────────────────────┘ Args: code: Python code. Must assign a non-empty DataFrame to `results`. query_name: Human-readable name for the query. days: Days of history to load (default 30). . It is categorised as a Read tool in the Technical Analysis MCP Server, which means it retrieves data without modifying state.

Add a rule in your Intercept YAML policy under the tools section for live_market_query. You can allow, deny, rate-limit, or validate arguments. Then run Intercept as a proxy in front of the Technical Analysis MCP server.

live_market_query is a Read tool with low risk. Read-only tools are generally safe to allow by default.

Yes. Add a rate_limit block to the live_market_query rule in your Intercept policy. For example, setting max: 10 and window: 60 limits the tool to 10 calls per minute. Rate limits are tracked per agent session and reset automatically.

Set action: deny in the Intercept policy for live_market_query. The AI agent will receive a policy violation error and cannot call the tool. You can also include a reason field to explain why the tool is blocked.

live_market_query is provided by the Technical Analysis MCP server (ta-mcp/technical-analysis-mcp). Intercept sits as a proxy in front of this server to enforce policies before tool calls reach the server.