NSE Option Chain Data: How to Get, Analyze, and Use Indian Option Chains in Excel

NSE option chain data is critical for traders and analysts who follow India's National Stock Exchange, where Nifty 50 and Bank Nifty options are among the most actively traded derivatives contracts in the world. Accessing this data in Excel — rather than relying solely on the NSE website or broker platforms — gives you the flexibility to build custom analysis models, track open interest patterns, calculate Greeks, and develop automated trading signals.

This guide covers everything you need to know about getting NSE option chain data into Excel: the types of data available, how to use MarketXLS functions to pull option chains, how to analyze the data for trading decisions, how to compare different methods of accessing NSE option chain data, and practical examples you can replicate in your own spreadsheet. Whether you trade Nifty weekly options or build longer-term Bank Nifty strategies, having option chain data in Excel is a foundational analytical capability.

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:

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: QM_GetOptionChain (Recommended)

The QM_GetOptionChain function returns the complete option chain for any supported symbol:

=QM_GetOptionChain("NIFTY")

This returns a comprehensive table including:

• All available expiration dates
• All available strike prices
• Call and put data for each strike
• Bid, ask, last price
• Open interest and volume
• Greeks (if available)

For Bank Nifty:

=QM_GetOptionChain("BANKNIFTY")

For individual Indian stocks (where options are available):

=QM_GetOptionChain("RELIANCE")

Method 2: Getting Prices for Specific Contracts

Once you identify a specific option contract from the chain, use QM_Last to get its current price:

=QM_Last("NIFTY_OPTION_SYMBOL")

Or use the general Last function:

=Last("NIFTY_OPTION_SYMBOL")

The exact symbol format depends on your data provider's convention for NSE options. Check the output of QM_GetOptionChain for the exact symbols used.

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:

1. Filter by expiration: Use Excel's filter function to show only the nearest weekly expiry or a specific monthly expiry
2. Sort by open interest: Identify the strikes with the highest activity
3. Add conditional formatting: Highlight high-OI strikes, unusual volume, or extreme IV levels
4. Calculate derived metrics: PCR ratios, max pain, IV percentiles

Analyzing NSE Option Chain Data in Excel

Open Interest Analysis

Open interest is the single most important data point in the NSE option chain for gauging market positioning.

Identifying Support and Resistance

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:

1. Pull the option chain using =QM_GetOptionChain("NIFTY")
2. For each strike price, calculate the total intrinsic value of all calls and puts that would be in-the-money
3. 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:

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

MarketXLS stands out because it combines the analytical flexibility of Excel with live data feeds, eliminating the manual data collection step that slows down most Indian options traders.

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

In cell A1:

=QM_GetOptionChain("NIFTY")

This populates a table with all available Nifty option contracts.

Step 2: Get the Current Nifty Level

=Last("NIFTY")

Or:

=QM_Last("NIFTY")

Step 3: Filter to Nearest Expiry

Use Excel's AutoFilter on the expiration date column to show only the nearest weekly or monthly expiry.

Step 4: Create a Summary View

Build a table that shows the most relevant data around the current Nifty level:

Values are populated from the QM_GetOptionChain output.

Step 5: Add Derived Metrics

• PCR per strike: =Put OI / Call OI for 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

=QM_GetOptionChain("NIFTY")
=QM_Last("NIFTY")

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 are the second most traded derivative on NSE. The analysis approach is identical:

=QM_GetOptionChain("BANKNIFTY")
=QM_Last("BANKNIFTY")

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:

=QM_GetOptionChain("RELIANCE")
=QM_GetOptionChain("TCS")
=QM_GetOptionChain("INFY")
=QM_GetOptionChain("HDFCBANK")

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:

Combining Option Chain Analysis with Technical Analysis

The most effective approach combines option chain data with price action:

1. Use open interest levels to identify key support and resistance zones
2. Confirm with technical indicators (moving averages, RSI, trendlines)
3. Use IV levels to determine whether to buy or sell premium
4. 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

• Weekly expiry (Thursday): Nifty and Bank Nifty have weekly expirations, creating rapid Theta decay in the final days
• 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 specific lot sizes (e.g., Nifty lot size is 25, Bank Nifty is 15)

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's =QM_GetOptionChain("NIFTY") function. This pulls the complete Nifty option chain directly into Excel without any manual data entry. The data includes all strikes, expirations, prices, open interest, and volume. Similar functions work for Bank Nifty and individual 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?

MarketXLS provides live and delayed data depending on your subscription and data feed. For active day trading, you may want to complement MarketXLS with your broker's real-time feed. For end-of-day analysis, swing trading, and position management, MarketXLS provides all the data you need.

How do I calculate Max Pain for Nifty options in Excel?

Pull the option chain using =QM_GetOptionChain("NIFTY"). 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 pulling the option chain data with =QM_GetOptionChain("NIFTY"), 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. Use =QM_GetOptionChain("BANKNIFTY") to pull the complete Bank Nifty option chain. The same analysis techniques — OI analysis, PCR, IV skew, Max Pain — apply to Bank Nifty options. Bank Nifty options are among the most liquid derivatives on NSE, providing robust data for analysis.

Start Analyzing NSE Option Chain Data in Excel

MarketXLS brings NSE option chain data directly into Excel, eliminating manual data collection and enabling sophisticated analysis. Use =QM_GetOptionChain() for complete option chains, =QM_Last() for individual contract prices, and Excel's built-in tools for custom dashboards, PCR calculations, OI tracking, and strategy analysis.

Explore MarketXLS pricing and plans → | Visit MarketXLS

Whether you trade Nifty weeklies, Bank Nifty strategies, or individual stock options on NSE, having option chain data in Excel is the foundation of data-driven trading.

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.