Trade With Excel – Professional Trading Dashboard

Trade With Excel is a spreadsheet-driven trading workflow for the Indian stock market. You bring NSE data — option chain, bhavcopy, FII/DII flow, your broker's tradebook — into a structured workbook, and Excel calculates PCR, max pain, open interest walls, position size, risk:reward, win rate, profit factor and drawdown. This page is the interactive version of that workbook: paste data, get analytics, export results to CSV and continue in Excel.

Four reliable free routes: (1) Copy the option-chain table on nseindia.com and paste into a sheet. (2) Data → From Web with the NSE option-chain JSON endpoint, then expand records.data. (3) Download the daily bhavcopy / FII-DII CSV and use Data → From Text/CSV. (4) Google Finance or your broker's exported tradebook for price history. Set Power Query to refresh every 5 minutes for a near-live dashboard. NSE blocks automated scraping without headers, so Power Query with a browser session or manual paste is the practical choice for retail traders.

Clean the numbers first (=VALUE(SUBSTITUTE(A2,",",""))), then compute six things: total call OI, total put OI, PCR, OI change by strike, volume by strike and IV by strike. The strike with the highest call OI above spot is your resistance (call wall); the highest put OI below spot is support (put base). Plot call OI and put OI as a two-sided bar chart against the strike ladder — the shape of that chart is the day's expected range. Add a conditional-format colour scale on OI change to spot fresh writing versus unwinding instantly.

=SUM(PutOI)/SUM(CallOI) for OI-based PCR, and =SUM(PutVol)/SUM(CallVol) for volume-based PCR. For a strike-filtered PCR use =SUMIFS(PutOI,Strike,"<="&Spot)/SUMIFS(CallOI,Strike,">="&Spot). Guard division by zero with =IFERROR(...,""). Roughly: below 0.7 = oversold, 0.7–1.0 = bearish, 1.0–1.3 = neutral-to-bullish, above 1.5 = overbought. For index options, use OI PCR for trend and volume PCR for intraday momentum.

For every candidate expiry price S, sum the intrinsic loss of all option writers: call loss =SUMPRODUCT(--(Strike<=S),(S-Strike)*CallOI) and put loss =SUMPRODUCT(--(Strike>=S),(Strike-S)*PutOI). Add them into a Total column and pick the strike with the smallest total: =INDEX(StrikeRange,MATCH(MIN(TotalRange),TotalRange,0)). That strike is where maximum option buyers lose money — historically, index expiry tends to close near it. The Max Pain panel on this page runs exactly this calculation from your pasted chain.

Use =INDEX(Strike,MATCH(MAX(PutOI),PutOI,0)) filtered to strikes below spot for support, and the same with CallOI filtered above spot for resistance. Strength matters more than a single peak: rank the top three strikes by OI and by OI change. A strike with heavy OI and positive OI change intraday is a live wall; heavy OI with negative change is being unwound and will likely break. Confirm with volume — walls with low volume are unreliable.

Minimum viable journal: Date, Time, Instrument, Side, Quantity, Entry, Stop Loss, Target, Exit, Charges, Strategy tag, Setup grade (A/B/C), Emotion, Rule followed (Y/N), Notes. Calculated columns: Risk ₹, R:R, P&L ₹, P&L %, R multiple, Result (WIN/LOSS/BE), running Equity, Peak equity and Drawdown %. On the report sheet add win rate, average win, average loss, profit factor, expectancy, best/worst strategy and a month-wise pivot. The Trade Journal module above generates all of these and exports to CSV that opens directly in Excel.

Aim for profit factor above 1.5 and positive expectancy over at least 30 trades per strategy before risking more size.

=ROUNDDOWN((Capital*RiskPercent)/(Entry-SL),0) gives quantity; divide by lot size for lots: =ROUNDDOWN(Qty/LotSize,0). Capital at risk is =Qty*ABS(Entry-SL)+Qty*Charges. Always use absolute difference so the same formula works for short trades, and wrap it in IFERROR because Entry = SL causes division by zero. Most profitable retail traders risk 0.5–1% per trade; above 2% a normal losing streak ends the account.

Build a running equity column, then a peak column =MAX($E$2:E2), then drawdown =E2/F2-1. Maximum drawdown is =MIN(G2:G1000). The recovery gain needed is =Loss/(1-Loss) — a 20% drawdown needs 25%, a 50% drawdown needs 100%. Use this number as your circuit breaker: cut position size by half once drawdown crosses 10%.

Download the daily provisional FII/DII report from NSE or BSE every evening (CSV/PDF), append it to a FIIDII sheet with columns for cash-segment purchase, sale and net for both investor types, plus the index close. Then build rolling sums (SUMIFS over 5, 20 and 60 trading days), a cumulative net-flow line and an alignment flag. Compare 5-day FII flow against 5-day Nifty change to catch divergences — FIIs selling into a rising index is a classic top warning.

Yes. In Queries & Connections → Properties you can enable Refresh every n minutes and Refresh data when opening the file. With a 1–5 minute interval and an incremental filter ([Date] > LastLoaded) your option chain, bhavcopy and FII/DII sheets stay current without macros. NSE endpoints need a valid session/headers, so many traders keep a manual “Refresh All” button plus a Power Query that reads a locally saved CSV. For true tick-level data use a broker API bridge instead of Power Query.

Prefer XLOOKUP on Excel 365/2021: it searches left or right, defaults to exact match and has a built-in if-not-found argument — ideal for fetching a strike's OI or a symbol's close. Use INDEX+MATCH on Excel 2016/2019 and Google Sheets. Avoid VLOOKUP in large option chains: inserting a column silently breaks it, and approximate match can return the wrong strike. For two-dimensional lookups (strike × expiry), use XLOOKUP nested or INDEX/MATCH/MATCH.

Excel has no native IV function, so you invert Black–Scholes. Practically: build the BS price formula with sigma as a cell, then use Goal Seek (Data → What-If Analysis) to match the market premium, or add a tiny VBA Newton–Raphson function. A quick approximation for ATM options is Straddle% ≈ 0.8 * IV * √(T), so IV ≈ (CE+PE)/Spot * 1.25 / √(DTE/365). NSE already publishes IV per strike — import it and compare against India VIX to judge whether premiums are rich or cheap.

Same architecture, different parameters: Bank Nifty lot size, a 100-point strike interval (50 near expiry), higher beta and higher IV, and heavier sensitivity to HDFC Bank and ICICI Bank weightings. Add two extra columns in your dashboard: Bank Nifty ÷ Nifty relative strength and the top-5 bank OI change. Volatility is roughly 1.3–1.6× Nifty, so halve your position size for the same rupee risk and widen stops to avoid noise stop-outs.

Yes — all of them, with recursive formulas. SMA is =AVERAGE(C2:C21); EMA is =Close*2/(n+1) + PrevEMA*(1-2/(n+1)); RSI uses AVERAGEIF on gains and losses over 14 periods; MACD is EMA12 − EMA26 with a 9-period signal EMA; ATR uses Wilder smoothing on true range =MAX(H-L,ABS(H-PrevC),ABS(L-PrevC)); VWAP is =SUMPRODUCT(TypicalPrice,Volume)/SUM(Volume) reset daily. Pivot points are simple arithmetic on yesterday's H/L/C. Build them once in an Indicators sheet and reference everywhere.

For daily and 5-minute swing/intraday strategies, yes: Excel handles ~100k rows comfortably and, crucially, every assumption is visible — slippage, charges, next-bar-open entry. Use the four-sheet framework (History → Signals → Trades → Report) with an out-of-sample split and a Monte Carlo reshuffle sheet. Move to Python/Amibroker only when you need tick data, portfolio-level optimisation or thousands of parameter combinations. Whatever the tool, always validate on Paper Trade before real money.

They solve different jobs. TradingView is best for charting and alerting. Python is best for large-scale backtesting and API-driven execution. Excel is best for structure: journals, position sizing, option chain maths, FII/DII logs and performance review. Serious retail setups use all three — charts on TradingView, decisions and risk in Excel, scale in Python. Excel remains unbeatable for the audit trail a trading business needs.

Completely free, no login, no download and no API key. Index levels, India VIX, market breadth, sector rotation and the gainers / losers / most-active lists are live market data pulled from TradingView's free public feed (read-only, may be delayed 15 minutes or more) and refresh automatically every 60 seconds while NSE is open, 09:15–15:30 IST. Everything else — option chain, FII/DII, journal trades and notes — is your own data, entered by you and stored only in this browser's local storage, never uploaded to any server. Clearing browser data or pressing Clear all removes it permanently. Export to CSV whenever you want a permanent Excel copy.

This page is mobile-first: swipe the card carousels, use the sticky section menu and tap-copy formulas on any phone or tablet. Exported CSV files open directly in Microsoft Excel (desktop, web and mobile app), Google Sheets, LibreOffice Calc and WPS. Formula cards flag which functions need Excel 365/2021 and give Google Sheets-compatible alternatives such as INDEX+MATCH and SUMPRODUCT.

Take snapshots at fixed decision points rather than continuously: 9:30, 11:00, 13:30 and 15:00 IST, plus one immediately before entry. Store each snapshot with a timestamp column so you can measure OI build-up through the day — that intraday delta is far more informative than a single EOD reading. On expiry day, refresh every 15–20 minutes after 13:30 when shifting and unwinding accelerate. Keep at least 20 days of snapshots to compare today's structure with historical expiry patterns.

Five non-negotiables: (1) separated input, calculation and output cells; (2) named ranges instead of hard-coded cell addresses; (3) a single dashboard sheet that answers bias, range, risk and action; (4) a journal with automatic win rate, profit factor and drawdown; (5) one-click refresh with error handling so a failed data pull never blanks the sheet. Avoid templates with 40 sheets and unprotected formulas — you will stop using them in a week. The Workbook Blueprint section maps a proven 10-sheet structure.

Yes, and you should. Maintain a Margin sheet with span + ELM + hedge benefit per position, available margin and a utilisation gauge: =UsedMargin/AvailableMargin. Add a rule that blocks new positions above 70% utilisation and a max-loss cell per short leg (=ABS(Strike-Spot)*Qty for a naked put). Track theta earned versus mark-to-market swings daily, and always log hedge legs together with the short leg so the workbook reflects true net exposure.

No. Option Matrix India is an education and analytics platform; nothing here is a buy/sell recommendation, and we are not SEBI-registered investment advisers. Index, volatility, breadth, sector and mover figures are live snapshots from TradingView's free public feed and can be delayed; option chain and FII/DII figures are only as accurate as the data you paste or import yourself, and the optional demo chain contains illustrative values. Always verify every number on nseindia.com or your broker's terminal before acting, and consult a SEBI-registered adviser before investing. Derivatives trading involves substantial risk of loss, including loss of the entire capital deployed.