To get NSE option chain data into Excel, use MarketXLS India (marketxls.in) with a connected Zerodha or Angel One account. Once connected, broker-sourced formulas such as =ZerodhaLast("NIFTY:IN") return the live index level and =ZerodhaOpenInterest("NIFTY23OCT24000CE:IN") returns open interest for one option contract, so you can lay out strikes in rows and fill in each call and put. MarketXLS India costs ₹3,500 a month (pricing). The US MarketXLS product (marketxls.com) covers US, Canadian, UK, German, and Australian markets, not NSE.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
With the chain in a sheet, you can use ordinary Excel formulas to calculate the put-call ratio (PCR), find the strikes with the highest open interest, estimate max pain, and compare implied volatility across strikes. This guide covers the data fields, the Excel workflow, and those calculations for Nifty, Bank Nifty, and stock options.
What Is an NSE Option Chain?
An option chain is a listing of all available option contracts for a specific underlying asset, showing every available strike price and expiration date along with key data points for each contract. The NSE option chain for indices like Nifty 50 and Bank Nifty includes:
| Data Point | Description |
|---|---|
| Strike Price | The price at which the option can be exercised |
| Expiration Date | When the contract expires (weekly or monthly) |
| Call/Put | Whether it is a call option or put option |
| Last Traded Price (LTP) | Most recent trade price |
| Bid/Ask | Current bid and ask prices |
| Open Interest (OI) | Number of outstanding contracts at each strike |
| Change in OI | How open interest changed from the previous session |
| Volume | Number of contracts traded during the session |
| Implied Volatility (IV) | Market's expectation of future price movement |
Why NSE Option Chain Data Matters
The NSE option chain is not just a list of prices — it reveals market sentiment, positioning, and potential support/resistance levels:
- Open interest concentration at specific strikes indicates where large traders are positioned. High call OI at a strike suggests resistance; high put OI suggests support.
- Change in open interest shows whether traders are adding new positions (bullish or bearish conviction) or closing existing ones.
- Put-Call Ratio (PCR) derived from option chain data indicates overall market sentiment — above 1.0 suggests bullish sentiment, below 1.0 suggests bearish.
- Implied volatility skew across strikes reveals how the market prices risk differently for upside vs. downside moves.
- Max Pain — the strike where the most options expire worthless — can indicate potential expiration-day price targets.
Getting NSE Option Chain Data in Excel with MarketXLS
MarketXLS provides Excel functions that pull option chain data directly into your spreadsheet. Here are the primary methods:
Method 1: Broker-connected formulas (MarketXLS India)
MarketXLS India pulls NSE prices through your own broker session. Connect your Zerodha or Angel One account in the add-in, then use formulas with the :IN suffix:
=ZerodhaLast("NIFTY:IN")
=ZerodhaOpenInterest("NIFTY23OCT24000CE:IN")
=AngelOneLast("TATAMOTORS:IN")
The first formula returns the Nifty level, the second returns open interest for one Nifty option contract (a 24,000 call in this example symbol), and the third returns a stock price through Angel One. In the Microsoft 365 add-in, these functions use the mxlsIN. prefix, for example =mxlsIN.ZerodhaLast("NIFTY:IN").
Method 2: Building the chain strike by strike
To build a chain, put the strikes in a column, build the call and put contract symbol for each strike and expiry in the next columns, and point ZerodhaLast and ZerodhaOpenInterest at those symbol cells. Copy the formulas down and the sheet becomes a live chain for the strikes you care about. Match the symbol format exactly to your broker's instrument list; a symbol for a contract that does not exist returns no value.
Method 3: Building a Custom Option Chain View
After pulling the raw option chain data, you can use Excel's built-in tools to create a custom view:
- Filter by expiration: Use Excel's filter function to show only the nearest weekly expiry or a specific monthly expiry
- Sort by open interest: Identify the strikes with the highest activity
- Add conditional formatting: Highlight high-OI strikes, unusual volume, or extreme IV levels
- Calculate derived metrics: PCR ratios, max pain, IV percentiles
Analyzing NSE Option Chain Data in Excel
Open Interest Analysis
Open interest is the main data point traders use in the NSE option chain to gauge market positioning.
Identifying Support and Resistance
| OI Pattern | Interpretation | Trading Implication |
|---|---|---|
| High Put OI at a strike | Large traders sold puts; expect support | Potential support level |
| High Call OI at a strike | Large traders sold calls; expect resistance | Potential resistance level |
| Increasing Put OI + Price rising | New bullish positions being added | Bullish confirmation |
| Increasing Call OI + Price falling | New bearish positions being added | Bearish confirmation |
| Decreasing OI + Price moving | Positions being closed, not new conviction | Potential trend weakening |
Max Pain Calculation
Max Pain is the strike price at which the maximum number of options (calls + puts combined) would expire worthless, causing the least payout by option sellers. To calculate it in Excel:
- Build the chain for one expiry with call and put open interest for each strike
- For each strike price, calculate the total intrinsic value of all calls and puts that would be in-the-money
- The strike where this total is minimized is the Max Pain level
This can serve as a potential expiration-day magnet, particularly for weekly options.
Put-Call Ratio (PCR) Analysis
The Put-Call Ratio compares put open interest (or volume) to call open interest (or volume):
PCR = Total Put OI / Total Call OI
In Excel, after pulling the option chain data:
=SUMIF(TypeColumn, "Put", OIColumn) / SUMIF(TypeColumn, "Call", OIColumn)
Interpreting PCR:
| PCR Value | Market Sentiment | Notes |
|---|---|---|
| > 1.2 | Bullish (contrarian) | High put writing suggests support |
| 0.8 - 1.2 | Neutral | Balanced positioning |
| < 0.8 | Bearish (contrarian) | Low put interest suggests complacency |
Note: PCR interpretation depends on whether the high values come from put buying (bearish) or put selling (bullish — indicating confidence the stock will stay above the strike). Analyzing PCR alongside open interest changes provides more reliable signals.
Implied Volatility Analysis
The option chain provides implied volatility for each strike. Analyzing the IV structure gives insights into market expectations:
- IV Skew: Compare IV at OTM puts vs. OTM calls. Higher put IV suggests the market is pricing in more downside risk.
- IV Term Structure: Compare IV across different expirations. Higher near-term IV suggests an expected event (earnings, policy announcement).
- IV Rank/Percentile: Compare current IV to its historical range to determine if options are "cheap" or "expensive."
Methods for Accessing NSE Option Chain Data: Comparison
| Method | Pros | Cons | Best For |
|---|---|---|---|
| MarketXLS India (broker-connected formulas) | Excel formulas, customizable, live data through your Zerodha or Angel One session | Requires a MarketXLS India subscription and a connected broker account | Traders building models in Excel |
| NSE Website | Free, official source | Manual copy-paste, no Excel integration, rate-limited | Quick reference, casual checking |
| Broker Platforms (Zerodha, Angel One, etc.) | Real-time, integrated with trading | Limited export options, not customizable | Order execution, quick reference |
| Google Sheets Add-ons | Free or low-cost, cloud-based | Limited NSE support, less reliable | Casual analysis |
| Third-Party APIs | Programmable, automated | Requires coding, may have costs | Developers building custom tools |
| Bloomberg/Reuters | Institutional-grade, comprehensive | Very expensive | Professional institutions |
The main difference with MarketXLS India is that the data arrives in Excel cells through formulas, so you skip the manual copy-paste from the NSE website.
Building an NSE Option Chain Dashboard in Excel
Here is a practical workflow for creating a comprehensive Nifty option chain dashboard:
Step 1: Pull the Option Chain
List the strikes for your chosen expiry in column A, build the call and put contract symbols in the next columns, and add =ZerodhaLast() and =ZerodhaOpenInterest() for each symbol (see Method 2 above).
Step 2: Get the Current Nifty Level
=ZerodhaLast("NIFTY:IN")
Step 3: Choose the Expiry
Build one sheet per expiry you trade (for example, the nearest weekly and the current monthly), since each expiry has its own contract symbols.
Step 4: Create a Summary View
Build a table that shows the most relevant data around the current Nifty level:
| Strike | Call OI | Call Change in OI | Call LTP | Put LTP | Put Change in OI | Put OI |
|---|---|---|---|---|---|---|
| 23,900 | — | — | — | — | — | — |
| 24,000 | — | — | — | — | — | — |
| 24,100 | — | — | — | — | — | — |
| 24,200 | — | — | — | — | — | — |
| 24,300 | — | — | — | — | — | — |
Values are populated from the broker-connected formulas in each row.
Step 5: Add Derived Metrics
- PCR per strike:
=Put OI / Call OIfor each row - Total PCR: Sum of all Put OI / Sum of all Call OI
- Max Pain: Use the calculation method described above
- ATM IV: Extract implied volatility for the ATM strike
Step 6: Add Conditional Formatting
- Highlight strikes with OI above 1 million in bold
- Color-code the current ATM strike
- Use heat maps for OI concentration
Practical Example: Nifty Weekly Options Analysis
Let us walk through a complete analysis workflow:
Scenario
Nifty is at 24,100. You want to analyze the nearest weekly expiry to identify support/resistance levels and potential trading opportunities.
Step 1: Pull data
=ZerodhaLast("NIFTY:IN")
Then refresh the strike rows of your chain sheet for the nearest expiry.
Step 2: Identify key OI levels
From the option chain data, find the strikes with the highest open interest:
- Highest Call OI: 24,500 strike — this is resistance
- Highest Put OI: 24,000 strike — this is support
Step 3: Calculate PCR
Total Put OI: 8,500,000
Total Call OI: 7,200,000
PCR = 8,500,000 / 7,200,000 = 1.18
A PCR of 1.18 suggests moderately bullish sentiment (more put writing than call writing).
Step 4: Check for unusual activity
Look for strikes with large changes in open interest — these indicate new positions being built and may signal expected price targets.
Step 5: Assess implied volatility
Compare the IV at the ATM strike to its recent range. If IV is in the lower percentile, options are relatively cheap — favorable for buying strategies. If IV is elevated, options are expensive — favorable for selling strategies.
NSE Option Chain Data for Bank Nifty
Bank Nifty options follow the same analysis approach. Use BANKNIFTY in the index and contract symbols:
=ZerodhaLast("BANKNIFTY:IN")
Bank Nifty tends to have:
- Higher implied volatility than Nifty (reflecting the banking sector's sensitivity to RBI policy)
- Wider strike price intervals
- Strong open interest concentration at round-number strikes
- Higher sensitivity to earnings season for major banks
The same dashboard approach works — pull the chain, filter by expiry, identify OI levels, calculate PCR, and monitor IV.
NSE Option Chain Data for Individual Stocks
Many Indian large-cap stocks have active options markets on NSE. Use the same functions:
=ZerodhaLast("RELIANCE:IN")
=ZerodhaLast("TCS:IN")
=ZerodhaLast("INFY:IN")
=ZerodhaLast("HDFCBANK:IN")
Build the strike rows for each stock the same way as for Nifty, using the stock's option contract symbols.
Stock options on NSE tend to have:
- Lower liquidity than index options (wider bid-ask spreads)
- Monthly expirations only (no weekly options for most stocks)
- Higher implied volatility around earnings dates
- Lower open interest compared to Nifty and Bank Nifty
When analyzing stock option chains, pay extra attention to liquidity — wide bid-ask spreads can significantly impact trading costs.
Advanced Analysis: Using NSE Option Chain Data for Strategy Selection
The option chain data can guide your strategy selection:
| Market Outlook | OI Signal | IV Level | Suggested Strategy |
|---|---|---|---|
| Bullish, moderate move | High Put OI at support | Low IV | Bull call spread |
| Bearish, moderate move | High Call OI at resistance | Low IV | Bear put spread |
| Range-bound | High OI at both support and resistance | High IV | Iron condor or short strangle |
| Breakout expected | Decreasing OI, rising volume | Low IV | Long straddle or strangle |
| Post-event decay | Stable OI | High IV (expected to crush) | Short options or credit spread |
Combining Option Chain Analysis with Technical Analysis
The most effective approach combines option chain data with price action:
- Use open interest levels to identify key support and resistance zones
- Confirm with technical indicators (moving averages, RSI, trendlines)
- Use IV levels to determine whether to buy or sell premium
- Size positions based on the distance between current price and OI-defined levels
Tips for Working with NSE Option Chain Data in Excel
1. Refresh Data Regularly
Option chain data changes throughout the trading session. Refresh your MarketXLS data periodically (especially during market hours) to capture the latest OI changes and price movements.
2. Focus on Liquid Strikes
Not all strikes in the option chain are equally useful. Focus your analysis on strikes within 5-10% of the current price, where most of the liquidity and meaningful OI resides. Far OTM strikes with minimal OI provide little analytical value.
3. Track OI Changes Over Multiple Days
A single snapshot of open interest is less informative than the trend. Build a tracking sheet that records daily OI at key strikes to identify building or unwinding patterns over time.
4. Account for NSE-Specific Factors
- Expiry calendar: NSE has changed weekly expiry days and discontinued Bank Nifty weekly options in late 2024, so check the current NSE expiry calendar before building an expiry sheet. Theta decay accelerates in the final days before any expiry
- RBI policy dates: Bank Nifty options see elevated IV before monetary policy announcements
- FII/DII activity: Foreign and domestic institutional flows significantly impact Nifty movements
- Lot sizes: NSE options trade in fixed lot sizes that NSE revises periodically, so check the current lot size before sizing a position
5. Use Data Validation
When building automated dashboards, add checks to ensure the data is fresh and complete. Compare the pulled data's timestamp to the current time and flag stale data.
Frequently Asked Questions About NSE Option Chain Data
How do I get NSE option chain data in Excel without manual copy-paste?
Use MarketXLS India with a connected Zerodha or Angel One account. List strikes in rows, build each call and put symbol, and use formulas such as =ZerodhaLast() and =ZerodhaOpenInterest() to fill in price and open interest. The values update from your broker session, so no copy-paste is needed. The same approach works for Bank Nifty and stock options.
What is the best way to analyze Nifty open interest data?
Focus on the strikes with the highest absolute open interest for calls and puts — these represent key resistance and support levels respectively. Track changes in open interest daily to identify whether new positions are being built or old positions are being closed. Calculate the Put-Call Ratio to gauge overall market sentiment. All of this can be done in Excel after pulling the chain with MarketXLS.
Can I get real-time NSE option chain data in Excel?
Yes, through your broker. MarketXLS India streams NSE prices from your connected Zerodha or Angel One session into Excel, so the data is as current as your broker feed while the market is open.
How do I calculate Max Pain for Nifty options in Excel?
Build the chain for one expiry with call and put open interest for each strike. For each strike, calculate the total intrinsic value that all call and put holders would lose if Nifty expired at that strike. The strike with the minimum total loss (i.e., maximum "pain" for option buyers) is the Max Pain level. This calculation involves iterating through all strikes and summing up the ITM value of all outstanding contracts at each potential expiry price.
What is PCR and how do I calculate it from option chain data?
PCR (Put-Call Ratio) is calculated by dividing total put open interest by total call open interest. After building the chain with open interest for each strike, sum the put OI column and divide by the sum of the call OI column. A PCR above 1.0 generally indicates more put writing (potentially bullish), while below 1.0 indicates more call writing (potentially bearish). Context matters: high PCR from put buying is bearish, while high PCR from put selling is bullish.
Can I access Bank Nifty option chain data with MarketXLS?
Yes. With MarketXLS India and a connected broker, use =ZerodhaLast("BANKNIFTY:IN") for the index and build the Bank Nifty strike rows the same way as Nifty. The same analysis techniques (OI analysis, PCR, IV skew, max pain) apply.
Start Analyzing NSE Option Chain Data in Excel
MarketXLS India brings NSE option chain data into Excel through your Zerodha or Angel One connection. Use =ZerodhaLast() and =ZerodhaOpenInterest() for each contract, and Excel's built-in tools for PCR, OI tracking, max pain, and strategy analysis. MarketXLS India costs ₹3,500 a month (pricing).
Disclaimer
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. Options trading involves significant risk and is not appropriate for all investors.
