Bulk Radius Analysis with Formula Arrays

A common question we receive concerns creating bulk radius reports with just formulas. Many users also want to only retrieve zip codes alone and not include the radius distance in a separate column.  We’ve previously written blogs describing similar operations including "Dynamic Formulas Radius Analysis" and “Getting Radius Information for a List of Zip Codes”. The technique described below combines these methods to produce single columns of zip code radius reports for one or more central zip codes.

 

We’ve created an example spreadsheet to demonstrate this concept which can be downloaded here. This spreadsheet will work with the “Lite” version of CDXZipStream or better versions which include the radius function. The picture below shows the report included on the first tab of the spreadsheet.

Columns B, C, and D contain CDXRadius formulas which were entered manually as formula arrays.  To find out more about entering formula arrays please see the following blog article. Rather than 2 columns, only one column was selected as an input range so that only zip codes are returned. The length of each column is 2500 rows which should accommodate most radius reports.  The formulas are dynamic so that when the central zip codes are changed in row 1 or the radius miles is updated in A4 the reports will update correspondingly. If you need to add additional radius reports to the bulk report simply copy the columns B,C or D to adjacent columns. 

 

This is just one of the very flexible methods of creating radius reports with CDXZipStream.  All you need to do is choose the one which best meets your analysis needs.

 

New Microsoft Office Macro (VBA) Warnings

Microsoft has recently made security changes will disable spreadsheets that use VBA macros by default unless you take certain steps. If you download our automation templates from the CDX Technologies website you may be initially prompted with the warning “Microsoft has blocked macros from running…”as shown in the picture below. 

Microsoft is doing this for security concerns so that users downloading unchecked software from the internet are protected. But in doing so they are making valid and safe software products more difficult to use. All of our templates are digitally signed to indicate they come from a trusted source.

There are simple steps you can take to run our templates.  Please see the following Microsoft article on this. On a one-time basis the simplest thing to do is unblock the file and then reload the spreadsheet as described in the article.  Basically the “Ünblock” web mark needs to be removed from file properties.

If you will be repeatedly opening the files the best way to proceed is to copy them to a trusted network or file location.   The download directory is typically not considered trusted.  You can also assign cdxtech.com as a trusted website as described in the article. More detailed information on this topic is available from Microsoft in the following technical brief.

CDXZipStream Trip Summary Feature

A feature you may not be aware of is the Trip Summary report.  This feature is available as a right-click function in the CDXRouteBing and CDXRouteHERE functions. This is present in the CDXZipStream Router version and better.  This report will show the total trip distance, time, waypoints, optimization options and a map to for any given route. This can all be done in a single step instead of entering different CDXZipStream formulas. The picture below displays a typical report.

To create this first enter you desired route waypoints the spreadsheet. In the example above enter your 5 waypoints in the range B2 thru B6 In this case we are doing a fixed endpoint optimization with the start address equal to the end address.  In an adjacent cell clear of data, for this example G2, right click and choose the “CDXZipStream Functions” menu item and select “ Insert CDXRouteBing Function”. This will display the CDXRouteBing dialog. For Address1, enter the waypoint range of “B2:B6” (without the quotes). When entering a range rather than a single point Address2 can be left blank. Then in the “Route Calculation” drop down, specify “Trip Summary (miles)’.  Following this press “OK” to generate your report.

There is a separate Route Calculation option to generate the trip report in kilometers instead of miles. Additional customization options are available in the “Bing Maps Settings” button feature to include or remove the map and/or waypoints from the report.

A YouTube video covering this specific report and other feature of the CDXRouteBing function can be seen at the following link.

 

2021 American Community Survey Data Now Available

The CDXZipStream ACS data feeds for state, county, ZIP code (ZCTA), place, CBSA (Core-Based Statistical Area) and Tract have been updated with the latest data releases from the U.S. Census Bureau. These data are from the American Community Survey, which surveys about 295,000 addresses monthly. It is the largest and most detailed survey that the US Census conducts. These feeds are available in the CDXZipStream Premium ACS version. Data are aggregated over the years 2017 through 2021.
 
The demographic areas covered in all ACS feeds are: age, population, household size, household income, individual earnings, race and ethnicity, educational attainment, school enrollment, housing value and rent, mortgage status, and marital status. For a full listing of all data fields included in the feeds, please download the CDXZipStream Data Feeds and Definitions file here.
 
Please note that the main CDXZipStream data table has not yet been updated with 2020 Census data because the zip code geography is not yet available from the US Census.The US Census has estimated this data will become available in May of 2023.
  
If you already own CDXZipStream ACS Premium and have an active data maintenance contract, select the "Account" option on the CDXZipStream Excel menu and click on "Check for Available Database Updates". Then select the option to update your premium database. 
 
For users without an active account you can update your data feeds by logging into your on-line account on CDXTech.com. Under "My Account", then "Product Management", select "Buy Data Update" and then purchase the Premium Maintenance contract. If you don't have the correct CDXZipStream version select "Upgrade License" in the account area and select the option to upgrade to the ACS version. The latest data will automatically be included with your purchase.

HERE Technologies Platform Changes

In 2020 we introduced CDXZipStream Version 5 that supported HERE Technologies API’s for geocoding and routing as covered in the following article. We also described in a blog article on how to create free API keys using the portal https://developer.here.com. These API keys allow up to 250,000 requests per month which is a significant benefit when compared to Bing Maps.

If you’ve previously set up API key at developer.here.com your key is still active and you can access the portal with credentials previously set.  But new users are being directed to a different portal at https://platform.here.com.  While you can still set up a key without a credit card, these keys are restricted to 1000 requests per day.  Adding a credit card to the HERE account will allow you 30,000 geocoding requests and 30,000 routing requests per month.  Additional requests have very attractive pricing <$.75 per1000 requests or less as described at the following web page.

You can set up a free API key using the following instructions. A sample screen shot of the web page used to create the REST API key required for CDXZipStream is shown below.  Keys are entered into CDXZioStream in the same fashion as described in our original blog article. Make sure to restrict the domains or IP4 addresses the key can be used on.

 

You can access the HERE features in CDXZipStream if you have purchased in the last year or if you have an active data maintenance subscription. You will need to download and install the latest CDXZipStream demo and reactivate your software using the "Account" button in the CDXZipStream Excel menu. If you don't reactivate you will not have access to the new features. This will not affect the number of computers that you can install CDXZipStream on.

CDXZipStream customers without an active data maintenance plan will be able to try out the new Here functions but will be limited in the number of requests they can make during a single excel session.  Your online account at cdxtech.com can be used to purchase a maintenance plan and get full access.