People who practice Options trading know very well how important ‘Option Greeks’ are. As per Investopedia, Trading options without an understanding of the Greeks – the essential risk measures and profit/loss guideposts in options strategies – is synonymous to flying a plane without the ability to read instruments. Option Greeks, denoted by certain Greek alphabets, are the parameters that determine how Option price varies with the change in external factors like time, volatility, and underlying stock Price.
These Greeks are calculated based on the Black and Scholes options pricing model, which was first published by Fisher Black and Myron Scholes (hence the name Black & Scholes) in 1973. In this post, we’ll go through an Option Greeks Calculator which updates real-time and calculate Greek values for all the strike prices of options traded in NSE.
Options Greeks definition
Image Source: http://optionspedia.blogspot.in/2012/10/option-greeks.html
- Delta – Measures the exposure of option price to the movement of underlying stock price
- Gamma – Measures the exposure of the option delta to the movement of the underlying stock price
- Theta – Measures the exposure of the option price to the passage of time
- Vega – Measures the exposure of the option price to changes in the volatility of the underlying
- Rho– Measures the exposure of the option price to changes in interest rate
Option Greeks Calculator: Excel Sheet overview
| Parameter | Description | |
| Worksheet Name | Greeks | |
| Inputs | Symbol (Cell J8) | Symbol of the stock or index from NSE |
| Symbol Type (Cell L8) | Type of symbol; Index or Stock | |
| Expiry Date (Cell O7) | Expiration date of Option series. Pick the value from drop down | |
| Risk free interest rate (Cell O8) | This is risk free rate prevailing in the economy. Use the RBI 91 day Treasury bill rate for this purpose. You can get the rate from the RBI website, RBI has made it available on their landing page here. | |
| Dividend Yield (Cell O9) | This is the dividend per share expected in the stock, provided the stock goes ex dividend within the expiry period. | |
| Outputs | Cells B11:R29 | Option Greeks values for each strike price. |
New Updates – 1st May 2020
We have rolled out some updates to the sheet based on requests from several users. See below the details:
- Added a feature to select any stock or index from NSE. Based on your selection the data would get updated automatically.
- Added a feature to select the expiry date from the drop-down provided.
- The sheet now connects to the new NSE website.
- Programmatic scraping of data from NSE is blocked natively. As a workaround, added the cookie parameter in the sheet to allow this.
Option Greeks Calculator: Screenshot
How to use this Excel Sheet?
Below are the simple steps to use Option Greeks Calculator excel sheet. There is very little manual intervention required for this.
Step 1: Open this Excel file and make sure you are connected to internet. Please accept if it asks to enable Macros and Data connections.
Step 2: You’ll also see some prompts to set the privacy level for the NSE India website when you open this excel for the first time. Just set it to “public” as per the below screenshots:
Step 3: Input the required fields, Symbol, Symbol Type, Expiry Date, Risk free interest rate and Dividend yield.
Step 4: The Greek values would automatically get updated. The sheet refreshes every five minutes.
Step 5: In case the data doesn’t refresh properly OR it gets timed out, please follow the instructions in the next section to set up the cookie value.
Setup Cookie Value (Optional)
There is a possibility that this excel sheet won’t fetch data for you automatically because of web scraping restrictions imposed on the new NSE website.
Don’t worry about it, we do have a workaround for you in case that happens. You’ll need to head over to the Cookie worksheet and paste the cookie value from your browser in the cell A2.
See the instructions below to fetch the value of the cookie in Chrome and Firefox browsers:
Google Chrome
Step 1: Open this URL from your browser
Step 2: Press CTRL + SHIFT + I to open Inspect window OR right-click anywhere on the web page and click on “Inspect”
Step 3: Navigate to Network menu from the Inspect window and press CTRL + R to reload the page
Step 4: Once the page is reloaded click on Option Chain.
Step 5: Select “option-chain-indices?symbol=NIFTY” from the list. Go to headers and copy the value of bm_sv located in Response Headers –> set-cookie
Step 6: Paste the copied value in cell A2 of cookie worksheet.
Check out the below video illustration:
Mozilla Firefox
Step 1: Open this URL from your browser
Step 2: Right-click anywhere on the web page and click on “Inspect Element”
Step 3: Navigate to Network menu from the Inspect window
Step 4: Click on Option Chain from the webpage.
Step 5: Select “option-chain-indices?symbol=NIFTY” from the list. Go to Cookies and copy the value of bm_sv
Step 6: Paste the copied value in cell A2 of cookie worksheet.
Check out the below video illustration:
Data Refresh Frequency
The data in the sheet gets refreshed automatically when:
- You open the sheet
- Click on Data –> Refresh All
- Every 5 minutes (as specified in the connection properties)
You may change the frequency as per your convenience from the connection properties.
Download Link
Enter you Email address below and get the sheet in your mailbox. Alternatively, you can use this link.
Let us know in comments section if you have any queries.
Common Errors and Troubleshooting
Error Message
Query ‘option-chain-nse-new’ (step ‘AutoRemovedColumns1’) is accessing data sources that have privacy levels that cannot be used together. Please rebuild this data combination.
Troubleshooting Steps:
- You should set the privacy levels to “public” when you accessed the sheet for the first time. This step is described in “How to use this Excel Sheet –> Step 2” above. In case you missed to set it to public, you can modify the privacy settings from Data –> Get Data –> Data Source Settings
- If the above step doesn’t work, go to Data –> Get Data –> Query Options. Select ‘Privacy’ option in the left and click on “Ignore the privacy levels and potentially improve performance”.
Error Message
[DataSource.Error] The request was aborted: Could not create SSL/TLS secure channel Microsoft excel 2016Troubleshooting Steps:
- If you are using Excel 2016, then the OS should be Windows 10 or above. Otherwise, this error may pop up.
- This error happens due to the TLS version mismatch. Check this link for a possible solution that may need a modification to the Windows registry.
- Few of the users were able to resolve the error by re-installing MS office
Your doing a great job …..your posts are highly informative. Every post is making me to learn new stuff.Thank you for your great work
Thank you Manikandan!
Can you please include the strikes at 50’s also, and do you have a similar sheet for Bank nifty
Hi Sir,
To be frank you are doing a great job . i never used to comment but the way you are sharing the knowledge is Awesome. Thanks buddy .
Regards,
Arunraj Nadar
Thanks for your kind words Arun!
Hello Admin,What is difference between NSE Call LTP and CallPrice.
Could you please let us know.
Great Great job.
All are giving real time thats great. If you add one 2 more feature if possible one is Greek atleast historcial last 10 days atleast on closing basis atleast with graph. That helps user to understand and use it proper basis. And also strategies expected loss and profit. Thanz
Can it be possible to get AFL for greeks in amibroker ?It will be of great help.
Hi Manish,
We’ll try to post it going forward. Stay tuned!
Hi … I am new to Options Trading : Downloaded Excel sheet .. based on this shall we buy (Put/Call) !!! if yes kindly explain how to proceed , thank you , God Bless You and Your Family ….
sir can u help me to use it. where to buy and sell . i dont know much about it?. plz help thanks and waiting for ur kind reply
Hi Ruchi,
This is not an automated Buy/Sell excel sheet. However, it helps in your trade decisions for options. Please read about Option Greeks in details.
dear sir your posts are amazing
i regularly visit your website , thx for helping newbies like me
in your option greeks pls add bank nifty if possible
or please teach us how to add bank nifty .
greatfull to you for all your posts
Hi sir,
I am new to trading, I want to develop more technical knowledge in this area. Can you please share a material which have basic concepts and step by step flow of concepts it will really help beginners. Please kindly drop a mail.
Thanks!
Hi Murali,
You would definitely like to look at the below article:
https://tradingtuitions.com/how-and-where-to-learn-about-stock-market/
Thank you so much sir
Is there a way to force a refresh a data load? On my system after the first load of data I don’t get a refresh
Hi Vohi,
In the excel sheet, go to Data tab in the top and click ‘Refresh All’.
hi sir , i have downloaded the excel sheet for option greek and none of the columns show correct values or sometimes it does not show anything in the delta vega rho theta columns pls help!
Dear Admin,
Thank you for such a wonder excel file on options. It helps in making descisions; can you please let us know what thie difference between NSE CALL LTP and Call Price?
Thank you for you quick response!
do we have a way to get the Bank Nifty data into excel?
Sir.Can you provide download link of greeks for bank nifty please?
Dear Admin,
I have developed Bank Nifty Option Greeks,its under trail , soon
I will post here. Thank you.
Hi Raju,
Thanks a ton for your continuous contribution. Looking forward for it.
Hello Admin,
Thanks for the fantastic work ,It will help a lot to adjust position during delta neutral.Can you please post same for Bank nifty ?and also please explain how to do it for any script / Index.
Well done friend! Undoubtedly this is cool and very helpful to option traders!
Best wishes!
I WANT TO DOWNLOAD
what to enter dividend value for nifty call put greeks.
Hi Pankaj,
It should be 0 for Nifty.
Your work is excellent!!! Is it possible to see the greeks of next month options in this excel? Also please check why ITM Call option greeks are not being calculated. Thanks and keep up the good work please….
Excellent Share, Thanks a lot. May I request the same sheet for Bank Nifty?
Excellent Share, Thanks a lot. May I request the same sheet for Bank Nifty?
Hi Namit,
We would share the similar sheet for Banknifty ASAP.
Thanks a lot. Waiting for the share!
Hi Can this sheet be used for other stocks like YesBank etc?
Sir, I some how feel the Gamma column in Option greek is eronous , it I think does not give the right figure . it is same for all the strike or r zero , pl check
HI , The theoretical value and the actual price difference is huge . How do i get the exact calculation ? . and addition to this is there any formula to calculate the implied volatility for a particular strike with out (Actual Option Price ) ? .
Kindly suggest
Kindly suggest .
Hi,
I try to entry the today date and it does not work.
Hi ,
Which pricing model do you use to calculate the greeks ? Kindly reply so that i can study more on those models .
Calculation is based on Black–Scholes model
Thank you first of all , in BS model we need the following variables in order to get the price of the option premium ( Ie Spot Price , Strike Price, No Days to expiry , Implied Volatility , interest rate and dividend) .
My Question here is : How NSE Option Chain publish the IV ( Is there any mechanism available to Predict the IV and if possible kindly let me know the Calculation Procedure for the same )
2nd Question is : Lets say nifty spot price is 9940 and for Call option strike 10000 the IV is 7.5 % as per the NSE option chain , in case if the nifty spot price is 9970 what would be the IV for the strike of 10000 for th e same expiry cycle .
Thank you for all your efforts !
Thank you first of all , in BS model we need the following variables in order to get the price of the option premium ( Ie Spot Price , Strike Price, No Days to expiry , Implied Volatility , interest rate and dividend) .
My Question here is : How NSE Option Chain publish the IV ( Is there any mechanism available to Predict the IV and if possible kindly let me know the Calculation Procedure for the same )
2nd Question is : Lets say nifty spot price is 9940 and for Call option strike 10000 the IV is 7.5 % as per the NSE option chain , in case if the nifty spot price is 9970 what would be the IV for the strike of 10000 for th e same expiry cycle .
Thank you for all your efforts !
Hi,
in “data” sheet IV values are not getting filled due to which greeks are not getting calculated in the main sheet. Please help.
Problem is resolved. Thanks.
Glad to know Sachin 🙂
Hi,
This is great work. Is it possible for you to enable the VBA code so that we can customize it. If not, would it be possible for you to make following changes,
1. Allow the user to enter the symbol and display the greeks for specified symbol.
2. Not all the strikes are available. Strikes only in multiple of 100 are displayed. Can we make it multiple of 50?
It would be of really great help.
Thanks,
Rakesh
This website gives lots of info. However, the excel sheet is a bit old and does not update even after refresh. Kindly check the same and upload corrected one. Thanks.
Hi Raj,
Its working fine at my end. Please update “28-12-2017” in the expiry data column and let me know if you face the same issue.
Hi The options greek sheet is not working. The Data Sheet is not there, can u please email me or upload.
Hi Bharat,
Please update expiry date on the sheet. For ex: change it to 25-01-2018 for Jan expiry.
Hi Bharat,
It works perfectly at my end. Can you please send the screenshot of the error at support@tradingtuitions.com
Just Like Nifty “Greek Calculator” Is it available for Bank nifty & stock Option “Greek Calculator”
Dear TradingTutions Admin,
Nice job for posting good articles. Is there a workbook for BankNifty weekly options as well. If yes the please share the link.
Thanks in advance and continue to post good articles on this website. Raj
Admin, Thanks for the excel sheets. can you please provide with stock option greek calculator similar to nifty option green calculator. Thanks a ton
Congrats on doing such a fine job at TradingTuitions.
And Thanks.
Request share a Greek sheet for Bank Nifty.
In Nifty strike prices are available in multiples of 50, whereas in your excel sheet it is in multiples of 100. Why this difference.
Hello sir
how can we find risk free interest rate and dividend yield for nifty
Hi Raju,
Dividend yield for Nifty is 0, and risk free interest rate can be found at below link:
https://www.rbi.org.in/
Dear Admin,
From where we will get the dividend yield?
Hi Vinay,
Dividend yield is 0 for Nifty
not update sir
after enabling error value coming please guide
Hi Vimal,
Have you changed the expiry date?
Hello Admin,
Great work with the excel sheet. It is very useful in understanding options and taking trade positions. I have modified the sheet to include calculate the greeks for NSE stock options. I have combined OI and Greeks analysis for stock options on NSE. The expiry date is auto calculated as the last thursday of current month. The stock option to be analysed must be selected on first sheet from drop down list. Some stock futures have been delisted from NSE recently. I haven’t updated those changes. You can find the sheet at: https://drive.google.com/open?id=1GY2j8fCPf1EnG1N_bxwezeHurf6ZWV0J
Please give comments and suggestions. Thank you.
Great Tushar! We will review and share our comments soon.
Thanks. Is there any way to fetch stock dividend yield and ex-dividend date? This can be incorporated in the sheet above.
hi sir how to select symbol not change so pls help me…
Hi Admin, ‘
The excel sheet shows Greek values only from 9700-10300 . for other strikes error is shown. Can you please correct this.
Hi Sanjay,
Thanks for pointing this out. Please download the updated sheet from the below link:
https://tradingtuitions.com/nifty-option-greeks-calculator-live-excel-sheet/
Hello Admin, This looks exciting Can you make greeks sheet for for Bank Nifty as well Thanks
I feel the options calculator has bug in it. Please check.
When you open the spreadsheet it downloads data and does the calculation for theoretical price for all contracts. today market ended @ 10527 and all the calculations of theoretical price are correct. suppose if i want to check what would be the theoretical prices if market ended @ 10700, then it doesnt provide the correct information, rather it provides the theoretical price of market when it was @ 10527.
Note: I changed the current market price from 10527 to 10700 to check the theretical price of contracts when market is @ 10700
You may please ignore my previous comment. I found the issue. its from my end. sorry for the inconvenience.
Please post the same kind of spreadsheet for banknifty as well. please.
Do you have any option calculation for expiry day . Current the option calculatior considers number of days to expiry in the calculation. is there anything for expiry day pricing calculation
thanks
Hi. Is Banknifty options excel sheets available. If so please help to share the link
hi sir
iam unable to open option greeks file getting message file is corrupted
you r too goog, great work . keep it up
You are doing a great job….
New Updates – 1st May 2020
We have rolled out some updates to the sheet based on requests from several users. See below the details:
1. Added a feature to select any stock or index from NSE. Based on your selection the data would get updated automatically.
2. Added a feature to select the expiry date from the drop-down provided.
3. The sheet now connects to the new NSE website.
4. Programmatic scraping of data from NSE is blocked natively. As a workaround, added the cookie parameter in the sheet to allow this.
Getting the bellow error:- Please help to resolve this.
Formula.Firewall: Query ‘option-chain-nse-new’ (step ‘Added Custom1’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
Hi Dennis,
Something to do with your firewall or antivirus. Which version of office you are using?
data source failed what should i do?
Hi Shridhar,
What version of excel you are using? The sheet works best with 2016 and above versions
I am not able to find the excel sheet.! Please help me find the excel sheet!!
You need to leave your email address at the end of the post to get the link to excel sheet
Getting the bellow error:- Please help to resolve this.
Query ‘ExpiryDateList’ (Step ‘AutoRemovedColumns1’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
how can i get the nifty option greek calculator excel sheet?
Hi Ajay,
You can download it from the end of this post.
Sir unable to start the excel, even updating the cookies by following the above steps
Hi I am getting the following error what is the cause and how do i fix it?
Query ‘option-chain-nse-new’ (step ‘AutoRemovedColumns1’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
Hi Rajesh,
Added a new section in the article called “Common Errors and Troubleshooting”. Please check it for the possible resolution of this error.
PLease send me the excel sheet
Hi Admin Thanks for the Great Job for Greeks sheets. But Now I m Traying the sheet to pull up Raw data from my Broker terminal through execl, (since NSE site has delay in data). it pulled up LTP, IV, expirary, strike price data in realtime for both CE and PE. but why calculation wrong? and some strike price are not showing greeks? Please Help me if You have any Idea to make it possible.
what settings need to take care if we are using excel 2010.pls suggest
Hello Admin,
I have requested for excel or spread sheet (Option Greeks Calculator) by downloading with email address, so far not yet received , even i have checked spam folder also Please check the your systematic.
This is amazing..!! Thank you
Thanks for your kind words, Venkat!
Hey Guys! You are doing great work.
Can you please tell me how to download this Excel .
I need some help. After I downloaded this excel. On opening it asked for the write access. Immediately excel crashed. After that my excel is not working property.