excel - PowerQuery use a cell values in a URL

Question

I have the following PowerQuery code. Which collects some data from MSFT in yahoo finance.

I have a cell A1 for example which I would like to put MSFT, GOOG or another symbol. How can I change it so that when I change A1 it will automatically redownload for the new symbol?

let  Quelle = Web.Page(Web.Contents("https://finance.yahoo.com/q/bs?s=MSFT+Balance+Sheet&annual")),

    Data = Quelle{1}[Data],

    FirstRowHeader = Table.PromoteHeaders(Data)

in  FirstRowHeader

Using; $A$1 did not work.

https://finance.yahoo.com/q/bs?s=$A$1+Balance+Sheet&annual

Answer 1

As explained in the answer to this question, you can load in a cell value if you make it a named range.

The first step is to name your cell A1. Let's name it Ticker. You can name it by typing in the name in the upper left box when the cell is selected

Then your query will look like this:

let
    Ticker = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
    Quelle = Web.Page(Web.Contents("https://finance.yahoo.com/q/bs?s="&Ticker&"+Balance+Sheet&annual")),
    Data = Quelle{1}[Data],
    FirstRowHeader = Table.PromoteHeaders(Data)
in  FirstRowHeader