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

Excel Howtos - 24 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 join my online video class to master Excel.

Thank you and see you around.

Related articles:

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

24 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.

  7. Jonathan says:

    I've generated a Bing key and inserted it at the right place but the spreadsheet doesn't seem to work for me:Most fields show " #NAME? ". Any idea what the problem might be?

    Cheers 🙂

  8. Hans says:

    Thanks a lot for this guide, it really helped!

    Since it is much easier to get an API Key for Bing, i am using this alternative. Is there way to calculate the distance and time between two coordinate avoiding tolls? (i used "&avoid=tolls&" in the URL, but it is not working).

  9. Donald Claessens says:

    Hello Chandoo

    In the example you calculate the time as a decimal number. How can ik convert this to the timeformat h:mm:ss

    • Hui... says:

      @Donald

      Select the cell/s
      Right click and select format Cells or Press Ctrl+1
      Select the Number Format tab
      Select the Custom Format Tab
      In the Custom Format Dialog enter h:mm:ss or [h]:mm:ss
      apply

      The [] mean that if the number is say 27 hrs it will display as 27 not as 3

  10. Fitch says:

    I use LibreOffice Calc, and having no luck in getting the travel distance with a filterxml.
    =FILTERXML(C32,”//TravelDistance[1]”)
    brings up #N/A.

    I tried:
    FILTERXML(C32,"//ResourceSets/ResourceSet/Resources/Resource/Results/Distance/TravelDistance")
    Again, I just get #N/A

    Could you explain how it's meant to be structured?
    Thanks

    • Fitch says:

      Got the answer from the LibreOffice forum.
      Apparently, you have to take the XML's "default namespace" into account
      FILTERXML(S3,"//*[local-name() = 'TravelDistance']")
      That worked for me.

  11. Anne says:

    Hello Chandoo

    I'm using the Bing Maps API. I've a question about the results of the formulas of coördinates, km and minutes. The calculationsheet gives a traveldistance and travelduration of -1. Also the coördinates aren't right. For example, I've got 532191 instead of 53.2191. Can you explain how this is possible?

    Thank you!

  12. Tim King says:

    Chandoo,

    Thank-you for your article, which has been a great help.

    It is frustrating that these solutions do not work on Excel for Mac, but I have Parallels and Excel for Windows running.

    Also to say that to get distance out, I used

    =filterXML(cell,"//row/element/distance/value") for metres, and then converted to miles.

    Best regards,

    Tim.

  13. Varun Raj Singh says:

    Hi Chandoo,

    Thanks for this. I used your google maps sheet to re-create one with columns based on my need. I procured the API key, but when I use it (either on your sheet or the one I created), the status reads REQUEST_DENIED.

    This is possibly nothing to do with the sheet itself, but any suggestions on how I can rectify this?

Leave a Reply


« »