Home / Commentary / Engineering Hacks and Spreadsheets

Engineering Hacks and Spreadsheets

Tony AbbeyEditor’s Note: Tony Abbey teaches live NAFEMS FEA classes in the United States, Europe and Asia. He also teaches NAFEMS e-learning classes globally. Contact him at tony.abbey@nafems.org for details.

Before the term “hacking” was used to describe antisocial and probably illegal activities on the internet, it was used to describe patching together a computer program to do a specific, usually limited, task. Today, of course, this is called an app. Examples of hacking usage included calculating beam section properties, deriving von Mises stresses, etc.

At the time, this was a big leap forward compared with writing down hand calculations on a piece of paper. Simple mathematical errors could be eliminated and no handwriting deciphering was required.

Take the Survey!

NAFEMS is conducting a survey on engineers’ use of spreadsheets. As an incentive, everyone who completes the survey will be given access to extracts from the NAFEMS Publication “Business Value from Simulation Data Management” and you can enter for a chance to win a set of Beats Bluetooth earphones.

Click here to help us with the survey!

The tools available to create these programs were invariably based around the original BASIC language. This was often a spaghetti-like sequence of GOTO statements. More sophisticated users adopted FORTRAN.

Lean Approach to Hacking

A typical program written in the ’70s and ’80s would have less than 50 lines of code and would run in a strictly stand-alone mode. The attraction of coding in this way was that it was quick and simple. It was a very linear approach to programming: The code was not reusable and not encapsulated in any form of object-oriented paradigm. Many would consider it a disgrace to modern programming ethos! The same functionality created today could run to thousands of lines of code when fully compiled into a graphical user environment with many supporting libraries and functions.

One of the limitations of this approach was that it required a computer. In those days that meant a walk to the computer center, or to an early, centralized workstation such as the Wang or the PDP-10. The initial wave of programmable calculators such as the TI-59 and the TI-66 gave an alternative, but they were rather tedious with arcane programming languages and the requirement to keep a library of magnetic strips.

For me, the revolution in hacking came with introduction of the early personal computers. In the UK, the Apricot personal computer became widespread, with a good BASIC interpreter. I believe the TRS-80 played a similar role in the U.S.

Hacking with Spreadsheets

The simple and quickly coded BASIC or FORTRAN program predated the use of spreadsheets. When the first spreadsheets appeared, such as Lotus 1-2-3 and Symphony, they were very primitive and awkward in syntax. They did not generally supplant the one-off computer program. Creating applications was much more difficult than with today’s Excel. Excel arrived in 1990 and was an instant hit, certainly with me. It was a quick and simple solution to programming those engineering hacks.

Almost all of the FEA work I do today is supported in some way by calculations or data manipulation within Excel. A lot of the work is very ephemeral; it is a scratchpad replacing the back of the envelope, used today and deleted tomorrow.

Going Beyond Hacking

If I want to write a more sophisticated program, rather than the immediate hack, then I use MATLAB or Visual Basic. Python seems to have a big following as well. I am reluctant to get too deep into Excel to do more complex programming. It is almost impossible to check the flow of calculation, and the accuracy of cell referencing, in anything other than the most basic spreadsheet calculations. I know my limitations and it takes a tremendous amount of discipline to make sure that the spreadsheet is correct. This may be endemic across all of Excel usage.

You could argue that the same issue would apply to my simple hacks. However, my defense is that it is no different in that sense from hand calculations. Big errors can be made in hand calculations, but the scope is so simple that there is a natural checking process based on common sense.

A Recent Hack

An example of a recent engineering hack is shown in Fig. 1. The task here is to calculate the contact radius and maximum stress due to Hertzian contact of two rollers. This is an example I use in my non-linear e-Learning course. It is fast to set up the spreadsheet and easy to play around and check the sense of the numbers. I have tidied this up for the course and for the presentation here. Normally I wouldn’t spend any time on the color coding and insertion of the parent equations.

Fig. 1: Excel spreadsheet example.

Fig. 1: Excel spreadsheet example.

An interesting alternative to creating my own spreadsheet here is to use any of the many calculators that do exactly the same task online. There are a tremendous variety of engineering calculators out there. The only problem is that you are not always lucky in finding exactly the right application that suits you. There is also always the concern that the app might not be accurate.

How Do I Hack in the Future?

Taking this idea one step further, I have recently been exploring the possibility of an online programming language that will allow me to quickly create this type of hack. I got excited when I saw there was actually a program called Hack! However, a quick look revealed a rich set of object-oriented constructs to create extremely sophisticated applications.

I also looked at the fascinating cloud-based program called Ruby. At least part of this effort is aimed at teaching young children to code. So, I thought it was probably worth a look! It is a very pleasant and engaging interface; amazingly, it quickly gets too sophisticated for my needs.

Going back to my roots, there are numerous virtual implementations of programmable calculators online. However, these are not quite sophisticated enough.

Somewhere in the middle must be a straightforward pragmatic coding language, suited to aging hackers such as myself. If anybody has any recommendations, please let me know.

About Tony Abbey

Tony Abbey is a consultant analyst with his own company, FETraining. He also works as training manager for NAFEMS, responsible for developing and implementing training classes, including e-learning classes. Send e-mail about this article to DE-Editors@digitaleng.news.