Macro to download data from multiple webpages

[newbieatexcel]

Hello all!

I'm looking for an automated task to download data from this website: Contratti Intraday di Atlantia - Borsa Italiana
As you can see any stock has multiple page and I'd like to get all infos in one single sheet. And I'd like to repeat this task for every stock in my watchlist.


Until now, I follow these steps:
1) a webquery (let me take this stock Contratti Intraday di Atlantia - Borsa Italiana, for example);
2) I select the table and this is downloaded properly in sheet1;
3) then I apply this macro

Sub getTables()
Dim Dest As String, myRoot As String, I As Long, myRan As Range


Dest = "Foglio3"        '<< Il foglio dove sara' creato l' elenco
aaa = Selection.CurrentRegion.Address


myRoot = "URL;http://www.borsaitaliana.it/borsa/azioni/contratti.html?isin=IT0003506190&lang=it&page="
With Range("A1").QueryTable
    For I = 0 To 1000
        .Connection = myRoot & I
        .Refresh BackgroundQuery:=False
        Set myRan = Range(Range("A2"), Range("E2").End(xlDown))
        myRan.Copy Destination:=Sheets(Dest).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        If myRan.Rows.Count < 20 Then Exit For
        DoEvents
    Next I
End With
'
End Sub

to download the following pages;
4) I get all the data I want in sheet3;
5) I repeat the previous steps for each stock I am interested;


Now I'd love to implement an efficent way (Is it possible with only one shot?) to get what I want.
I'm trying to reach that goal but I get no results.
So I need your precious help!

I repeat what I'd like to get:
a macro that downloads the data for N ISIN (=N stocks), and for each ISIN creates one sheet (one file with N sheets).
The number N of stocks is fixed and doesn't change in time. So I could list the stocks in the first sheet or directly into the macro.


Thanks in advance for your help

 

[newbieatexcel]

Can someone help me, please?

 

[John_w]

Try this.

Sub Macro1()
    
    Dim r As Long, page As Integer
    
    r = 1
    For page = 0 To 9
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://www.borsaitaliana.it/borsa/azioni/contratti.html?isin=IT0003506190&lang=it&page=" & page _
            , Destination:=Cells(r, 1))
            .Name = "contratti.html"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "4"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        ActiveSheet.QueryTables(1).Delete
        If r <> 1 Then
            'Delete headings
            ActiveSheet.Rows(r).Delete
            r = r + 20
        Else
            r = r + 21
        End If
    Next
    
End Sub

It retrieves 10 pages. Change the 9 to the last available page number you want (or do an initial web query for the entire page and search for "Pag." to determine the last page. Plus, I'll leave you to modify the code to loop through your list of stocks.