Bringing Location Information Into Excel 2013 With The WebService() Function

Last summer I wrote a post about the new WebService() function in Excel 2013 and showed how it could be used to display data from the BBC Weather web service in a worksheet. I hadn’t thought about it much after that, but the other day I was working with a customer who wanted to work out how far people had to travel to visit a location and I wondered whether this was something that could be achieved with the WebService() function. It turns out that it can be – once you’ve worked around some of the quirks of WebService() – so I thought I’d post a couple of worked examples of how to use Bing Maps and Google Maps REST APIs to solve this kind of problem in Excel.

Let’s start with Bing, because after all I’m still a Microsoft loyalist and it does get some good reviews! Before you can use the Bing Maps REST API you need to create an account key (which is free), which you can do here:
https://www.bingmapsportal.com
You’ll need to use this in all your API calls. Now, what can we do with the Bing Maps REST API? Well, lots of things as you can see here:
http://msdn.microsoft.com/en-us/library/ff701713.aspx
Geocoding (finding a latitude and longitude for an address so you can plot it on a map) is a popular thing to do, but actually in Excel it’s not so important because all of the options for mapping in Excel 2013 such as Power View, GeoFlow (Jason Thomas did a good post on it recently) and the Bing Maps Excel 2013 app, all do it for you automatically. So I decided to show how to do reverse geocoding instead – looking up an address from a given latitude and longitude – which is also possible with the Bing Locations API. Here’s how:

In cell C3 I have pasted my Bing Maps API key. C5 and C6 contain the latitude and longitude I want to search for. B8 contains the formula with the WebService() function that calls the API and returns the result as XML:
=WEBSERVICE("http://dev.virtualearth.net/REST/v1/Locations/" & C5 & "," & C6 & "?o=xml&key=" & C3)

C10 uses the FilterXML() function to retrieve the address from the XML:
=FILTERXML(B8, "/Response/ResourceSets/ResourceSet/Resources/Location/Name")

Easy-peasy. Another common problem is that one I mentioned earlier about calculating the distance between two points. There are a few examples out there of how to calculate direct distances (like this one I saw halfway through writing this post), but direct distances aren’t all that useful – what you really need is the time needed to drive or walk somewhere along the available roads. Bing has a web service that provides route information and it works perfectly, but I found that the WebService() function returned errors in some cases – I suspect because the XML returned by Bing was too long, but I’m not sure. Anyway, Google has a similar API (with no key needed, at least for light usage) that returns only distances and not detailed routes, and WebService() seems to like it better. Here’s an example:

Here, I’m entering a start address and an end address in cells C2 and C3, and then calling the Google Distance Matrix API as follows:

=WEBSERVICE("http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & ENCODEURL(C2) & "&destinations=" & ENCODEURL(C3) & "&mode=driving&sensor=false")

Then, I’m using FilterXML() to get the text of the driving distance and duration in cells C7 and C8 from the XML returned:

=FILTERXML(B5, "/DistanceMatrixResponse/row/element/distance/text")

=FILTERXML(B5, "/DistanceMatrixResponse/row/element/duration/text")

You can download a sample workbook that contains both these examples, minus my Bing Maps API key, here.

One final thing to mention is that the WebService() function seems to be horrendously slow, so you might not be able to use it to make a large number of calls very quickly, although many of these services allow you to make multiple lookups in a single API call. Also, remember that there’s usually a limit on the number of calls you can make to these APIs without paying.