How to find combined Open Interest from Bhavcopy - Part 1

Hello Guys, 

Finally after four years, I managed to post something on my blog. 

Couple of years back, I made a video on How to interpret Derivatives Data (Open Interest) properly ? (Link : https://www.youtube.com/watch?v=mC6_PULcITE&t=400s)

I’m overwhelmed to see so many positive comments on my video. I also noticed one thing that many people asked for the excel file in the comment. From last couple of weeks, many fellow traders (on Twitter) also requested to share some knowledge on Open interest and how to find combined open interest.

Since it’s a regular process, sharing the excel file won’t solve the purpose as everyday we need to update data.

So, I’m sharing the process – How to find combined open interest using Bhavcopy.

Before that, one must keep few things in mind.

• Derivatives data analysis is very subjective and your interpretation may differ with others.
• Here, we are talking about OI of Futures, not options.
• EOD OI data is more important than intraday data.
• Here, we will discuss about EOD data only
• It’s a regular process and you need to maintain data on daily basis.
• With continuous practice, you can find the overall positions in the stock/index (whether its long or short).
• It works best along with price action. 

Now, follow all the steps to find combined OI from bhavcopy.

Steps :

Download Bhavcopy from NSE website (Link : https://www1.nseindia.com/products/content/derivatives/equities/homepage_fo.htm) 

After downloading, open the folder and then file. You will find below excel file.

In this excel file, you can see Instrument (Index Fut, Stock Fut, Index Options and Stock Options), Symbols, Expiry date, Option Type (XX for Futures, PE for Put and CE for Call), Open, High, Low, Close, Settlement Price, Contracts Traded, Value in Lakhs, Open Interest (OI), Change in OI and Time (date).

Now, we need to combine the OI of future contracts. So, use filter tool, which is available in Data (shortcut: Shift + Ctrl + L) and filter only ‘XX’ in Option Type (in E column).

After that, Go to Data and then Click on Subtotal.

A box (Subtotal) will pop-up on your excel.

In first option - At each change in:  Select Symbol

Second Option - Use Function: Select Sum

Third Option – Tick on OPEN_INT and CHG_IN_OI

Below that, tick on ‘Replace current subtotals’ and ‘Summary below data’

Then click on OK

After that, you will see subtotal of each symbol calculation in you excel.

Now, we can to make this data properly understandable. Copy the entire data on this sheet and paste special (Shortcut: Alt ESV) values in new sheet