How to find combined Open Interest from Bhavcopy - Part 2

We discussed half process in previous post (http://jay-purohit.blogspot.com/2020/07/how-to-find-combined-open-interest-from_12.html)

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

You will see below data in your excel :

Now use filter option from Data (shortcut: Shift + Ctrl + L).

In Instrument (A column), select the ‘Blank’ only.

Once you do this, you will get the Combined OI data of all three series.

Now, just delete the columns C to L.

Copy B to D column and paste special values (Alt CSV) in New Sheet :

If you want to calculate the % change in OI for that particular data, then apply this formula :

% Change = [ Change in OI / (Total OI - Change in OI) ] * 100

% Change = C/(B-C) and then click on % in Home

Now, you are done with the Combined OI of stocks and indices. Save this file with whatever file name you want. Maintain this data everyday and compare % Change in OI with % Change in Price.

Make a Derivatives Data of 10th July on you own and cross check with the above attached image. If data matches, then you’re on right path. If it doesn’t matches then again read the process properly and make the file.

Note : I only compare current series future Price along with Combined OI for interpretation.

If you miss to make file on any particular day, you can get the historical Bhavcopy in NSE website. So, Pls maintain data of each day and keep on analysing.

Would appreciate your feedback on the same.

For any query or more learning on derivatives data, you can write us to derivatives17@gmail.com

Thanks & Regards,

Jay Purohit