Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"
Thank you very much for the info on RelativePath.
Roland
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
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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |