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”)
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.