HOW TO EXTRACT OPTION CHAIN FROM NEW NSE WEBSITE INTO EXCEL

hi everyone welcome to this another video in market talk youtube channel today we are going to see how to import option option chain from the new nsc website to excel is very simple process just you have to follow the steps and do it so let's first go open the google chrome click this uh link ns india.com option dash chain you will get this like this now what you do is right click anywhere in this in the browser and go to inspect now you will get a message like this where will show the scripts of this website so you don't bother about anything on that now what we'll do is here in the top you click network okay now go to excel in the excel you need to have power query this is enabled in the office 2017 2019 and onwards so ensure or you can have office 365 or excel 365. so what you do is now go to data and there you tell from web without this power query you will not be able to import this data that is very important okay a very good tool also now what we do is here we will go here to the browser back and select here instead of nifty you select here now bank so the script changes then you will come to know what the changes here now what we do is here you can see option chain and this is click here okay now now let us not uh space here same space here go here you can see here the request url now what it is copy this link ctrl c and then go to excel now you got this message then click advanced and click here and paste ctrl v now what you do is next step here select here accept encoding because you give this headers parameters then only be able to import the data so what you do here come here request headers come to request headers accept encoding copy this gzip deflate only this part only copy and then paste here then add header now language accept language come here and copy this only this part enus en control c then control v add header now another one what you do is user agent user space agent okay u and a is capital and in between this semicolon dash then come here use the result you can see here and copy this here ctrl c and ctrl v next what you do is here cookie c i e cookie now in the top just here you can see cookie in this bmsb equal to okay from here we select here until equal to control c then here control v so these four things you do it then tell ok so now it will connect to the power query and then you will be able to see the data what is there inside there are some few steps you have to just blindly follow that that's it so now it's opening the power query module automatically if you do the normal excel with your power query you'll not be able to do this that's very important so now the power query model is opening now what you do is okay this is json date now you can see record click records and then now you can see click data now you tell to table click now you tell ok now you can see some small uh small icons like this there clicks icon now you can sometimes pc may be hidden don't do anything just uncheck this use virtual columns then tell okay now we'll get like this similarly for pe also you can get the small icon and check this all the data i will get now what are irrelevant you don't interested you can uncheck this now currently for i'm just selecting everything until okay now p data all we got now go to the last column c call option now click this icon tell okay now all the data relating to ce we got it anything you don't want you can later on delete also so now all this like macro is getting recorded now i don't want this say identifier this i don't want this now you can tell remove column okay certain things will be like showing uh the values will be in the text format so what you do is select all the columns now for the purpose of this demonstration i will show you only open interest i will make it as a data type whole number and here here also this p operators was what we saw now come on the right side you can see for the call what open interest here the call operator is there now you till here whole number okay what happens here in the open interest sometimes it will be showing a null value issuing null value correct so for this what you do is select here go to transform replace values and type here null replace with 0 till ok so that all the null values will become 0 because when you want to total this open interest you'll not be able to do it without do this process here another way for the call order to select the column replace values null until 0 till okay so this ah do the process for any column which have a problem like this now you tell here all done now control home close and load now close and load now the data is getting imported to excel so any columns you don't want you can just remove that go to edit option and then do it so how do it edit option right click here anywhere and go to table edit query will be able to edit the data so this way i can go back to the query and do the editing also now again we just go back to the clause needed now we will see open enters what is showing here equal to sum total ok now the total is coming to 295 873 needs for all the open interest actually now we wanted for only for 23rd july so what we do is here right click table edit query in the data expiry date we will select only the 23rd july here drop down here uncheck all 23rd july select ok now we got for entire thing now expedite also wherever it is they're showing null you can do the same process of replace transform replace and you can do that also now let us go home closer load now we got only for 23rd july the data for 23rd july so you can see 187 168 is the total open interest for put now we'll do for call also same way so equal to sum you can see one nine three two eight seven just go to this option where you can now close this this is not required now for bank nifty come down total operators 187 168 for put 180 168 for put then for the call option 193 193287 it's on so this is the way you can do the import of option chain from nsc new website hope you like the video please subscribe and like the channel so that i can bring more videos for you thank you

Channel: Market Talk

Views: 6,712

Keywords:

Id: 50AIfspQQXk

Length: 8min 40sec (520 seconds)

Published: Sat Jul 18 2020