none
Web Query in Excel 2016 Question - two different query's results are the same, what's wrong with me? RRS feed

  • Question

  • Hi folks,

    I'm trying to scrap stock prices from Google finance.  As below, I've written two different query from PowerQuery.   but  the results are the same from the two query, and only 20 rows of results I can get even I add more  stock query statements, 30, or 40 symbols.    Can anyone please advise me what was wrong ?    

    query 1

    ================

    let
        Source = Web.Page(Web.Contents("https://finance.google.ca/finance?q=fb"))&
     Web.Page(Web.Contents("https://finance.google.ca/finance?q=GOOG" ))&
     Web.Page(Web.Contents("https://finance.google.ca/finance?q=AMZN" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=MSFT" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=IBM" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=AAPL" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=HPQ" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=ORCL" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=ADBE" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=NFLX" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=CSCO" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=LMT" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=TSLA" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=DIS" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=HD" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=XOM" ))&
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=F" )),


        Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}})
    in
        #"Changed Type"

    ==================

    And query 2

    --------------------------------

    let
        Source = Web.Page(Web.Contents("https://finance.google.ca/finance?q=AAL"))&
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=PG" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=MDT" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=INTC" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=AXP" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=BA" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=CVX" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=DD" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=GE" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=GS" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=JNJ" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=KO" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=MMM" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=MRK" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=PFE" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=TRV" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=UNH" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=UTX" ))& 
    Web.Page(Web.Contents("https://finance.google.ca/finance?q=MCD" )),
           Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}})
    in
        #"Changed Type"

    ------------------------------------

    Thanks a lot.

    Tuesday, November 14, 2017 8:47 PM

Answers

  • Hi Colin,   It works beautifully!    I appreciate your time effort!!
    • Marked as answer by eric oh Friday, November 17, 2017 5:11 PM
    Thursday, November 16, 2017 6:01 AM

All replies

  • Power Query/Power BI picks up several tables from the source. For each stock symbol, one table is a list of related companies. This table is returned in Source{0}[Data]. You need to filter the ClassName column to select the name "gf-table." The first row is in table contains the requested stock symbol data. In addition, you can avoid repeating the web page function for each symbol by looping with a list of the symbol names.

    The complete code for your query 2 is as follows:

    let
        SymbolList = {"AAL", "PG", "MDT", "INTC", "AXP", "BA", "CVX", "DD", "GE", "GS", "JNJ", "KO", "MMM", "MRK", "PFE", "TRV", "UNH", "UTX", "MCD"},
        Source = List.Accumulate(SymbolList, #table({"column1"}, {{""}}), (accum, curr) => accum & Web.Page(Web.Contents("https://finance.google.ca/finance?q=" & curr))),
        FilteredRows = Table.SelectRows(Source, each ([ClassName] = "gf-table")),
        FirstRow = Table.TransformColumns(FilteredRows, {"Data", each Table.First(_)}),
        SelectedColumnNames = {"▲▼", "Company name▲▼", "Valuation Price▲▼", "Valuation Change▲▼", "Valuation Chg %▲▼", "Valuation Mkt Cap▲▼"},
        ExpandedData = Table.ExpandRecordColumn(FirstRow, "Data", SelectedColumnNames),
        RemovedOtherColumns = Table.SelectColumns(ExpandedData,SelectedColumnNames),
        ChangedType = Table.TransformColumnTypes(RemovedOtherColumns,{{"▲▼", type text}, {"Company name▲▼", type text}, {"Valuation Price▲▼", type number}, {"Valuation Change▲▼", type number}, {"Valuation Chg %▲▼", Percentage.Type}, {"Valuation Mkt Cap▲▼", type text}})
    in
        ChangedType



    • Edited by Colin Banfield Wednesday, November 15, 2017 6:17 PM
    • Proposed as answer by Dale Hohm Thursday, November 16, 2017 11:28 PM
    Wednesday, November 15, 2017 6:12 PM
  • Hi Colin,   It works beautifully!    I appreciate your time effort!!
    • Marked as answer by eric oh Friday, November 17, 2017 5:11 PM
    Thursday, November 16, 2017 6:01 AM