Excel Plugin VSTO for Nse Bhavcopy , Delivery Report, Option Chain

Hi,

I have came up with an excel plugin to download and display bhavcopy for Cash & FO, Delivery report for Cashe and Option Chain display in excel via a sigle button click Plugin

You can download this plugin from here

You can install the plugin from the above source. Once you download the zip file extract to a folder
and click on setup it will complete the installation.

A video having more explanation of how to use it.

After installation open excel you will be able to see an menu option named "M2 Plugin" as show in the below image.

You can click on below mentioned options for getting relevant data.
 1) Get Option Chain

It will display and Form for specifying the url for Nse option chain.  By default it will take current expiry option chain of nifty.

Once you click on ok it will parse the Nse option chain and display in the excel as show in below image.
It will also calcuate max pain, PCR and highest OI at which call & put strike is available.

2) Get Bhav Copy CM
 On click of this button it will display an form which will ask for the date for which bhavcopy is to be downloaded. On selection of correct date it will dowload the bhav copy and display it in excel.
Do remeber for current date bhavcopy is uploaded by exchange after 4:30 PM but many a time they delay in uploading the bhavcopy.




3) Get Bhav Copy FO
 On click of this button it will display an form which will ask for the date for which bhavcopy is to be downloaded. On selection of correct date it will dowload the bhav copy for Futures and display it in excel. It will take a minute or show once it starts displaing in excel as data will be huge.

4) Delivery Report CM
On Click of this button it will display an form which will ask for the date for which delivery report is to be downloaded. On selection of correct date it will download the delivery report and display it in excel.

NSE Option chain parsing in C#

 This is a very basic tutorial on displaying how to parse nse option chain in C# using html agility  utility.

If you guys are not aware about nse option chain. It is a table structure displaying details about call and put on NSE Index or any NSE FNO stock.

Its most important component is OI and IV for particualr Option. along with Option Price.
it helps you in identifying your trade stratagy either you have a mean reverting stratagy or a trend folow stratagy. It will help you in identifying it and also help you in predicting stoploss levels.

Through option chain two most calculations people derive is PCR ratio and Max Pain.

PCR Ratio is a ratio of call to put postitions in the market.
Max Pain is the strike price at which sellers or call and put will have the most impact.



So now going to Parsing it with Html Agility pack and displaying it on a Data grid.

  HttpClientHandler handler = new HttpClientHandler();
            handler.AutomaticDecompression = System.Net.DecompressionMethods.GZip | DecompressionMethods.Deflate;

            System.Net.Http.HttpClient client = new System.Net.Http.HttpClient(handler);

            System.Net.Http.HttpRequestMessage requestMessage = new System.Net.Http.HttpRequestMessage();
            requestMessage.RequestUri = new Uri(url);

            requestMessage.Headers.TryAddWithoutValidation("Accept-Encoding", "gzip, deflate, br");

 var responseMessage = client.SendAsync(requestMessage).Result;

                if (responseMessage.IsSuccessStatusCode)
                {
                    var responsetext = responseMessage.Content.ReadAsStringAsync().Result;
                    HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument();
                    document.LoadHtml(responsetext);
                    List<OptionChain> optionChains = new List<OptionChain>();
                    string message = "";

                    HtmlNodeCollection rows = document.DocumentNode.SelectNodes("//table[@id='octable']/tr");

                    for (int i = 0; i < rows.Count - 1; i++)
                    {
                        HtmlNodeCollection columns = rows[i].SelectNodes("td");

                        //for (int j = i; j < columns.Count - 1; j++)
                        {
                            OptionChain chain = new OptionChain();
                            double value = 0;
                            Double.TryParse(columns[1].InnerText, out value);
                            chain.Call_OI = value;
                            value = 0;
                            Double.TryParse(columns[2].InnerText, out value);
                            chain.CallChginOI = value;
                            value = 0;
                            Double.TryParse(columns[3].InnerText, out value);
                            chain.CallVolume = value;
                            value = 0;
                            Double.TryParse(columns[4].InnerText, out value);
                            chain.CallIV = value;
                            value = 0;
                            Double.TryParse(columns[5].InnerText, out value);
                            chain.CallLTP = value;
                            value = 0;
                            Double.TryParse(columns[6].InnerText, out value);
                            chain.CallNetChg = value;
                            value = 0;
                            Double.TryParse(columns[7].InnerText, out value);
                            chain.CallBidQty = value;
                            value = 0;
                            Double.TryParse(columns[8].InnerText, out value);
                            chain.CallBidPrice = value;
                            value = 0;
                            Double.TryParse(columns[9].InnerText, out value);
                            chain.CallAskPrice = value;
                            value = 0;
                            Double.TryParse(columns[10].InnerText, out value);
                            chain.CallAskQty = value;
                            value = 0;
                            Double.TryParse(columns[11].InnerText, out value);
                            chain.StrikePrice = value;
                            value = 0;
                            Double.TryParse(columns[12].InnerText, out value);
                            chain.PutBidQty = value;
                            value = 0;
                            Double.TryParse(columns[13].InnerText, out value);
                            chain.PutBidPrice = value;
                            value = 0;
                            Double.TryParse(columns[14].InnerText, out value);
                            chain.PutAskQty = value;
                            value = 0;
                            Double.TryParse(columns[15].InnerText, out value);
                            chain.PutAskPrice = value;
                            value = 0;
                            Double.TryParse(columns[16].InnerText, out value);
                            chain.PutNetChg = value;
                            value = 0;
                            Double.TryParse(columns[17].InnerText, out value);
                            chain.PutLTP = value;
                            value = 0;
                            Double.TryParse(columns[18].InnerText, out value);
                            chain.PutIV = value;
                            value = 0;
                            Double.TryParse(columns[19].InnerText, out value);
                            chain.PutVolume = value;
                            value = 0;
                            Double.TryParse(columns[20].InnerText, out value);
                            chain.PutChginOI = value;
                            value = 0;
                            Double.TryParse(columns[21].InnerText, out value);
                            chain.Put_OI = value;
                            value = 0;
                            optionChains.Add(chain);
                        }
                    }
                    return optionChains;
                }

So you can see once we have html document received from HttpClient, We converted the response into an htmlagility Htmldocument .

         HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument();
         document.LoadHtml(responsetext);

then we just searched for the table object in the document which stores this Option chain data.

         HtmlNodeCollection rows = document.DocumentNode.SelectNodes("//table[@id='octable']/tr");

After this we just iterated over the rows of the table and stored the details in a list object.


Once this data was available with us we done processing to find out PCR & MaxPain for this chain.
and displayed on the form.