For all those who have been searching for a reliable "Nifty Option Pain Calculator", the search is finally over. Feel free to download this excel sheet showing calculation based on live data from NSE website. Link below:
I will explain the calculation in excel sheet in detail in some time. As I can see from the comments that visitors are finding it hard to comprehend the formula's used in excel sheet. Meanwhile, feel free to post your queries in comment below so that it can help me better understand what parts are difficult to understand and where to add more clarity.
https://www.dropbox.com/s/2z4d8estdptjsxw/Option_Pain_Calc_May_2016.xlsx?dl=0
For data source, it is as usual NSE website. Refer to screenshot below so that it is easy for you to locate. It can get very confusiong, especially when you are short on time and need this instantly.
As can be seen from the current position, minimum pain is at 7700 level followed by 7800 level. So the current positions indicate that if things do not change then market will close between 7700 & 7800 level on F&O expiry day. Breakout is not expected at this moment (at least till expiry on 26 May 2016), need to track week by week if there is any change in the position and how long this remains true.
All you need to do is to paste the latest data for the day downloaded from NSE site. Chart will refresh automatically to display the least painful Nifty closing level on settlement day (expected). You can verify from historical data and paper trading that indeed, Nifty closes at least painful level on futures and options expiry day which is the last working Thursday of any month.
Also known as "Option Pain", "Max Pain".
I will explain the calculation in excel sheet in detail in some time. As I can see from the comments that visitors are finding it hard to comprehend the formula's used in excel sheet. Meanwhile, feel free to post your queries in comment below so that it can help me better understand what parts are difficult to understand and where to add more clarity.
Update: 8 May 2016
It's been a while since I have posted something. For those who need another working example, I have posted this one for the month of May 2016 (link to excel sheet below);
For data source, it is as usual NSE website. Refer to screenshot below so that it is easy for you to locate. It can get very confusiong, especially when you are short on time and need this instantly.
Disclaimer: We do not guarantee success by arriving at decisions based alone on the use of this tool. Use this at your own risk.
