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. 

The 2010 decennial census results are slowly being released by the Census Bureau, and you may have seen news reports since last December about redistricting and demographic data, provided on a state-by-state basis.  However, one of the more important releases of interest to business and other organizations looking for demographic data, will occur over the summer months of 2011, from June to August, of what's referred to as Summary File 1 or SF-1.

SF-1 is also issued state-by-state, but will include data all the way down to the smallest geographies of block and census tract.  (For a review of Census Bureau geography types, please see our blog article Census Geography - It's not Quite What You Learned in School.)  What data will SF-1 include?  Since the 2010 Census did not include a long-form version and covered only ten questions, the results are somewhat limited, to the following areas:

- Population counts for 63 race categories and Hispanic or Latino

- Population counts for many detailed race and Hispanic or Latino categories, and American Indian and Alaska Native tribes

- Selected population and housing characteristics, such as household and family information by age, size, and type

Attached for your reference is an Excel file listing all the data fields of SF-1: Census 2010 Summary File 1 Fields.xls (946.00 kb)

Other socioeconomic data, covering areas such as income, educational attainment, school enrollment, and occupation, are now collected with the American Community Survey, or ACS.  The ACS is performed annually by the US Census Bureau and results can be combined over a several year period to obtain statistically significant data for smaller census geographies.  The CDXZipStream ACS Premium version currently provides ACS data for states, counties, cities, and Core Based Statistical Areas for the years 2005-2009.  Zip code data, for both the 2010 decennial census and the ACS, will not be available until 2012-2013.

Assuming SF-1 is released by August, we expect to have portions of this data available in CDXZipStream by our regularly scheduled data update at the end of September 2011.  The following fields will be covered by CDXZipStream, and we are now soliciting input from our clients to determine if there are any other fields of interests we should add, either to the standard product or a customized version:

Please contact us at support@CDXTech.com if you would like to provide input to the CDXZipStream data feed for Census 2010 data.  If you are interested in your own customized database of Census 2010 data, you can contact customsolutions@CDXTech.com for a free quote.

Our Excel add-in CDXZipStream can return zip code radius and list data in the form of an Microsoft Excel array. An array is a group of related values that each occupy its own worksheet cell in an Excel worksheet.  It's often desirable to use arrays in cases where the returned data is too large for single worksheet cell.  We're going to review here how you can apply CDXZipStream arrays to large data sets.  This information applies to the right-click functions CDXRadius and CDXZipList, both of which allow the user to specify arrays as the output.

Let's say we're interested in finding all the zip codes within a 100 mile radius of target zip code 08033.  We can use the right-click function CDXRadius, with the following input:

The returned data is in an array two columns wide, showing both the zip codes within the radius in the first column, along with its distance in miles from 08033 in the second column.  Here's what the first few rows of the array look like:

If you click on any individual cell within the array, the formula is: {=CDXRadius("08033",50)}.  The custom function formula is surrounded by "curly" brackets indicating it is part of an array.  Note that the array acts as a single entity, such that you can't change or delete only part of the array; the array must be deleted in its entirety.

But what if we need to find zip codes within a 50 miles radius for a list of target zip codes?  It's possible to copy and paste the array to easily apply it to more than one target zip – here's how:

1.  Input the first zip code in cell A1 of a worksheet.

 2.  Right-click on cell A2, and select "Insert CDXRadius Function".

 3.  Use these inputs:

     Zip Code: A1

     Radius: 1000 (this will be changed later)

     Distance Unit:  distance in miles

     Result Out:  Array Formula

     Maximum Rows: 10000

4.  When you click OK, an array will be returned for the first zip code.   We purposely made this an extra large array with a 1000 mile radius, so when we copy and paste it for other zip codes we will be sure to have an array large enough to capture all the returned zips.  

5.  Now with the first array highlighted, use Excel's find and replace function to replace all "1000" values with "50" (while leaving out the quotation marks). 

6.  This array can now be copied and pasted immediately below any zip code to find the list of zip codes within 50 miles. 

You may want to experiment a bit with the size of the first array, by changing the radius distance or the number of maximum rows.  Just make sure it is large enough to capture all the data you need for subsequent target zips.

You can copy and paste the array manually, or record an Excel macro that can automate this process for you.  To prevent Excel from slowing down due to the large number of array formulas, just remember to occasionally copy and "paste special" the arrays as values as you proceed.

Even beyond their use in CDXZipStream, arrays can be powerful calculational tools for your worksheeets. For more general information about using Excel arrays, please check out the Microsoft website.

The CDXZipStream function CDXRouteMP is used to calculate driving distance, time, cost, and total trip duration for two or more stops on a driving route.  It can also optimize a route by reordering intermediate stops on the route in order to achieve the shortest driving time.  Please see our YouTube videos on route optimization and driving calculations for more information.

CDXRouteMP works in conjunction with Microsoft MapPoint, and it's important to make sure that MapPoint settings properly reflect the requirements of the route so the calculated values and route order (when optimizing) are accurate.  It's easy to set MapPoint settings using the Route Settings button on the main dialog box, shown below:

The main dialog box can also be accessed, just by right-clicking on a worksheet cell, in CDXZipStream free templates that are downloadable from our links page.  (This applies to the templates related to driving calculations which employ Microsoft MapPoint.)

After pressing Route Settings, the following dialog appears:

Each tab corresponds to a particular area of interest: driving speeds, fuel, driving costs, road types, and schedule.  There are also some general data to the right which are always visible regardless of the selected tab.

The data on the right covers route optimization and output preferences.  If more than four stops are part of the specified address range being analyzed, you can specify whether the stops will be optimized, i.e. the intermediate stops will be placed in the order that results in the shortest driving time.  Note that route optimization assumes that the first and last stops listed will remain constant; their order in the list will remain unchanged.  If the requested output is a trip summary, you can also request to include a map of all locations and a list of the waypoints according to their order in the driving route.  For output that includes directions and waypoint lists, the lists can be provided either as an array formula (where each value occupies its own worksheet cell), or as a text string in one single worksheet cell.

Note that the order of the stops when performing route optimization is always based on achieving the quickest driving time.  However, if the route type as specified on the main dialog box is the preferred or shortest (distance), then these selections will be accounted for when calculating the actual route directions, driving distance, cost, etc.  The order of the stops will not be affected by these selections, however. 

The driving speeds tab as shown above describes the preferred maximum driving speeds as a function of road type.  When specifying preferences for arterial roads, keep in mind that these are high capacity urban roads that are not quite as large as highways, and are designed to deliver traffic from collector roads to freeways, or between urban centers.

Fuel information on the second tab can impact both driving cost calculations and driving time, the latter particularly for long trips that require multiple stops for refueling:

 Driving costs are also impacted by the settings on the driving cost tab, which can be based on actual fuel consumption or a fixed price per mile:

The road types tab provides a qualitative measure of preferred roads.  This input only has an impact on driving calculations when the route type selected on the main dialog box is "preferred".  If the calculations are based on the "shortest" or "quickest" route, changing these settings will not impact the driving calculation results:

And finally, the schedule tab is useful when calculating total trip duration (including multi-day trips), where it is necessary to account for limitations on daily driving time, required rest periods, and time spent at each stop:

Whenever you perform driving calculations, we recommend that you quickly review the route settings described here to ensure they reflect the driving conditions you expect.  It can also useful to vary the settings to explore how even minor changes to your driving habits may impact cost and time savings.  For instance, estimating the cost savings provided by optimized routing, fuel efficient vehicles, or changes in driving schedules is easy to do by adjusting these settings, and can ultimately provide a good impetus for change.