Excel VBA-Based Index Option Analysis System - 01/08/2026 15:02 EDT -- 2
Budget: ₹600 – ₹1,500 INR
Requirement 1 – Multi-Index Support
The system shall support multiple index option chains, including NIFTY and SENSEX. The user shall be able to select the desired index from the Excel dashboard before starting the analysis.
________________________________________
Requirement 2 – Automatic Data Download
Based on the selected index, the system shall automatically download the corresponding option chain data from the respective exchange source without requiring any changes to the VBA code.
________________________________________
Requirement 3 – Common Analysis Engine
The same analysis engine shall be used for all supported indices. The system shall automatically calculate the ATM strike, identify six strikes above and six strikes below the ATM, perform momentum calculations, determine option statuses, writer strength, support, resistance, market trend, and generate trading signals irrespective of the selected index.
________________________________________
Requirement 4 – Dynamic Strike Interval
The system shall automatically determine the appropriate strike interval for the selected index (for example, NIFTY or SENSEX) and calculate the ATM strike accordingly. No manual modification shall be required when switching between indices.
________________________________________
Requirement 5 – Historical Data
The system shall maintain separate historical data and analysis records for each selected index. Historical data for one index shall not overwrite or interfere with the records of another index.
________________________________________
Requirement 6 – Future Expansion
The system shall be designed using a modular architecture so that additional indices (such as BANKNIFTY, FINNIFTY, MIDCPNIFTY, BANKEX, or any other supported index) can be added in the future without major changes to the existing VBA code or workbook structure.
________________________________________
ஒரு Professional Requirement
The Excel VBA application shall be developed as a generic Index Option Chain Analysis System rather than a NIFTY-specific application. The system shall support multiple indices, including NIFTY and SENSEX, using a common analysis engine. All calculations, momentum analysis, status determination, support and resistance identification, writer analysis, historical data management, and dashboard generation shall be performed dynamically based on the selected index. The application architecture shall be modular and scalable to allow future integration of additional option chain indices without requiring significant code modifications.
NIFTY 50 OPTION CHAIN EXCEL ANALYSIS SYSTEM – SOFTWARE REQUIREMENTS
1. Objective
Develop an Excel VBA-based NIFTY Option Chain Analysis System that automatically downloads, stores, analyzes, and displays live option chain data every three minutes throughout the trading session. The system shall generate trading analytics, market strength indicators, and decision-support signals based on option chain data.
________________________________________
2. Data Download
At 9:15 AM, download the complete NIFTY Option Chain data from the NSE website.
The system shall retrieve all available strike prices for the selected expiry date.
The system shall not download only the ATM strikes.
The complete option chain shall be available for analysis.
________________________________________
3. Master Database
Create a worksheet named MASTER DATA.
This worksheet shall store all strike prices available in the option chain.
Every strike price shall have only one permanent row.
The following values shall be stored for every strike:
• Strike Price
• CE OI
• CE Change in OI
• CE Volume
• CE IV
• CE LTP
• CE Bid
• CE Ask
• PE OI
• PE Change in OI
• PE Volume
• PE IV
• PE LTP
• PE Bid
• PE Ask
• Spot Price
• Last Update Time
________________________________________
4. Previous Data
Create a worksheet named PREVIOUS DATA.
This worksheet shall always contain the previous refresh values for every strike price.
Previous data shall never be matched by row number.
Previous data shall always be matched by Strike Price.
Strike Price shall be treated as the unique key.
________________________________________
5. Live Refresh
Starting from 9:18 AM, the system shall automatically refresh every 3 minutes until 3:30 PM.
During every refresh, download the complete option chain again.
Update only the changed values.
________________________________________
6. ATM Detection
During every refresh, determine the latest ATM strike using the current Spot Price.
ATM Strike shall be calculated dynamically.
The ATM strike shall change automatically whenever the Spot Price changes.
________________________________________
7. Analysis Range
After identifying the ATM strike, select:
• ATM Strike
• Six strike prices above ATM
• Six strike prices below ATM
A total of 13 strike prices shall be analyzed.
________________________________________
8. Momentum Calculation
For every selected strike, compare the current data with the corresponding strike in the PREVIOUS DATA worksheet.
Calculate:
• CE OI Momentum
• PE OI Momentum
• CE LTP Momentum
• PE LTP Momentum
• CE Volume Momentum
• PE Volume Momentum
• CE IV Momentum
• PE IV Momentum
• Bid/Ask Ratio
________________________________________
9. Status Engine
Automatically determine:
CE Status
• Long Build-up
• Short Build-up
• Short Covering
• Long Unwinding
PE Status
• Long Build-up
• Short Build-up
• Short Covering
• Long Unwinding
________________________________________
10. Writer Analysis
Calculate
• CE Writer Strength
• PE Writer Strength
Classify
• Strong
• Medium
• Weak
________________________________________
11. Momentum Analysis
Calculate
• Bullish Momentum
• Bearish Momentum
• Neutral Momentum
________________________________________
12. Strength Score
Generate a numerical score from 0–100 for every strike.
The score shall be calculated using
• OI
• OI Change
• LTP Momentum
• Volume Momentum
• IV Momentum
• Bid/Ask Ratio
________________________________________
13. Support & Resistance
Automatically identify
• Strong Support
• Medium Support
• Weak Support
and
• Strong Resistance
• Medium Resistance
• Weak Resistance
________________________________________
14. Dashboard
Create a worksheet named DASHBOARD.
Display
• Spot Price
• ATM Strike
• Support Levels
• Resistance Levels
• Bull Score
• Bear Score
• CE Writer Strength
• PE Writer Strength
• Breakout Probability
• Breakdown Probability
• Market Trend
• Final Signal
________________________________________
15. Historical Analysis
Create a worksheet named STATUS HISTORY.
At every refresh, append the analysis results as new records.
Never overwrite previous records.
Store
• Time
• Strike
• CE Status
• PE Status
• CE Writer
• PE Writer
• Momentum
• Strength Score
from 9:18 AM until 3:30 PM.
________________________________________
16. Data Integrity
The system shall never compare data by worksheet row number.
All comparisons shall be performed using Strike Price as the unique identifier.
If the ATM strike changes during the trading session, the analysis shall continue without data loss.
Historical momentum calculations shall remain accurate even when the ATM shifts.
________________________________________
17. Performance
The workbook shall refresh every 3 minutes without freezing Excel.
Previous data shall always be preserved.
Historical records shall remain available throughout the trading session.
The system shall support multiple index option chains, including NIFTY and SENSEX. The user shall be able to select the desired index from the Excel dashboard before starting the analysis.
________________________________________
Requirement 2 – Automatic Data Download
Based on the selected index, the system shall automatically download the corresponding option chain data from the respective exchange source without requiring any changes to the VBA code.
________________________________________
Requirement 3 – Common Analysis Engine
The same analysis engine shall be used for all supported indices. The system shall automatically calculate the ATM strike, identify six strikes above and six strikes below the ATM, perform momentum calculations, determine option statuses, writer strength, support, resistance, market trend, and generate trading signals irrespective of the selected index.
________________________________________
Requirement 4 – Dynamic Strike Interval
The system shall automatically determine the appropriate strike interval for the selected index (for example, NIFTY or SENSEX) and calculate the ATM strike accordingly. No manual modification shall be required when switching between indices.
________________________________________
Requirement 5 – Historical Data
The system shall maintain separate historical data and analysis records for each selected index. Historical data for one index shall not overwrite or interfere with the records of another index.
________________________________________
Requirement 6 – Future Expansion
The system shall be designed using a modular architecture so that additional indices (such as BANKNIFTY, FINNIFTY, MIDCPNIFTY, BANKEX, or any other supported index) can be added in the future without major changes to the existing VBA code or workbook structure.
________________________________________
ஒரு Professional Requirement
The Excel VBA application shall be developed as a generic Index Option Chain Analysis System rather than a NIFTY-specific application. The system shall support multiple indices, including NIFTY and SENSEX, using a common analysis engine. All calculations, momentum analysis, status determination, support and resistance identification, writer analysis, historical data management, and dashboard generation shall be performed dynamically based on the selected index. The application architecture shall be modular and scalable to allow future integration of additional option chain indices without requiring significant code modifications.
NIFTY 50 OPTION CHAIN EXCEL ANALYSIS SYSTEM – SOFTWARE REQUIREMENTS
1. Objective
Develop an Excel VBA-based NIFTY Option Chain Analysis System that automatically downloads, stores, analyzes, and displays live option chain data every three minutes throughout the trading session. The system shall generate trading analytics, market strength indicators, and decision-support signals based on option chain data.
________________________________________
2. Data Download
At 9:15 AM, download the complete NIFTY Option Chain data from the NSE website.
The system shall retrieve all available strike prices for the selected expiry date.
The system shall not download only the ATM strikes.
The complete option chain shall be available for analysis.
________________________________________
3. Master Database
Create a worksheet named MASTER DATA.
This worksheet shall store all strike prices available in the option chain.
Every strike price shall have only one permanent row.
The following values shall be stored for every strike:
• Strike Price
• CE OI
• CE Change in OI
• CE Volume
• CE IV
• CE LTP
• CE Bid
• CE Ask
• PE OI
• PE Change in OI
• PE Volume
• PE IV
• PE LTP
• PE Bid
• PE Ask
• Spot Price
• Last Update Time
________________________________________
4. Previous Data
Create a worksheet named PREVIOUS DATA.
This worksheet shall always contain the previous refresh values for every strike price.
Previous data shall never be matched by row number.
Previous data shall always be matched by Strike Price.
Strike Price shall be treated as the unique key.
________________________________________
5. Live Refresh
Starting from 9:18 AM, the system shall automatically refresh every 3 minutes until 3:30 PM.
During every refresh, download the complete option chain again.
Update only the changed values.
________________________________________
6. ATM Detection
During every refresh, determine the latest ATM strike using the current Spot Price.
ATM Strike shall be calculated dynamically.
The ATM strike shall change automatically whenever the Spot Price changes.
________________________________________
7. Analysis Range
After identifying the ATM strike, select:
• ATM Strike
• Six strike prices above ATM
• Six strike prices below ATM
A total of 13 strike prices shall be analyzed.
________________________________________
8. Momentum Calculation
For every selected strike, compare the current data with the corresponding strike in the PREVIOUS DATA worksheet.
Calculate:
• CE OI Momentum
• PE OI Momentum
• CE LTP Momentum
• PE LTP Momentum
• CE Volume Momentum
• PE Volume Momentum
• CE IV Momentum
• PE IV Momentum
• Bid/Ask Ratio
________________________________________
9. Status Engine
Automatically determine:
CE Status
• Long Build-up
• Short Build-up
• Short Covering
• Long Unwinding
PE Status
• Long Build-up
• Short Build-up
• Short Covering
• Long Unwinding
________________________________________
10. Writer Analysis
Calculate
• CE Writer Strength
• PE Writer Strength
Classify
• Strong
• Medium
• Weak
________________________________________
11. Momentum Analysis
Calculate
• Bullish Momentum
• Bearish Momentum
• Neutral Momentum
________________________________________
12. Strength Score
Generate a numerical score from 0–100 for every strike.
The score shall be calculated using
• OI
• OI Change
• LTP Momentum
• Volume Momentum
• IV Momentum
• Bid/Ask Ratio
________________________________________
13. Support & Resistance
Automatically identify
• Strong Support
• Medium Support
• Weak Support
and
• Strong Resistance
• Medium Resistance
• Weak Resistance
________________________________________
14. Dashboard
Create a worksheet named DASHBOARD.
Display
• Spot Price
• ATM Strike
• Support Levels
• Resistance Levels
• Bull Score
• Bear Score
• CE Writer Strength
• PE Writer Strength
• Breakout Probability
• Breakdown Probability
• Market Trend
• Final Signal
________________________________________
15. Historical Analysis
Create a worksheet named STATUS HISTORY.
At every refresh, append the analysis results as new records.
Never overwrite previous records.
Store
• Time
• Strike
• CE Status
• PE Status
• CE Writer
• PE Writer
• Momentum
• Strength Score
from 9:18 AM until 3:30 PM.
________________________________________
16. Data Integrity
The system shall never compare data by worksheet row number.
All comparisons shall be performed using Strike Price as the unique identifier.
If the ATM strike changes during the trading session, the analysis shall continue without data loss.
Historical momentum calculations shall remain accurate even when the ATM shifts.
________________________________________
17. Performance
The workbook shall refresh every 3 minutes without freezing Excel.
Previous data shall always be preserved.
Historical records shall remain available throughout the trading session.