Geographic Access Analysis with CDXZipStream

Geographic Access Analysis involves evaluating the accessibility of a location or multiple locations based on geographic and demographic criteria. This type of analysis is vital for sectors like retail, real estate, healthcare, and public services to determine the most strategic spots for their operations and to understand their audience better.

CDX Technologies offers a suite of tools that can enhance these analyses, with CDXZipStream being a standout option for Windows Microsoft Excel users. Apple Mac users can employ our CDXGeodata product in a similar way. We’ve created a custom template to automate the creation of Geographic Access Analysis reports. Using this template is as simple as setting up lists of addresses for your proposed locations (stores) and targets (customers). The locations can be specified as a full address or a simple zip code.

The template first geocodes all the locations to find latitude and longitude. If you already have this data you can enter it in the spreadsheet directly These points of latitude and longitude are used to calculate straight-line distances between all combinations of target and locations So to obtain a report showing the closest locations within radius of each target, all we need to do is specify the radius distance (such as 10 miles) and click a button to automatically create the report. 

Here is a video showing how this works:

To obtain the template you can login to your on-line account and select "Buy Data Updates" from the CDXZipStream or CDXGeodata product management areas. Select the "Geographic Access Analysis Template" from the drop-down list and proceed through the purchasing process. The current price of the template is $99.95. For CDXZipStream you must own the Router, Basic, or one of the premium demographic versions to see this as an option.

 

CDXZipStream Basics - Getting County Data for a Zip Code

In today's data-driven world, having accurate and detailed geographical information can significantly enhance your analysis, especially when working with location-based data in Microsoft Excel. One common requirement is to find the county name for a given zip code, which can be a tedious task if you're dealing with a large dataset. Fortunately, CDXZipStream simplifies this process. In this article, we'll guide you through the steps to quickly and efficiently get county data for any zip code.

To find the county for a zip code, start by entering the zip codes you need in one column of your Excel worksheet. For example, let's say you place them in column A, starting from cell A2. Then perform the following steps:

1. Click on the cell where you want the county data to appear. This could be directly next to the corresponding zip code, for instance, in cell B2.

 2. Click on the CDXZipStream toolbar, then choose the "Formulas" option. This opens a dialog box with a range of functions. You can also use the CDXZipStream right-click dialog for this.

3.From the function list, select "Insert CDXZipCode Data" (or a similar function, depending on your version of CDXZipStream) to look up geographic details related to a zip code.

 4. In the function parameters, you'll need to specify the cell containing the zip code (e.g., A2) and indicate that you're seeking the "CountyName".

 5. After setting your parameters, click OK to run the function. The county name for the specified zip code should now appear in the selected cell

If you have a list of zip codes, you can drag the corner of the cell (where you've just entered the formula) down the column to apply the same function to other cells. Excel's auto-fill feature will automatically adjust the cell references, allowing you to get county names for all listed zip codes quickly.

Please click on the video below to see a short demonstration of how it works: 

 

 

 

 

CDXZipStream 5.5 Released

We've upgraded CDXZipStream to increase the speed and performance of the CDXRadius function. This was accomplished by using new caching techniques and by restricting the zip codes being evaluated. A “boxing” method was used to only compare zip codes that could possibly meet the radius criteria based on their latitude and longitude.  You will especially see a large improvement when dealing with the Canadian Postal code database that has nearly 900 thousand records.

We are also now using Office 365 dynamic formula arrays to specify the size of the matrix when using the CDXRadius right-click functions. Previously we pre-calculated and set the size of the formula array separately.  This option can be turned off if you wish in the right-click function dialog if you are using an older version of Excel.  If you are not familiar with this new Excel feature, you can simply enter a formula such as “=CDXRadius(“07869”,5) in a cell and Excel will automatically scale the output range to accommodate all the data.

To get this new feature you need to install CDXZipStream version 5.5.2 or better, which is the current trial version on our site. There is no cost to get access to the update. The installation file for this can be downloaded here.  

The Conundrum of VBA Macros: Unraveling Microsoft's Restrictive Stance

In the world of software development, automation is king. And when it comes to automating tasks within Microsoft Office applications like Excel, Word, and PowerPoint, Visual Basic for Applications (VBA) macros have long been the tool of choice. However, recent trends indicate a tightening of the reins by Microsoft on the use of VBA macros, leaving developers and users alike grappling with the implications.

VBA macros provide a powerful way to automate repetitive tasks, streamline workflows, and enhance productivity within Office applications. With VBA, users can write custom scripts to perform actions ranging from simple formatting tasks to complex data manipulations. For years, VBA has been a staple tool for power users and developers looking to extend the capabilities of Office applications beyond their out-of-the-box functionality.

At CDX Technologies, we've taken the step of code signing our Templates and VBA add-ins. This identifies our organization as the originator and only editor of the code.  But the advantage of this has been diminished by Microsoft's recent changes.

Microsoft's approach to VBA macros has become increasingly restrictive in recent years, driven by concerns over security and potential misuse. One of the most significant moves was the introduction of the "Macro Security" feature, which prompts users to enable macros only from trusted sources or disables them altogether by default. While this feature aims to protect users from malicious code, it also adds an extra layer of friction for legitimate macro usage.

Moreover, Microsoft has implemented additional measures to restrict VBA macros, particularly in cloud-based versions of Office applications such as Office 365 and SharePoint Online. These platforms often limit or disable the execution of VBA macros altogether, citing security and compatibility reasons. This shift towards cloud-centric computing has left many traditional VBA developers feeling sidelined, as their once-reliable toolset becomes increasingly marginalized.

Another area of concern is Microsoft's emphasis on alternative technologies such as Office Add-ins and Office Scripts. While these newer solutions offer some advantages in terms of security and cross-platform compatibility, they often lack the flexibility and power of VBA macros. Additionally, transitioning existing VBA codebases to these new platforms can be a daunting task, requiring significant time and resources.

Despite these challenges, there are still ways for developers to leverage VBA macros effectively within the Microsoft ecosystem. One approach is to adopt a hybrid strategy, combining the best aspects of VBA macros with newer technologies like Office Add-ins. By encapsulating VBA functionality within secure, sandboxed environments, developers can mitigate some of the security concerns associated with traditional macros.

Furthermore, Microsoft has introduced tools like the Office JavaScript API, which allows developers to interact with Office applications using modern web technologies. While not a direct replacement for VBA macros, these APIs offer a path forward for developers looking to modernize their Office automation workflows while maintaining compatibility with legacy systems.

In conclusion, while Microsoft's stance on VBA macros may seem restrictive at first glance, it's essential to recognize the underlying motivations driving these changes. By prioritizing security and embracing newer technologies, Microsoft aims to create a more robust and resilient ecosystem for Office users and developers alike. While the transition may be challenging, embracing these changes can ultimately lead to more secure, efficient, and future-proof automation solutions within the Microsoft ecosystem.

2022 American Community Survey Data Release

The CDXZipStream ACS data feeds for state, county, ZIP code (ZCTA), place, CBSA (Core-Based Statistical Area) and Tract have been updated with the latest data releases from the U.S. Census Bureau. These data are from the American Community Survey, which surveys hundred of thousands or individual addresses monthly. It is the largest and most detailed survey that the US Census conducts. These feeds are available in the CDXZipStream Premium ACS version. Data are aggregated over the years 2018 through 2022.

 

The demographic areas covered in all ACS feeds are: age, population, household size, household income, individual earnings, race and ethnicity, educational attainment, school enrollment, housing value and rent, mortgage status, and marital status. For a full listing of all data fields included in the feeds, please download the CDXZipStream Data Feeds and Definitions file here.

 

If you already own CDXZipStream ACS Premium and have an active data maintenance contract, select the "Account" option on the CDXZipStream Excel menu and click on "Check for Available Database Updates". Then select the option to update your premium database. 

 

For users without an active account you can update your data feeds by logging into your on-line account on CDXTech.com. Under "My Account", then "Product Management", select "Buy Data Update" and then purchase the Premium Maintenance contract. If you don't have the correct CDXZipStream version select "Upgrade License" in the account area and select the option to upgrade to the ACS version. The latest data will automatically be included with your purchase.