Create a trip calculator with Excel and web services

Using Excel, you can create a trip calculator that uses web services to provide dynamic information that is up to date. I created this post after reading a post with a similar theme from Glen Gilchrist. In the example below, you’ll see that you can create the trip calculator using Excel functions and without needing to write any code.

Note: The illustrations below will work with Excel versions 2003 – 2010 by installing the Excel PowerUps Premium Suite. If you have Excel 2013, you can create this trip calculator without the add-in — just remove the “pwr” prefix from the function names in the examples below. Excel 2013 already has built in WEBSERVICE and FILTERXML functions!

Here’s the gist of how we’ll put this together

For the calculator, we’ll set up 3 user input cells.

1. Your starting point
2. Your destination
3. Your average gas mileage

We’ll make two webservice calls.

1. Call to Google’s Distance Matrix API (https://developers.google.com/maps/documentation/distancematrix/)
2. Call to FuelEconomy.gov’s fuel prices API (http://www.fueleconomy.gov/feg/ws/index.shtml#fuelprices)

We’ll parse the XML to pull out Drive Time, Total Miles, and Average Price/Gallon.

The image below shows what a finished worksheet might look like. I used the pwrFORMULATEXT function in column D to help illustrate the actual function calls being used.

Let’s get started

Below I’ll give step-by-step notes on how I set up the functional part of the worksheet and note the Excel 2013 equivalent functions. I’m going to jump around on the sheet so that hopefully the data you’re gathering makes sense as you build up the sheet.

1. In cell A2, enter the label “Enter Starting Place”. Add a box and/or shading to cell B2. This is where you’ll enter your starting point, which can be in the form of City, State or even a full address.
2. In cell A3, enter the label “Enter Destination”. Add a box and/or shading to cell B3. This is where you’ll enter your ending point. This is where you’ll enter your ending point, which can be in the form of City, State or even a full address.
3. In cell A15, enter the following formula. This will call the Google maps API using the pwrWEBSERVICE function and pass the start and end points using the pwrURLENCODE functions.

   • pwrWEBSERVICE("http://maps.googleapis.com/maps/api/distancematrix/xml?origins="&pwrURLENCODE(B2)&"&destinations="&pwrURLENCODE(B3)&"&mode=driving&language=en-us&sensor=false&units=imperial")

   • In Excel 2013 use the following instead.

     • WEBSERVICE("http://maps.googleapis.com/maps/api/distancematrix/xml?origins="&ENCODEURL(B2)&"&destinations="&ENCODEURL(B3)&"&mode=driving&language=en-us&sensor=false&units=imperial")

4. In cell A16, enter the following formula. This will call the FuelEconomy.gov’s fuel prices API.

   • pwrWEBSERVICE("http://www.fueleconomy.gov/ws/rest/fuelprices")

   • In Excel 2013 use the following instead.

     • WEBSERVICE("http://www.fueleconomy.gov/ws/rest/fuelprices")

5. In cell A5, enter the label “Drive Time”.
6. In cell B5, enter the following formula to get the drive time from the XML returned from the web service.

   • pwrFILTERXML(A15,"/DistanceMatrixResponse/row/element/duration/text")

   • In Excel 2013 use the following instead.

     • FILTERXML(A15,"/DistanceMatrixResponse/row/element/duration/text")

7. In cell A7, enter the label “Total Miles”.
8. In cell B7, enter the following formula to get the total distance in miles between the start and end points. The value returned also is being converted from meters to miles.

   • pwrCONVERTDISTANCE(pwrFILTERXML(A15,"/DistanceMatrixResponse/row/element/distance/value"),"m","mi")

   • In Excel 2013 use the following instead.

     • CONVERT(FILTERXML(A15,"/DistanceMatrixResponse/row/element/distance/value"),"m","mi")

9. In cell A8, enter the label “Enter your average fuel economy”. Add a box and/or shading to cell B8. This is where you’ll enter your average fuel mileage.
10. In cell A9, enter the label “Average price/gallon”.
11. In call B9, enter the following formula to get the average price/gallon from the XML returned from the web service. The data is returned from this web service as text so you’ll also need to use the VALUE function to convert the text to a numeric value.

    • VALUE(pwrFILTERXML(A16,"/fuelPrices/regular"))

    • In Excel 2013 use the following instead.

      • VALUE(FILTERXML(A16,"/fuelPrices/regular"))

12. In cell  A11, enter the label “Trip Fuel Cost”.
13. In cell B11, enter the following formula.

    • B9*(B7/B8)

Watch it below

Give it a try for yourself

At this point you should be able to enter a starting point, ending point, and your average MPG and the worksheet will give you the distance, drive time, and fuel costs for the trip. Have fun and try it out!

There you go.

Here are some related posts:

Work Beyond Excel’s 32k Per-Cell Character Limit Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions How you can add the 50 New Functions in Excel 2013 to your old copy of Excel How you can use an add-in to make a quick-and-dirty sentiment analysis spreadsheet with Excel How to use Excel to test a list of URLs or links to see if any are broken