Importing Azure Marketplace Data into Excel 2013 with Web Queries

A few weeks ago, when I was playing around with the new WebService() function in Excel 2013, I was disappointed to learn that it didn’t work with services that require authentication – a pretty big limitation in my opinion – so, for example, it meant I couldn’t use it to import data from the Azure Marketplace into Excel. You might be wondering why I’d want to do this, when there’s already built-in functionality for importing Azure Marketplace data into Excel 2013 and an addin to do this for earlier versions of Excel; the reason is that I want to be able to dynamically construct the url used to call the service inside the spreadsheet. For example, in this post I’m going to be using the Bing Search API and I want to be able to enter the search term I pass to it in a cell in a worksheet rather than hard-code it in the connection, which is what happens when using the native functionality. I’m sure I could use some VBA to do the same thing but using VBA always seems like an admission of defeat to me, so in this post I’m going to show how you can use Excel Web Queries to do it instead.

So let’s start with the Bing Search API, one of the many APIs and datasets available via the Windows Azure Marketplace. There are plenty of posts explaining how to use the Azure Marketplace website to import data into PowerPivot (see here for example) so I won’t go into too much detail at this point, but here’s an example Url that returns the top 15 news results for the search term ‘Microsoft’:

https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27microsoft%27&$top=15&$format=Atom

Next, you need to fire up Excel 2013 and create a Web Query, a feature that’s been in Excel since ohhhh, Office 2000. There are a lot of good articles on the web describing how this feature works but here’s one of the best I found:
http://www.vertex42.com/News/excel-web-query.html

Specifically, you need to go to the Data tab and click the From Web button:

This opens the New Web Query dialog; you then paste the Url from the Azure Marketplace into the Url box and click go. You’ll be prompted for a username and password, and you need to enter your Azure Marketplace account key for both. Click on the small yellow arrow in the top left hand corner of the browser window and you’ll see something like this:

Do not press Import at this point though! Instead, press the Save Query button in the toolbar (highlighted in the screenshot above) and then save the query to disk as an .iqy file and click Cancel. Next, find the .iqy file you’ve just saved and open it in Notepad. The contents will be something like this:

WEB
1
https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27microsoft%27&$top=15&$format=Atom

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

What you need to do now is to parameterise the Url in this file so that, instead of hard-coding it to search for news about microsoft (as in this example) you can enter your own search term. The link above describes how to do this in detail, but here’s the parameterised version of the Url for reference:

https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27%5B"Query", "Search For:"]%27&$top=15&$format=Atom

You then need to save the file and go back to Excel.  First, enter a search term in a cell in a worksheet. Then, go to the Data tab, click the Existing Connections button and then the Browse For More button and select the .iqy file. The next step is to choose a cell in a worksheet to dump the data to, and then when prompted for the parameter value click on the cell where you entered your search term and check the Use This Value/Reference For Future Refreshes and Refresh Automatically When Cell Value Changes boxes.

Click OK and you’ll be prompted for credentials again – although Excel will remember them – and the query will run, resulting in some XML appearing in the cell you selected:

This is ugly, and indeed Excel has the ability to import XML returned from a Web Query as XML – but what I’ve found is that if you don’t use the approach I’m showing here, you won’t be able to refresh you query properly.

So you now have our search results as XML and you want to be able to display them in a more meaningful way. This is where the new FilterXML function in Excel 2013 comes into its own. Create an Excel table with four columns: Rank, Title, Description and Url; then enter numbers from 1 to 15 in the Rank column (there are fifteen results returned from the Bing search). You can then use Excel table formulas like this one to get the Title, Description and Url from the XML and display it in the table:

=FILTERXML($B$4, "//feed/entry[" & [Rank] & "]/content/m:properties/d:Description")

(I’m very grateful to Phil Quinn, who I met at SQL Saturday 162, for helping me with the XPath here – I spent ages trying to get it to work with no luck and he managed to provide a working query in 2 minutes)

With this done, you now have the ability to enter a search term in your worksheet and automatically get Bing search results displayed in an Excel table. Of course, this approach would work with any dataset from the Azure Marketplace and indeed any OData source – for example, now that SSRS 2008 R2 and greater can render reports to OData, it should be possible to consume data from an SSRS report in a much more elegant way than the method I described here, because you’d be able to pass parameters from Excel to SSRS (hmmm, maybe this needs its own blog post). Of course, now you have a table in Excel you can add it to the Excel Model and do all kinds of interesting PowerPivot-y things with it.

What this really highlights, though, is that it should be much easier to parameterise the queries used to load data into the Excel Model/PowerPivot, whether they are OData data sources, SQL, MDX or DAX. Not being able to parameterise these queries means you increase the temptation to load all the data that might ever be needed by a user into PowerPivot; if it were easier to parameterise these queries then it would encourage PowerPivot users to build solutions where they only imported the data they actually needed to work with at any given moment.