How to Use Wildcards with QueryTable?

[jonathanwang003]

Hi there,

I recorded a macro to download a Query Table from the Web that gives me revenue estimates. The code below works for the company ticker "NVDA" which is declared below. However, when I change the ticker to "ADBE", the code breaks because the column header (which specifies a date) doesn't match NVDA's column header. This is because their earnings dates are different. I tried using a wildcard "*", but that doesn't work. How could I use a wildcard so that the Query Table will still download for any ticker I input?

Here's the code I am using:

Sub DownloadRevenueEstimates()

Dim Ticker As String: Ticker = "NVDA"

    ActiveWorkbook.Queries.Add Name:="Table 3", Formula:="let" & Chr(13) & "" & Chr(10) _
    & "Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/" & Ticker & "/analysis?p=" & Ticker & """))," & Chr(13) & "" & Chr(10) _
    & "    Data3 = Source{3}[Data]," & Chr(13) & "" & Chr(10) _
    & "    #""Changed Type"" = Table.TransformColumnTypes(Data3,{{""Revenue Estimate"", type text}, {""Current Qtr. (Jul 2018)"", type text}," _
    & "{""Next Qtr. (Oct 2018)"", type text}, {""Current Year (2019)"", type text}, {""Next Year (2020)"", type text}})" _
    & 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 3"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 3]")
        .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_3"
        .Refresh BackgroundQuery:=False
    End With

End Sub

 

[John_w]

You can reference columns by position instead of name by using the Table.ColumnNames() function - see https://social.technet.microsoft.co...mn-position-rather-than-name?forum=powerquery

Here is your code modified to use Table.ColumnNames() for the 4 columns.

Sub DownloadRevenueEstimates()

    Dim Ticker As String
    Dim queryFormula As String
    
    Ticker = "ADBE"
    Ticker = "NVDA"

    While ActiveWorkbook.Queries.Count > 0
        ActiveWorkbook.Queries(1).Delete
    Wend
    
    While ActiveSheet.ListObjects.Count > 0
        ActiveSheet.ListObjects(1).Delete
    Wend
    
    queryFormula = _
        "let" & vbCrLf & _
        "    Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/" & Ticker & "/analysis?p=" & Ticker & """))," & vbCrLf & _
        "    Data2 = Source{3}[Data]," & vbCrLf & _
        "    #""Changed Type"" = Table.TransformColumnTypes(" & vbCrLf & _
        "    Data2,{{""Revenue Estimate"", type text}," & vbCrLf & _
        "       {Table.ColumnNames(Data2){1}, type text}," & vbCrLf & _
        "       {Table.ColumnNames(Data2){2}, type text}," & vbCrLf & _
        "       {Table.ColumnNames(Data2){3}, type text}," & vbCrLf & _
        "       {Table.ColumnNames(Data2){4}, type text}})" & vbCrLf & _
        "in" & vbCrLf & _
        "    #""Changed Type"""
    
    ActiveWorkbook.Queries.Add Name:="Table 3", Formula:=queryFormula
    
'    ActiveWorkbook.Queries.Add Name:="Table 3", Formula:="let" & Chr(13) & "" & Chr(10) _
'    & "Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/" & Ticker & "/analysis?p=" & Ticker & """))," & Chr(13) & "" & Chr(10) _
'    & "    Data3 = Source{3}[Data]," & Chr(13) & "" & Chr(10) _
'    & "    #""Changed Type"" = Table.TransformColumnTypes(Data3,{{""Revenue Estimate"", type text}, {""Current Qtr. (Jul 2018)"", type text}," _
'    & "{""Next Qtr. (Oct 2018)"", type text}, {""Current Year (2019)"", type text}, {""Next Year (2020)"", type text}})" _
'    & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    
    Debug.Print ActiveWorkbook.Queries(1).Formula
    
    'ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 3"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 3]")
        .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_3"
        .Refresh BackgroundQuery:=False
    End With

End Sub

 

[jonathanwang003]

Wow this was incredibly helpful! Thanks so much John.