Dynamic URL for web query

Question

I used to download data from "nseindia" website using attached macro code.

Macro does the following job.

1. take the inputs from "Input" sheet about indices name, start date and end date.
2. Generate the URL according to input data. Dynamically created URL is mentioned in Second sheet.

In this URL "NIFTY%20BANK&fromDate=30-09-2017&toDate=31-10-2017" is dynamically created part of whole URL based on user input.

3. Download data available at this link in CSV format, in to "Total Return Index" sheet. The CSV file is opened in web browser itself.

Old - visit nseindia site -> go to "product" tab on top -> select "Indices" -> Select "Historic Data" -> Select "View Total Returns" -> Select Index as "Nifty 50" from drop down -> enter start and end date -> click "Get Data" button -> click "Download file in csv format"

old website : https://www1.nseindia.com/products/content/equities/indices/historical_index_data.htm

New - visit niftyindices site - to to"reports" tab on top -> select "Historical Data" -> select "Total Returns Index Value" from drop down on left top side -> select start date and end date -> press "submit" button -> click on "csv format"

new Website : https://www.niftyindices.com/reports/historical-data

Can someone advise..

Answer 1

You don't seem to have attempted anything.

I am only posting this educational post hoping that it might inspire you to write your own code in the future.

As I said in the comments the website you're trying to scrape, offers a very convenient way to download the data you want, through an HTTP request.

An HTTP request is a structured way to request something from a server. In this case we want to send two dates to the server and get the corresponding search results.

To find out how this request should look like, you have to inspect the network traffic when the submit button is clicked. You can do that through your browser's developer tools (F12):

If you go through the Headers and the Params of the request you will see how the url, the body and the headers should look like. In this particular case, all the parameters are sent in the request's body in JSON format and most of the headers are not essential to the success of the request.

The body of the request looks like so:

{'name':'NIFTY 50','startDate':'01-Feb-2020','endDate':'29-Feb-2020'} 

In this particular case the response's payload is a json string inside another json string. You can inspect its structure using a tool like this. Here's how the second json looks like:

It basically consists of one item per requested date and each item consists of 7 parameters and their corresponding values.

CODE

Option Explicit

Sub nse()
Dim req As New MSXML2.XMLHTTP60
Dim url As String, defaultPayload As String, requestPayload As String, results() As String
Dim payloadJSON As Object, responseJSON As Object, item As Object
Dim startD As Date, endD As Date
Dim key As Variant
Dim i As Long, j As Long
Dim rng As Range

startD = "01/02/2020" 'change the date to whichever you want
endD = "29/02/2020" 'change the date to whichever you want
url = "https://www.niftyindices.com/Backpage.aspx/getHistoricaldatatabletoString"
defaultPayload = "{'name':'NIFTY 50','startDate':'','endDate':''}"
Set rng = ThisWorkbook.Worksheets("Name of your Worksheet").Range("A2") 'use the name of the worksheet in which you want the results to be printed.


Set payloadJSON = JsonConverter.ParseJson(defaultPayload)
payloadJSON("startDate") = Day(startD) & "-" & MonthName(Month(startD), True) & "-" & Year(startD) '01-Feb-2020
payloadJSON("endDate") = Day(endD) & "-" & MonthName(Month(endD), True) & "-" & Year(endD) '29-Feb-2020
requestPayload = JsonConverter.ConvertToJson(payloadJSON)

With req
    .Open "POST", url, False
    .setRequestHeader "Content-Type", "application/json; charset=UTF-8"
    .setRequestHeader "X-Requested-With", "XMLHttpRequest"
    .send requestPayload
    Set responseJSON = JsonConverter.ParseJson(.responseText)
End With
Debug.Print responseJSON("d")
Set responseJSON = JsonConverter.ParseJson(responseJSON("d"))
ReDim results(1 To responseJSON.Count, 1 To 7)
i = 0
For Each item In responseJSON
    i = i + 1
    j = 0
    For Each key In item
        j = j + 1
        results(i, j) = item(key)
    Next key
Next item
rng.Resize(UBound(results, 1), UBound(results, 2)) = results
End Sub

The above code for demonstration purposes prints the results starting from cell A2 of an empty excel worksheet. You can modify the code to best fit your needs.

You will need to add the following references to your project (VBE>Tools>References):

Microsoft XML version 6.0
Microsoft Scripting Runtime

You will also need to add this JSON parser to your project. Follow the installation instructions in the link and you should be set to go.

RESULTS

Here's a sample of the results for the period 1/2/2020 to 29/2/2020