Both CDXZipStream and CDXStreamer Excel add-ins provide the option of using custom function formulas to insert data into a worksheet.  One of the big advantages of these formulas is that they can easily recalculate to reflect changes in either the input data in the worksheet, or the data source itself.   Let’s take a closer look at how this works.

Here we have a list of customer addresses where we've used CDXZipStream to get median household income for each ZIP Code in the list.  This data is from the latest ACS (American Community Survey) administered by the U.S. Census Bureau:

We have the option in the CDXZipStream Settings interface to request the data in the form of formulas or values.  In this example we’ve requested formulas.  The first formula is:

=CDXACSZCTA(F2, “Household Income Median”)

Where:

CDXACSZCTA is the name of the CDXZipStream custom function that accesses data from the ACS.  ZCTA, or ZIP Code Tabulation Area, is the Census equivalent of the USPS ZIP Code.

F2 is the worksheet cell address of the first ZIP Code in the customer address list.

“Household Income Median” is the requested data field.

The CDXACSACTA function is used like other functions in Excel, like SUM or AVERAGE, but here  it will extract the requested data for the ZIP Code in Cell F2 from the CDXZipStream database.  

If Microsoft Excel is set to “Automatic Calculation” any changes in the worksheet, such as a change in a customer ZIP Code, will cause the affected CDXZipStream formula to extract household income data for the new ZIP.  You also have the ability to turn off automatic calculation in the “Calculation” group on the Excel “Formula” tab, which can be helpful when working with a lot of data.  You can then make all necessary worksheet changes, and do a bulk recalculation when convenient – when you’re ready, just press F9 on your keyboard to calculate the entire workbook, or SHIFT-F9 for just the active worksheet.  

Excel’s automatic calculation does not work, however, if changes have been made to the CDXZipStream database as a result of a data update.  If you’ve just installed an updated database, you can easily force recalculation for all formulas, and get that new data in your workbook, by using the keyboard shortcut CTRL-ALT-F9.

If you need to send your workbook to someone who doesn't have CDXZipStream/CDXStreamer, remember to convert all formulas to values.  You can do this by copying the formulas and pasting them to the same of worksheet as values.  You can also automate this process using VBA, as described in this ozgrid.com article.  

CDXZipStream, our Excel add-in for zip code and location-based analysis, is an intentionally non-subscription, pay-as-you-go software so our clients can decide for themselves how often to purchase new data.  The need for data updates certainly varies depending on what data is used and how it is applied, and ultimately we feel this decision is best left up to our clients.

CDXZipStream updates are available as a one-time purchase and as a one-year subscription, where twelve updates are provided monthly.  Updates apply to CDXZipStream Basic and Premium versions, and to a separate Canadian database of postal codes.  Please see the last portion of our price list for pricing for all update versions.

Our basic and premium data feeds for zip codes are updated with new data every month.  Since zip codes are frequently retired or added by the U.S. Postal Service, this data is most subject to change. Out of about 42,000 zip codes in the U.S., an average of 0.9% of records change per month, and 11.2% of records change per year.  Unemployment data (provided in county, state, and Core Based Statistical Area data feeds) are also added and updated monthly.

All our other data feeds, which are primarily based on U.S. Census Data, are updated less frequently.  Data from the American Community Survey (ACS), which will soon be replacing much of the data from the ten-year census, is updated annually, usually in the last quarter of the following year.  We will be using our newsletter to announce updates to these data feeds as they occur.

When you need to update your database, click on the "License Information and Software Updates" icon on the CDXZipStream toolbar, and select "Data Updates" to login to your account and purchase an update. If you've forgotten your user name or password, please contact us at support@cdxtech.com. (Alternatively, your username is normally your email address, and you can retrieve a new password by selecting "Forgot your password?" to the right of the sign-in area.)  For our clients who have purchased support plans, we send an email notification when monthly updates are available.  For more information please call 1-877-239-8321 or contact us at sales@cdxtech.com.

Once a new update has been purchased, or to obtain a monthly update as part of a subscription plan, you can select the “Product Management” under the My Account area of our website (on the left-side menu), and click on “Download Data Files” to download and install the new database.  Alternatively, you can also click on “Buy Data Updates” here to purchase updates.

So, how often should you get updates?  First, consider how critical accuracy is for your application.  If you’re using CDXZipStream to verify addresses in a direct marketing campaign, and even a small percentage of non-valid addresses can make or break the return on investment, consider getting a year’s subscription so you get new data every month.  For non-critical applications, we generally recommend updates at least every six months.  In fact, you’ll see a reminder when you open CDXZipstream if your data is more than six months old.  (You can disable this feature by clicking on the “License Information and Software Updates” icon on the CDXZipStream Toolbar, and unselecting the “Warn if Zip Code Data Out of Date” checkbox.)

Please note that CDXZipStream functions related to driving and mapping use information from Microsoft MapPoint, and are only as good as the MapPoint version installed on your computer.