In a blog post two weeks ago we talked about how you can customize some of our CDXZipStream Excel templates to meet your specific needs.  However, when it comes to more complex calculations, especially for very large data sets, do-it-yourself customization may not be enough.  When you need an Excel template for zip code, address, and other location-based analysis, and our CDXZipStream free templates don’t fit the bill, consider contacting us for a free quote on a custom template built just for you.  In many cases we can build on our past experience, allowing for very quick (less than 5 business days) turn-around time on your project.   Here are a few examples of the kinds of analyses a custom template can perform:

Multiple Route Optimization:  When managing sales call schedules or customer service visits, it may be necessary to perform repeated route optimizations on a frequent basis.   In some cases, the number of stops on a route can be fairly large, up to 50 or more addresses that need to be included in the optimization process.  Although we do offer a free template that performs optimization for a single route, running this multiple time may not an efficient use of your time.   A customized template can be specifically designed to manage multiple routes, and also be customized to make input of multiple address groups quick and easy.  

Radius Analysis or Route Optimization with additional data:  Address records usually include a lot of other associated data, such as name, telephone number, and other identifying numbers or historical information important for your organization.  When radius analysis or route optimization re-orders a long list of addresses, you can retain all associated data using a customized CDXZipStream template.  Field column headings can be custom-designed for your particular set of data, or left blank so you fill them in yourself.   

Multiple Radius Analyses with sorting:  Need to assign all customers to all your sales staff based on geographic proximity?  When doing multiple radius analyses involving a large x by y matrix of locations, it may be important to sort the results for each analysis based on some other variable.  For example, it may be necessary to find all customers within a 50 mile radius of each salesperson, then sort the customers for each based on account size, distance, product type, or any other data that may be helpful in deciding sales staff assignment.  Even without sorting this process can involve literally millions of calculations.  A customized template can handle the multiple radius analyses as well as automate the multiple sorting involved. 

Driving Distance Radius Analysis for very large data sets:   Some radius analyses require driving distance calculations for added accuracy.  Driving distance calculations are inherently slower than straight-line calculations, so when performing radius analyses for a large x by y matrix of locations, it’s best to take a two-pronged approach.  First, perform straight-line calculations for a wider radius than desired to narrow down the choices; for instance, if you need to find locations within a 25 mile driving radius, calculate the straight-line distance to all possible locations within a 50 mile radius.  Then from these results, perform driving calculations to determine which fall within the 25 mile radius.  Again, this technique involves many iterative calculations, including the use of mapping software like Microsoft MapPoint, that can best be handled in an automated, customized template.  

Think a customized template could work for you?  Contact us for a free quote at customsolutions@cdxtech.com or call 1-877-CDX-TEC1 (1-877-239-8321).

Route optimization is simply determining the most efficient driving route between multiple destinations, and can be applied to complex delivery systems as well as Saturday afternoon errands.   

The advantages are pretty obvious.  Reductions in fuel use, the savings in employee travel time, and the goodwill created with a truly effective, earth-friendly conservation program are significant.  Route optimization provides a truly win-win opportunity for everyone.  But the critical question, particularly for small business, is:  What's the return on investment? 

The cost of optimization software, as well as employee time to learn and run the software, must be weighed against its benefits.  That's one of the reasons why we've developed a simple "one-click" Microsoft Excel template for route optimization, which provides no-risk testing of route optimization so organizations of all sizes can see its benefits firsthand before making any investment.  The template can be downloaded free from our website, and works with free trials of the software (CDXZipStream supplied by us at CDX Technologies, and Microsoft MapPoint).  

The template is very easy to use, and can be run on any PC that has Microsoft Excel.  Just enter the addresses in the route, make a selection regarding the preferred optimization type, and the optimized route is calculated.  It will even create a file that's compatible with many GPS systems.  No outside consulting or special training is required, and just about anyone with basic computer skills can use it.  See our YouTube video for a quick demonstration.

We've just posted two Microsoft Excel templates that allow CDXZipStream users to optimize driving routes or geocode addresses with just "one click".  These are pre-formatted spreadsheets which are programmed to import CDXZipStream data automatically – just cut and paste your address data into the template of your choice, click on a button, and the desired result is returned to the spreadsheet.  There's no need to input CDXZipStream functions or formulas on your own, and no prior experience working with Excel or CDXZipStream is necessary.

Both these templates use programming language in the form of Visual Basic "macros", which must be enabled through the security settings of Microsoft Excel.  These particular macros also offer additional security in that they are digitally signed by our parent company, Hughes Financial Services, ensuring that the macros originated from us and have not been altered in any way.  In today's blog post we're going to review the procedure for enabling macros, which varies depending on the version of Excel being used:

For Excel 2007/2010 -  First, open up a blank Excel workbook and at the top left hand corner of your screen, click on the round Microsoft Office button.  Then click Excel Options, then Trust Center, and Trust Center Settings.  Click on Macros Settings where you'll see the following options:

1. Disable all macros without notification
2. Disable all macros with notification
3. Disable all macros except digitally signed macros
4. Enable all macros (not recommended, potentially dangerous code can run)

Select any option you prefer, except for option 1, which will not allow macros to run under any circumstance.  We recommend you select options 2 or 3 in this case, and then just click OK until you are returned to the empty workbook.

Now open a CDXZipStream template and you should see the security warning bar underneath the ribbon like this:

(If you do not see the security warning, from the Microsoft Office button click Excel Options -> Trust Center -> Trust Center Settings -> Message Bar -> and choose Show the Message Bar in all applications when content has been blocked, then click OK to exit.  Close and then open the template again in order to see the security warning.)

From the Security Warning box, click on Options, and you will see this:

Choose Trust all documents from this publisher and click OK.  At this point the macros are enabled.  You will not have to repeat this procedure the next time you open the template.

For Excel 2003:  In a new workbook in Excel 2003, click on the Tools option of the Excel main menu, click Options, then click on the Security tab.  Click on Macro Security, and from the Security Level tab you will see the following options:

-    Very High.  Only macros installed in trusted locations will be allowed to run.  All other signed and unsigned macros are disabled.
-    High.  Only signed macros from trusted sources will be allowed to run.  Unsigned macros are automatically disabled.
-    Medium.  You can choose whether or not to run potentially unsafe macros.
-    Low (not recommended).  You are not protected from potentially unsafe macros.  Use this setting only if you have virus scanning software installed. Or you have checked the safety of all documents you open.

Any of these options can be used with CDXZipStream templates, with the following provisions:

For Very High Security, the template must be placed in (and run from) the "trusted location" XLStart folder, which is usually located in one of the following folders:

  •    C:\Documents and Settings\user name\Application Data\Microsoft\Excel
  •    C:\Program Files\Microsoft Office\Office11

Just place a copy of the template in the XLStart folder, and it will immediately be ready to run.

For High Security, when you first open the template you will see the following:

Check the box Always trust macros from this publisher then click on Enable Macros.  The template will open and will be immediately ready to use.  The next time you use the template, there will be no security warning.

When macro security is set to Medium, Excel displays the same dialog box where you can you can immediately click Enable Macros to access the template.  However, if you also select Always trust macros from this publisher, the template will immediately open the next time you use it, with no prior security warning.

Finally, the Low security setting will automatically enable macros;  this setting should only be used with appropriate virus protection installed.

Excel tends to be one of those pieces of software that everyone has (what other spreadsheet application is there?), but just a few of us really know how to use it. As software developers we see a wide range among our CDXZipStream clients in their ability to use Excel, and that's just fine; after all, not many of us have it spelled out in our job descriptions to be experts in Excel.

Excel is a terrfic resource - powerful, complex, and unfortunately it has a very clunky help facility. It must drive the folks at Microsoft a little crazy that so many people bypass the help file and go straight to Google to get answers, especially when there are so many good (non-MS) Excel sites and blogs on the web. One of our favorites is Chandoo.org (formerly known as the Pointy-Haired Dilbert), which, among lots of other resources, has a nice Tutorialon the Excel 2007 ribbon. But yes, this does beg the question: why does Microsoft build an application with such a complicated menu (i.e. "ribbon") that it needs its own tutorial?

In view of all this, we'd like to introduce our new client service where we do all the Excel legwork for you. Actually, it's not so new – we've been doing it all along as our clients request it, but we are now offering it as a standard service after realizing that some potential clients may forego using CDXZipStream because of issues with Excel. Just give us your specifications, and we will build a turn-key Excel file that performs the analysis you need using CDXZipStream. At your request we can include automation of spreadsheet tasks such as data input, sorting, and charting. And if you need data that isn't in our standard data feeds, we can also create a custom database that works with our patented interface for Excel.

We have also developed some free Excel templates that do basic CDXZipStream functions such as geocoding with a single click of a button (and absolutely no knowledge of Excel is required). These are available through our Facebook page - just give us a thumbs up, and the files can be downloaded from the "For Our Fans" tab.

If you think you'd like us to build your own custom Excel template, please give us a call at 1-877-CDX-TEC1 (239-8321) or email us at customsolutions@cdxtech.com. And let us be the Excel experts - so you don't have to be.