How to Install Python on Windows 10

This tutorial guides you through how to fetch option chain data from NSE using Excel VBA only. With automating the retrieval of live option chain data from the National Stock Exchange (NSE) directly into Microsoft Excel using VBA (Visual Basic for Applications) you can use this versatile tool (Excel) to analyze huge amount of data and make strategy for trading.

Prerequisites

• Installed Microsoft Excel
• Basic understanding of Excel and VBA
• Internet connectivity

Step 1: Enable Developer Tab in Excel

1. Open Excel.
2. Go to File > Options.
3. Select Customize Ribbon.
4. Check the box for Developer in the right pane.
5. Click OK.

Step 2: Insert a New Module in VBA Editor

1. Click on the Developer tab.
2. Click on Visual Basic to open the VBA editor.
3. In the VBA editor, go to Insert > Module.
4. A new module window will appear for code entry.

Step 3: Write VBA Code to Fetch Data

In the new module, paste the following VBA code:

Step 4: Run the VBA Macro

1. Close the VBA editor to return to Excel.
2. Press Alt + F8 to open the Macro dialog box.
3. Select fetch_oc_json_data from the list.
4. Click Run.

The macro will fetch the latest option chain data for NIFTY from the NSE website and populate it into Sheet1 with columns for Strike Price, Call Open Interest (OI), and Put Open Interest (OI).

Optional: Automate Data Refresh

To refresh the data at regular intervals:

In the VBA editor, add the following code to the ThisWorkbook module:

Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:05:00"), "fetch_oc_json_data"
End Sub

This code schedules the fetch_oc_json_data macro to run every 5 minutes after opening the workbook.

Important Notes

1. Ensure that your system’s security settings allow macros to run.
2. The NSE website may have restrictions or may change its API endpoints; always check for the latest access methods.
3. Use this tool responsibly and in compliance with NSE’s terms of service.

Conclusion

By following this tutorial, you can automate the process of retrieving live option chain data from NSE into Excel, facilitating real-time data analysis and decision-making. Next, you can Learn How to Fetch Index Future Data from NSE. You can visit my YouTube Channel for more informative videos.