This is the second part of the getting directions and travel info using Bing Maps. The first part shows how to do it in Python (check it out here), and in this part, I’ll share some tips on how to get the same info from within Excel without writing any VBA code!
To get the latitude and longtitude of a location by address, the URL we need has to be as following:
https://dev.virtualearth.net/REST/v1/Locations?countryRegion=&adminDistrict=WA&locality=&postalCode=98012&addressLine=1 main street&maxResults=1&o=xml&key=bingAPIKey
where WA is the state, postal code is 98012 and address Line will contain the street address, and bingAPIKey is the key you’ve been given by Bing service. Also, here we’re asking for a response in XML format. Remember, in part 1, we used JSON.
So, in Excel, if we have a table such as below,
we just have to get the cell values from the origin column by referencing the cells and concatenate the values to form the above URL. That URL will return latitude and longitude information. So, how do we call the RESTful API? In Excel, WEBSERVICE() does the trick. It takes just one parameter, the URL. In this case, the URL we just formed above. If we have that URL is A1 for example, and we put the call function WEBSERVICE(A1) in C10, we’ll the get the whole response in XML format in C10. It looks like this:
Out of this, we only need the values in <Latitude> and <Longtitude> nodes. In Excel, FILTERXML() can parse and extract what we need. So, =@FILTERXML(C10,"//Latitude[1]") will extract from it the latitude value from the XML response in C10. Notice that it’s returned as an array and we just want the first item. (In Excel, array index starts at 1, in Python it starts at 0).
Now, we repeat the same process for Destination values provided in the table above and get its latitude and longitude information.
So far we just got the latitude and longitude values of the starting point and destination point. Next, we need to call another API to get the travel time and distance information. The API needs to be in this format: https://dev.virtualearth.net/REST/v1/DistanceMatrix?origins=latitutudevalue1,longtitudevalue1&destinations=latitutudevalue2,longtitudevalue2&travelMode=driving&o=xml&key=bingAPIKey
where latitutudevalue1 is the latitude value we got by FILTERXML() above for the origin, and latitudevalue2 is the latitude value we got by FILTERXML() above for the destination, and so forth. Notice the travelMode is set to “driving”. Other values we can use are: “walking”, “transit” and the API will give us the values for those transport modes. When decomposed, the URL in this example looks like this: https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=47.8581391010564,-122.220323017774&destinations=47.6473652,-122.1391327&travelMode=driving&o=xml&key=bingAPIKey
What happens if you copy this and paste in your browser now? Right, you’ll get an XML response but not the info. You’ll most likely get among other things an item: <AuthenticationResultCode>InvalidCredentials</AuthenticationResultCode> Why? Because you need to supply the actual key that was given to you and replace the string “bingAPIKey” with it. If the key is provided, the XML response we get is:
Perfect! We have all the required information. All we have to is parse it like we did with the latitude and longtitude using FILTERXML(). In this case, we want to extract TravelDistance and TravelDuration values (so pass “//TravelDistance[1]” as the 2nd argument in FILTERXML() call to get travel distance, and “//TravelDuration[1]” for travel time. These are returned in kilometers and minutes respectively (remember, the duration in JSON was in seconds as I showed in part 1). So, we can easily convert the km to miles. And putting them in a table, it may look like this:
Now, you know how to call any RESTful APIs from Python as well as in Excel. The concepts are the same in whatever language you code so it’s important to grasp the reasonings behind each step and what’s happening in the payload and how to digest it properly for further calculation. Hope you found this informative.
▛Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino! 
▟