How to identify format of dynamically created URL for downloading CSV file using macros

[jdvisaria]

Hi,

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 - https://www.nseindia.com/products/d...TIES 50&fromDate=30-09-2017&toDate=31-10-2017

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.

Now nseindia has come up with new website - niftyindices

I am not able to figure out new dynamic URL from where CSV files get downloaded. More specifically, I am not able to find out format of dynamic URL created by new site.

It will be helpful if someone can help me find out format of new dynamic URL for downloading CSV file.

Website path from where dynamic data is downloaded.

Old - visit nseindia site -> https://nseindia.com/products/content/equities/indices/historical_total_return.htm -> Select Index as "Nifty 50" from drop down -> enter start and end date -> click "Get Data" button -> click "Download file in csv format"

New - visit niftyindices site - http://niftyindices.com/reports/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"

 

[Kyle123]

There isn't a dynamic link generated anymore. The data is returned in JSON format though, you could parse that.

What code are you currently using?

 

[jdvisaria]

Thanks Kyle123 for the reply.

Currently following VBA code is used for macros.

Public wsInput As Worksheet, wsData As Worksheet, wsCon As Worksheet, wsTemp As Worksheet, wb As Workbook, wsA As Worksheet, wsIndDa As Worksheet, wsRat As Worksheet, wsTRI As Worksheet, wsVIX As Worksheet
Public Sub Setup()


    Set wb = ThisWorkbook
    With wb
        Set wsInput = .Sheets("Input")
        Set wsTRI = .Sheets("Total Returns Index")
        Set wsTemp = .Sheets("Temp")
    End With
End Sub


Sub Download()
    Application.ScreenUpdating = False
    
    Call Setup
    
    With wsInput
        strType = "Total Return Index"
        strIndex = UCase(Replace(.Range("A1").Value, " ", "%20"))
        strFrmDate = CDate(.Range("C2").Value)
        strToDate = CDate(.Range("D2").Value)
        strNetDays = DateDiff("d", strFrmDate, strToDate)
            
        If strNetDays > 85 Then
            lngPart = Int(strNetDays / 84) + 1
        Else
            lngPart = 1
            strDynFrmDate = CDate(strFrmDate)
        End If
        
        strBaseURL = "URL;https://www.nseindia.com/products/dynaContent/equities/indices/total_returnindices.jsp?indexType=" & strIndex & "&fromDate="
        strURL2 = ""
        Set wsCon = wsTRI
        strFor = 0
        
        For i = 1 To lngPart
            If i = lngPart Then
                strDynToDate = CDate(strToDate)
                lRowCon = wsCon.Cells(wsCon.Rows.Count, 1).End(xlUp).Row
            ElseIf i = 1 Then
                strDynFrmDate = CDate(strFrmDate)
                strDynToDate = DateAdd("d", 83, CDate(strDynFrmDate))
                lRowCon = 3
            Else
                strDynToDate = DateAdd("d", 83, CDate(strDynFrmDate))
                lRowCon = wsCon.Cells(wsCon.Rows.Count, 1).End(xlUp).Row
            End If
            
            wsTemp.Activate
            wsTemp.Cells.Clear
            
            strURL1 = FormatDate(strDynFrmDate, strFor) & "&toDate=" & FormatDate(strDynToDate, strFor)
            strURL = strBaseURL & strURL1 & strURL2
            With wsTemp.QueryTables.Add(Connection:=strURL, Destination:=Range("$A$1"))
                .Name = "NIFTY1"""
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .WebSelectionType = xlEntirePage
                .WebFormatting = xlNone
                .WebPreFormattedTextToColumns = False
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = True
                .WebDisableDateRecognition = False
                .WebDisableRedirections = False
                .Refresh BackgroundQuery:=False
            End With
            wsTemp.Activate
            lRowTemp = wsTemp.Cells(wsTemp.Rows.Count, 1).End(xlUp).Row - 1
            wsTemp.Range("A4:B" & lRowTemp).Select
            Selection.Copy
            wsCon.Cells(lRowCon + 1, 1).PasteSpecial Paste:=xlPasteValues
            strDynFrmDate = CDate(DateAdd("d", 1, CDate(strDynToDate)))
            
        Next
        
    End With
    lrowcons = wsCon.Cells(wsCon.Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = True
    If strFor = 1 Then
        wsCon.Range("A1").Value = FormatDate(strFrmDate, 1) & " To " & FormatDate(strToDate, 1)
    Else
        wsCon.Range("A1").Value = Replace(strIndex, "%20", " ") & " for " & FormatDate(strFrmDate, 1) & " To " & FormatDate(strToDate, 1)
    End If
    strT = Timer
    wsCon.Activate
    wsCon.Range("A1").Activate
    wsCon.Range("D1").Value = strURL '- This will print URL
    
End Sub


Public Function FormatDate(strDate, boolFormat)
    If boolFormat = 0 Then
        FormatDate = Format(strDate, "dd-mm-yyyy")
    ElseIf boolFormat = 1 Then
        FormatDate = Format(strDate, "dd-mmm-yyyy")
    End If


End Function