Our Microsoft Excel add-in CDXZipStream has several demographic versions that provide extensive data from the 2010 Census and recent American Community surveys.  CDXZipStream is especially well-suited for quickly and easily obtaining demographics for long lists of zip codes, cities, counties, states, and other geographies.  For more information please refer to this comparison of CDXZipStream demographic versions as well as the data fields listing for each provided data feed.

There may be situations, however, where you need data for a single address or geographic area, and the particular demographic variable required is not offered by CDXZipStream.  For these cases we’d like to recommend a handy little tool provided courtesy of the U.S. Census Bureau.  It’s called American Factfinder, and it was redesigned last year to provide the most up-to-date online facility for getting census data.  It can be a goldmine of information but is not necessarily intuitive for the average user, so let’s walk through getting some demographic data around a specific street address.

First, go to the American Factfinder2 main page and click on “street address” in the Address Search area:

A dialog box will appear where you can input the address of interest. Then click “Go” and you’ll see a menu selection that will allow you to choose the geographic type: 

Geography types include state, county, county subdivision, block census tract, etc.  For this example we require data for the entire census tract that contains this address, so we click on the left-hand column selection (in blue) Census Tract 1002, Lackawanna County, Pennsylvania.  (For data for the entire zip code, you would need to select ZCTAS 18503.  ZCTA, or Zip Code Tabulation Area, is the Census Bureau’s approximation of a zip code area.)  The census tract now shows up in the “Your Selections” display in the upper-left hand corner of the screen:

You can repeat this process for more addresses, and can even mix-and-match different geography types in your selections.  

Now let’s move on to selection of the data type.  If necessary, close the “Geography Results” box by clicking on “Close” in the upper right hand corner.  You should see a list of search results showing “Tables, Files, and Document Names” that all contain data for this particular census tract:

What’s shown in this list is dependent upon the geographic selection made in the previous step.  For example, census tract data is available from both the latest American Community Survey (ACS) 5-year results, and the 2010 Census, shown here as 2010 SF1 and 2010 SF2.  However, census tract data is not available from the more recent ACS 1-year results, since census tract areas are not large enough provide statistically significant data over a 1-year sample period.  Therefore, the availability of data will vary as a function of geographic type.

Assuming we don’t quite know which table we need yet, let’s narrow our search by inputting some relevant keyword(s).  The keyword “income”brings up some specific tables and areas of interest; we click on Income/Earnings for Individuals:

... and another listing of tables appears:

If our interest is income for the foreign born population, we select the box for table B06010 (Place of Birth by Individual Income ….).  Up to ten difference tables can be selected in a single download.  We can then either select “View” to see the data online, or “Download” to obtain the data file.  These files are provided in .csv format that can be opened and viewed using Microsoft Excel or Access.  

If you don’t like the search results or need to use another keyword, make sure to delete the previous search selection in the “Your Selections” box in the upper left-hand corner.  It is also sometimes helpful to view some of the larger tables (covering topics such as Population, Economics, Social, and Housing characteristics) to get a sense of the kind of data available.  These larger tables generally cover a large majority of the more interesting topics.

AmericanFactfinder2 is an excellent source of data that is well-suited for finding demographics for a specific address.  When CDXZipstream is not a good fit, try American Factfinder as just one more tool that can support your business needs.

 

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.  

 

There are currently eight versions of CDXZipStream, our Microsoft Excel add-in for zip code, address, and route analysis.  Due to the broad spectrum of functionality of CDXZipStream, we’ve purposely created these versions to allow our customers to pick and choose the one that best fits their needs.  With the release of CDXZipStream 11.0 late last year we’d like to provide in today’s blog an updated review of the functionality of each version, to help you decide which one is right for you.  You can also refer to this reference table for a shorthand comparison.

Note:  We also offer a Canadian postal code database which is compatible with most CDXZipStream functionality.  Please refer to this article for more information about accessing Canadian data with CDXZipstream, or our FAQ page.)

Here are the eight versions and what they can do, along with relevant video tutorials:

FindZip:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)

Reference:  Zip Code Finder in Excel

Lite:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation) as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip code, and find the closest zip code to a target zip. 

Reference:  Find Zip Codes in a Radius

MapPoint:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation), but also has all the mapping and routing functions provided by Microsoft MapPoint, including creation of radius, location, drive time, and route maps, calculating driving time and distance, and route optimization.  It can also geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between two points of latitude and longitude.  In general, this CDXZipStream version is best suited for those requiring driving distance calculations, route optimization, and/or latitude and longitude determination.  Please note that CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel

Basic:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  CDXZipStream Basic is primarily used by those who require both extensive zip code functionality as well as driving and routing calculations supplied by MapPoint.

CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel, Geocoder in Excel

The next four versions also include demographic data.  For a detailed description of the data available in these versions, please refer to data fields information.

Population:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Population counts from the 2000 Census and current population estimates are also covered.

Demographics:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.

Reference:  Demographic Data in Excel

Premium:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Premium ACS:  Contains all the functionality and data of the Premium version, plus demographic data from the American Community Survey.  Covers basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.  Finally, 5-year aggregated data from the annual American Community Survey is provided in data feeds by city (place), county, state, and Core Based Statistical Area (CBSA).   Note that the ACS is administered by the U.S. Census Bureau, and replaces the old long form that was phased out after the 2000 Census.  It contains earnings data and other social and economic information that are not currently covered by the 10-year census. 

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Census Records in Microsoft Excel

 

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 “GPS 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 

 

CDXZipStream, our Excel add-in for address, zip code, and route analysis, has a licensing agreement that is significantly different (in a good way!) from most other software.  Understanding that CDXZipStream contains a number of functions for route planning and optimization, it makes sense to allow our users to install it on multiple computers.  For example, you can plan and optimize a route on your desktop, then follow the directions for the optimized route on a laptop in your car.  For very long calculations, it is also helpful to use a secondary desktop or laptop so your main computer is not tied up.  Since the CDXZipStream license is provided per user, you won’t have to buy multiple licenses to do this; each CDXZipStream license comes with three key codes, allowing you to install it on three separate computers.  

To set up CDXZipStream on a new computer simply download and install the CDXZipStream demo on the new computer.  When installation is complete and Microsoft Excel opens, press the third button on the CDXZipStream toolbar named "License Information and Software Updates". (If you are using Excel 2007 or 2010, the CDXZipStream toolbar is located under the "Add-Ins" menu.) Then press the "Buy Now" button followed by pressing "Generate KeyCode" and log into our web site using the username and password you received with your original purchase.  If you did not retain your original purchase information please contact us at support@cdxtech.com

A web page allowing you to generate a new keycode should appear with your new Computer ID automatically entered. This Computer ID is unique for each PC and your registration code is based on it. Then press "Next" to generate your new registration key.

Copy your new keycode from the box that appears and then press the "x" in the upper right corner and minimize your browser. Then paste the copied code into the "Keycode" section of the "CDXZipStream Purchase and Registration" box and press "OK". (This is the box where you pressed the "Generate Keycode" button.) CDXZipStream will now be fully activated on your computer.

Your online account can also be used to purchase data updates or upgrade your product to a more powerful version.  A history of all data purchases is maintained in the "Download Data Files" section.

Our license does not permit sharing with other users on additional machines.  We do require that multiple users purchase multiple licenses.  Each CDXZipStream license will be registered under a person’s name, which can be displayed by clicking on the “License Information and Software Updates” button on the main CDXZipStream toolbar.  Also shown is the number of licenses associated with the name.   If the name needs to be transferred, such as in cases where an employee has left an organization, please contact us at support@ cdxtech.com.  We also offer generous volume discounts of up to 20% for 10 licenses; please contact us for details if you need more than 10 licenses.  

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.

If you've ever hesitated with indecision when the dessert cart comes around, we know how you feel.  Choice is a good thing when it comes to the sweet things in life, but sometimes a little help is needed to make the right decision.  We especially believe that having a choice is a good thing when it comes to software, and so we've always offered CDXZipStream in several versions to provide our clients with the functionality that best suits their needs.  Now with eight different versions and a wide (and growing) range of capabilities, we thought it would be a good idea to review in detail the functionality of the most popular versions, as an aid to current and future clients as they purchase and upgrade their software. (Note:  Pricing information for both new purchases and upgrades is available here, with discounts available for multiple licenses.  And remember, all versions of CDXZipStream come with a 30-day money-back guarantee, so don't worry, you can send back that cherry tart after taking a bite ...)

CDXZipStream Lite 

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic). See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

CDXZipStream Basic 

Does everything the Lite version does, and contains additional functionality provided by Microsoft MapPoint.

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic).  See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Basic:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium

Does everything the Basic version does, and contains extensive demographic data.

- Provides basic demographic data by zip code, city, county, area code, CBSA, and state

  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA, CDXState, CDXCensus, and CDXCensus2.   See here for specific data fields for    these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium ACS

Does everything the Premium version does, and contains demographic data from the latest American Community Survey.

- Provides extensive demographic data by zip code, city, county, area code, CBSA, and state, from the American Community Survey, 2000 Census, latest Business Census,    and other sources.  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA,  CDXState, CDXCensus, CDXCensus2, CDXACSCBSA,    CDXACSCounty, CDXACSPlace, and CDXACSState   See here for specific data fields for these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium ACS:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

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.

CDXZipStream, our Microsot Excel add-in, has some very useful functions that perform a variety of location-based calculations, ranging from creating zip code lists for a given city to finding latitude and longitude for an address.  These functions are readily accessible by right-clicking with the mouse on any cell in an Excel worksheet - a "custom function" (similar to standard Excel functions like SUM or AVERAGE) is then inserted into the worksheet cell and performs the desired calculation.  Please refer to our previous blog for more general information about custom functions in Excel.

To help guide CDXZipStream users when accessing these functions, here is a quick review that includes short video tutorials (in Shockwave and Windows Media formats) on their use:

CDXFindZip:  Finds all the zip codes for a given city.  CDXFindZip.swf (1.56 mb)  CDXFindZip.wmv (1.03 mb)

CDXDistance:  Finds the distance between two zip codes.  CDXDistance.swf (1.88 mb)  CDXDistance.wmv (1.15 mb)

CDXRadius:  Finds all zip codes within a radius distance of a target zip code.  When used with Microsoft MapPoint, it can also create a radius map around a target zip code.  CDXRadius.swf (4.41 mb)  CDXRadius.wmv (3.10 mb)

CDXRouteMP:  Working with Microsoft MapPoint, calculates driving time, distance, and cost between two locations, and can also create a map of the route.  CDXRouteMP.swf (5.73 mb)  CDXRouteMP.wmv (3.77 mb)

CDXLocateMP:  Working with Microsoft MapPoint, verifies latitude, longitude, and address information for a given location, and can also create a map of the location.  CDXLocateMP.swf (4.24 mb)  CDXLocateMP.wmv (3.06 mb)

CDXDistance2WP:  Working with Microsoft MapPoint, calculates the distance between two waypoints.  A waypoint is a location defined by its latitude and longitude.  CDXDistance2WP.swf (2.27 mb)  CDXDistance2WP.wmv (1.47 mb)

CDXClosest: Finds the zip code from a list that's closest to a target zip code.  CDXClosest.swf (4.10 mb)  CDXClosest.wmv (2.70 mb)

CDXZipList:  Creates a list of zip codes for any city, county, or state.  CDXZipList.swf (3.08 mb)  CDXZipList.wmv (1.82 mb)

In upcoming posts we'll be reviewing these functions in more detail, showing how they can be used to solve real-world problems when dealing with zip code, address and location-based data in general.  Stay tuned!