Much of the demographic data of interest to businesses, including the data provided by our Excel add-in CDXZipStream, is based on data from the U.S. Census Bureau.  Census demographic data by zip code is especially useful, since just about everyone has a zip code associated either with their home or business address.  However, if you go to the Census Bureau's American Factfinder tool and input a zip, you'll see that the data returned is for something called a "Zip Code Tabulation Area" or ZCTA.  Is this really a zip code?

The short answer is – it's an approximation of a zip code.  Zip codes are ever-changing geographic entities determined by the Postal Service that can cross city, state, and county lines. They are also not defined by straight-line boundaries (like a polygon) and as a result are very difficult to digitally map.  To help overcome these problems,  ZCTA's were developed by the Census Bureau for tabulating Census 2000 data.  ZCTA's approximate zip codes by aggregating small census-defined areas called blocks, based on the majority of addresses in the area.  So for most instances the ZCTA code equals the zip code for an area, but since it is an approximation, a small number of addresses may have a ZCTA code different from their zip code.  And for zip codes with very low populations, there may not be a corresponding ZCTA at all.

When the results of the 2010 census are rolled out, ZCTA areas will be redefined to reflect changes in zip codes that have occurred over the past 10 years.  Stay tuned as we keep you updated on the status of pending census data.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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