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

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
49
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
414
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
What code are you using to build URL ?

Please check below.

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

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
49
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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

Active Member
Joined
Jun 6, 2020
Messages
414
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
No, not in Power Query.

it's in VBA.
 

Dustinkli

New Member
Joined
Mar 26, 2019
Messages
49
Office Version
  1. 365
  2. 2011
Platform
  1. Windows
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

Active Member
Joined
Jun 6, 2020
Messages
414
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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: 12
  • queryChange.JPG
    queryChange.JPG
    49.1 KB · Views: 12
  • queryName.JPG
    queryName.JPG
    21.3 KB · Views: 12

Watch MrExcel Video

Forum statistics

Threads
1,130,089
Messages
5,640,052
Members
417,124
Latest member
Herostrata

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
Top