Extracting and Analyzing Live Stock Data in Excel

Downloading and analyzing real-time stock data

In my previous article, Beginner’s Guide for Stock Option Analysis Using Excel I have covered in detail about option chain analysis in Excel. I had mentioned that I would discuss about how to analyze real-time stock data in Excel. So in this article I am explaining about how to extract data from the NSE website, refresh it periodically, perform various analysis, and build your portfolio in Excel.

I will show how to pull option data to Excel and build custom reports based on option strategies. These reports will in turn help you to predict trends for option trading. Same technique can be applied for other stock analysis as well.

Importing Options Data in Excel

I will show in this section how to load option chain data in Excel. Once the data is loaded you will learn various strategies to analyze this data and predict trends.

You can link to live data on the NSE website to analyze stocks data in real time. The data is in JSON format that has to be parsed from the NSE website. Following steps details this process:

1. Open the link www.nseindia.com in Chrome.
2. Navigate to Market Date > Option Chain
3. Right-click and select the Inspect option to open the Diagnostics pane.
4. Click the Network tab.
5. Reload the page.
6. Click the Name specific to the data you want to parse for example, option chain for NIFTY.
7. In the Network pane select the row related to option chain (refer the screenshot).

8. Make a note of the fields required to store the cookie values for this page. These values are available under the Response Headers section. They are bm_sv value under cookie, accept-encoding value, accept-language value, and user-agent value.

These are the steps related to establishing connection with the NSE website specific to the page that you want to parse the data. Same steps has to be followed for parsing data from any other section in the website.

Next follow these steps to download the data into the Excel file:

1. Open an Excel worksheet and create a column by name “Cookie”.
2. Copy and paste the cookie value from the Headers section into this column. Only copy the cookie value stored in the variable bm_sv.
3. Convert this column in Excel to Table by selecting Data > From Table/Range.
4. Open Power Query Editor and name the query as “Cookies” under Properties. Open Advanced Editor to convert this column to a function. Paste this in the Advanced Editor:
   let Cookies = () =>
   let
   Source = Excel.CurrentWorkbook(){[Name=”Table3"]}[Content],
   #”Changed Type” = Table.TransformColumnTypes(Source,{{“Cookie”, type text}}),
   Cookie = #”Changed Type”{0}[Cookie]
   in
   Cookie
   in
   Cookies
5. Close the Power Query Editor.
6. Under the Data tab in Excel, select From Web and select Advanced.
7. Enter the URL of the NSE website from where you want to parse the data (copy the Request URL from the Headers section in the Diagnostic pane).
8. Paste the values for accept-encoding, accept-language, and user-agent under HTTP request header parameters. For Cookie parameter enter the function name i.e. Cookies(). If the parameter value is not available in the drop-down then you can enter it manually.

This setup will simulate browser like connection in Excel and load the data from the URL given. A pop-up window with the confirmation to establish the connection displays. Once you confirm, the connection with the website is established. The data is loaded in JSON format.

If you are downloading stocks data then the format will be slightly different. As here I have downloaded option chain data the format has two rows, records and filtered data. Records has all the data whereas filtered has only data for selected expiry date.

Double-click the data that you want to open. Here I have opted to download the records data. The data expands to a list of records. Convert this list of records to table by clicking the button Into Table at the top right corner. Expand the column to view all the data extracted. You can choose the columns that you want to download. After all the columns are expanded you can load the data to the Excel sheet by clicking Close & Load button at the top right corner. The entire option chain data loads in the Excel sheet.

As this data is connected to the NSE website it can be frequently refreshed for real-time updates. To extract data from other sources, you only have to update the URL in the Advanced Editor, Source = Json.Document(Web.Contents(“https://www.nseindia.com/api/option-chain-indices?symbol=NIFTY")

For example, if the source URL is changed to, https://www.nseindia.com/api/option-chain-equities?symbol=WIPRO then data for Wipro stocks is extracted.

Conclusion

Now that the data is downloaded, you can analyze based on the steps and strategies explained in the article, Beginner’s Guide for Stock Option Analysis Using Excel. I will soon roll out another article in this series on automating the Excel worksheet for dynamic data analysis.