CDXStreamer is our Microsoft Excel add-in that performs address verification and correction as well as data retrieval for ZIP+4, ZIP+4/TIGER and Canadian postal codes.  Here we will walk through the simple process of performing address verification and correction, which can virtually eliminate undeliverable addresses and significantly reduce the costs associated with incorrect address data.

Let’s say we have a long address list in Microsoft Excel, which could be addresses of customers, service call locations, delivery points, or other locations.  Here’s a portion of the list (in Column A):  

Select an empty cell next to your address list, and then use the cursor to click on the "Verify Address" icon on the CDXStreamer toolbar, as seen here in Excel 2010:

A dialog box will pop up:

Input the cell location of the first address in your list, in this case cell A2.  In this example the addresses are each listed within a single cell, but CDXStreamer can also handle addresses where the components are in separate cells.  In this situation, just select the “Multi-Line Address” option, and input the separate cell locations for the street, city, state and ZIP code. 

Now we choose the type of data to return to the worksheet.  Here we request the ZIP+4 code, also referred to as the 9digit_zip.  We also select the AutoCopy option so data will be returned for all the addresses in our list (so long as it doesn’t contain any empty rows).  Click OK, and the returned data in our worksheet looks like this:

In cases where an address match could not be found in the database, an error is returned.  All addresses that return N/A (an example in row 11 is highlighted in red) should be manually checked.

CDXZipStream will also correct addresses that contain limited errors, such as misspellings, incorrect or missing zip codes, cities, or states, and incorrect street suffixes and directionals.  Standard US Postal Service formatting, such as abbreviations and capitalization, will be also included.   To perform address correction, repeat the process described above, but specify that the entire address (full_address_out) be returned. This will also include the full ZIP+4.  For the list shown above, the corrected addresses are:

Besides formatting and abbreviation issues, here are some of the corrections made using CDXStreamer:

91 Gurnet Road, Brunswick, Maine (Missing ZIP code)
Correction - 91 GURNET RD, BRUNSWICK, ME 04011-9328

41 Sherman Road, St. Johnsbury, VT 05819 (Incorrect street suffix)
Correction - 41 SHERMAN DR, SAINT JOHNSBURY, VT 05819-9280

46 Cheltingham Ave., Schenectady, New York 12308 (Incorrect ZIP code)
Correction - 46 CHELTINGHAM AVE, SCHENECTADY, NY 12306-5106

200 MacIntyre Road, Pittsburgh, NJ 15237 (Street name misspelled)
Correction - 200 MCINTYRE RD, PITTSBURGH, PA 15237-4035

202 Kings Highway West, Haddonfield, NJ 08033 (Incorrect directional)
Correction - 202 KINGS HWY E, HADDONFIELD, NJ 08033-1905

320 W Ottawa St., Lansing, MN 48922 (Incorrect state abbreviation)
Correction - 320 W OTTAWA ST, LANSING, MI 48933-1590

3810 Forbes Ave., Philadelphia, Pennsylvania 15260 (Incorrect city)
Correction - 3810 FORBES AVE, PITTSBURGH, PA 15260-6995

Street address correction in CDXStreamer follows the US Postal Service "one component failure rule" to find a correct address match.  If not more than one address component mismatch exists, where a "component" is defined as a predirectional, street name, street suffix, or a postdirectional, then a correct address can be returned.  For instance, the address  “500 North Main Street West” has four components.  If one of the components has to be added, changed, or deleted to achieve a unique match, CDXStreamer will correct the address and return the ZIP+4 code.  If more than one component is incorrect, no match is allowed.  Also, no match can be returned if adding, changing or deleting a component results in multiple matches.  The priority is to add a suffix first before adding a directional.  If no match exists, the suffix is changed or deleted, and then the directional.

If there is not an exact match within the city, the "one component failure rule" is applied within the input ZIP code. If there is still no match, the rule is applied to all addresses within the postal finance number, and is only considered invalid if there is no match within that.

For a short tutorial covering address verification and address correction using CDXZipStream, please refer to the YouTube video “Address Correction and ZIP+4 Analysis in Microsoft Excel”.

Our newest version of CDXStreamer provides address correction, ZIP+4, ZIP+4/TIGER, and Canadian postal code data, all within Microsoft Excel.  But to the casual user of the US Postal Service (which covers most of us) ZIP+4 codes seem to be just extraneous information, and in fact, there is certainly no requirement from the USPS to use them when addressing mail.  So why the interest in ZIP+4 codes?

The use of ZIP+4 is intended mostly for business mailers that use computerized or machine-printed labels that can be read by automated scanners.  The 4-digit add-on number identifies a much more specific geographic segment, such as a city block, office building floor, or other high-volume mail receiver, as compared to the 5-digit ZIP code alone, and as a result, helps the USPS deliver mail more efficiently and accurately.  Since it reduces handling and decreases the possibility of mis-delivered mail, mail can get there sooner and even better, can also be eligible for reduced rates.

But ZIP+4 codes also provide a way to access valuable data that can help identify potential customers and markets.  With the millions of ZIP+4 codes assigned in the United States, they can be used to very accurately pinpoint an address and link it to specific demographic information available from the U.S. Census Bureau.  For instance, CDXStreamer can identify the ZIP+4 codes for a list of addresses, and then find the latitude, longitude, census tract, and block number associated with each of those codes.  These data are the keys to the extensive demographics available from the 10-year and annual American Community Surveys performed by the U.S. Census.

CDXStreamer specifically uses a USPS data product that matches the information on the Census Bureau's TIGER File to the United States Postal Service's ZIP+4 database. TIGER (Topologically Integrated Geographic Encoding and Referencing) data is used by the Census Bureau to describe physical land attributes, such as lakes and rivers, as well as other areas such as census tracts, to support the process of taking the decennial census. The resulting USPS file contains over 33 million data records from more than 27,000 5-digit ZIP Codes. The records relate ZIP+4 codes to state, county, Standard Metropolitan Statistical Areas (SMSA) codes, tract number, block number, and geographic coordinate information. Coverage of the file is limited to the address ranges in ZIP+4 database that could be successfully matched to the address ranges in the Census Bureau's TIGER File.

The end result is an effective way to help identify and develop market opportunities using one of the most extensive demographic databases available for the U.S. market.

For a short tutorial showing how CDXStreamer works, see the YouTube video Address Correction and ZIP+4 Analysis in Microsoft Excel.

Also available is a complete listing of all datafeeds and formulas provided with CDXStreamer in the downloadable Excel file CDXStreamer Example Data.

CDX Technologies also has a companion product, CDXZipStream, that provides extensive demographics based on census tract and is available as an add-in in Microsoft Excel.  The CDXZipStream Premium version includes census tract demographics covering population, age, income, household size, educational level, school enrollment, and marital status.  You can download an Excel file with a complete list of available demographics from CDXZipStream here.

Our Microsoft Excel add-in for zip code and location analysis, CDXZipStream, can generate zip and postal code lists for various geographies like state, county, and city.  But if you need to compare two lists of zip or postal codes from CDXZipstream, you can use the computational power of Excel to do the job.  Here’s a couple of different approaches, depending upon the version of Excel you are running:

VLOOKUP:  The worksheet function VLOOKUP works with Microsoft Excel 2003 and higher versions.  For example, let's say we have two zip codes lists like this:

And we want to find which zip codes are in List 1 but not List 2, and vice versa.  Just to the right of the first zip code in List 1, in cell C3,we input the formula:

=VLOOKUP(B3,E:E, 1, FALSE)

This indicates that we are trying to find the value of cell B3 (ZIP code 95229) in Column E (the location of List 2).  If the value is found, the value in the first column (the Zip code) will be returned; if not, “#N/A” is returned.  The input value of FALSE indicates an exact match must be made.

We copy this formula all along List 1.  We also input a similar formula to the right of List 2 that will search List 1:  

=VLOOKUP(E3,B:B, 1, FALSE)

The final result is:

For long lists you may want to use Excel’s sort function to sort these results and more easily identify where the lists are different.

Previously we’ve also shown how you can use VLOOKUP to compare results of zip code radius analysis; for example, identifying the zip codes where two radius areas intersect.  Please refer to the blog post ZIP Code Radius Analysis for Multiple Areas for more information.

CONDITIONAL FORMATTING: This method comes to us from Chandoo.org, and can be used in Microsoft Excel 2007 and up:  

1. Use the cursor to select the cells in both lists.  (After selecting cells in List 1, hold down the CTRL key and select List 2.)

2. From the Home tab, select Conditional Formatting, then Highlight Cells Rules, then Duplicates.

3. You can select to conditionally format unique or duplicate values, and also select color combinations. 

Here is the result, where we highlight duplicate values:

Again, the data can be sorted (based on cell color here) to more easily group and identify differences between the lists.

Our zip code and location analysis add-in for Microsoft Excel, CDXZipStream, can be combined with the computational power of Excel to do a variety of interesting things.  For instance, we’ve just recently introduced an easy-to-use bulk radius analysis template for Excel that can take a matrix of X and Y locations (these could be stores, customers, warehouses, delivery points, etc.) and create a comprehensive report showing which X locations are closest to all Y locations, or vice versa. 

Let’s say we have a list of customer addresses and would like to determine which store locations are within a 10 miles radius of each customer.  This could be used to support, for example, a direct mail campaign to alert customers to the closest stores in their area.  But in cases where there may thousands of customers and thousands of stores, determining the closest stores for every single customer is not a trivial calculation.

So how does the template work?  First we copy and paste all store and customer addresses into the Excel template where they can be verified using the address verification function of CDXZipStream (MapPoint version).  Just click a button in the template, and CDXZipStream automatically determines if each address can be matched to a location in Microsoft MapPoint running in the background.  If a match can be found,  CDXZipStream geocodes the location to find its latitude and longitude.   These points of latitude and longitude are used to calculate straight-line distances between all combinations of stores and customers.  So to obtain a report showing the closest stores within radius of each customer, all we need to do is specify the radius distance (in this case, 10 miles) , click one more button, and a listing of all customers, along with the store locations within 10 miles, is automatically created in a worksheet.

The template can work with either full addresses (street, city, state, zip code) or zip codes alone if full addresses are not available.  The user can also bypass the address verification step by inputting latitude and longitude data directly, if it is available from a gps or other source.  And if you need to do the reverse calculation, i.e. find the closest customers for all store locations, just reverse the order of the data input.

For a short tutorial about the template, please watch the YouTube Video Store Locator Analysis in Microsoft Excel.  For more information or to purchase the template, contact us at sales@cdxtech.com or 1-877-CDX-TEC1 (1-877-239-8321).

Our Microsoft Excel add-in, CDXZipStream, contains demographic data feeds based on data from the American Community Survey.  The ACS is a survey performed by the U.S. Census Bureau which has replaced the old “long form” of the 10-year census.  Please see our article ZIP Code Census Demographic Data in Microsoft Excel about our newest data feed, CDXACSZCTA, which provides for the first time comprehensive ACS data based on ZIP code.

Since the American Community Survey is performed annually, all the ACS data feeds in CDXZipStream are updated annually too.  However, the smaller the geography, the longer the span of data required.  For small geographies like census tract and ZCTA, the data must be aggregated over a five-year period to get statistically significant results; as of this writing the most recent set of five year data spans the years 2007 to 2011.  Therefore, in these cases every annual update does need to incorporate some older data as well. 

For some geographies, such as places (or cities), deciding what span of data to use is a balance between including as many geographies as possible versus using the most recent data.  For CDXZipStream feeds, here are the data spans we use, along with the number of geographies for each feed:

State:  1-year data, 52 geographies (all states, including Washington D.C. and Puerto Rico)

CBSA’s*:  5-year data, 955 geographies (all CBSA’s)

Counties:  5-year data, 3,221 geographies (all counties)

Places (Cities):  3-year data, 3,128 geographies (only places with populations greater than 20,000)

ZCTA’s**:  5-year data, 33,120 geographies (all ZCTA’s)

All geographies were not included for places (cities), excluding those with populations of less than 20,000.  In this case, it is generally a better choice to use ZCTA’s, which do cover a smaller population (averaging about 8,000) and is generally an easier index to use due to the availability of zip codes.  

  *CBSA’s (Core Based Statistical Areas) are the U.S. Census Bureau term for an urban area of at least 10,000 people.

**ZCTA’s (ZIP Code Tabulation Areas) are the U.S. Census Bureau version of a ZIP Code area.  For more information please see our article What’s a ZCTA?

Our Microsoft Excel add-in for zip code analysis, CDXZipStream, now includes a new feed that provides extensive demographic data based on ZIP Code Tabulation Areas. (ZCTA's are the U.S. Census Bureau version of ZIP codes.)  This is the first time ZIP code-based data has been released from the American Community Survey.   The ACS is an annual survey of about 3 million households performed by the U.S. Census Bureau. It has replaced the old "long form" version of the census, and includes many social, economic, and housing questions that were not part of the 2010 Census. The over 200 data fields in this feed cover: 

- Age

- Individual Earnings

- Educational Attainment

- Household Size

- Household Income

- Housing Value and Rent

- Marital Status

- Mortgage Status

- Population

- Race/Ethnicity

- School Enrollment

Click here if you would like to download a complete listing of the data feeds and fields provided in CDXZipStream, including the new feed listed under the name "CDXACSZCTA".

CDXZipStream includes a patented user interface that makes importing demographic data into Excel both fast and easy.  For a short tutorial on how to use our newest data feed, please refer to the YouTube video ZIP Code Census Demographic Data in Microsoft Excel.

The new feed is available with the Premium ACS version of CDXZipStream, which includes all demographic data feeds and data analysis functions.  Pricing for all versions of CDXZipStream is available on our pricing page. If you already have this version, the new feed is included in the most recent Premium ZIP Code data update.    Other current users of CDXZipStream can upgrade to this version by logging into their CDXTech.com account and selecting “Upgrade Licenses”.  In both cases, be sure to also get the newest 11.2.2 (free) update of CDXZipStream, available by clicking on the "License Information and Software Updates" button on the toolbar, then selecting "Software Updates".

In our Microsoft Excel add-in, CDXZipStream, there are three data feeds (CDXState, CDXCounty, and CDXCBSA) that provide population estimates for their respective geographies for each of the past ten years.  (These data feeds are available in the CDXZipStream Premium and Premium ACS versions.)   These population estimates are developed by the U.S. Census Bureau, and the entire time series of estimates beginning with the most recent decennial census is revised annually. Ultimately these provide an extremely accurate historical picture of population growth trends over the previous decade, a very useful piece of information for many industries ranging from home construction to car sales.

To access these population estimates within Microsoft Excel, select the first worksheet cell that starts your list of geographic locations.  For instance, here is a list of counties in an Excel workbook:

When working with county lists, use the format "County|State", where a vertical bar delimiter is used to separate the county name and state two-letter abbreviation.  When working with state lists, you must also use the state two-letter abbreviation.  For Core-Based Statistical Areas (CBSA’s) use the five-digit designation by the Office of Management and Budget, which can be found on the census website.   For instance, the Akron, Ohio CBSA has the five-digit designation 10420.

After selecting the first cell in the list, click on the CDXZipStream button on the main toolbar, and on the main dialog select the appropriate feed (in this case, CDXCounty) and desired years of estimated data:

Click “Get Data” and the requested data for the last ten years is returned to the worksheet (only partial data is shown here):

You can now use Excel’s calculational and charting capabilities to look for population trends.  For instance, do you need to locate a franchise to take advantage of high population growth within a particular county, state, or CBSA?  The combination of CDXZipStream and Microsoft Excel can quickly help you quantify trends that will allow you to make decisions based on hard, accurate data. 

Microsoft Excel has a variety of methods for formatting zip codes, enough to be a bit confusing to the non-expert user (which covers just about most of us).  Fortunately,  CDXZipStream, our Excel add-in for analyzing address and zip code information, can handle both five-digit and nine-digit (+4) formats and can also automatically assume that a leading zero exists even when Excel drops it from a zip code (e.g. 08030 turns into 8030).  Nevertheless, for viewing and printing purposes, you will want to be able to control the appearance of zip code data using one of the following techniques:

1. Force the cell contents to be viewed as text using an apostrophe

In this case just simply enter an apostrophe before the cell contents, and this will force Excel to display the entry as text “as is” regardless of the cell formatting.  For instance, a cell with general or number formatting will display the entry 08030 as 8030, but by using the apostrophe like this (‘08030), the leading zero will be retained.   This is a good approach if you have a fairly short zip code list.

2. Use the zip code formats provided with Excel

Excel has built-in five and nine-digit formats that you can access from the Format Cells dialog box, under the Number tab.  Under the Special category, select the Zip Code or Zip Code + 4 format that works best for your data:

3. Make your own custom format

From the Format Cells dialog box shown above, under the Number tab, select the Custom category and under Type, input 00000 or 00000-0000 for a five or nine-digit code, respectively.  Leading zeroes will be retained.

4. Use the TEXT cell function

This requires the use of a formula in a nearby cell.  For example, if the first zip code in your list is in cell A1, type the following in cell B1:

= TEXT (A1, “00000”) 

This automatically converts the value to text, regardless of how the cell is formatted, and retains leading zeros for the first five digits of the code.  You can then paste the formula down the rest of your list to convert all the zip codes to this format; use autofill for very long lists.

Keep in mind that all the data returned from CDXZipStream are returned as text.   If you are using the LOOKUP cell function with this returned data, then the lookup values, such as zip codes, must also be in text format.  (This applies to finding zips in a radius area, as illustrated in our YouTube video “Find Zip Codes in a Radius Using Excel”).   In this case use options 1 and 4 above, which forces the zip codes to text independent of cell formatting; and for very long lists, use option 4. 

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'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.