Data Automation Professionals Development Methodology

Overview

We take our projects very seriously and follow a time proven development process. From our overall solutions development methodology to our proven hybrid project management to our best practices for generating clean, fast, efficient, and bug free code, we know how to create optimal solutions that work for you and your company.

• Methodology – Our overall methodology and development philosophy is the basis of our success.
• Project Stages – How we manage our Excel customization projects from discovery to development to support.
• Best Practices – We utilize a number of best practices that increase performance and minimize risk which results in custom Excel solutions that work amazingly well.
• Working Solutions – Curious about what we've created in the past? We're proud of all of our work and present a small sample on these pages.

Methodology

Data Automation Professionals stands by our methodology that begins with you and your end users. We keep close tabs on your goals and that of your customer, so we understand where the pain points are that represent real opportunities for growth, scalability, and ease of use. Data Automation Professionals continuously finds solutions that reduce errors, increase efficiency, and save time.

Once the value to the client is identified and the problems bounded, we chart a high-level path and begin the work of designing the solution. A tool we frequently use to facilitate the design process is the use case — a set of well-thought-out use cases validates project goals while ensuring complete coverage of every goal. Use cases produce an effective design that supports the testing process and ultimately ensures a solution that works.

To aid rapid development and production of solid code in our Excel automation applications, Data Automation Professionals maintains the most comprehensive and well-tested set of code libraries that exist in the industry. Our libraries are routinely maintained and augmented as needs arise. Extensive regression tests ensure the libraries are always production ready. These libraries coupled with automated code generators reduce the amount of custom code required to complete the automation solution while enabling us to create powerful and effective custom Excel solutions tailored to your specific needs.

In development, Data Automation Professionals utilizes a technique called Extreme Programming. This methodology utilizes rapid and repeating cycles of development, testing, and feedback which allows for quick refinement of the custom application as the project is progressing. It also results in a better-informed client with clear expectations, continuous project updates, and meaningful accountability, creating strong and collaborative client relations.

Project Stages

Every solution we develop has a beginning and an end. During the time we are creating your solution, we will move through various stages that define what we are doing and your role in that process.

There are two classic and proven project step progressions: linear and extreme. Linear is the classic discovery to design to development to testing to support model. Extreme is a more interactive and iterative process. We've blended the two and lean on whatever method best suits the situation and your working style.

Linear Progression

Linear progression stages occur one after the other. This model requires more attention to the discovery and design stages to minimize rework in the development and testing stages. A robust set of use cases helps to mitigate such risks.

Discover

At the start of any effort, the current process is explored, assessed, and bounded. It is important to fully understand the steps that are causing an extraordinary amount of time to complete or increase the propensity for erroneous results. Equally important is understanding the user's cognitive model of the data and the process of working with that data. The overall objective of this stage is to develop a clear and descriptive target goal that resolves the known pain points and optimizes the user's experience. This is also the time to define and bound the project scope to ensure a clear path to a successful conclusion. This stage culminates in a project scope document including time estimates.

Design

This stage takes what was learned in the discovery stage and, with additional analysis of the internal processing and user experience, a design is created documenting the user interface, the internal data storage, and the process architecture. At the start of this stage and if not already done as part of the discovery stage, multiple use cases are documented. A use case is an example scenario the final solution will face — it defines whatever is necessary to perform the application's part of working that scenario. As the design develops, it is important to revisit the use cases to ensure that they are still accommodated by the evolving design.

Develop

This is where the rubber meets the road. Where we put code to work and leverage our years of experience, network of expert developers, extensive pre-existing code libraries, and powerful code generators. Our overall goal during this stage is to produce quality code that is well-documented and easily maintained. Not to mention fast and sporting as lean a footprint as we can muster. It isn't always easy and challenges happen, but we've never met a challenge we couldn't handle at the end of the day. And, if getting there requires a few trips to the library, that's on us.

Test

We perform numerous unit and limited integration tests well before you see the results. But we also rely on you, your team, and your end users to help us with this stage. It's imperative that we push all the buttons, pull all the levers, and cram as much junk data into the work in progress as possible while we are in the thick of it and reworking code is easy. We would rather fix everything now versus getting a call a year from now.

Support

But, if something comes up that slipped by us and the testers, we're not going to leave you high and dry. We guarantee all our work for as long as you use our solutions and will fix anything without charge if the work is within the original project scope and isn't a result of an unpredictable change in Excel functionality as new releases are produced. You have our word.

Extreme Progression

Extreme development or programming takes the above stages and iterates through them repeatedly and quickly, with the emphasis shifting to a cycle of designing, developing, and testing as the solution develops and matures. The advantage to this method is that feedback is possible earlier in the process which, if executed smartly, leads to a solution more optimized to the nuances of the process being automated. Most of our projects lean heavily on extreme progression.

Best Practices

At Data Automation Professionals, we follow a strict set of self-imposed guidelines to ensure our custom application designs are solid, and our automation code is optimally structured and thoroughly documented. We rigorously use unit, integration, and regression testing to optimize performance and minimize issues. Our extensive collection of code libraries provides generic and tested utility functionality that minimizes the amount of custom code in any automation solution. We employ powerful code generators that instantly create low-level accessor and maintenance code that further alleviates the amount of custom code.

Use Cases

We try to employ use cases wherever possible. A use case is a real use scenario documenting the input, process, and expected output or result of a bounded usage situation. When capturing use cases, our goal is to define enough to define the entire system. Overlapping use cases are often created to ensure ample coverage. Use cases can also cover exception cases such as how missing or incorrect information is handled. The creation of use cases is your responsibility and represents an aspect of the overall solution design. Our responsibility is to challenge the set of use cases with the goal of ensuring ample coverage. We will do this by asking you "what if" questions given the existing use cases. Your response will lead to an affirmation of completion or a request for additional use cases to cover the unhandled scenarios. Use cases not only help define the design, but they also guide development and aid testing and help ensure a custom solution that works for you.

Documentation

You may want, at some point, to bring your own Excel hot shots on board and pass maintenance responsibility to them. We strive to document our code as much as possible without losing the code itself so that your developers can easily slip into our shoes and make the changes you need. Documentation also means smart and intuitive variable names. You will never see variables named "i" and "j" in our code.

Structure

Structure, also known as structured or modular programming, is a classic technique of ensuring that code is organized in narrowly defined routines and that as many routines as possible are made generic so they can be reused. We are relentless structured programming hounds!

Testing

We employ all the classic testing strategies: unit, integration, and regression. Unit testing is employed as the code is being written and involves applying as many scenarios as we can think of to the routine being constructed. This is a good time to ensure that we have enough pre-flight code (normalization and validation of parameters) in the routine so that the routine doesn't blow up when something bizarre is thrown at it.

Integration testing is a higher-level test process where we take use cases and run end-to-end tests ensuring that any user input and variations in imported data are handled. From the database reads and writes to the final reports and exported files — our results are solid and per specification.

Regression testing is used to handle situations where there is a reasonable probability of "whack-a-mole" and/or there are complex inputs. The "whack-a-mole" conundrum is when we change one low-level routine to handle a high-level change and one or more other medium and high-level functions break. Regression tests can reveal these phenomena earlier versus later while we are buried in the code. Of course, if we've been doing a good job with the structure, this should never be a problem. When we have complex inputs, regression testing can help ensure that adding functionality to handle a few new data use cases doesn't break the processing of the thousands of other data use cases already implemented and tested.

Code Libraries

We've been at this for years and have been adding to and reworking our massive collection of routines in our code libraries. Having built hundreds of world-class solutions that run the gamut of tasks our clients have dreamed up, we have just about everything covered. From basic importing of crazy data from the IT department's decades-old database, to scraping web pages, to punching out PDF reports and PowerPoint presentations. These libraries not only save us time, they also provide us with a stable base of code that has minimal risk of breaking under pressure.

Code Generators

We employ code generators that generate the code we need to access various Excel objects without having to manually write a routine for every one. One example is our worksheet accessor code generator that generates a complete set of accessors for the locally defined range names and all Excel Tables defined on the worksheet. Speaking of range names, we employ a powerful range name generation and maintenance tool that makes maintaining all those range names a breeze. Agentic AI has also proven to be an invaluable tool to more quickly generate and test solutions.

Working Solutions

Custom Excel Workbook Applications

We excel at Excel and anything Excel automation. We have yet to encounter a project, problem, or process that we could not automate to some degree with Excel. Seriously, sometimes it has nothing to do with a worksheet, so we just hide the workbook and display a dialog! Gathering, analyzing, reporting, and distributing data in any area of finance, investing, accounting, marketing, sales, customer service, or centralized enterprise planning software (ERP) systems is where we go and, if we haven't been there yet, we're ready to do it now.

Here are some examples of solutions we have built:

• Customize real-time and historical financial market data analysis.
• Automate instruction transmission to direct access trading systems.
• Condense, clean, join, merge, segregate, analyze, interpret, translate and format data.
• Intuitive and flexible report generation.
• Effective and useful graphical representations of analyzed data.
• Allow easy and robust data entry and generate dynamic and meaningful results suitable for presentation to management and clients.
• Clean, aesthetic, and professional worksheets, custom worksheet forms and more.
• Add security and flexibility with custom Excel add-in functionality across multiple spreadsheet applications.
• Integrate Excel with databases, e-mail systems, and word processing applications.
• Integrate and consolidate multiple worksheets and multiple workbooks across networks.
• Maintenance and auto-generation of html pages for websites.
• Dynamic web page interpretation, analysis, and data gathering.
• Read and process large quantities of database information stored in SQL Server, Access, and other database systems.
• Populate and update databases.
• Generation of flat (function and macro free) worksheets and html reports for distribution to customers and clients.

C++, C#, VB.NET, and VBA

VBA is a standard development language for internal and distributed applications and utilities with extensive user interfaces that need to be built and maintained quickly. We also work in C# and VB.NET.

• Effective, clean, and professional application user interfaces.
• Server-utility applications (web services) to move data between server applications and external sources and destinations.
• Compiled (protected code) Add-Ins for Excel, Word, PowerPoint, and Outlook.

Custom Access Applications

Access can be a practical and cost-effective front-end for database maintenance and reporting.

• Integrate Access applications with Visual Basic for powerful and flexible data analysis and data-dependent application solutions.
• Maintain and report small and medium-sized databases for any type of data-dependent application, or from a centrally stored Access, SQL Server, or other database.
• Integration with Excel, Word, PowerPoint, and Outlook.
• Use of Access VBA to enhance Access and create more powerful and flexible solutions.
• Query construction using form input.

Data Recovery, Cleaning, and Recovery

One-time services are available for recovering, converting, and cleaning old or damaged data.