Welcome to the CDXZipStream May 2014 Newsletter

Unemployment Data - We'd Like Your Input

 

Using the CDXZipStream Demographics version and higher, you can track unemployment data for the last 36 months (as well as annual averages) for states, counties, and CBSA's (Core Based Statistical Areas).  This data is sourced from the Bureau of Labor Statistics and is updated monthly.

How to Use Sample VBA Code Tutorial

  

Lately we've been trying to encourage CDXZipStream users to take full advantage of how Visual Basic for Applications can access CDXZipStream functionality.  Especially with repetitive data analysis, VBA is really an efficient way to customize CDXZipStream for your particular project.  It can also be applied to Microsoft Office applications beyond Excel;  check our our recent blog post Accessing CDXZipStream Functions using VBA in Microsoft Access.

 

If you haven't worked much with VBA, however, you may want to download our newest template 

which covers example VBA macros using all CDXZipStream worksheet functions.   Just copy and paste the VBA code into a module in your worksheet, and modify as needed to make it work for your data.  We've also created a short video tutorial showing how to work with the template:

 

To download the CDXZipStream VBA template, please click here. 

A Smart New Feature - Flash Fill in Excel 2013

 

Excel 2013 offers a very nice little tool, called flash fill, that just might convince users to upgrade from older versions of Excel.  This tool allows you to "teach" Excel how to fill in new columns of data based on existing column entries.  It doesn't require the use of formulas; flash fill simply uses some examples provided by the user to figure out how the new data should look.  It's especially good for parsing (separating) or concatenating (combining) columns of text data, such as long lists of address data.  

  

For instance, to parse out street, city, state, and ZIP code into separate columns, the user would input the street for the first entry immediately next to the column containing the full addresses.  After typing  the first character of the next street, Excel recognizes a pattern in the entries and automatically suggests the rest of the street for this address as well as all subsequent addresses:

 

 

  

Hit Enter and the suggested entries will now fill the rest of the list.  See some mistakes in how the data was parsed?  Correct the first occurrence of the error and Excel will correct all subsequent entries accordingly.  You can then continue on to the next column and repeat the process for city, state, and ZIP code.  This also works for concatenation, and if you want to change the case to all capitals, flash fill will do that too.

  

For a flash fill tutorial, please see this short Microsoft training video.

  

Flash fill requires reasonably consistent formatting from address to address. Inconsistent use of commas, for example, will not work with flash fill.  For these cases you can use the CDXLocateMP function of CDXZipStream, or our subscription-based service CDXStreamer.  Please refer to the blog post Address Verification and Correction in CDXZipStream and CDXStreamer for more information.

Latest Data Updates

 

Canadian and U.S. data feeds were updated as of May 23.  There were 58 changes made to the U.S. database this month.  If you would like to update your database to reflect these changes, click on the "License Information and Software Updates" icon on the CDXZipStream toolbar, and select "Data Updates" to login to your account.

 

We hope you find the information here helpful for you and your organization.  Please contact us with your feedback and suggestions by replying to this email.

 

The Team at CDX Technologies