Changing a URL in a Web Data Query based on a cell

[Dustinkli]

I'm doing a basic web query in Microsoft Excel with the following url:

stockanalysis.com/stocks/MSFT/financials/

But I have spent the past few hour searching but none of the tutorials or explanations seem to work for me. I'm not sure what I'm doing wrong.

What I'd like to do is make it so the web query will change depending on the input in a specific cell (A1 in this case) so that if I input "FB" it will change the url to stockanalysis.com/stocks/FB/financials/ and then I refresh the data and it will pull up the financials for Facebook opposed to Microsoft.

Any tips or assistance would be greatly appreciated.

 

[Saurabhj]

What code are you using to build URL ?

Please check below.

URL = "stockanalysis.com/stocks/" & ActiveSheet.Range("A1") & "/MSFT/financials/

 

[Dustinkli]

What code are you using to build URL ?

Please check below.

URL = "stockanalysis.com/stocks/" & ActiveSheet.Range("A1") & "/MSFT/financials/

Are you referring to in Power Query?

 

[Saurabhj]

No, not in Power Query.

it's in VBA.

 

[Dustinkli]

The way that I was doing it was going to Data>

No, not in Power Query.

it's in VBA.

The way that I was doing it was going to Data> Get & Transform Data area> From Web and then inputting the URL. That's where I'm getting tripped up.

 

[Saurabhj]

Hi,

Do following steps in Power Query:

1. Give a name to cell A1, for example myRange.
2. In Power Query tab, add a blank Query (Get Data -> From Other Sources)
3. Now in Power Query Editor, Goto View -> Advance Editor.
4. Add below code. (see image)

let GetNamedRange=(NamedRange) =>

let
    name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
    value = name{0}[Column1]
in
    value

in GetNamedRange

5. Click Done button.
6. Change the Query Name (e.g. functionGetNamedRange) (see image)
7. Now create another query ((Get Data -> From Other Sources) -> From Web) for accessing webpage

Microsoft (MSFT) Financial Statements: Income | Stock Analysis

Detailed financial statements for Microsoft stock (MSFT), including the income statement, balance sheet, and cash flow statement.

stockanalysis.com

8. Edit the query.
9. Now in Power Query Editor, Goto View -> Advance Editor.
10. Change as below in first line of code.

Source = Web.Page(Web.Contents("stockanalysis.com/stocks/" & FunctionGetNamedRange("myRange") & "/financials/")),

11. Click Done.
12. Load the query.
13. Write the name in A1 and refresh the query.

 

[researcherjake]

Hi Saurabhj,

This solution works fantastically, but I have a question.

How can I add a second dynamic parameter?
I tried both adding a second term and a second function, but receive the error: "Expression.Error: We cannot apply operator & to types Text and Number"

Hi,

Do following steps in Power Query:

1. Give a name to cell A1, for example myRange.
2. In Power Query tab, add a blank Query (Get Data -> From Other Sources)
3. Now in Power Query Editor, Goto View -> Advance Editor.
4. Add below code. (see image)

let GetNamedRange=(NamedRange) =>

let
    name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
    value = name{0}[Column1]
in
    value

in GetNamedRange

5. Click Done button.
6. Change the Query Name (e.g. functionGetNamedRange) (see image)
7. Now create another query ((Get Data -> From Other Sources) -> From Web) for accessing webpage

Microsoft (MSFT) Financial Statements: Income | Stock Analysis

Detailed financial statements for Microsoft stock (MSFT), including the income statement, balance sheet, and cash flow statement.

stockanalysis.com

8. Edit the query.
9. Now in Power Query Editor, Goto View -> Advance Editor.
10. Change as below in first line of code.

Source = Web.Page(Web.Contents("stockanalysis.com/stocks/" & FunctionGetNamedRange("myRange") & "/financials/")),

11. Click Done.
12. Load the query.
13. Write the name in A1 and refresh the query.

 

[alansidman]

Here is a link to learn how to do parameter queries provide by MrE himself