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

Excel Howtos - 13 comments

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

calculate distance using Excel - Distance and travel time between two places using formulas + Maps API

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.

data format - geolocation lookup - bingmaps api

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.

format for address - google distance matrix api

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():

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.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or may be join my online video class to master Excel.

Thank you and see you around.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

13 Responses to “Calculate travel time and distance between two addresses using Excel + Maps API”

  1. José Lôbo says:

    Following Google (https://developers.google.com/maps/documentation/javascript/get-api-key), you could give your key:
    Get API Key
    To use the Maps JavaScript API, you must register your app project on the Google Cloud Platform Console and get a Google API key which you can add to your app.
    Would you please?

  2. Amit Jalan says:

    Thank You so much... It's Really Helpful. We have HTTP Api for Sending SMS and Getting status of the same. With the help of WebService Function, It's become very easy to update the status.

  3. Trevithick says:

    Back in 2012 I attempted to create a Great Circle Calculator with Excel based on examples at ExcelAutomate.

    I was able to calculate bearings, distances, and so forth, but never could get them to plot properly.

    I would love to see you take your disctance and time calculating capability and tweak it to produce Great Circle plots.

  4. FrankT says:

    Is there a way to make this stuff culture aware?

    Currently the nice file provided is useless if your decimal separator is a comma.

    Using Bing I tried adding "&culture=de" but that didn't help.

  5. Karl Mueller says:

    How do I change output to miles?

    • Chris Hanson says:

      For the Microsoft API insert "&distanceUnit=mi" into the Distance Lookup URL

      • Chandoo says:

        Good suggestion Chris. I believe Google Maps API too has a parameter to set units for the output values. You can also use Excel's own CONVERT function to do this - =CONVERT(90,"km","mi")

  6. Kanyalal Bhatia says:

    Dear Chandoo,
    This is Awesome, could u please Tweak a little to add the function such as :
    1. drop down to calculate time value in any $ range 10 to 20.
    2. To calculate road/Bridge Tolls
    3. Drop down Cost per mile using range $1 to 3
    4. To select one way or round trip cost.
    5. To cal calculate weight of package more than 5 lbs @ rate $ 0.10.
    This will be a snap for u to tweak, I currently calculate manually using Excel.
    Thanks for all the help u provide to excel community
    kanu
    2156774149.

Leave a Reply


« »