Calculate travel time and distance between two addresses using Excel + Maps API

Ever wanted to calculate distance using Excel  – between two locations (physical addresses)?

If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the distance, travel time, address points (latitude, longitude) and may be even distance matrix (given two sets of points, all distances between them)? We can use the public APIs from Bing maps or Google Maps to get the answer to our spreadsheet.

What you need:

• Free maps API from either Google Maps or Bing Maps
• Excel 2013 or above (we will be using WEBSERVICE() and FILTERXML() functions in Excel)

How to get the API Key from Google Maps or Bing Maps:

The API key process is somewhat technical and can be confusing. Plus for Google Maps API, you need to provide your credit card details (according to Google, you will not be billed automatically though). I made a small video explaining the process. Watch it below (or on our YouTube channel).

Using Excel to calculate distance & travel time between two points – Bing Maps API

As the process for getting Bing Maps API key is easy, let’s assume that is what you have.

Let’s say you have the api key in a cell named bingmaps.key

In this demo, we focus on calculating distance & travel time between one set of points, but you can use the ideas to calculate distance matrix for a range of points. For example, you can calculate travel time between all your warehouses and customer locations easily.

Start by creating a range of cells to capture origin & destination addresses. For Bing maps API, we need address to be broken in to below pieces.

Step 1: Fetch Latitude and Longitude for the addresses

Before calculating the distance, we need to know where on earth our addresses are. So we will use point lookup API to convert address to geolocation (lat&long). To do this, we call

http://dev.virtualearth.net/REST/v1/Locations?countryRegion=$1&adminDistrict=$2&locality=$3&postalCode=$4&addressLine=$5&maxResults=1&o=xml&key=bingmaps.key

with our address.

Notice all $ symbols? Use SUBSTITUTE to replace them with actual location values.

When you call this URL using WEBSERVICE(), you will get an XML output (as our output parameter is o=xml, if you omit this, you will get json).

Sample output for this looks like below:

<?xml version=”1.0″ encoding=”utf-8″?><Response xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.microsoft.com/search/local/ws/rest/v1″><Copyright>Copyright © 2018 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright><BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri><StatusCode>200</StatusCode><StatusDescription>OK</StatusDescription><AuthenticationResultCode>ValidCredentials</AuthenticationResultCode><TraceId>_REMOVED_</TraceId><ResourceSets><ResourceSet><EstimatedTotal>1</EstimatedTotal><Resources><Location><Name>Phillip St, Johnsonville, Wellington 6037, New Zealand</Name><Point><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude></Point><BoundingBox><SouthLatitude>-41.2241799</SouthLatitude><WestLongitude>174.80136</WestLongitude><NorthLatitude>-41.22166</NorthLatitude><EastLongitude>174.80196</EastLongitude></BoundingBox><EntityType>RoadBlock</EntityType><Address><AddressLine>Phillip St</AddressLine><AdminDistrict>Wellington</AdminDistrict><AdminDistrict2>Wellington City</AdminDistrict2><CountryRegion>New Zealand</CountryRegion><FormattedAddress>Phillip St, Johnsonville, Wellington 6037, New Zealand</FormattedAddress><Locality>Wellington</Locality><PostalCode>6037</PostalCode></Address><Confidence>High</Confidence><MatchCode>Good</MatchCode><GeocodePoint><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude><CalculationMethod>Interpolation</CalculationMethod><UsageType>Display</UsageType></GeocodePoint><GeocodePoint><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude><CalculationMethod>Interpolation</CalculationMethod><UsageType>Route</UsageType></GeocodePoint></Location></Resources></ResourceSet></ResourceSets></Response>

From this XML, we need to extract the LAT & LONG values highlighted in blue. We can use FILTERXML() to do that.

Let’s say the output of WEBSERVICE is in cell C21.

We can use FILTERXML() like this:

=FILTERXML(C21,”//Latitude[1]”)

=FILTERXML(C21,”//Longitude[1]”)

This will give us both lat & long values.

How does FILTERXML() work? It takes the XML value in C21 and finds the first Latitude tag (hence [1]) anywhere (hence //)

You can use FILTERXML to test the status code for the response or other interesting bits too.

Step 2: Calculate distance between two geolocations

Once we have lat & long values for both origin and destination, we can call distance lookup API to calculate distance, travel time values.

The distance lookup URL is:

https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=$1&destinations=$2&travelMode=$3&o=xml&key=bingmaps.key

For example, the distance lookup URL for above addresses is:

https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=-41.22292,174.80164&destinations=-41.27868,174.77506&travelMode=driving&o=xml&key=$k

The output for this is an XML that looks like:

<?xml version=”1.0″ encoding=”utf-8″?><Response xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://schemas.microsoft.com/search/local/ws/rest/v1″><Copyright>Copyright © 2018 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright><BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri><StatusCode>200</StatusCode><StatusDescription>OK</StatusDescription><AuthenticationResultCode>ValidCredentials</AuthenticationResultCode><TraceId>_REMOVED_</TraceId><ResourceSets><ResourceSet><EstimatedTotal>1</EstimatedTotal><Resources><Resource xsi:type=”DistanceMatrix”><ErrorMessage>Request accepted.</ErrorMessage><Origins><Coordinate><Latitude>-41.22292</Latitude><Longitude>174.80164</Longitude></Coordinate></Origins><Destinations><Coordinate><Latitude>-41.27868</Latitude><Longitude>174.77506</Longitude></Coordinate></Destinations><Results><Distance><DepartureTime xsi:nil=”true” /><OriginIndex>0</OriginIndex><DestinationIndex>0</DestinationIndex><TravelDistance>8.96955555555556</TravelDistance><TravelDuration>7.29166666666667</TravelDuration><TotalWalkDuration>0</TotalWalkDuration></Distance></Results></Resource></Resources></ResourceSet></ResourceSets></Response>

Again, we can use FILTERXML() to extract the relevant bits (=FILTERXML(C32,”//TravelDistance[1]”) and =FILTERXML(C32,”//TravelDuration[1]”))

The default output values are in KM for distance and minutes for duration. You can change this  to miles, hours etc. too by using extra parameters in the lookup URL. Please read the Bing maps developer documentation for more.

Distance & travel time in Excel – Google Maps API

Let’s say your Google Maps API key is in a cell named gmaps.key

This API is really easy to use compared to Bing maps (as we need to make just one call).

The request URL is:

https://maps.googleapis.com/maps/api/distancematrix/xml?origins=$1&destinations=$2&mode=$3&key=gmaps.key

For example, let’s lookup the travel time and distance between Microsoft & APPLE offices.

The sample URL is:

https://maps.googleapis.com/maps/api/distancematrix/xml?origins=1,+Infinity+loop,+San+Francisco,+CA&destinations=Redmond,+Seattle,+WA&mode=driving&key=gmaps.key

The response is XML (if you want json, then replace xml with json) like below:

<?xml version=””1.0″” encoding=””UTF-8″”?>
<DistanceMatrixResponse>
<status>OK</status>
<origin_address>Apple Campus, Cupertino, CA 95014, USA</origin_address>
<destination_address>Redmond, WA, USA</destination_address>
<row>
<element>
<status>OK</status>
<duration>
<value>47736</value>
<text>13 hours 16 mins</text>
</duration>
<distance>
<value>1379709</value>
<text>1,380 km</text>
</distance>
</element>
</row>
</DistanceMatrixResponse>

We can FILTERXML this response to extract the important bits like this:

=FILTERXML(C15,”//distance[1]/text”)

=FILTERXML(C15,”//duration[1]/text”)

Download distance calculator template

Click here to download distance, travel time calculator template to see all these formulas in action. You must enter your API key to get it work. Examine the formulas and XML formats to learn more about how these APIs work and how to integrate them to your spreadsheet models.

More examples of WEBSERVICE():

• Finding nearest zip codes using webservice() and filterxml()
• Integrating WEBSERVICE() with trip planner spreadsheet [Microsoft blog]

Made something cool with WEBSERVICE()?

Did you make something cool and fun using WEBSERVICE() and FILTERXML()? Please share the ideas and tips in comments section.