Dynamic Web Query Refresh

[Nittanyblu]

How do I set up a web query in excel and make it dynamic for yahoo finance? I’d like to have a reference cell for the ticker symbol of which the web query references this cell. Therefore when the query is refeshed it reflects the stock ticker in the reference cell.

 

[Ron Coderre]

Here's one way:

• Put a stock symbol in a cell and name that cell RefSymbol
(I put BAC in that cell)

• Create a blank query in Power Query
• Edit the query in the Advanced Editor and replace ALL of the M-Code with this:

let
    Symbol = Excel.CurrentWorkbook(){[Name="RefSymbol"]}[Content]{0}[Column1],
    BaseURL = "https://finance.yahoo.com/quote/SYMBOL?p=SYMBOL",
    URL = Text.Replace(BaseURL,"SYMBOL",Symbol),
  
    Source = Web.Page(Web.Contents(URL)),
    #"Filtered Rows" = Table.SelectRows(Source, each ([ClassName] = "W(100%)")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Symbol", each Symbol),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
    Result = if Table.IsEmpty(#"Expanded Data") = false
            then #"Expanded Data"
            else null
in
    Result

• Close the query and load the data wherever you'd like.

As of this time, that query returned:

Column1             Column2         Symbol
Previous Close      30.63         BAC
Open                29.55         BAC
Bid                 28.45 x 1800  BAC
Ask                 28.74 x 900   BAC
Day's Range         28.91 - 30.30 BAC
52 Week Range       26.21 - 35.72 BAC
Volume              117,103,804   BAC
Avg. Volume         46,852,875    BAC

Is that something you can work with?

 

[DaleKeel]

I go this to work with one ticker symbol. But I have no idea how to get this to work with many symbols with one query. Could that be done with many symbols in one query? Would it be possible to have the reference cells down Column A and have the Data in rows? I have upload an example of how I think it should look like. I have home an student 2013 version of Excel.

Thanks in advance for your time and brain energy.