Project Management

PBA Mechanical Engineer and Project Leader, K. Fitzgerald, PE Offers Insight into Automation for Calculations and Design

Calculation software, BIM files, and spreadsheets all offer a wealth of information that could be useful in other engineering applications.
By Kenneth Fitzgerald, PE January 8, 2020
Photo courtesy: Peter Basso Associates

Engineers have many tools at their disposal that streamline calculations, equipment selections, drafting and modeling. Calculation software, BIM files, and spreadsheets all offer a wealth of information that could be useful in other engineering applications. It is not uncommon for these tools to be dependent on one another too (i.e. data from load calculation software can be used in a ventilation calculation spreadsheet which then needs to populate data in a building model).

One of the challenges that come with these tools is the ability to transfer data. There are many times when information needs to be copied from one source to another, but there are only a few ways for this to be done both quickly and efficiently. This forces data to be transferred manually, which is not only time consuming, but it creates many opportunities for error. However, even though there may be no way to quickly move data natively between two tools, it may be possible to create a method for resolving this issue quickly and effectively.

By automating the data transferal process, many of these problems can be avoided. Scripts can be written and utilized to quickly, efficiently, and accurately communicate data. In some cases, macros (which are readily available in many document processing suites) may be all that is needed. In other cases, something more advanced may be required whether it be writing a standalone script or utilizing visual programming languages (which are available in some modeling programs). The tasks that are worth automating are generally tasks that are repetitive, time-consuming, and create room for error when done manually.

The following is a list of common tasks that will often see the most benefit to automation, quickly outweighing the time spent developing a script.

Example: Manipulating and Formatting Spreadsheets

Macros are commonly used to manipulate data in spreadsheets. If a spreadsheet performing complex calculations needs to be formatted in two different ways depending on whether it is for internal use or is going to be sent out to a client, then a macro could be written to change the formatting. This eliminates the need to manually apply formatting changes every time this spreadsheet is sent to a client. Sometimes these spreadsheets are linked into drafting software for scheduling equipment and need to have a separate copy made with most of the formatting removed. This can also be easily automated and performed instantly with a script.

Example: Reading a Ventilation Spreadsheet to Size Terminal Units

After calculations are performed in a spreadsheet, some of the resulting data may need to be brought into BIM software. This is where visual scripting tools shine. These tools are designed to work with the data in a building model and often have the ability to interact with spreadsheets and other external sets of data, so information can easily be transferred into and out of the model. A visual script or ‘graph’ can be developed. It takes information from a spreadsheet and creates or modifies elements in a model. One example of this could be a script that takes data from a ventilation calculation spreadsheet, determines what size each air terminal unit should be, and modifies the terminal units in the model to be the proper size. The screenshots below show a simple example of this. The first screenshot is of a basic table listing a single terminal unit, its required airflow, and its required size. The second screenshot shows a potential script to take this data and apply it to the actual terminal unit in the model. It selects all the terminal units in the model, then filters down that list based on the unit IDs provided in the spreadsheet, and finally applies the correct airflow and size to the unit.

Photo courtesy: Peter Basso Associates

Example: Copying Model Data to a Spreadsheet

Many BIM programs have the capacity to perform calculations within the modeling environment. However, the calculations they can perform may not always line up with the engineer’s needs. Sometimes the software only provides the results with very little explanation of how the results are achieved, so the accuracy may be questioned. In these instances, engineers may find that they need to manually transfer data from the model to a spreadsheet to perform the calculation. By taking these calculations out of the software and performing it in a spreadsheet it allows a record to be saved that can provide some insight as to how the result was achieved.

If a spreadsheet is already in use to perform duct pressure loss calculations, for example and each line in the spreadsheet is an itemized instance of each straight run of duct and each fitting, a script can be written that “reads” the duct system and formats the data in a way that’s consistent with what the spreadsheet expects.

There may be some calculations where the engineer may still need to manually finish the calculation, but the script can almost instantly populate a majority of the data. In the case of the duct pressure loss calculation, there are many types of fittings each with numerous parameters that go into determining the pressure drop coefficient of the fitting. While it is certainly possible to build all the fitting coefficient lookup tables into a script, that level of complexity may not be necessary to build into the script.

Example: Automatically Sizing Roof Drains

Sizing roof drains is another task that can be easily streamlined with a script. Since roof drains are most often sized based on the roof area they serve, it is a simple process to select a proper size. In a model, a region could be drawn to indicate the area served by a roof drain. Programmatically the area of the region could be checked against code to determine the proper size, the roof drain in the region could be selected, and the size could be changed as needed to comply with code. This could even extend to check the sizes of ALL the connected roof conductor pipes by checking the pitch of the pipe and determining the proper size for the anticipated flow.


The main benefit of automation is the time savings. Manual data entry is typically not a complex task but can take hours to get done regardless. With today’s fast-paced building design industry, it is important to find ways to save time wherever possible. The time savings can even be recursive in some situations. Changes to a project are a common challenge design teams face, and the closer to the deadline the change occurs the more difficult it is to address. By streamlining these time-consuming tasks, a last-minute change may not be as much of a hurdle as it would have been before.

Automating a task can also minimize the number of mistakes made. If a task involves manually copying data between sources, there are plenty of opportunities for the wrong value to be transferred. With proper setup, a script will return the expected results every time it runs. Regardless of manual versus automated; an engineer should always spend a few minutes to validate the results.


The biggest challenge for some firms may be finding the people to develop and maintain these scripts. If it is not typically expected that engineers know how to program, it could take some time for these processes to get up and running. Fortunately, there are countless resources available to learn how to program and interested staff could be trained at little-to-no cost to the company.

Once there is staff in place with programming knowledge, the next challenge is balancing time between working on the projects that bring in money and working on the scripts that save the firm time and money. Some tasks are straightforward and quick to automate, but many will be complex and will take more time to write and troubleshoot. Since time is such a critical factor, it is important that time savings from having the script outweigh the time spent developing it.

An important factor to consider is that when a script is run that copies data from one source to another, there is a risk that data gets unintentionally overwritten. There are ways to mitigate this risk, but careful planning needs to occur to best prevent it. As a precaution it is easy to add a step to the script that reminds the user about this risk and has them confirm before any data is transferred. In scripts that are used to push data to empty cells in a spreadsheet, it could also check if the cells are populated and alert the user if there is risk to overwriting what was already filled out.

Automation Can Save Time and Mitigate Errors

While automation has numerous benefits, it is important that the engineers utilizing the tools know what to expect from the results. When a calculation is performed automatically, it may be tempting to take the results and move on. However, it is important to review how the result was reached. Every project will have something unique about it, and while the script may have been written to address most cases there could be an unforeseen scenario (or perhaps just too uncommon to justify the time to build it into the script).

Automation can assist in many of the daily tasks of an engineer. By streamlining some of the repetitive tasks (particularly when it comes to copying or manipulating large sets of data), engineers can dedicate more time to complex tasks that require more involvement. Additionally, by programmatically working with data the risk of making errors can be greatly reduced and the time spent validating the results is minimal compared to the time savings. Getting started with developing scripts can be an initial challenge depending on the skills available in the firm, but it may be well worth the time investment in the long-term.

This article originally appeared on Peter Basso Associates’ website. Peter Basso Associates is a CFE Media content partner. 

Kenneth Fitzgerald, PE