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

jdvisaria

New Member
Joined
Nov 9, 2017
Messages
2
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"
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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?
 
Upvote 0
Thanks Kyle123 for the reply.

Currently following VBA code is used for macros.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top