Forum Discussion

BoaRoland's avatar
BoaRoland
Regular Visitor
2 years ago

Need help with Power Query web/URL

I have the below Power Query which results in a table with financial data from Yahoo Finance for 3 specific securities (Microsoft, Apple, and Disney in this case). I want to build a solution which looks at another worksheet which has a list of security symbols; the solution would then change the below query to match the security symbols in that worksheet. To clarify, neither the end user not I as the developer should have to "manually" modify the query. 

I have not found a way to "code this in VBA". Can you point me in the right direction or point out books, blogs, Youtubes, etc.

Thank you all in advance.

 

Here is the query M-code (I did build this with the PQ GUI):

let
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quotes/MSFT,AAPL,DIS/view/v1")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Symbol", type text}, {"Last Price", type number}, {"Change", type number}, {"Chg %", Percentage.Type}, {"Currency", type text}, {"Market Time", type text}, {"Volume", type text}, {"Shares", type text}, {"Avg Vol (3m)", type text}, {"Day Range", type text}, {"52-Wk Range", type text}, {"Day Chart", type text}, {"Market Cap", type text}, {"", type text}})
in
#"Changed Type"

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    you need to use RelativePath,  and construct the symbol list separately (or source it from your Excel etc)

     

     

     

     

    let
Symbols = {"MSFT","AAPL","DIS"},
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quotes/",[RelativePath= Text.Combine(Symbols,",") & "/view/v1"])),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Symbol", type text}, {"Last Price", type number}, {"Change", type number}, {"Chg %", Percentage.Type}, {"Currency", type text}, {"Market Time", type text}, {"Volume", type text}, {"Shares", type text}, {"Avg Vol (3m)", type text}, {"Day Range", type text}, {"52-Wk Range", type text}, {"Day Chart", type text}, {"Market Cap", type text}, {"", type text}})
in
#"Changed Type"

     

     

    Here's all the documentation you will ever need 🙂  Web.Contents - PowerQuery M | Microsoft Learn

    • BoaRoland's avatar
      BoaRoland
      Regular Visitor
      2 years ago

      Thank very much lbendlin,

      Can I use a named range in my Excel to hold the symbol list and then reference that named range in the Power Query code? I'm still fairly new with M-syntax/code.

      How can I use the set the variable "Symbols" to equal the named range "Secs"?

      Or maybe I should refer to the named range in the Text.Combine part??

      I tried various syntax, but all resulted in errors.

      Thank you very much in advance.

      Roland

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago
        Can I use a named range in my Excel

        The preferred way is to use a table. Then use Text.Combine on the relevant column of that table source.

  • BoaRoland's avatar
    BoaRoland
    Regular Visitor
    2 years ago

    Thank you very much for the info on RelativePath.

    Roland