Fig2 Voltammetry Template

Recovering Computing’s Broken Efficiency Promise with Spreadsheets

May 15, 2020
Recovering the Broken Promise of Increased Efficiency in Science, Industry, and Engineering Enabled by the Advent of the Personnel Computer

In the early days of the personal computer, we were going to be freed from routine data-handling tasks in science, industry, and engineering, with heady promises of vast new efficiencies. We would shed our clipboards, pencils and ledgers while our data flowed along digital Easy Street and organized itself into summaries and archives. Everyone would become a programmer, taking full advantage of digital organization and instant computation.

Computing was almost entirely text-based. A graph was an adventure, a photograph was an impossibility. No two printers were alike, and a floppy disk holding the operating system and the BASIC language interpreter barely had room for your program. Standard input/output on an IBM PC was a carryover from mainframe computers, and was wonderfully flexible, but so slow that it was practically useless.

 Before long powerful, generalized word processor and spreadsheet software defined our interactions with computers. When the graphic user interface took over in the 1990’s, the occasional programmer had to relearn how to program with a much higher bar to get over. Now, most of us have given in and become consumers of software, not programmers.

That would be fine if our computers did what we asked of them reliably, conveniently, and affordably. Too often, instead of being creatively unleashed by the flexibility of our computers, we are boxed in by the assumptions and compromises made by the purveyors of the canned software that we use. Add to that the cost of support and upgrades, and the controlled access that goes along with commercial software.

Our purpose here is to recover the promise of that open-ended capability for harnessing the power of hardware and software. In addition, it would be nice to have some protection from predatory pricing and planned obsolescence. The mechanism, again, is to use the spreadsheet, which is now almost universally familiar, as the programmable element to acquire, massage, and present your data while performing the necessary control and housekeeping tasks.

Getting real
Several manufacturers of data acquisition equipment provide Excel templates for general purpose data acquisition and control for real-time applications. The questions are, will it do your job, how well, and with how much effort to get started? How do you evaluate feasibility?

Make a list:

• Quantify the necessary range, precision, and time resolution of your inputs

• Count the analog outputs, noting voltage, current, power level, etc.

• Count the digital inputs and outputs. Again, note power levels for digital controls

• Think about the required cycle time for gathering inputs and updating outputs

In the case of an electrochemistry instrument, we need to characterize the relationship between voltage and applied current across a membrane. The voltages are not large, but need to be known within a few µV. Current is to be in the range of +/- 100 µA, and known to the nA. Because electrochemical reactions tend to be slow, cycle times of a few Hz are more than fast enough. No more than three differential voltages would need to be measured, at pairs of high-impedance electrodes, and at least one additional voltage input would be needed to monitor the actual applied current.

The electrodes require an input impedance in excess of 10 to the 13th ohms, and the measurements must be differential. To resolve a part in several million, an analog-to-digital converter (A/D) with 21 bits of effective resolution is required. Because high-impedance sources inevitably pick up ambient noise, a 24-bit delta-sigma data system with good noise rejection is suggested. For example, the Lawson Labs Model 302 24-bit data system has six available input channels, with two available for future expansion in this case.

A precision current output in that range is not a standard item. For special functions, the Model 302 has an expansion port that accepts a stacking circuit card. Analog output channels are used to control the current source, given a little customized hardware. If temperature stabilization was to be needed, a spare analog output could drive a proportional heater, and a spare analog input channel could monitor a temperature sensor. The spreadsheet can perform any software-in-the loop control that might be needed. Up to 8 digital outputs are available to turn on or off pumps, stirrers, etc., and digital inputs can be used for limits, start, stop, or other control signals.

Because vanishingly small currents are involved, you need to pay close attention to avoid ground currents and possible ground loops. The Model 302 is optically isolated from the host computer. That isolation is essential in this class of applications. That discussion sums up the hardware requirement. Usually, the next question is, does the control cycle fast enough? As it happens, in this case, speed is not an issue.

Measurement considerations
There is a tendency to take too much data too fast, and then to average it to remove noise. The result is much the same as taking quieter data at a lower data rate, except for all the extra computing overhead involved. Start by determining the highest frequency of meaningful data coming from your sensors. Electrodes with megohm impedances driving stray capacitance of even a few pf have time constants measured in seconds. There is rarely a benefit to be had by a sampling interval less than a fourth of the time constant. In this case, one scan per second is more than enough.

The physical system being monitored has a maximum rate of change. For example, when controlling the temperature of a water bath, it can only heat or cool so fast. Adjusting a system at a rate faster than it can respond is an invitation to oscillation. Cycling faster than the response time will make the control worse, not better, because the feedback path is effectively open over very short time periods. That said, you will need to cycle fast enough to do the job.

A generalized, computer-in-the-loop application based on a spreadsheet will probably stop being practical much above 10 updates per second. For control loops that must run faster than that, use a hardware loop controller with a software supervisory interface. Remember that a computer-in-the-loop control system’s performance will be bounded by the worst-case latency, not by the average latency, so leave yourself a generous timing margin.

The next step is to define the user interface and the desired outputs. The control rules can be dead simple or fabulously complex, whatever you need. The rules can be incorporated into tables or formulas with logic for special cases, all done without programming skills by using cells on a spreadsheet with the familiar rules for math and logic. In this case, a straight-forward spreadsheet with color-coded cells for various types of input was all that is required. The datapoints occupy columns in the spreadsheet, in the usual fashion.

Customizing the Spreadsheet Template
The free template available for this application has all the basic functions included. General-purpose templates will draw in the data and leave to the user calculations, formatting, display, operational controls, graphics and control loops, if any. The number of channels to scan, the underlying data rate, block or running averaging, zero drift suppression and other data acquisition setup information is entered once to begin.

The template for characterizing a membrane includes two modes of control. Either the current is controlled and the voltage measured, or, the voltage is controlled, and the current is measured. That means the setpoint is either a voltage or a current, can be fixed, ramped, or stepped, or made to follow an arbitrary AC waveform. When the current is servo’ed to match a voltage setpoint, the gain of the system depends on the resistance of the membrane, which can vary over five orders of magnitude.

A single gain setting for the control loop will not cover that wide a range. The user can enter a gain value in an input cell, or an adaptive algorithm will find a suitable gain setting over time. With a purely resistive membrane, Ohm’s Law dictates the relationship of voltage and current (see Figure). Variations from that linear relationship are due to the chemistry, which is the subject of study.

Expansion is simple. Copy your spreadsheet to a new tab and connect another instrument to another USB port. If you want to run more than eight systems at one time, you will need something faster than an entry-level computer. We have not yet explored the expansion limits for a fast machine.

You can’t change the spreadsheet while data is being taken. If you edit cells during acquisition, Excel will quit. It is possible to protect cells in sheets to prevent any possibility of a crash. For serious applications, that is the recommended process. Protection does add complexity, and requires the extra step of unprotecting cells to allow editing, when appropriate.

The other thing to watch out for when you are using an Excel template for control is that it must keep up with your data. The input data is buffered, by necessity. If data is piling up in the input buffer, the control loop is acting on old data. If your computer cannot reliably stay current with the data, a mechanism must be added to prevent any control function from acting incorrectly based on old data. Very long acquisition sequences with complex graphics can slow down execution as the spreadsheet expands. Make certain that your system is fast enough even during worst-case conditions.

Avoiding Obsolescence
If you are going to take back ownership of data acquisition and control tasks, you cannot be expected to do it all over again each time the operating system changes, or as computer hardware evolves. Look for hardware with defined dynamic link libraries (dlls) that are kept up-to-date by the manufacturer. That way, downloading the latest dll can be all you would need to migrate to a new computer and operating system. Contrast that to the more common scenario where the old computer with slots finally fails, and the whole system goes in the pile for recycling.

The particular example detailed here is not limiting in any way. Beyond simple data acquisition, monitoring, and alarming, the spreadsheet-based approach is suitable for quality control, gas chromatographs with or without autosamplers, viscometers, and mechanical testers, just to begin a list of applications. If you already know how to use Excel, you start halfway up the learning curve.

Ask yourself, what do I want the system to do? And what should it look like? Then, you make it happen for yourself. You are then positioned to maintain your own system and adapt or improve it as you may require. Will Microsoft obsolete Excel? Not without leaving an upgrade path. Use hardware with a history of non-obsolescence, and you can take back ownership of your data acquisition and control systems. Sounds promising, doesn’t it?

Sponsored Recommendations


To join the conversation, and become an exclusive member of Electronic Design, create an account today!