Web Query with dynamic URL, parameter based on cell value

**Graham W** · 11-21-2017, 05:58 PM

I would like to import data from a website. I have successfully created the query with a static URL. The URL is: https://coinmarketcap.com/currencies...8&end=20171121

Tomorrow I would like to be able to just hit refresh to update the table with the new row of data. However, tomorrow the URL will be: https://coinmarketcap.com/currencies...8&end=20171122

So I need a way to have the URL of the Web Query depend on a parameter, which is in turn set at the value of a cell. (I will use a cell formula to create a cell containing the current date in YYYYMMDD format).

Having searched, all I can find was a way to make Excel prompt the user for a parameter when the query is run, which is not what I want, because I will always want it run with the current date in the URL. (By the way, I was not able to follow the instructions given to do even this; I need it properly spelt out, as I am new to web queries, and my VBA knowledge is intermediate.)

I am using Excel 2016 and Windows 10.

Can anyone help me out?

**Graham W** · 11-21-2017, 06:02 PM

I just realised I can do this simply by putting a date way out into the future in the URL. Still, it would be good to learn how to do this in case it comes up again.

**AB33** · 11-21-2017, 06:09 PM

Yes, you can append the parameter from a cell

URL="https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=" & cell value

Since this appears to be a table, your best bet is to use excel's built-in table export from a web. The interface in excel has changed in 2016, so I can not longer access it, but there is a better function called Get and Transform. It is actually much better than the old version. You can include parameters with the URL. The advantage of export method is you can refresh the data.

**Graham W** · 11-21-2017, 07:33 PM

Thank you. I will try that tomorrow and get back to you.

**Graham W** · 11-22-2017, 05:03 PM

I've not made any progress.

Yes, you can append the parameter from a cell

URL="https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20130428&end=" & cell value

I don't understand where I am supposed to enter that.

Currently, I click on the Data tab, then from the Get & Transform group, I click on New Query > From Other Sources > From Web.

A simple pop-up box appears, with one input box, for a URL. That input box won't let me refer to any cell. After pasting in my URL and clicking OK, it makes the connection, and then brings up a list of everything I can import from the page. In this case, there is only one table (Table 0), so I select that, and then I can edit or load the table. In the window for editing the table, I can't find any option for editing the URL. If I want a different URL, it appears I have to start again.

Since this appears to be a table, your best bet is to use excel's built-in table export from a web. The interface in excel has changed in 2016, so I can not longer access it, but there is a better function called Get and Transform. It is actually much better than the old version. You can include parameters with the URL. The advantage of export method is you can refresh the data.

That sounds like what I am doing, but I can't see how I can include parameters in the URL.

There is another method I have tried. From the Get External Data group, I click on From Web. This brings up a mini-browser window showing a webpage (it defaults to MSN.com) and asking me to click on the table I want to import. However, if I paste my coinmarketcap URL in here, I get 6 error messages about "the script on this page", asking me if I want to continue running scripts. Whether I answer yes or no, the page loads in the mini-browser, but then I can't click on the table I want. So this method was a non-starter, as I can't even make it work with a static URL.

As I said, my workaround (entering a date way off in the future) works perfectly, so it is not urgent that I solve this problem, but any further help would be appreciated so I can learn more.

**AB33** · 11-22-2017, 05:33 PM

I did have a go. I have recorded the macro, using a macro recorder- right from the start to end. "Get and Transform" is part of "Power Query", not excel and uses the "M" language. You can see what the "M" language looks like by going in to advance editor.
This is what happens when you see the recorded Marco.
The entire "M" language is added or embedded in to VBA. You can change the parameters (i.e., the cell value) in the VBA editor.
The language looks horrible. If you are not familiar with VBA and M, I suggest, you should look at some on-line sources. I seem to remember one of this forum's member posts a link to a site which deals with M language.
I have not played around with the "M" language myself.

**Graham W** · 11-22-2017, 07:58 PM

Good idea to record a macro of it. Here is the VBA code it generated:

Please Login or Register  to view this content.

Now, if I can just replace the 20171122 in the URL with a VBA variable, I should be able to figure out the rest. How do you tell VBA "what you are about to read is a VBA variable"?

**AB33** · 11-23-2017, 08:26 AM

Please wrap your code with code tags as per forum's rule.
Click edit on the above thread
Highlight the entire code and then
There is a sub menu (at the bottom of the screen)
Select(Click) the hash(Pound) sign.

I have tried to adjust the code, but end up with lots of errors and connection strings. The code has created connection queries for each request. If the request exists, the code complains. I have never tried this crap "M" language. I will though at some point, but for now, I am gone back to the old way of getting tables from a site. As you can see from the attached, the code works and you can also refresh it. I think the functionality with Power query is similar. I have not incorporated the start date. The end date comes from column A, starting from row 2. You should enter a normal UK dates. Thesheet name is sheet1, so if your sheet name is different, you need to change it.