What is a Bhavcopy?

It is the End of Day data of Derivatives traded on the Indian Exchange NSE which can be used to predict the future movement of stocks as well as indices.
Data which is provided in the Bhavcopy includes the following:

• Instrument – Future Index, Future Stock, Option Index, Option Stocks, Future VIX
• Symbol – Index and Stock names
• Expiry Date – Mostly 3 consecutive months expiry dates but have yearly expiry dates for some nifty options
• Strike Price – 0 (for futures) and for options as per symbol/price
• Option type – XX (for futures) and CE and PE for call and put
• Open, High Low, Close – End of day prices (daily candles)
• Settle price – Settlement Price for that day
• Contracts – Volume of derivatives contracts that was traded during that particular day
• Value (in lakhs) – Derivatives turnover that was traded during that day ( Contracts * Lot Size * Close Price)
• Open interest – Number of open derivatives positions for that expiry (irrespective of long or short)
• Change in open Interest – change in OI value from previous day
• Timestamp – Date

From where can we download the bhavcopy?

NSE f&o bhavcopy

What is the importance of a pivot table in Excel for data analyses?

• It helps you to filter, sort, group data
• You can use columns as rows and rows as columns (Pivoting). You can just play around with data very easily
• It can be linked to extended sheets for Data or other excel workbooks
• It can compress large amount of data to a summary
• It allows quick updates

We will be discussing 3 types of pivot tables:

Case 1: Open Interest, Change in Open Interest and Close Price Analysis

Case 2: Rollover Analysis

Case3: Option Chain Analysis

Steps to make a pivot table for Case1:

1. Go to : Insert => Pivot Table => Click Ok

2. Playing with Rows & Columns

• Instruments, Symbol, Expiry Date in Filters
• Strike Price, Timestamp as axis (Rows)
• Option type as Legend (Columns)
• OI, Change in OI, Close as values (Make the values max and not sum)

3. Charting inside a pivot table

• Go to Pivot Table Tools => Go to Analyze => Select Pivot Chart => Select Combo => Click on Secondary axis for Close Prices and make the chart type for close prices to be line chart and clustered column chart for OI
• You can also change the Style and Color of the chart

4. Select XX in the option type filters

Steps to make a pivot table for Case2:

1. Go to : Insert => Pivot Table => Click Ok
2. Playing with Rows & Columns

• Instruments, Symbol in Filters
• Strike Price, Timestamp as axis (Rows)
• Expiry Date , Option type as Legend (Columns)
• Change in OI as values (Make the values max and not sum)

3. Charting inside a pivot table

• Go to Pivot Table Tools => Go to Analyze => Select Pivot Chart => Select Column Chart
• You can also change the Style and Color of the chart

4. Select XX in the option type filters

Steps to make a pivot table for Case3:

1. Go to : Insert => Pivot Table => Click Ok
2. Playing with Rows & Columns

• Instruments, Symbol, Expiry Date in Filters
• Timestamp, Strike Price as axis (Rows)
• Option type as Legend (Columns)
• OI as values (Make the values max and not sum)

3. Charting inside a pivot table

• Go to Pivot Table Tools => Go to Analyze => Select Pivot Chart => Select Column Chart
• You can also change the Style and Color of the chart

4. Select CE and PE in the option type filters

Some additional pointers for Data Analysis with Pivot Table

• Slicer
  • Go to Pivot Table Tools Tab => Analyze => Click on Insert Slicer in the Filter Section
• Timeline
  • Go to Pivot Table Tools Tab => Analyze => Click on Insert Timeline in the Filter Section
• Refresh – Do this when you add more data to your existing data source
  • Go to Pivot Table Tools Tab => Analyze => Click on Refresh in the Data Section
• Change Data Source – We can use the pivot table in this excel with the data in another excel sheet
  • Go to Pivot Table Tools Tab => Analyze => Click on Change Data Source in the Data Section
  • Do select the proper data rows and column range, else the pivot table will not show the unselected data. So avoid this error and this will save your time finding the error

Important Note:

• I have made the sample pivot table in MS Office 2013.
• So some excel options may be unavailable for you if you use a lower version of excel. But I think Excel 2010 and Excel 2013 should be fine. Try to avoid Excel 2007.

You can download the pivot table template here