In the past we’ve mentioned customization services for our Microsoft Excel add-in CDXZipStream, and we’d like to take the opportunity here to discuss some examples of how customization may help better support your business needs around address, zip code, and data analysis in general. 

Do a One-Time Analysis:  

Need to use CDXZipStream functionality just once?  If you are not interested in purchasing the software for continuing use, we can provide the hard data that fills your specific need.  This is often an economically attractive alternative, particularly in lieu of the more expensive demographic versions of CDXZipStream.     

Get a Custom Excel Template:  

We can build pre-formatted templates that perform specific tasks around existing CDXZipStream functions, often combining multiple functions from CDXZipStream and Excel to get the final required answer.  Tasks can usually be fully automated so that after data entry, a simple click of a button is all that’s required of the user.  We currently offer free, downloadable templates that perform basic analyses, such as geocoding (getting latitude/longitude data), route optimization, radius analysis, and more.  But if you have a more complex task, consider a customized template.  This is also a great option if you need to standardize CDXZipStream applications for less experienced Excel users.

Please refer to the following tutorials to see our standard templates in action.  We can modify any of these templates to fit your particular needs:

- Route Optimization with One Click

- Driving Distance and Time Calculations in an Excel Template

- Driving Distance and Time for a Matrix of Addresses

- Zip Code Radius Analsysis in an Excel Template

- Get Latitude and Longitude for Addresses in Excel

- Reverse Geocode Template for Microsoft Excel

Apply CDXZipStream Beyond Excel:  

We’ve reviewed in a past blog post the basic programming code that allows CDXZipStream to be applied to other Office applications beyond Excel.  This code is not proprietary and we invite our clients to use it as they please.   Not into programming?  Let us do it for you, and get CDXZipStream functionality, ranging from route optimization to radius analysis, exactly where you need it.

Use CDXZipStream with a Custom DataBase:

We’ve designed CDXZipStream around the use of address, zip code, and demographic data, but the concept of selectively taking data from a local database and putting it in Excel with custom functions can be applied to a wide variety of data needs.  Whether you have a private database you would like to use more efficiently through a spreadsheet platform, or want to mine a commercially available set of data that we don’t currently include in our own feeds, we can build a version of CDXZipStream that will work for you, using our patented interface.  Some types of custom databases include:

- Commercial address or postal code databases for countries other than the U.S. or Canada

- Census data for specialized demographics not now included in CDXZipStream

- Proprietary customer data

- Demographics by other geographies, such as voting districts, school districts, urban growth areas, etc.

For more information about CDXZipStream customization, or to request a free quote, contact us at 1-877-CDX-TEC1 (239-8312) or customsolutions@CDXTech.com.

We’re going to review today a special feature of the CDXZipStream Optimized Route template that allows you to send a list of optimized stops to your vehicle GPS, using something called a GPX data file.  Despite the ominous-sounding acronyms used in the previous sentence, this is really a pretty simple process that can save you lots of time as compared to manually entering locations into your GPS.

First, we need to back up a bit and talk about what CDXZipStream, our Microsoft Excel add-in for address and zip code analysis, does when it performs route optimization.   Let’s say you own a business that delivers a product or service to a list of locations that can change on a daily basis, and each morning you need to determine the best or optimized route between these locations in order to save time, money, and fuel.  Just list these locations in Microsoft Excel, and CDXZipStream, working in conjunction with Microsoft MapPoint running in the background, can re-order these stops to achieve either the shortest (by distance) or quickest (by time) route.   CDXZipStream combines the ease of using Microsoft Excel coupled with the mapping and routing power of MapPoint, and delivers the answer you need typically in just a few seconds.  For a quick tutorial, please see Route Optimization in Excel showing how to use CDXZipStream for route optimization:

As previously mentioned, we also offer a pre-formatted and automated Excel template that performs route optimization.  The user just inputs their route locations, clicks a button, and the desired output is returned to the worksheet.   The template is free, works in Microsoft Excel 2003 and up, and can be evaluated with free trials of CDXZipStream and Microsoft MapPoint.  You can download it from our links page, and see it in action in the tutorial Route Optimization with One Click. 

The results from this template can also be exported to a GPX file and input to your fleet’s GPS.  Just click on the “Export to File” button on the Optimized Route worksheet and then name the file to save it on your computer.  GPX stands for “GPX eXchange format” and GPS is “Global Positioning System.”   Each GPX file created by the CDXZipStream template will contain the address and latitude/longitude of each stop, in its optimized order.  Since GPX is a data format that allows for the exchange of location and route information between different applications and devices, it can be used or converted to a similar format, that can be plugged into your vehicle GPS.   Here’s an example of what a GPX file looks like for a route of five locations in New York City:

If you’re familiar with data exchange files you may have noticed that this looks a lot like an XML (Extensible Markup Language) file, and that’s exactly what it is, albeit formatted for the exchange of location-based data such as latitude and longitude.  But if this looks like Greek to you, no worries, the file contains all the information you need about your route with no manual modification required by you.

(Note:  If desired, you can create a GPX file without going through the route optimization process.  Just input your address list directly in the template, in column G of the Optimized Route worksheet, and press “Export to File”.   The GPX file will be generated with corresponding latitude and longitude data for each location.  The order of the addresses will remain unchanged.)

The next step is dependent upon the manufacturer of your GPS.  To import the GPX file into a Garmin or Magellan GPS, these manufacturers provide free communications software that can be downloaded from their websites at the following links:  

Garmin Communicator Plug-In

Magellan Communicator for PC

For other manufacturers please refer to the support section of their website for further information.

As an excellent general resource for the use of GPX files, we also recommend the website topografix.com.

Address information is a very basic component of running a business, whether you’re defining a marketing plan based on yours customer’s geographic location or routing service calls for your technical team.  Address accuracy is also critical for minimizing marketing, customer service, and delivery costs. Our Microsoft Excel add-in CDXZipStream is great at helping you perform zip code and address analysis, but we want to highlight here another good option which tends to get overlooked:   geocode (latitude and longitude) information as an alternative to text addresses.

You may remember from third grade geography how the idea of using points of latitude and longitude on the globe started a long time ago in ancient Greece.   The use of this “geocode” system was pretty much limited to navigating the seas until the general use of GPS (Global Positioning Systems) via satellite exploded in the marketplace.  Now a Garmin GPS can get you to Grandma’s house on Thanksgiving Day, and your GPS-enabled iPhone can find your morning coffee at the nearest Starbucks.

The great thing about latitude and longitude data, as opposed to a text address, is that it is an unequivocal identification of a specific place on the map.  Streets get renamed, buildings get torn down and renumbered, zip code areas get added or redefined, but latitude and longitude does not change.  Particularly when address validity is questionable, i.e. the zip code doesn’t match the city, the house number doesn’t exist, or the street name is misspelled, you may want to use latitude and longitude instead.

CDXZipStream, working in conjunction with Microsoft MapPoint, can both geocode an address (find its latitude and longitude) and reverse-geocode (find the closest address for a latitude/longitude point).  It can also use latitude and longitude as input to functions like CDXRouteMP and CDXLocateMP.   For example, if we want to calculate the driving distance to a customer location with a questionable street address, we can use CDXRouteMP with latitude and longitude, like this:

This is equivalent to using the worksheet equation:

=CDXRouteMP(0,0,"752 W End Avenue, NY, NY 10025","40.789283|-73.966078")

Latitude and longitude is provided as “40.789283|-73.966078" where the values are separated by a vertical bar "|".  Just remember to use decimal format and negative values for west and south global locations.  All locations in North America will have positive latitude and negative longitude values, and all European locations will have positive latitude and positive longitude values.

To use latitude and longitude for the function CDXLocateMP, input the geocode data as shown below:

In this case, we are requesting street information for the geocoded point, input as a single-line address.  You can also input latitude and longitude separately as a multiline address, in the street and city input boxes, respectively.  Again, this is equivalent to the worksheet formula:

=CDXLocateMP(4,"40.789283|-73.966078",,,,,1)

Do you know the location exists, but the address can’t be found by Google or MapPoint?  You can find the latitude and longitude of the location in question, or a nearby landmark, by right-clicking on the point in Google Maps and selecting the option “What’s here?”  The latitude and longitude values will automatically show up in the Google search box near the top of the screen.  You can also use resources like Itouchmap.com or Microsoft MapPoint, which shows latitude and longitude of the pointer at the lower right-hand area of the displayed map.  Once you have latitude and longitude, input these values into the CDXRouteMP or CDXLocateMP functions.

Latitude and longitude is also very useful for calculating straight-line distance between locations.  You can use the CDXZipStream function CDXLocateMP to find latitude and longitude for a list of addresses, then use CDXDistance2WP to find the distance between all desired points.  This is a very fast, very accurate calculation.  (For a more detailed description of this process, please refer to our prior blog article “How to Very Accurately Filter Addresses Based on Distance“.)  Don’t have detailed address information?  CDXLocateMP can find latitude and longitude based solely on zip code.  You can then use these points to calculate distance using CDXDistance2WP.  In this case, CDXZipStream uses Microsoft MapPoint to find the latitude and longitude of the zip code centroids. The centroid is the weighted geographic center of each zip code area.  

We also provide preformatted Excel templates which can perform geocoding and reverse-geocoding.  Just cut and paste your address or zip code list into the template and get your data at the click of a button.  These are free and can be downloaded from our links page.

For some short tutorials related to using latitude and longitude with CDXZipStream, please see the following videos:

Get Latitude and Longitude for Addresses in Excel 

Reverse Geocode Template for Microsoft Excel 

Gecoder in Excel