Excel VBA-Based Index Option Analysis System - 01/08/2026 15:02 EDT -- 2

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.

Project ID: 40619837