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

Dustinkli

Board Regular
Joined
Mar 26, 2019
Messages
62
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
What code are you using to build URL ?

Please check below.

URL = "stockanalysis.com/stocks/" & ActiveSheet.Range("A1") & "/MSFT/financials/
 
Upvote 0
No, not in Power Query.

it's in VBA.
 
Upvote 0
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.
 
Upvote 0
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)

Excel Formula:
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 8. Edit the query.
9. Now in Power Query Editor, Goto View -> Advance Editor.
10. Change as below in first line of code.

Excel Formula:
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.
 

Attachments

  • functionDeclaration.JPG
    functionDeclaration.JPG
    34.4 KB · Views: 620
  • queryChange.JPG
    queryChange.JPG
    49.1 KB · Views: 642
  • queryName.JPG
    queryName.JPG
    21.3 KB · Views: 621
Upvote 0
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)

Excel Formula:
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 8. Edit the query.
9. Now in Power Query Editor, Goto View -> Advance Editor.
10. Change as below in first line of code.

Excel Formula:
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top