We have often been asked if CDXZipStream can apportion a large number of destinations into individual routes, then optimize the order of stops along each of these routes.  Unfortunately the route optimization function of CDXZipStream, which relies on the use of Microsoft MapPoint, is not sophisticated enough to do this kind of calculation.  The vast number of permutations that could occur here requires a lot of computational muscle, and if there are rules (such as geographic constraints) that must be followed in apportioning the destinations, these must be programmed into the selection process as well.

But this is also a case where the human eye and a little brain power, with some help from Microsoft MapPoint, can compete pretty well with even the more sophisticated (and expensive) software.  The trick is to be able to quickly and easily visualize and manage the data, which is right up MapPoint’s alley.   So let’s get started.

Let’s say we have a list of 100 destinations, which we need to divide into 10 routes (of about 10 destinations each), followed by optimization of each route.     First we import all the destinations into MapPoint using the data import wizard available from the “Data” menu on the MapPoint toolbar:

From here you can then select the Excel file that contains the destination list; you can also select other file types here such as text or Access database files.  After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:

You can also choose to import data beyond just address information, which may be useful if this data is part of the decision-making process in apportioning routes.  (You can display all information associated with a destination by right-clicking on it later on the MapPoint map.)  Click on “Finish”, then select the pushpin map type in the next dialog:

Once the data is displayed as pushpins in MapPoint, you can use MapPoint drawing tools to apportion the data into routes.  The freeform tool was used to draw the area below:

You may also want to try the radius tool so you can specify the radius distance of the area.  Once an area has been drawn, right-click on the area boundary line and select “Import to Excel”.  You’ll immediately see all the points within the area in an Excel spreadsheet, ready to optimize with the CDXZipStream function CDXRouteMP.  You can also cut and paste these points into our Route Optimization template, available for free download from our links page.

This technique may not be appropriate in cases where there are many criteria for apportioning routes.  In the case above, we used logical boundaries to define the route area, such major highways and the river to the northwest.  (This is very easy for us humans to do, but can be pretty complicated for a computer.)   As mentioned previously, you can also import other data into the spreadsheet which can be used as criteria in apportioning.   This data can be displayed by right-clicking on a destination on the map, or even better, can be used to define the appearance of the destinations when selecting the map type, by selecting options such as color-coded pushpins or sized or shaded areas.  

Keep in mind that before optimizing each defined route, you may need to modify the beginning and end point destinations.  CDXZipStream assumes these points are fixed and only optimizes the order of the intermediate points.   In the majority of cases this assumption works, but you may also want to add one or more addresses that represent the true beginning and end points if they fall outside the area you just defined in MapPoint.

For more information on using CDXStreamer for route optimization, please watch the following tutorials:

Route Optimization in Excel

Route Optimization with One Click

A major priority for CDX Technologies is to provide excellent support for all of our software.  We find that first-class support not only (obviously) improves our customers experience with our products, but also gives us the opportunity to learn about the myriad ways our software is used and how we can improve it.

But the ever-changing world of PC’s can provide a real challenge for developers, and problems can and do arise with even the most carefully designed software.  If you start having a problem with CDXZipStream, we’d like to provide some advice here on how to proceed.

First, check the product support articles on the website.  They cover the most common problems, including:

• CDXZipStream Toolbar Location in Excel 2007/2010

• Filtering Addresses with Radius Distance Returns #NA for all entries

• CDXZipStream Custom Functions Return #NAME?

• Error 1303 During CDXZipStream Installation

• CDXZipStream Toolbar Not Visible

• CDXZipStream Returns "Address is Ambiguous or Invalid" Error

Another common issue is how to reinstall CDXZipStream on a new computer, which is covered in our blog article The CDXZipStream Licensing Agreement. 

If the resources above don’t quickly solve the problem, send us an email at support@CDXTech.com or call 973-895-5542.   It always helps to describe the problem in as much detail as possible, including 

- A description of what you were attempting to do at the time the problem occurred

- The text of any error message (or a screen shot attached to an email if convenient)

- Your PC and software configuration

If appropriate, it can also be extremely helpful to email us your Excel file or a screenshot of the worksheet showing the problem.  This can really speed up the process of troubleshooting issues associated with using CDXZipStream worksheet functions, since the nature of the input data plays an important role in how well the functions work.   In this case, if you must send sensitive or proprietary information, please do not use regular email; you can contact us for access to our secure server.

We now have a new database for CDXZipStream that provides an extensive variety of demographic data for 2010 census tracts. CDXZipStream is our Excel-based zip code software that performs address, zip code, and geographic analysis, including route optimization, geocoding, zip code radius calculations, address verification, and demographics import.

Demographics based on census tract offers significant advantages over zip code data. Since census tracts are about half the population of zip code areas, the smaller size provides a more accurate demographic snapshot.  Also, zip code census data is actually based on ZCTA's, or Zip Code Tabulation Areas, which the Census Bureau use to approximate zip codes. Census tracts, which are precisely defined by the Census Bureau, do not include the possible error associated with using ZCTA's.

The CDXTract feed provides a variety of data from the 2010 Census and the on-going American Community Survey.  Topics are:

- Age

- Individual Earnings

- Educational Attainment

- Household Size

- Household Income

- Housing Value and Rent

- Marital Status

- Population

- Race

- School Enrollment

- Land, Water Area

For a complete listing and description of each demographic variable, you can download an Excel file which describes each CDXZipStream data feed in detail, including CDXTract.

To access the data, census tract codes (also referred to as FIPS codes), must be provided as input in an Excel worksheet. CDX Technologies now offers a free Excel template that can identify census tract codes for an address list – please see our blog posting Get Census Tract FIPS Codes in an Excel Template.  You can also view a short Youtube tutorial on how to use the template: Get Census Tract FIPS Codes in Microsoft Excel.

Once you have the list of FIPS codes for each location, use your cursor to select the first code in your list, then click on the CDXZipStream icon on the main toolbar. You’ll see a dialog like this:

We’ve selected the CDXTract data feed (under the Select Data Feed option), and have also added the data fields we need to the list on the right, in this case covering median age data and earnings levels.

Click "Get Data", and the selected data will now be automatically returned to the worksheet (only part of the returned data is shown here):

For a short tutorial on how to use the CDXTract data feed, please see the YouTube video Census Tract Demographics for an Address List.

Between the recent 2010 Census and the ongoing American Community Survey, there is now an unprecedented level of extensive, up-to-date demographics data available for the U.S. market.  Please note that census tract data is an additional purchase, and is compatible with all demographic versions of CDXZipStream.  For pricing information and access to the CDXTract database, please contact us at 1-877-CDX-TEC1 (1-877-239-8321) or support@CDXTech.com.

We were recently asked by a customer if it is possible to use CDXZipStream, our Microsoft Excel add-in for address and zip code analysis, to determine whether a zip code is in a rural area.  Here’s our response:

A fairly stringent determination of rural zip codes, which can be done easily using CDXZipStream, is based on whether the zip code is part of a Core Based Statistical Area, or CBSA. CBSA’s are defined by the Office of Management and Budget as urban areas; a metropolitan area contains a core urban area of 50,000 or more population, and a micropolitan area contains an urban core of at least 10,000 (but less than 50,000) population. Each metro or micro area consists of one or more counties and includes the counties containing the core urban area, as well as any adjacent counties that have a high degree of social and economic integration (as measured by commuting to work) with the urban core. Based on the 2010 Census, approximately 95% of the U.S. population lives in either a micropolitan or metropolitan CBSA.   If a zip code is not associated with a CBSA then, it is a good assumption that it is in a non-urban, or rural area. Using the CDXZipCode premium feed (available in CDXZipStream Demographic version and higher), request the data field “CBSA”; if the returned value is "N/A", then the zip code is not associated with any CBSA and you can assume that it is rural.

The attached map shows the current coverage of Core Based Statistical Areas in the U.S. and Puerto Rico:

Source: http://www2.census.gov/geo/maps/metroarea/us_wall/Dec_2009/cbsa_us_1209_large.gif

Using an alternative U.S. Census Bureau definition, rural areas comprise open country and settlements with fewer than 2,500 residents.  All other (urban) areas are of two types—urbanized areas and urban clusters—identical in the criteria used to delineate them but different in size. The Census Bureau defines an urbanized area wherever it finds an urban nucleus of 50,000 or more people. They may or may not contain any individual cities of 50,000 or more.  Urban clusters have a population of at least 2,500 but less than 50,000 persons.  Using these criteria, about 16% of the U.S. population lives in what is considered to be rural areas, based on the results of the 2010 Census.  You can download from the Census website relationship files showing which ZCTA’s (the Census Bureau’s approximation of zip codes) correspond to urban and non-urban (rural) areas.

You may also want to consider population density as a rough measurement of urban/rural characteristics, Population density can be easily calculated by dividing the total population of the zip code by the land area.  Again, using the CDXZipCode Premium feed, request the data fields “Population” and “LandArea” and then use Microsoft Excel to perform the calculation.  Keep in mind, however, that population density provides only a partial picture, since there may be non-residential areas with low population counts that exist in the middle of urban centers.  

CDXZipStream, our Excel add-in for zip code and location-based analysis, is an intentionally non-subscription, pay-as-you-go software so our clients can decide for themselves how often to purchase new data.  The need for data updates certainly varies depending on what data is used and how it is applied, and ultimately we feel this decision is best left up to our clients.

CDXZipStream updates are available as a one-time purchase and as a one-year subscription, where twelve updates are provided monthly.  Updates apply to CDXZipStream Basic and Premium versions, and to a separate Canadian database of postal codes.  Please see the last portion of our price list for pricing for all update versions.

Our basic and premium data feeds for zip codes are updated with new data every month.  Since zip codes are frequently retired or added by the U.S. Postal Service, this data is most subject to change. Out of about 42,000 zip codes in the U.S., an average of 0.9% of records change per month, and 11.2% of records change per year.  Unemployment data (provided in county, state, and Core Based Statistical Area data feeds) are also added and updated monthly.

All our other data feeds, which are primarily based on U.S. Census Data, are updated less frequently.  Data from the American Community Survey (ACS), which will soon be replacing much of the data from the ten-year census, is updated annually, usually in the last quarter of the following year.  We will be using our newsletter to announce updates to these data feeds as they occur.

When you need to update your database, click on the "License Information and Software Updates" icon on the CDXZipStream toolbar, and select "Data Updates" to login to your account and purchase an update. If you've forgotten your user name or password, please contact us at support@cdxtech.com.  For our clients who have purchased support plans, we send an email notification when monthly updates are available.  For more information please call 1-877-239-8321 or contact us at sales@cdxtech.com.

Once a new update has been purchased, or to obtain a monthly update as part of a subscription plan, you can select the “Product Management” under the My Account area of our website (on the left-side menu), and click on “Download Data Files” to download and install the new database.  Alternatively, you can also click on “Buy Data Updates” here to purchase updates.

So, how often should you get updates?  First, consider how critical accuracy is for your application.  If you’re using CDXZipStream to verify addresses in a direct marketing campaign, and even a small percentage of non-valid addresses can make or break the return on investment, consider getting a year’s subscription so you get new data every month.  For non-critical applications, we generally recommend updates at least every six months.  In fact, you’ll see a reminder when you open CDXZipstream if your data is more than six months old.  (You can disable this feature by clicking on the “License Information and Software Updates” icon on the CDXZipStream Toolbar, and unselecting the “Warn if Zip Code Data Out of Date” checkbox.)

Please note that CDXZipStream functions related to driving and mapping use information from Microsoft MapPoint, and are only as good as the MapPoint version installed on your computer. MapPoint is updated every one or two years and the newest 2011 version does contain updated maps with new geographic data.  If you wish to purchase MapPoint North America 2011 we recommend buying at the reputable reseller MP2KMag.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 

CDXZipStream, our Microsoft Excel add-in for zip code and address analysis, has a number of built-in functions that perform area calculations around a given geographic location.  These can be invaluable tools for designating sales and service territories, finding customers for a brick-and-mortar store, designing delivery routes, and more.  Here’s a quick review of this capability:

Generate a zip code radius list:  The CDXZipStream function CDXRadius  can quickly find zip codes within a specified radius of a central zip code.  This is based on straight-line distance calculations between the centroid points of the target zip code and all nearby zip codes.  To use CDXRadius, right-click on any cell in your Excel worksheet, and from the menu click on  CDXZipStream Functions, then Insert CDXRadius Function.   The input box will look like this:   

After specifying the target zip and radius distance, you can ask for the resulting list of zip codes either as an Excel array formula (see here for more information on Excel arrays), or as a text string within a single worksheet cell.   The output will also include distance data from the target zip, with the data separated by vertical bars when listed as a text string: 

Find locations within a zip code radius:  Do you have a long list of addresses or zip codes, and want to determine if they fall within a specified radius of a store or other location?  It’s very easy to do this by cross-checking your address list against the list generated by CDXRadius.  First, run CDXRadius with the desired target zip code and radius distance, making sure to specify the output as an array formula.  Then for each location in your original list, use the Excel LOOKUP function to see if the address zip code exists in the array generated by CDXRadius.  A step-by-step tutorial of this method is shown in the YouTube video Find Zip Codes in a Radius Using Excel.

As an alternative, CDX Technologies also offers a free, downloadable Excel template that automatically identifies locations around a zip code.   This template is especially well-suited for use with very long address lists, since the template code accesses CDXZipStream functions directly (versus using Excel worksheet functions) which increases calculational speed.  Please refer to our links resource for CDXZipStream template downloads.

Find exact locations within a radius area:  Sometimes zip codes are just not accurate enough, so when you need to use exact locations for a radius analysis, use a combination of two CDXZipStream functions.  First use CDXLocateMP to find the latitude and longitude of each address, then CDXDistance2WP to calculate the straight-line distance between each latitude/longitude point and the central point of interest.   The Excel autofilter feature can then quickly find the locations that meet the radius distance criteria.  This multi-step process is explained in detail in our blog post How to Very Accurately Filter Adresses Based on Distance.

Map an Area:  You can also use the CDXRadius function to map a radius around a zip code.  Specify the target zip code and radius distance, and request the output as a radius map.  

Need a drive time map that delineates the area within a specific driving time of a location?  Specify the target zip and drive time, and request the output as a drive time map (using the CDXRadius function again).  The drive time area is shown in red and is typically a polygon, reflecting variations in road conditions and driving speeds:

Geographic area analysis can be a critical component of activities ranging from target marketing to product delivery.  Remember CDXZipStream when you need a fast and economical area analysis that meets your business needs.

In a past post we briefly discussed (in an admittedly negative way) the ribbon GUI that first appeared in Excel 2007 and is now entrenched in all Office 2010 applications.  Well, around here we started to talk a little more about it and it's pretty clear that the ribbon is one of those things (like anchovies on pizza or the New York Yankees) that you either love or hate – there's not a lot of middle ground. 

The real issue is that the ribbon is a major hurdle to many Excel users who would like to upgrade to a later version of Excel to take advantage of new features.  So let's talk about the ribbon in a little more detail and see if we can help with the decision-making process in upgrading to either Excel 2007 or Excel 2010.

So, what about that gosh-darned ribbon?  First of all, if you really don't want to use the ribbon, you don't have to.  Third-party vendors have stepped in with relatively inexpensive (about $15 to $30) add-ins that can recreate the classic toolbar in either Excel 2007 or 2010.  Some versions can be used in all Office 2010 applications and can even be deployed among multiple computers; in at least one case (ToolbarToggle) the toolbar is customizable just like in older versions of Excel.     

Keep in mind, however, that many people do like the ribbon and you should probably consider trying it before buying a toolbar add-in.  If you decide to use the ribbon, be prepared to spend some time learning it.  We don't recommend doing this by the seat-of-your pants – sit down at your computer with your favorite caffeinated drink (you'll need it) and go through some good training materials.  Some possibilities are the Chandoo.org Master the Excel 2007 Ribbon free learning guide, and Microsoft's Up to Speed with Excel 2007 learning tool.

But beware:  Microsoft decided (what were they thinking?) that the ribbon in Excel 2007 could only be customized using xml (Extensible Markup Language) and programming code.  Many Excel users save precious time and significantly improve their productivity by customizing that classic toolbar to fit their particular needs; did Microsoft expect the typical user would want to learn xml too after going through the ribbon learning curve?  You can also purchase add-ins that do this programming for you, but fortunately this is not an issue with Office 2010 and the ribbon can now be customized as easily as the classic toolbar.

There are lots of other things to consider if and when you decide to upgrade. You can find more than a few discussions on the web weighing the pros and cons of the various versions of Excel, and that is beyond our scope here.  Ultimately, many users who are perfectly happy with their trusty old software (remember Windows 98?) will upgrade simply because of the inconvenience of using an outmoded version, especially with the loss of technical support and compatibility. At the end of the day, there are thankfully a few inexpensive workarounds to address most concerns about the ribbon – it's just too bad that something as basic as the user interface had to be an issue at all.