Route Optimization in Excel using Bing Maps

CDXZipStream, our Microsoft Excel add-in for location-based data, now offers a route optimization function that uses mapping data from Bing Maps. Up to 25 destinations (waypoints) can be optimized, resulting in the most efficient order of destinations; you have the option to choose the route based on either the shortest distance or quickest time of travel, with or without traffic taken into account.

When Microsoft decided to discontinue its desktop software MapPoint in favor of the web-based Bing Maps, they unfortunately did not include route optimization capability as a built-in function. As a result, we decided to build that functionality into CDXZipStream using ant colony optimization algorithms. These are modeled on how ants in nature search out and find optimal paths to food sources, and can be applied to solving vehicle routing problems. The end result is that basic mapping and routing data are provided by Bing, but the computational algorithms for finding the optimal route reside in CDXZipStream. This results in a fast and efficient routing analysis.

CDXZIPStream route optimization output calculates travel time, distance, provides the optimized list of waypoints, directions, and can provide a route map as well. And it’s easy to use from within your Excel worksheet. To get started, right–click on an empty worksheet cell, and under the “CDXZipStream Functions” menu, select “Insert CDXRouteBing function”. You’ll see the following dialog:

The address range in the first text box represents the range of cells that contain the list of destinations to be optimized. These should be in a single line format (all in one worksheet cell) such as “123 Main Street, Springfield, USA 55555”, or can also be in latitude|longitude format, such as “39.994245|-75.082304”. Also select the route option (quickest, shortest, quickest with traffic), the desired route calculation output, and travel type.

Now click on the “Bing Maps Settings” button above to ensure route optimization is enabled, by checking the box “Optimize Routes (3 or more stops)”:

Here you also select avoidance options (such as highways and tolls), the output and route map style, whether the waypoints are validated (found in Bing Maps) before routing, and whether a map and waypoint list is included in any trip summary output. If an end point for the route is fixed, the optimized route will both begin and end with the same locations provided in the original list. If the endpoint is not fixed, optimization will select an endpoint that generally facilitates return to the beginning of the route. Once all the desired options have been selected, click “OK” to get back to the original dialog. All of these settings will be saved for future use.

When you are returned to the original dialog, click “OK” once more to start the optimization process. Once completed, the requested route calculation data will be returned to the worksheet. Here is an example of an optimized trip summary:

 Here is a short tutorial on how to perform route optimization with Bing Maps:

Using the Bing Maps route optimization feature requires a Bing Maps license key that is entered into CDXZipStream from the “Settings” option on the main CDXZipStream commandbar.   Please see the blog post How to Get a Bing Maps License Key for instructions on getting a free basic key directly from the Microsoft website, or an enterprise key from Microsoft partners.

We also offer a free Microsoft Excel template for performing route optimization with CDXZipStream, which completely automates the process for you.  It works with either Bing Maps or Microsoft MapPoint.  Here’s a quick look at how it works:

 

On a final note, CDXZipStream continues to support the desktop software Microsoft MapPoint, which does have built-in functionality to optimize 100+ destinations.  Since MapPoint was discontinued in 2013 and its data has not been updated since, it will eventually become outmoded for many applications.  But Microsoft still does provide access to the MapPoint free trial here, and for more information about how to use it with CDXZipStream, please view the short tutorial Route Optimization in Excel.

 

Add comment

Loading