adding parameters in web scraping to connect and scrape data from a Dynamic URL

[ExcelSammy]

Hello! Someone please help!

I think my issue is most likely a syntax issue but I need to pass parameters to yahoo finance to scrape the data for different tickers on different exchanges. To simplify my problem i just created a seperate test Sub to show my problem. I created 2 string variables (ticker, exchange) and set to examples "TGIF" and "CN" (these are valid) now i just want to ammend these variables as part of the URL.

Sub OpenWebStockDataTest()
'
' OpenWebStockDataTest Macro
'


'
    Dim sticker As String
    Dim exchange As String
    
    sticker = "TGIF"
    exchange = "CN"
    
    ActiveWorkbook.Queries.Add Name:="Table 2", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/"" & sticker & ""."" & exchange &""/history?p="" &sticker &"".""&exchange))," & Chr(13) & "" & Chr(10) & "    Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data2,{{""Date"", type date}, {""Open"", type number}, {""High"", type number}, {""Low"", type number}, {""Close*"", type number}, {""Adj Close**"", type number}, {""Volume"", Int64" & _
        ".Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 2"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 2]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

the problem is it is not connecting to the table on the website i am trying to scrape. If i hard code the symbol and exchange as part of the URL it works. I am so confused, please help!!

 

[John_w]

Your quotes in the Formula string are wrong. A string starts with a quote character (") and ends with a quote. Quotes inside the string must be doubled ("") to produce a single quote character.

It's slightly easier to construct the Formula string if you first concatenate the symbol and exchange, like this:

    Dim ticker As String
    ticker = "TGIF.CN"
    
    ActiveWorkbook.Queries.Add Name:="Table 2", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/" & ticker & "/history?p=" & ticker & """))," & Chr(13) & "" & Chr(10) & "    Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data2,{{""Date"", type date}, {""Open"", type number}, {""High"", type number}, {""Low"", type number}, {""Close*"", type number}, {""Adj Close**"", type number}, {""Volume"", Int64" & _
        ".Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

The following code, added above that line, deletes the existing Table_2 ListObject (table) and workbook query on the active sheet, allowing you to run the macro multiple times:

    On Error Resume Next
    ActiveSheet.ListObjects("Table_2").Delete
    ActiveWorkbook.Queries("Table 2").Delete
    On Error GoTo 0
    ActiveSheet.Cells.Clear