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:
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:
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("" & 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("" & 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.

12 responses

  1. Pingback: Útvonaltervezés Excellel - Kővári Attlia szakmai blogja - devPortal

  2. Pingback: Calling A Web Service From Data Explorer, Part 1 | Chris Webb's BI Blog

  3. Hi Chris,

    I have been trying to solve an issue where webservice has stopped pulling in my XML data.

    I have the following formula:

    =+WEBSERVICE(+CONCATENATE(“”,H1295,”.xml”)) where H1295 is cell containing a number saved as text 08057982. this pulls data from Companies House which I can then use FilterXML to pull data from.

    However having previously worked the formulas now return #Value once the sheet is updated for all companies.

    The URL appears to still give the XML data when typed as a hyperlink into excel, and I’ve scoured the internet looking for an answer but no joy and your blogs are very useful so thought I would ask! Hope you don’t mind.

    I’m fairly new to WEBSERVICE so I may be missing something fairly simple.



    • Hi Dan,

      This sounds weird, but to be honest I’ve seen lots of weird behaviour with the Webservice function. I have no idea what the problem might be I’m afraid… my recommendation, if you’re calling a web service, is to use Power Query to call the web service – it’s a lot easier to work with.


      • Hi chris,

        Thanks for looking at it. Will look into power query.

        Appreciate the help.



  4. Hi Chris, this is a great post. I love the details! I was curious if you have a post on how to pull Google Maps location data like name, address and phone for all businesses in a certain city? I see some people asking how to do it but can’t find an article on it.

  5. Hello Chris,
    I have been using Excel for getting the distance between two addresses” = webservice” for a long time. But now I am starting to use Excel online. And I found out that webservice function does not work for excel online. Is there a different formulas that I can use? And thank you.

  6. Pingback: Excel Dynamic Arrays And FilterXML « Chris Webb's BI Blog

Leave a Reply to umairali111 Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: