locked
URL issue when creating power query with VBA RRS feed

  • Question

  • Hello,

    I am trying to write a macro to create a power query once I duplicate a worksheet from a template. The power query pulls quarterly income statement data from the NASDAQ website, using the worksheet name as the ticker symbol.

    As I am no M formula expert, I recorded a macro and then proceeded to modify it. The main modification consists in changing the ticker symbol inside the URL, and making the data type modifications refer to the actual column names (as the sequence of historical quarters will change, using the actual column names will result in an error once the data on the web page rotates to the next quarter).

    Strangely, when running the code, the new query seems to ignore the "?query=income-statement&data=quarterly" portion at the end of the URL, and proceeds to download the data from "https://old.nasdaq.com/symbol/" & ActiveSheet.Name & "/financials" only, which is annual income statement data, instead of quarterly.

    I have searched the web without finding any clues as to what might be happening. 

    Any help is very much appreciated. The VBA code is below.

    Robert

    Sub AddQuarterlyIncomeStatements()

        Dim QueryName, URL As String
        QueryName = ActiveSheet.Name & " Quarterly Income Statements"

        ActiveWorkbook.Queries.Add _
            Name:=QueryName, _
            Formula:= _
            "let" _
            & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://old.nasdaq.com/symbol/" & ActiveSheet.Name & "/financials?query=income-statement&data=quarterly""))," _
            & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," _
            & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{Table.ColumnNames(Data0){0}, type text}, {Table.ColumnNames(Data0){1}, type text}, {Table.ColumnNames(Data0){2}, type text}, {Table.ColumnNames(Data0){3}, type text}, {Table.ColumnNames(Data0){4}, type text}, {Table.ColumnNames(Data0){5}, type text}})," _
            & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table .RemoveColumns(#""Changed Type"",{""Trend""})" & Chr(13) & "" _
            & Chr(10) & "in" _
            & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryName & ";Extended Properties=""""" _
            , Destination:=Range("Quarterly_Income_Statements")).QueryTable
             
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & QueryName & "]")
            
            .RowNumbers = False
            
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = ActiveSheet.Name & "_Quarterly_Income_Statements"
            .Refresh BackgroundQuery:=False
        End With
        
    End Sub

    Monday, December 2, 2019 3:37 PM

Answers

  • Hi Robert

    The problem has nothing to do with VBA. If you create a new query from the Power Query editor and paste this code:

    let
        Source = Web.Page(Web.Contents("https://old.nasdaq.com/symbol/MSFT/financials?query=income-statement&data=quarterly")),
        Data0 = Source{0}[Data]
    in
        Data0

    you get Annual figures, not the Quarterly ones you expect:

    I don't have enough experience to help you further with this I'm afraid. I suggest you change the title of your thread so it better reflects the issue - hopefully someone else will be in a position to assist then

    • Marked as answer by gnilsch Monday, December 2, 2019 10:38 PM
    Monday, December 2, 2019 9:56 PM

All replies

  • Hi Robert

    The problem has nothing to do with VBA. If you create a new query from the Power Query editor and paste this code:

    let
        Source = Web.Page(Web.Contents("https://old.nasdaq.com/symbol/MSFT/financials?query=income-statement&data=quarterly")),
        Data0 = Source{0}[Data]
    in
        Data0

    you get Annual figures, not the Quarterly ones you expect:

    I don't have enough experience to help you further with this I'm afraid. I suggest you change the title of your thread so it better reflects the issue - hopefully someone else will be in a position to assist then

    • Marked as answer by gnilsch Monday, December 2, 2019 10:38 PM
    Monday, December 2, 2019 9:56 PM
  • Hello,

    Thanks a lot for getting back to me. And you are right, that's exactly what happens.

    And guess what, the URL is case sensitive. If you try 

    https://old.nasdaq.com/symbol/msft/financials?query=income-statement&data=quarterly

    you get the quarterly data.

    Problem solved!

    Robert

     

    Monday, December 2, 2019 10:38 PM
  • Good to know, thanks Robert

    Quick suggestion to make your like easier. Create a generic query with code:

    let
        Source = Web.Page(Web.Contents("https://old.nasdaq.com/symbol/msft/financials?query=income-statement&data=quarterly")),
        Data0 = Source{0}[Data],
        RemovedTrend = Table.RemoveColumns(Data0,{"Trend"}),
        ChangedTypes = Table.TransformColumnTypes(RemovedTrend, List.Transform(Table.ColumnNames(RemovedTrend), each {_, type text}))
    in
        ChangedTypes

    load it as a Connection only and name it i.e. BaseQuarterlyIncome

    Then your VBA code could look like this:

    Sub AddQuarterlyIncomeStatements()
    '
        Dim BaseQuery       As WorkbookQuery
        Dim QueryName       As String
        Dim QueryFormula    As String
        
        Set BaseQuery = ThisWorkbook.Queries("BaseQuarterlyIncome")
        
        QueryName = ActiveSheet.Name & " Quarterly Income Statements"
        QueryFormula = Replace(BaseQuery.Formula, "/symbol/msft", "/symbol/" & LCase(ActiveSheet.Name))
        ActiveWorkbook.Queries.Add Name:=QueryName, Formula:=QueryFormula
            
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & QueryName & ";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
                    .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & QueryName & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = ActiveSheet.Name & "_Quarterly_Income_Statements"
            .Refresh BackgroundQuery:=False
        End With
    End Sub




    • Edited by Lz._ Tuesday, December 3, 2019 1:58 AM
    Monday, December 2, 2019 11:13 PM