We’ve talked previously about the advantages of formula arrays in the following blog article.
For performance reasons we recommend that arrays be used when working with large address lists. Formulas arrays allow Excel to request a bulk data call, instead of copying individual formulas one at a time. Since much of the calculation time is caused by internet delays in reaching cdxtech.com servers a single request will have significantly faster response time.
When using CDXStreamer manually, you can enter formula arrays using the standard Address Verfication right click-dialog. Make sure you enable the “Autocopy” option which applies the array to all contiguous items in your address list. Formula arrays can also be entered manually should you choose using CTRL-SHIFT-ENTER.
But what if you have a template where you want to automate multiple data items for a variable length list? This is where the use of VBA comes to the rescue. The template VBAFormulaArrays.xlsm is an example of using VBA. It will calculate the corrected and verified address information for a list of addresses (address, city, state and zip) in columns A thru D. The data returned includes the verified address plus Zip+4 and County. Run the macro “GetData” to automatically enter the formulas. You must have CDXStreamer installed with an active demo or purchased account for this to work.
The macro code can to do this can be reviewed in the upprotected Module1 in the GetData subroutine. The code first determines the length of this list and then subsequently uses the formula array property of each column range to enter the data.
This concept can easily be applied to other formulas for speed advantages in requesting data over the internet in bulk.
CDXStreamer started out as an Excel Add-in for address correction and Zip+4 data. We have significantly expanded the capabilities of this product line with our cloud based software available at cdxtech.com. It’s now possible use the same address correction function by simply using your browser to upload an address list and then retrieve the processed results. This doesn’t require Excel and will work on non-Windows platforms such as the Mac or Linux or even on your phone.
We’ve expanded the CDXStreamer product line with additional tools to retrieve zip code demographics for a particular geography, radius or for a custom list of zip codes. Reports can be generated for a particular state, county or city or combination thereof. We’ve gone into more detail on this software in a previous blog article.
With the online tools you have an option to purchase a one time report for a price of $.01 per address request or zip code with a minimum charge of $9.95. But if you will be creating many reports over an extended period of time it makes sense to purchase a CDXStreamer account. Pricing for CDXStreamer is available here. Both monthly subscription and yearly fixed accounts are available with significant savings over the one-time cost of reports.
Using your online CDXStreamer is a simple matter of logging in at CDXTech.com at https://www.cdxtech.com/login/ with your username and password received with your purchase. Once logged in simply upload your file or select the desired report, preview your data, and if satisfactory press “Next”. The next screen should appear:
Press the “Use My CDXStreamer Account” button to get access to the report. Please be patient as when the report is ready a “Download” button will appear. The number of requested data points are simply deducted from the number you originally purchased. No additional credit charge is made. This also saves you significant time from having to enter your original purchase information again.
If you see the following screen this means that you did not log-in before using the online tools.
Simply press the “Log into your account” blue link text and enter your credentials. A screen with the “Use My CDXStreamer Account” button will appear if successful.
If you would like to see how this all works you can try CDXStreamer for free for 7 days for up to 1000 total requests by signing up for our free trial.
Do you have the need to identify company locations or resources that are closest to a long list of customers? We’ve talked about our Geographic Access Analysis template as a solution to this in prior blog articles and videos such as the following:
The template initially produces a straight line distance report so it can rapidly sort and compare locations, tens of thousands at a time. This latest release now includes a driving distance report so that the top locations received can be sorted by driving distance as well. Distance can be output in kilometers as well as miles and we’ve made improvements in calculation times. We also now support Maptitude data as well Bing and Mappoint to reduce the cost when analyzing long lists of locations.
The Geographic Access Analysis template is now on sale at $99.95 and can be purchased in your online cdxtech.com account in the “Buy Data Update” menu. This includes support and access to the latest template for 12 months. A demo of the template is also now available by contacting sales@cdxtech.com.
Microsoft Excel add-ins with the extension (.xlam) do not install open directly in Excel as do standard spreadsheets (.xlsx) files. Since they will run each time Excel starts you will need to install the add-in using menu commands in Excel as described in the procedure which follows below. These instructions apply to Excel 2010 and latest versions of Excel. While this example is for CDXGeodata, it can be used to install other add-ins as well.
The first step is to download the CDXGeodata add-in which can be found at the following link.
https://www.cdxtech.com/cdxgeodata/excel-addin/
Make sure that you remember the location where the file is downloaded.
If you are using the Mac all you need to do is click on the Tools – Add-in menu in Excel. Browse to the file and then press OK to load the add-in. The CDXGeodata file should now be active.
If you are using Windows you will need to “Unblock” the file. This step is not necessary if you are using Excel for the Mac.
In Windows to unblock a file use the File Explorer to browse to the file Location. Then right-click on the file and select “Properties” in the menu that appears. If you see “Unblock” in the lower right as shown in the image below click on it and then press “OK” and close the dialog.
For Windows 7 systems the “Unblock” option will be presents as a button rather than a check box.
We recommend copying this file to a trusted location that Excel recognizes and then installing the file from that location. You can determine this by clicking on “Trust Center Settings” and then “Trusted Locations” in the Excel File-Options-Trust Center menu. Placing the file in the XLSTART folder suggested for “Excel Startup” in this list should install the add-in when Excel restarts.
If the add-in is still not visible after restarting Excel or if the file is in another location you can load the add-in manual using the “File-Options-Add-ins” menu in Excel. At the bottom of the dialog you will see a “Manage” function as shown in the picture below.
Make sure that the dialog is set to manage “Excel Add-ins” and press “Go”. In the dialog that appears press “Browse”.
The screen shown below will appear. Switch to the directory where you have placed the unblocked “CDXGeodata” addin and press “OK”.
The CDXGeodata add-in will be added to your Excel add-in list as shown below. Click OK to close the dialog.
The Geodata add-in is now successfully installed. You should now see the CDXGeodata menu in the top menu of Excel and have access to the right click functions and formulas.
We updated all CDXZipStream templates that involve geocoding and routing to include Caliper Maptitude support. In addition to supporting Bing and Mappoint a third option is now available for Maptitude. Just select Maptitude from the data source dropdown to use it as shown below.
Adding Caliper Maptitude allows you to make an unlimited number of requests for a one-time cost of a license. Just copy in your address data and press a button to calculate. There is no need to learn the details about CDXZipStream or its formulas.
The templates updated include.
- Geocoding Template
- Reverse Geocoding Template
- Driving Calculations Template
- Driving Calculations Matrix Template
- Census Tract FIPs Codes
- Route Optimization Template
- Route Optimization with Geocodes Template
- Geocode and Sort By Distance Template
They are available to both registered versions of CDXZipStream and demo users. All are digitally signed so they can be run in secure environments that require this. Visual Basic for Applications (VBA) macro language provides the automation that powers the templates as described in prior blog articles. These applications are available for download at the following link.
The templates are a more efficient way to deal with calculating long lists. This is especially important when dealing with routing as this can involve significant calculation times. Copying large groups of formulas in Excel can be quite cumbersome. Excel doesn't calculate formulas linearly and can appear to be "hung" even though calculations are proceeding in the background. The templates calculate in the order your data is entered and have an indicator showing progress to completion.