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.

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 

Sometimes it's particularly important to get accurate distance information between addresses, and when managing large address lists, the fastest and most accurate approach is to first geocode the locations (get their latitude and longitude) and then use this information as the basis of the distance calculations.  CDXZipStream does this particularly well with a combination of two functions, CDXLocateMP and CDXDistance2WP.  We're going to review step-by-step how you can use these two functions within Microsoft Excel, to filter an address list based on the calculated straight-line (as the crow flies) distance to a target address. 

Let's say you have a long customer address list and would like to determine which ones are closest to a specific store location.  In many cases, especially in sparsely populated areas where zip codes may cover hundreds of square miles, it's not appropriate to use the centroid location of the zip codes as the basis of the distance calculations, the approach used in many zip code-to-zip code calculators.  To get accurate address-to-address distances, first geocode all addresses using CDXLocateMP.  The input box would look like this, where Excel cell references are used as the input for each address.

This is a multi-line address where the street, city, state and zip code are listed in cells A2 through D2.  We also request that latitude is returned to the worksheet cell where the cursor is pointed, and here is the result returned to cell E2:

We repeat the process to get the longitude returned to cell F2, by simply specifying longitude as the returned data.  Now all we have to do is copy and paste the formulas in these cells to the rest of the list, and all customer addresses are now geocoded.

After also geocoding the location of the targeted store, we're now ready to calculate the actual distances to the store based on latitude and longitude, using the function CDXDistance2WP.  Since this function uses a straightforward mathematical formula, the calculations are very fast.  Here's an example of the CDXDistance2WP input:

We use cell addresses for the data, in this case the geocoded "waypoint".  For the target location, we also specify an unchanging cell location using the dollar sign ($) before the column letters and row numbers.

To get distance data for the entire list, we again just copy the resulting equation from the first row of data all along the list of geocoded points.  To see a short tutorial showing the use of both CDXLocate and CDXDistance2WP functions, please see the YouTube video Geocoder in Excel.

Now use Excel's sort capabability to sort the distances, or use the autofilter function for more advanced features.  Autofilter can easily show you all the distances that fall within a radius.  Let's say we want to narrow the list of addresses to those within a 50 mile radius of the target address.  In Excel 2007 and 2010, follow this procedure:

1. Use your cursor to select all the data (addresses and distances) you wish to filter.

2. From the Data tab, select Filter.

3. You should now see drop-down buttons at the top of each column of data.  (You should be using column headings here so the buttons don't cover the first row of data.) Click on the drop-down button in the column of distance data.

4. Click on "Number Filters"

5. Select "Less Than or Equal to"

6. Enter the value "50" in the box to the right of "Less than or Equal to"

All rows that do not meet the 50 mile or less requirement are now hidden.  You can now copy and paste the filtered data to a new workbook to save it.

For more information about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

Over the last several months we've been developing Microsoft Excel templates that use CDXZipStream to perform a variety of tasks, ranging from route optimization to zip code radius analysis.  They are pre-formatted and automated so that users simply input their data, click a button or two, and the desired output is returned to the worksheet. The templates are free, work in Microsoft Excel 2003 and up, and can be evaluated with free trials of CDXZipStream and Microsoft MapPoint, which is also used in some of the templates.  (See the top of our links page to download.) At this point there are enough templates that we thought it would be a good idea to review in one place all their functionality and provide links to their YouTube tutorials:

Radius Calculations Template:  Input a list of addresses or zip codes, and this template will identify which ones are within a specified radius distance of a target zip code.  The calculations are based on the straight-line distance between the centroid (center) locations of the zip codes.  This is a one of our more popular templates and can be used for a variety of applications, such as filtering customer addresses around a store or other key location.  You can view the tutorial here.

Nuclear Facility Radius Calculator:  An example application of our Radius Calculations template that identifies nuclear facilities in a radius around a target zip code.   You can view the tutorial here.

Driving Distance Template:  This template works with Microsoft MapPoint to calculate driving time, driving distance, cost, or total trip duration between two lists of addresses. Input the same address for one of the lists if you need driving data around a single location.  This is a very easy way to validate travel expenses.  Please view the tutorial here.

Route Optimization Template (with GPX export):  This template also works with Microsoft MapPoint, to re-order and optimize intermediate stops on a driving route to achieve the shortest possible driving time.  The optimized route can then be exported to a GPX file that is compatible with many GPS devices.  This is a very easy-to-use, economical approach to route optimization that can reduce transportation costs for applications ranging from food delivery services to customer sales calls.  You can view the tutorial here.

Driving Matrix Template:  Input a matrix of addresses or zip codes to calculate driving distance, driving time, cost or trip duration between all combinations of routes.  For instance, the input matrix would look something like this:

Driving calculations will be performed for the route between Address 1 and Address A, Address 1 and Address B, Address 1 and Address C, etc., until all combinations are covered.  This is a quick and easy way to assign driving destinations, such as for delivery, taxi, or limousine services.  You can view the tutorial here.   

Geocoding Template:  Get latitude and longitude data for a long list of addresses.  This template works with Microsoft MapPoint, and can also be used to verify the accuracy of mailing lists based on the quality of the address match to the MapPoint database.   View the tutorial here.   

Reverse Geocoding Template:  Working with Microsoft MapPoint, this template reverse geocodes by finding the closest address to a geocode (latitude and longitude) point. This can be extremely useful for processing points from a gps device.  You can view the tutorial here.   

Although these templates cover many uses and will fit the bill for most clients, please contact us at customsolutions@CDXTech.com if you have a special need and require template customization. 

We like to think of CDXZipStream as a Swiss-army knife for working with location-based data, be it zip codes, addresses, or latitude/longitude (geocode) points. Since it performs so many functions, ranging from retrieving the most up-to-date demographic data to optimizing driving routes, we've put together in our CDXZipStream links page a virtual supermarket of resources that helps our clients get the most out of the software.   Here's a quick review of some of our links:

"One-Click" CDXZipStream Templates

These are free, downloadable, pre-formatted Excel files that perform CDXZipStream functions with a single click of the mouse.  Just enter your address, zip code, or other data of interest, and click a command button to start the calculations.  We currently have templates designed for route optimization, geocoding and reverse geocoding, and for driving calculations such as driving distance, time, cost, and total trip duration.  These templates can also be used with the trial version of CDXZipstream, so they're especially well-suited for doing a quick and easy evaluation of whether CDXZipStream is right for you.  Client feedback has been extremely positive, and we will be adding new templates to cover all CDXZipStream functionality.

YouTube Videos

These are short, informative videos that show you how you can use CDXZipStream in a Microsoft Excel worksheet.  We've found that video instruction is extremely effective in helping our clients understand what CDXZipStream can do, and is a great way to get the word out when new functionality is added.  Consider subscribing to our YouTube channel if you would like to be notified when new videos are released.

Technical Articles

These include Microsoft Excel files showing concrete examples of how CDXZipStream can be used, our on-line help file, a video quick-start guide to CDXZipStream,  and a spreadsheet of all available demographic data fields and their descriptions.

Close-Up Articles

These are an informational series of articles highlighting how you can get and use address, zip code, and geocode data to help grow your business.  These provide a good introduction on how to leverage location-based information into improved efficiencies, more targeted marketing and identification of your customer base, and direct cost savings, using CDXZipStream.  Four areas are covered: customer demographics, route optimization, driving (such as driving distance, time, and cost) calculations, and geocoding.  

We also include links to our newletters, articles on Ezinearticles.com, PC Magazine, and Squidoo.com, as well as links to the many awards won by CDXZipStream.  So please feel free to check back frequently, as we continually update and add to this page as CDXZipStream grows.

We've just posted two Microsoft Excel templates that allow CDXZipStream users to optimize driving routes or geocode addresses with just "one click".  These are pre-formatted spreadsheets which are programmed to import CDXZipStream data automatically – just cut and paste your address data into the template of your choice, click on a button, and the desired result is returned to the spreadsheet.  There's no need to input CDXZipStream functions or formulas on your own, and no prior experience working with Excel or CDXZipStream is necessary.

Both these templates use programming language in the form of Visual Basic "macros", which must be enabled through the security settings of Microsoft Excel.  These particular macros also offer additional security in that they are digitally signed by our parent company, Hughes Financial Services, ensuring that the macros originated from us and have not been altered in any way.  In today's blog post we're going to review the procedure for enabling macros, which varies depending on the version of Excel being used:

For Excel 2007/2010 -  First, open up a blank Excel workbook and at the top left hand corner of your screen, click on the round Microsoft Office button.  Then click Excel Options, then Trust Center, and Trust Center Settings.  Click on Macros Settings where you'll see the following options:

1. Disable all macros without notification
2. Disable all macros with notification
3. Disable all macros except digitally signed macros
4. Enable all macros (not recommended, potentially dangerous code can run)

Select any option you prefer, except for option 1, which will not allow macros to run under any circumstance.  We recommend you select options 2 or 3 in this case, and then just click OK until you are returned to the empty workbook.

Now open a CDXZipStream template and you should see the security warning bar underneath the ribbon like this:

(If you do not see the security warning, from the Microsoft Office button click Excel Options -> Trust Center -> Trust Center Settings -> Message Bar -> and choose Show the Message Bar in all applications when content has been blocked, then click OK to exit.  Close and then open the template again in order to see the security warning.)

From the Security Warning box, click on Options, and you will see this:

Choose Trust all documents from this publisher and click OK.  At this point the macros are enabled.  You will not have to repeat this procedure the next time you open the template.

For Excel 2003:  In a new workbook in Excel 2003, click on the Tools option of the Excel main menu, click Options, then click on the Security tab.  Click on Macro Security, and from the Security Level tab you will see the following options:

-    Very High.  Only macros installed in trusted locations will be allowed to run.  All other signed and unsigned macros are disabled.
-    High.  Only signed macros from trusted sources will be allowed to run.  Unsigned macros are automatically disabled.
-    Medium.  You can choose whether or not to run potentially unsafe macros.
-    Low (not recommended).  You are not protected from potentially unsafe macros.  Use this setting only if you have virus scanning software installed. Or you have checked the safety of all documents you open.

Any of these options can be used with CDXZipStream templates, with the following provisions:

For Very High Security, the template must be placed in (and run from) the "trusted location" XLStart folder, which is usually located in one of the following folders:

  •    C:\Documents and Settings\user name\Application Data\Microsoft\Excel
  •    C:\Program Files\Microsoft Office\Office11

Just place a copy of the template in the XLStart folder, and it will immediately be ready to run.

For High Security, when you first open the template you will see the following:

Check the box Always trust macros from this publisher then click on Enable Macros.  The template will open and will be immediately ready to use.  The next time you use the template, there will be no security warning.

When macro security is set to Medium, Excel displays the same dialog box where you can you can immediately click Enable Macros to access the template.  However, if you also select Always trust macros from this publisher, the template will immediately open the next time you use it, with no prior security warning.

Finally, the Low security setting will automatically enable macros;  this setting should only be used with appropriate virus protection installed.

It can be frustrating when analyzing a long address list if one or more of the addresses can't be found in MapPoint.  Instead of returning the answer you need, CDXZipStream may return an error message stating that the address is ambiguous or invalid.  You can verify this by entering the address directly in MapPoint and checking the possible locations that return.  If MapPoint can’t find a good match for the address, it will pop up a “Find” dialog and ask you to select the best match.

There are many reasons for this.  The most obvious is that the address is not included in the MapPoint database.  This data is only updated when MapPoint releases a new version, so a new address may not be in the database yet.  Another possibility is a different spelling (such as “Ln” vs  “Lane”) or a generic address (such as “P.O. Box 58”) is being used.  There are also situations where MapPoint finds two or more good matches, or ambiguous data, and needs you to decide.  Finally, it just may be due to the address being invalid.  You can find out more about how CDXZipStream treats this in the support article: CDXZipStream Returns  "Address is Ambiguous or Invalid" Error.

In the CDXLocateMP geocoding function you can set the ambiguous flag to a value of 1 to allow ambiguous data.  (Set this flag by entering the desired value at the end of the formula in the worksheet.)  CDXZipStream will then return data for the first location it finds.   Setting the ambiguous flag to 2 allows you to return any data that MapPoint finds, even if it is a bad match.  Since completely inaccurate data can be returned in this case, it should be used with caution.

Using the CDXRouteMP function will only return data when an exact or ambiguous match is found.  So how should you proceed in cases where you have a valid address but MapPoint can’t find it?  The first step is to get a valid latitude and longitude from another source such as Google or Bing Maps.  Once you have this information you can reverse geocode using the CDXLocateMP function to find the closest valid address in MapPoint. 

For cases where the reverse geocoded coordinates aren't close enough to the desired location, we’ve added the ability in CDXZipStream 10.3.1 to use latitude and longitude into the CDXRouteMP function.  Latitude and longitude should be entered in a single worksheet cell as decimal values in a text string and be separated by a pipe character, like this:  40.8480 | -74.5851.  If MapPoint can find a road close to this location, it will be able to calculate driving distance or optimize a route. 

With CDXZipStream you now have all the tools you need to analyze complex routes, even for locations MapPoint can’t directly find.