Intraday Trading Dashboard in Excel with Live Zerodha Data

Why Build an Intraday Dashboard in Excel?

Every serious intraday trader in India knows the limits of broker terminals. Zerodha Kite is excellent for order execution, but when it comes to customised multi-stock monitoring, comparing sectors, tracking buy/sell pressure across your watchlist, or combining live data with your own analysis — you hit a wall.

Excel gives you unlimited flexibility. The problem has always been getting live data into it.

MarketXLS India solves this by streaming real-time data from your Zerodha account directly into Excel cells using simple formulas. No Kite API coding. No VBA macros. No Python scripts. Just type a formula and the data streams live.

This guide walks you through building a professional intraday trading dashboard from scratch.

Dashboard Overview

Your finished dashboard will have four sections:

1. Market Pulse — Nifty, Bank Nifty, and key indices with live data
2. Intraday Watchlist — Your active watchlist with price, volume, and change data
3. Buy/Sell Pressure Monitor — Real-time order flow analysis
4. Options Quick View — Live Nifty option data with OI for derivatives traders

Section 1: Market Pulse

Start by creating a quick-reference panel for the major indices. This gives you market context at a glance.

Index Data Setup

Formulas for Nifty 50 Row

Last:    =mxlsIN.ZerodhaLast("NIFTY:IN")
Open:    =mxlsIN.ZerodhaOpen("NIFTY:IN")
High:    =mxlsIN.ZerodhaHigh("NIFTY:IN")
Low:     =mxlsIN.ZerodhaLow("NIFTY:IN")
Change:  =mxlsIN.ZerodhaChange("NIFTY:IN")

Formulas for Bank Nifty Row

Last:    =mxlsIN.ZerodhaLast("BANKNIFTY:IN")
Open:    =mxlsIN.ZerodhaOpen("BANKNIFTY:IN")
High:    =mxlsIN.ZerodhaHigh("BANKNIFTY:IN")
Low:     =mxlsIN.ZerodhaLow("BANKNIFTY:IN")
Change:  =mxlsIN.ZerodhaChange("BANKNIFTY:IN")

Add Calculated Fields

Day Range Percentage — shows how much of the day's range has been covered:

=(ZerodhaLast - ZerodhaLow) / (ZerodhaHigh - ZerodhaLow)

A value above 0.8 means the index is near its day high. Below 0.2 means it is near its day low.

Section 2: Intraday Watchlist

This is the core of your dashboard. Create a watchlist of 10-20 stocks you actively trade intraday.

Column Layout

Suggested Intraday Watchlist Stocks

For a typical intraday trader on NSE, here are high-liquidity stocks:

Enter these in column A and the streaming formulas will populate all other columns automatically.

Conditional Formatting

Apply conditional formatting to make the watchlist scannable:

1. Change % column — Green for positive, Red for negative
2. Day Range % — Color scale from red (near low) to green (near high)
3. Volume column — Highlight cells where volume exceeds 1.5x the typical daily average

Section 3: Buy/Sell Pressure Monitor

This is where your dashboard gives you an edge that most retail traders do not have. MarketXLS streams the total buy quantity and sell quantity for each stock, which reveals real-time order flow.

Setup

Add two more columns to your watchlist:

Calculated Metrics

Buy/Sell Ratio:

=J2/K2

• Ratio > 1.5 → Strong buying pressure
• Ratio between 0.7 and 1.5 → Neutral
• Ratio < 0.7 → Strong selling pressure

Net Order Imbalance:

=(J2-K2)/(J2+K2)

This gives a normalised value between -1 (all sellers) and +1 (all buyers).

Interpreting Buy/Sell Pressure

Use conditional formatting to highlight extreme readings. When multiple stocks in your watchlist show buy/sell ratios above 2.0, it often indicates a broad market rally.

Section 4: Options Quick View for Derivatives Traders

If you trade Nifty or Bank Nifty options intraday, add a quick options panel. For a deeper dive into Greeks analysis, see our dedicated option Greeks calculator guide.

ATM Option Data

First, find the ATM strike and next expiry:

=mxlsIN.StrikeNext("NIFTY:IN")
=mxlsIN.ExpirationNext("NIFTY:IN")

Build ATM Call and Put Symbols

Call: =mxlsIN.OptionSymbol("NIFTY", ExpiryCell, "C", StrikeCell)
Put:  =mxlsIN.OptionSymbol("NIFTY", ExpiryCell, "P", StrikeCell)

Get Live Option Prices and OI

ATM CE Price: =mxlsIN.ZerodhaLast(CallSymbol&":IN")
ATM PE Price: =mxlsIN.ZerodhaLast(PutSymbol&":IN")
ATM CE OI:    =mxlsIN.ZerodhaOpenInterest(CallSymbol&":IN")
ATM PE OI:    =mxlsIN.ZerodhaOpenInterest(PutSymbol&":IN")

Derived Metrics

Straddle Price (ATM CE + ATM PE):

=ATMCallPrice + ATMPutPrice

This tells you the market's expected range for the day. If the straddle costs 300 points and Nifty is at 24,000, the market expects Nifty to stay between 23,700 and 24,300.

Put-Call Ratio (OI-based):

=ATMPutOI / ATMCallOI

• PCR > 1.2 → Bullish (more put writing suggests support)
• PCR < 0.8 → Bearish (more call writing suggests resistance)

Section 5: Nifty Futures Tracking

For traders who trade Nifty futures intraday, add a futures row:

Build the Futures Symbol

=mxlsIN.futureSymbol("NIFTY", ExpiryDateCell)

Get Live Futures Data

Last:   =mxlsIN.ZerodhaLast(FutSymbol&":IN")
Volume: =mxlsIN.ZerodhaVolume(FutSymbol&":IN")
OI:     =mxlsIN.ZerodhaOpenInterest(FutSymbol&":IN")

Premium/Discount

Calculate the futures premium or discount to spot:

=FuturesLast - NiftySpotLast

A rising premium during the day often indicates institutional buying. A falling premium or discount indicates bearish pressure.

Pro Tips for Intraday Traders

1. Use Last Trade Time to Spot Stale Data

=mxlsIN.ZerodhaLastTradeTime(A2&":IN")

If a stock has not traded for several minutes during market hours, it may indicate a trading halt or extremely low liquidity. Avoid these stocks for intraday.

2. Build Sector Heat Maps

Group your watchlist by sector and calculate the average change percentage:

=AVERAGEIF(SectorColumn, "Banking", ChangeColumn)

This tells you which sectors are leading or lagging in real time.

3. Volume Comparison

Compare current volume to a threshold to spot unusual activity:

=IF(VolumeCell > 1000000, "HIGH", "NORMAL")

Unusually high volume on a stock during the first hour often signals institutional interest.

4. Pre-Market Setup

Before the market opens at 9:15 AM:

1. Update your watchlist symbols based on the day's plan
2. Set up conditional formatting thresholds
3. Ensure your Zerodha connection is active
4. The previous close data will already be populated

Once the market opens, all live columns start streaming automatically.

Angel One Version

Everything in this guide works identically with Angel One. Simply replace every Zerodha function with its AngelOne equivalent:

Frequently Asked Questions

Can I track more than 20 stocks at once?

Yes. MarketXLS streams data via WebSocket, so you can track as many symbols as your broker connection supports. Most traders find 15-25 stocks to be the optimal watchlist size for intraday.

Does the dashboard slow down Excel?

No. MarketXLS uses efficient WebSocket streaming. The data flows directly into cells without heavy computation overhead. Even with 50+ streaming cells, Excel remains responsive.

Can I place orders from Excel?

This guide focuses on monitoring and analysis. Order placement requires the MarketXLS Pro tier. Most traders keep their order execution on Zerodha Kite and use this dashboard for analysis and decision-making.

Do I need to restart the dashboard daily?

No. Open your workbook, ensure your broker connection is active, and the data starts streaming when the market opens. The previous close and reference data persist between sessions.

Get Started

Build your intraday trading dashboard in under 30 minutes and never waste time on manual data entry again.

Want to track your long-term holdings too? See our portfolio tracker guide. Or skip the setup and download our free pre-built template with a watchlist, portfolio tracker, and option chain already wired up.

Start your MarketXLS India subscription →

Works with Zerodha and Angel One. Starts at ₹3,500/month. Cancel anytime.