HOW TO GET DATA from NEW NSE WEBSITE in EXCEL​ | | EXCEL TRICKS

hey guys uh my name is arjeet and welcome to another session with quantosis diary and in this video we are going to talk about how to get data from the new nsu website in excel file so some of you already familiar with this process of getting data from the nsu website in excel but in the new website some of you may be facing problem to get the data in excel file so in this video we're going to we are going to talk about how to get those data clearly okay so before jumping into some of the example let's talk a bit what's happened in the back end okay when you are getting a data from website so each of the website may have a backend server where their actual data is presented then there is something called api or application programming interface which basically expose those data in the website and we see those data okay but some of the website you may have seen like google or gmail your facebook account or any of the account those website you will see there is a login feature so you will need to give password to access those website okay so there is authentication method is there that whether the user is yourself you need to give a password and then only you can access the data from those website but normally in general website uh on the public website where the data is available for public like this or like this where no login is required you are able to face the data directly without having to authenticate but in this new website there has been a in the middle layer where there is a authentication token in the browser side that is get created and that browser side token authenticates and get the data in the browser but when you are getting the data in the excel file that token is missing and that's why in the new website you are not able to get the data that's why you have to use a token now how to get the token and how to get the data i'll come to that on the demo sections okay so we'll start with some of the examples on how can i get that data in the description section of this uh video if you find there will be two link to download two file one is called power query one another one is power query two so we'll be using this uh power query feature to so in the description section you will have a power query and power query 2 which you will need to download keep those file opened and open a new excel file as well now first i'll go to this website and see that's the market data and if you go to snapshot then you will get uh sections here so first i'll try to get most active equities list which are by volume which are most active so i will click on this view details and go in this page so the database should look like the actual data should be there and there it should be a refresh button okay you can directly download the csv file from here itself okay but that will be offline download and that file cannot be refreshed but the method i am going to show it here you can refresh the data from the excel itself uh so first you need to do what you need to go either press f12 to open this window okay or click on the browser settings and more tools click on the developer tools so once you click on this it will open this network tab clear if there is already anything in there otherwise um just open this and then click on the refresh button what will happen there will be some data will come here you will select the link with so i am checking most active equities and volume so most active securities index is equal to volume if you right click on this link it will open in a new tab and you will get the url of that so this is the api url that i am talking about you will need to get for getting the data so i mentioned that you will need to get have a api url now next part will come to the authentication part so authentication part what you will have to do go to back to this again you see there is a selected called response tab there is preview there is header so click on this headers tab while you have selected this link okay so header say uh tab will have a general section response headers and there will be something called request header go to this section and under this you will find there is something called cookie so if you select this this is highlighted now if you select after the cookie colon copy from here to this double quotation where the double quotation is ending copy in here okay so copy this value from cookie colon after the cookie colon to this double quotation come back to your excel file and let's uh make a header called cookie and then i'm going to do what i'm going to um a little bit to make it bigger because this and then paste it here okay then i'll use the wrap text to look at little bit better so this cookie uh heading and this is the cookie value that i have copied from here okay now select these two um cell cookie and this value then go to the data tab click on the from table or range once you click on that it'll come to this window make sure this box is ticked my table has header and click on ok once you click on ok there will new window will come up this is the power query editor window where on the cookie header if you see if you click on the cookie header part the cookie written and right click and then go to the drill down so if you drill down first level you will see list and this but again i'll select on the here and right click on that and do drill down again so until you see the copied value directly in this panel as you can see from here to here i have copied or cookie 0 something like this you will be going here and after you came come here go back to your park over your editor home window this is a transform window and then go back to home window home tab and click on advanced editor so once you are in advanced editor the file you downloaded remember previously power query one and two go to power query two dot txt copy the first line first and then paste it on top of this so on the very first line will be the very first line copied from power query to and then copy the last two lines in and cookies from power query 2 and paste it end of it okay so this should look something like this okay so this line the first line i copied and this two line i copied everything done then click on done button now what will do in the left side there is a queries window expand this one this call is table i'll just make it a little bit user-friendly later let's call it cookies fn so this is already done now what i have to do is i have to make another query so this is one query is done i'll make another query so go to this blank space below cookie fn right click on it new query under new query go to other sources and blank query i repeat new query other sources blank query click on it so it will create a new query here if you i'll just make it rename and let's make it data query okay once you are here go back to advanced editor again and now this time i'll open power powerquery1.txt copy the entire content and replace the existing content with the copied content so if you see this here there is something called cookiesfn already there in the code so this cookies happen should match whatever the name you are giving in the previous cookies fm so this name and this name should match okay and this part this double quotation will be the url of the data so if i see go back here so okay i'll just clear it and refresh again so this is the most active security right so i copied this api url what i will do i'll come back here and check if this url is the same as the data i am looking for done now after you have done this this will come information is required about data privacy click on continue check ignore privacy levels okay once you check this click on save and this data will come like this now all you have to do is click on close and load this arrow or down arrow icon and click on close and load to select new worksheet so you will take some time and get downloaded so sheet2 has a data so i'll just rename that as a data tab and this is a let's say cookie cookie tab okay so i have so what will happen basically uh the school keys gets expired after a certain period of time so if it's get expired if you refresh this data and this data will not get replaced in that time you will have to follow this procedure go to the website and get so it does not mean you have to get this url you can go to any of the link url here here or if any of the network tab and under this all you have to do go to the request header sections and copy the cookie part so cookie colon whatever is there from strike wise to this double quotation ending okay so it should look like this strike price to double quotation ending so the entire thing you copied and replaced it here and then you can refresh again those who are familiar with the excel file handling you can also write your macro or make your connection automated if you want to okay if you are not finding this right side tab queries and collection click on this query and connection you will get this tab okay so the data currently i am getting is from most active securities so now let's take another example i will see spots in open interest this data so i'll basically follow the same process here i'll press the go to the developer tool or press f12 to open this window and then click on the network tab then click on the refresh so by underlying i want to see so this is the url i am looking for right click open in tab so i'll just copy this url go back here and this in the query tab i will right click on this data query click on edit so it will bring it here and then i'll click on advanced editor so if you see here this url i have used previously i have to change the url now since i am using a new data change the url again make it done if you see this symbols are coming correctly but data is not coming so those who are familiar with this because i have already write written a code and by default this conversion happened i'll have to retrace back to the source so i'll just delete the steps one from the bottom steps to from the bottom and step this step until i get to the source so if you see source then you are in correct place so in the source you will see data and there is a clickable button called list if you click on this list it will come to this record window and it's showing some record once you are in this record window you can click on the two table so once you click on two table this pop-up will come just don't change anything and click on ok once that is done this converted so source data and converted to table these two steps are done and then i'll go to this column one name and then right side there is a two arrow double arrow type of button click on this it will come like this uh you can use our original column prefix or you may uncheck this so that that that value will be the your column header this name once all of this done just do close and load the data you are seeing previously has been changed to the new data so this is like latest toy previous way whatever data you can see here open interest change changing y percent change in y all these data has been even some more details has been coming in here what is the future value and etcetera etcetera okay so these are like simple uh kind of uh data table which i am importing back to excel now one of the most important in terms of people who are interested in getting the data is getting open high low close of nifty data so you have to come to the market data and then market watch off uh any of those so you can go to live equity market and you can find this or you can come to the market watch of indices and find that so this is nifty hundred i'll go to nifty 50 i want this nifty 50 so i come to nifty 50 and i see this is ohlc data i want to get this data in excel okay so i'll do the same thing open the developer tools by f12 or going from here to modules developer tools go to network tab same thing click on refresh button you will see the third one right click on this open it you will get this kind of data nifty 50. okay copy this url same as the before okay and go back here and then the same data query right click on this edit it will open the power query window will go to directly to advanced editor and go to this double quotation url part and replace that with the new url just to copy it click on done you notice the same thing left side this are gaming coming but this data is empty so you will need to do the same thing retrace back to the source so delete the steps that are below source one two okay so i i am at source again now if you see there are multiple things advanced data metadata mark etc you have to select the data one click on this list and you can see this record page again once your record page click on two table okay use the default one don't change anything click on ok so you can see data has been done converted to table now if you go to column one this button again click on this these are the column that you need so if you don't need any column you can just uncheck it let's say i don't need meta i don't need priority i don't need identifier things like that okay i don't need ffmc chat today path series these are the data i don't think so i am uncheck that and i will not use the prefix so these are the header that will come so these will be the name of the header if you select the prefix it will be a dot something will come okay you can try it yourself once you done that the data is coming open high low close all this data is coming okay this is the similar thing go close and load you can see that now nifty 50 close ohlc data is there so these data can be refreshed from the excel itself if you click on the refresh here or if you go to the data connection right click refresh so this data will be refreshed automatically i mean sorry manually from the excel itself okay and if you can write a macro or you know if you can enable the connection properties uh refresh every sometime you can try this this will maybe give you a frequent refresh automatically so that's goes for the ohlc data and the last one i'll show you as part of the example are the option chain so let's go to option chain so the option chain one is little bit complicated because you will need to do some formatting but the main procedure is same so you will go to open the developer tool click on the refresh button and from this select this one option chain indices symbol is equal to nifty open in this one this data will look like this copy this url same thing basically go to excel right click on the query edit once your this page go to advanced editor then change the url from here so once i have done the url change click on done you'll see some error because this one i said a little bit formatting is required but don't worry go back to the source again so source you will see records and filter you have to select the filtered one to get both c and p click on the filtered record then click on the data list so filter data and then this will give you the record list okay once you are in this page go to true table and use the default one click on ok and then you will be seeing something like this use the column one button to expand again uh i'll not use the prefix in this case okay i'll click on ok you will see after expanding also there is two column having record so i'll need to expand these also so what i'll do i'll click on let's say p and i don't need strike price i don't need expiry date i don't need underlying i don't need identifier i only need these implied volatility percentage by bit okay so i also don't need underlying value and i'll use the prefix here because to get the this is a p open interest or c of interest i'll expand this so p open interest p change in open interest like this it will come okay now what i will do i can drag this column to make it uh look like the old option chain that i am used to so i'll just drag to the right till i'll drag it to further right before the c why this is because i'm used to the previous option chain where strike spice is the in the middle so i launched expiry underlying under so the columns that i need i'm just unchecking it here and then use column prefix and click on ok so all the columns are now expanded here if you if you don't if you have by mistake selected a column you can also delete it like this is third september so i don't need to filter it okay i'll just going to right click and remove this column okay this is the open interest change in open interest this is looking like the option chain i want and some formatting i have also done you can do further formatting as per your requirement then click on close and load okay now if you see here p open interest p change in open interest pe volatility all this data came up option chain data if i want i can also filter some of the stuff here right so my strike price is here and i want strike price to be between something right let's say i don't want to check below 10 500 so i will check greater than equal to 10500 and is less than equal to 12 000 i okay so this will filter less number of columns so you will see strike wise has been reduced and you can do further analysis using this excel for the open interest okay so these are the few examples that i have shown you if you have missed something just rewatch the video and you will be able to get the details the file that i mentioned power query and power query 2 this two code will be shared in the description section so check the description of this video for to download this codes okay i think that will be all for this video and thank you all do subscribe and refer our channel to your friends okay thank you

Channel: Quantnosis Diary

Views: 24,337

Keywords: Stock Market, Excel, Data, Powerquery

Id: 3kZwCQlQ658

Length: 21min 44sec (1304 seconds)

Published: Mon Aug 31 2020