Widget HTML Atas

Home / Option Greek Calculator Free Download

Option Greek Calculator Free Download

Post a Comment

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

option-greek

Image Source: http://optionspedia.blogspot.in/2012/10/option-greeks.html

  1. Delta – Measures the exposure of option price to the movement of underlying stock price
  2. Gamma – Measures the exposure of the option delta to the movement of the underlying stock price
  3. Theta – Measures the exposure of the option price to the passage of time
  4. Vega – Measures the exposure of the option price to changes in the volatility of the underlying
  5. 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:

  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.

Option Greeks Calculator: Screenshot

Option Greeks Calculator

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:

Open Interest Analysis Excel- Privacy Settings 1

Open Interest Analysis Excel- Privacy Settings 2

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.

Set Cookie Value

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:

  1. You open the sheet
  2. Click on Data –> Refresh All
  3. 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:

  1. 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
  2. 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".

Privacy Levels

Error Message

[DataSource.Error] The request was aborted: Could not create SSL/TLS secure channel Microsoft excel 2016

Troubleshooting Steps:

  1. If you are using Excel 2016, then the OS should be Windows 10 or above. Otherwise, this error may pop up.
  2. 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.
  3. Few of the users were able to resolve the error by re-installing MS office

Spreadsheets for Success

Related Posts

Source: https://tradingtuitions.com/nifty-option-greeks-calculator-live-excel-sheet/

Posted by: courtneylabombare0191227.blogspot.com