CDX Technologies

Discussions related to CDX Technology products and services

How to Get a Bing Maps License Key

Our Microsoft Excel add-in CDXZipStream contains Bing-related functions that can do driving and route calculations, geocode and reverse geocode, look up postal codes and verify address information. To use Bing Maps as the source of mapping data for these functions, the first step is to get a Bing Maps license key. Free basic keys for evaluation purposes can be created from the Microsoft website, or you can purchase an enterprise version from Microsoft resellers such as Onterra Systems.

To get a free basic key:

1. Go to the Bing Maps Dev Center and sign in with your Microsoft Account. (You can use the same sign-in credentials you may already use for Office, Outlook, or Skype.) If you don't have an account, you have the opportunity to create one from the sign-in page; there is no cost or obligation associated with having a Microsoft account.

2. After signing in, you will be forwarded to a page with a "My Account" menu. Select "My Keys" and on the next page click on the link for creating a new key.

3. Next you'll see a box, shown below, with several fields to fill in:

 

4. Click on "Create" and you will see your new Bing Maps key and related details listed. To the right of the key select "Copy key".  If your browser doesn't allow you to use the "Copy key" link, you can also highlight the key with your cursor and use CNTL-C to copy it.

5. Now you can open Excel and from the "Settings" icon on the CDXZipStream commandbar, click on "Bing Maps Settings", then "Set Bing Maps Key", and use the keyboard shortcut CTRL-V to paste the new key into CDXZipStream.

Below is a detailed, step-by-step tutorial on how to get a free Bing Maps key, and enter it in CDXZipStream:


To get an enterprise key:

Contact an authorized Microsoft reseller for an enterprise Bing Maps key. Reseller Onterra Systems provides enterprise keys for $300 per year that cover up to 5 users and provide 100k data requests per day. We have found that this license works well for most CDXZipStream applications. You can contact Onterra using their on-line order form. If you work for a larger organization, please check with your IT department first to see if you already have access to a key as part of a Microsoft enterprise agreement. Microsoft also provides a good review of Bing Maps licensing options on its website.

Geographic Access Analysis - Customized for You

We currently offer a Microsoft Excel template that, working with our CDXZipStream add-in, performs geographic access analysis.  In general terms it can take a matrix of X and Y locations (these could be stores, customers, warehouses, delivery points, etc.) and create a comprehensive radius report showing which X locations are within a specified radius distance of all Y locations, or vice versa.   In its most common application as a store locator tool, it can identify the stores located within a radius distance of a list of customers.  Here’s a short tutorial showing how it works:

A more detailed description can also be found in our blog article Store Locator and Geographic Access Analysis in Microsoft Excel.


The template can also be customized to fit your particular needs, by incorporating additional data and generating custom reports. Here’s the short list of the kinds of things customization can do:


1. Calculate access performance
Once the radius report is generated it can be used to calculate performance any number of ways. What percentage of customers live within x miles of at least two stores? Which stores within y miles cover the most customers? How many customers must travel at least y miles to a store? These and other measurements can be extremely useful in areas ranging from resource allocation to developing a long-term business strategy.


2. Add demographic or other location-based data to the radius report.
Get demographic data for each location and/or target using one of CDXZipStream’s demographic feeds, and include that data as part of the radius report. For example, access requirements for health care facilities can be dependent on population density. With a radius report showing the closest facilities to each prospective patient, population density for each patient ZIP Code (based on population and land area data obtained from the CDXZipCode Feed) can be used to classify each patient as living in an urban, suburban, or rural area.


3. Add more criteria to the radius report.
The radius report shows locations closest to each target, within a radius distance you specify, but additional criteria can be used as well. Need to find distribution centers within a radius distance of retail stores, but only those within the same state? State location can easily be added as a secondary criterion in the report. These additional criteria don’t have to be based on location; any descriptive data that can be included in the location listing (number of employees, services provided, etc.) can be used in the radius report.


4. Create custom reports based on the radius report
The results of the radius report can be used to produce more detailed reports for each location, or groups of locations. For example, with a radius report showing stores within a radius distance of each customer, additional reports can be built showing customers (within the radius distance) associated with each store. Similar reports can also be generated across stores groupings, showing customers associated with full service stores, stores within a single state, etc.


5. Add billing and other numerical data

Any numerical data associated with targets or locations, such as billing and cost data, can be included as part of the radius report or other custom reports described in the point above. These values can then be summed or averaged, for example, for a group of targets and/or locations. Numerical-based reports such as these can often replace time-consuming manual processes as well as provide hard numbers for facility planning purposes.


For more information about our customization services, please call 1-877-CDX-TEC1 (239-8321) or email customsolutions@cdxtech.com. If you are a current CDXZipStream client, you can purchase the standard, un-customized template by signing into your on-line account and selecting "Buy Data Updates". Select the Geographic Access Analysis Template from the drop-down list and proceed through the purchasing process. You must already own the CDXZipStream Bing, Basic, or one of the premium demographic versions to see this template as a purchasing option.

The Versatility of Bing Maps When Working with Address and Location Data

Our Microsoft Excel add-in CDXZipStream can now use the Bing Maps web service to geocode, reverse geocode, perform routing calculations, and check address information, all for long lists of location data in a worksheet. Especially noteworthy is the ability of Bing Maps to accurately retrieve “best match” addresses or points of latitude and longitude, even when working with problematic source addresses or locations. Here are a few examples:

Incorrect or Missing ZIP or Postal Codes

Incorrect or missing ZIP or postal codes is a very common issue with address data. Bing Maps can provide correct ZIP Code data that matches the provided street, city, and state, as shown below:

Here's a short tutorial showing how it works:

Intersections
Bing Maps can identify the intersection of two roadways, providing both the name of the roads as well as geocoded (latitude and longitude) coordinates. This can be valuable for first responders, since accident locations are often associated with intersections rather than specific street addresses.

No Commas or other Delineation
Addresses compiled from other data sources may be missing commas or other delineation between street, city, state, and ZIP code components. Once the addresses are in Excel, it can be very difficult to accurately parse these components from a single text string, using Excel text functions and formulas. Bing Maps can provide the standard address format with commas, or can provide individual parsed components:

Landmarks
Bing Maps can also locate landmarks that do not have conventional addresses:

Missing Street Numbers
In cases where street numbers are missing, Bing Maps can return the street location as well as the geocoded location of the approximate centerpoint:

Other Text Corrections 

Bing Maps can correct other issues with address text such as misspellings, missing or incorrect directional data, and missing or incorrect street designations:

To check and correct problematic address data that may have issues like the ones described here, use the Bing version of CDXZipStream or higher. Please refer to our pricing page for current pricing information for this and other versions.

Updating Custom Function Formulas from CDXZipStream and CDXStreamer

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.  

 

ZIP or Postal Code Lookup Using Bing Maps and Excel

Our Microsoft Excel add-in CDXZipStream now has a new function, CDXLocateBing, that can find ZIP or postal codes for addresses listed in an Excel worksheet.  CDXLocateBing uses the web service Bing Maps, running in the background, as the source of address data.  Bing Maps has some level of geographic coverage for every country in the world, with postal code lookup available for many developed countries.  For the latest information on Bing Maps coverage, please refer to the Microsoft website.  Postal code retrieval is generally available when geocoding precision is either “Address” or “Rooftop”, which applies to about 70 countries.

To find a postal code for a list of addresses in Excel, right-click in an empty cell next to the list, and select the function CDXLocate Bing.  Input the worksheet cell location of the first address, and select “ZIP (Postal Code) as the returned data, as shown below

Enabling the Autocopy option will obtain data for the entire list of addresses until the first occurrence of an empty cell.  Using the “Set to Text” option will return the data as text instead of CDXLocateBing formulas, which is recommended when working with long lists of data.

When address data is in separate columns for street, city, and state, you can use the multi-line address option which will allow separate input of worksheet cell locations for each part of the address.  Whether a single-line or multi-line address is used, however, it is also a good idea to request that CDXLocateBing return the entire address (“Best match”)as well as just postal code, to both ensure Bing Maps identified the correct address and to obtain the full address in standard format.   You can see how this is done in the following video:

In almost all cases, CDXLocateBing will return the complete postal code, with the known exception being for locations in the UK where only partial codes are available.  Note that postal codes are available in their entirety for Canada, despite the use of alphanumeric codes similar to those in the UK. 

When using CDXLocateBing, complete address information is not required, although the more information provided, the more likely it is Bing will correctly identify an address.  The use of country data is generally recommended  although not required in cases where an otherwise complete address is provided for a U.S. location.   Also keep in mind that Bing does not have the ability to verify the existence of a specific house number; so long as a street number (e.g. 100 Main Street) is within a valid, allowable range for that street, it will return a valid postal code even though the address may not physically exist.