Spreadsheet Add-Ins: Not Bleeding Edge, But Still Extremely Useful
Of course, we’re speaking of spreadsheet programs. Today, when you speak about a PC-based spreadsheet, it’s understood that you mean Microsoft Excel.
For years, manufacturers of data acquisition hardware have supplied spreadsheet add-ins. In fact, some of the first data acquisition software arose from this concept (see sidebar). While you might’ve thought the concept was long ago exhausted, you’re wrong. True, only a limited number of basic operations are possible with a spreadsheet, but hardware vendors continue to add new wrinkles to make their spreadsheet utilities ever more useful.
Key Words: Fast and Easy
First, let’s recognize that spreadsheet add-ins aren’t for everybody. They’re suited for a nonprogrammer who wants to perform a simple task quickly. Rather than manually logging test data onto a clipboard and keyboarding values into a spreadsheet for presentation and analysis, an engineer might like this process to happen automatically. Or, perhaps that person simply wants to connect a few probes and take a quick look at the waveforms without going to a specialized acquisition or analysis package.
The two key words are fast and easy. In contrast, two words seldom associated with spreadsheet add-ins are comprehensive and complex.
What makes Excel so attractive? In short, it’s a tool that virtually every test engineer owns, that virtually everyone is comfortable with, and that over the years has picked up surprising power. For instance, Excel’s standard analysis tools include operators for complex numbers, statistical functions, regression analysis, covariance, exponential smoothing, Bessel functions, and Fourier analysis.
The Doctor Speaks
Given the standardized platform from which data acquisition add-ins operate, it’s not surprising that they appear very similar. Digging beneath the veneer, though, uncovers some interesting differences.
An example comes from the most recent entry in this category: DoctorDAQ from United Electronic Industries (UEI). The standard version costs $195, but if all you need is one channel of analog input, the Lite version comes at no charge.
After loading the software, a new item appears on the Excel menu bar, and it brings up a dialog box with tabs dedicated to all the I/O subsystems: analog I/O, digital I/O, and counter/timers. The vendor also recommends you go to Customize/Toolbars from the menu bar and click on DoctorDAQ, which then places several buttons on a menu bar so you can run a task on a mouse click without the dialog boxes.
This package is unusual because the doctor speaks—literally. An option allows you to activate voice-output help tips that explain how various controls work.
DoctorDAQ can simultaneously run all the subsystems on UEI’s PowerDAQ II cards at their full-rated speeds—an assumption you can’t safely make about every Excel add-in. You can implement closed-loop control algorithms within Excel, but the worksheet limits loop-update times to the range of tens to hundreds of milliseconds.
The hardware can operate at full speed even though Excel generally can’t keep up with faster digitizing rates. So in addition to the two input modes you’ll find with most products (single-shot and continuous), UEI added a third in which the Doctor fetches the latest snapshot of data from the card’s FIFO at a user-determined rate.
Certainly, the fetching rate can be so slow that the FIFO overwrites data before the add-in imports them into Excel, leaving gaps in a continuous waveform file. In this mode, gaps aren’t a problem because the add-in emulates a digital scope. It sucks in the latest waveform data with minimum delay, meaning you can have pseudo real-time displays.
In fact, the vendor has created an example template that turns Excel into a real-time spectrum analyzer (Figure 1).You select the digitizing rate, and Excel’s built-in Fourier functions work with data records that are a power of 2, as large as 4,096. This example template also includes windowing functions (rectangular, Hanning, or Hamming), and it plots magnitude vs frequency. On a typical system, you can expect an update of the magnitude plot roughly once per second. While it won’t rival a stand-alone spectrum analyzer, it’s not bad for the price.
Automatic Documentation
Another recent entry into the add-in arena is Keithley with ExceLINX. The first thing that might grab you is its price: free, even in the full-functioned version. It supports simultaneous analog inputs and outputs as well as tasks running on several devices.
Another unique aspect is how users set up an acquisition run. In virtually all other add-ins, you change operating parameters through dialog boxes. However, dialog boxes don’t store this data with a worksheet for post-experiment examination. As a result, when you run a test, the data might be on the worksheet but there’s no record of the settings that created that data.
Rather than force you to document setups by hand, ExceLINX eliminates dialog boxes and lets you configure all options directly on the worksheet. When you activate the add-in and choose the type of task (analog I/O or digital I/O), a template comes up with cells marked for various operating parameters and already filled with intelligent defaults (Figure 2).
You can execute the data acquisition run immediately or change these cell contents such as to select another board, change the digitization rate, or modify the target range for the collected data. This worksheet stands as the documentation for the experiment setup.
For long acquisition runs, you can have data overwrite in the same column or increment the column with successive scans. One drawback is the lack of an automation interface on this add-in. It’s not easily automated from VBA, and stimulus/response operations are fairly basic.
Adding Power With ActiveX
Recognizing the inherent limitations of Excel, other vendors provide packages that augment their add-ins. One example is ComputerBoards’ DAS Wizard, whose base version sells for $149. The $349 Pro version adds the firm’s VIX-Components, a set of ActiveX controls with which you can create a graphical user interface. Although Excel offers a charting option, it’s optimized more for business charts rather than real-time displays, so Pro adds oscilloscope displays, datalogging strip charts, and X-Y plots, all of which update in real time.
The base add-in uses the dialog-box method for controlling hardware. It supports analog I/O, digital I/O, and temperature logging with units conversion. It also recommends a default data-import mode based on the desired digitization rate, a selection you can override.
To initiate a task, you click on the Run Task! button on the dialog panel. DAS Wizard doesn’t limit scans to the number of rows. In the default situation, each successive scan of the channels appends to the bottom of the current column, and it automatically wraps around to the top of the next column when it reaches the bottom. A check box allows the spreadsheet to scroll up as it puts new values in cells or hold position so you can watch the progress of a graph or calculation as data is gathered.
Another package that adds power through ActiveX controls is Measure from National Instruments (NI), which has several interesting traits. This $495 package is the only one of the add-ins that directly supports IEEE 488 and serial instruments. In fact, the ability to import data from serial instruments is among the most frequent uses of the package.
Because it is one of the first products of its type, Measure has no integral support for digital I/O. To remedy that situation and respond to requests for other extensions such as advanced triggering and live cell updates, NI bundles a set of ActiveX controls from the ComponentWorks family with Measure. With them, you can build GUIs, control hardware operations including digital I/O, and automate processes with VBA scripts. Likewise, while Measure can’t run multiple I/O tasks simultaneously, a ComponentWorks control can fire one task and then a second one without waiting for the first to complete.
Interestingly, the Measure-to-ComponentWorks Converter uses a hardware I/O task you define with Measure’s dialog boxes to create a Measure macro that you run from Excel’s Toolbar>Macro>Macros menu bar selection. These converted tasks are built on forms that contain appropriate ComponentWorks controls whose configurations you can edit by accessing the corresponding property pages in the VBA editor. The macros are functionally equivalent to the Measure tasks except generally they run faster. Measure is compatible with all versions of Excel, even 16-bit versions, so it can’t take advantage of all the technologies built into the latest versions of Excel.
A final vendor with an Excel add-in is IOtech, whose $195 DaqView XL handles programming of analog I/O, digital I/O, and counter/timer tasks through dialog boxes. This add-in also allows you to add a charting window to a worksheet. This window provides real-time scrolling for as many as 16 channels of strip-chart display. All 16 traces are synchronized and feature an adjustable, deterministic scrolling speed that you can set independent of the sampling rate.
An Attractive Alternative
From the Start
Spreadsheets haven’t always been as powerful or supplied as many functions as they do today. Nonetheless, they’ve been popular for technical data analysis since the days of the first PCs, especially since dedicated instrumentation and analysis packages weren’t available. In most cases, the only alternative was to program an application from scratch in FORTRAN or one of the rudimentary BASICs floating around at that time.
One of the first companies to use spreadsheets as a viable analysis platform was Laboratory Technologies, a third-party developer. Already in early 1984, the firm was selling Labtech Notebook, which allowed you to control data acquisition hardware from all the popular board vendors of the day.
The package integrated some basic analysis and plotting capabilities, but one mode also dumped data from high-speed runs into a data file, automatically invoked Lotus 1-2-3 (and soon thereafter, Symphony), and even ran macros to accomplish certain predefined analysis or plotting tasks. Over the years, this developer migrated away from spreadsheets as the primary analysis tool and moved those capabilities into the core package. In the process, a new class of products was ushered in: stand-alone, third-party acquisition/analysis packages.
The attraction of spreadsheets for technical data analysis was not lost on Lotus Development which, in those halcyon days, was looking for all sorts of new markets. In November 1986, it released Lotus Measure. That package removed Labtech Notebook as a middleman, allowing engineers to control data acquisition cards as well as IEEE 488 and serial instruments and import experimental data directly from the 1-2-3 menu bar.
Although moderately successful, Measure never met the sales expectations of Lotus. When the firm’s fortunes started to wane, it jettisoned lines that hadn’t met lofty expectations.
In mid-1989, Measure found a better home with National Instruments. NI enhanced the package and made it compatible with its latest drivers and cards.
Then in the fall of 1995, NI changed the complexion of Measure by introducing a Windows version. At the same time, the company ported it to Microsoft Excel instead of 1-2-3 which, by this time, was rapidly losing market share. And while few explosive developments have arisen with spreadsheet add-ins over the years, products of this class continue to sell well to users who want fast, easy access to their data without programming.
About the Author
Paul G. Schreier is a marketing consultant in the fields of data acquisition, DSP, and emerging technologies. He was the founding editor of Personal Engineering & Instrumentation News and previously served as chief editor of EDN magazine. 25 Washington Rd., Rye, NH 03870, (603) 427-1377, e-mail: [email protected].
Return to EE Home Page
Published by EE-Evaluation Engineering
All contents © 2000 Nelson Publishing Inc.
No reprint, distribution, or reuse in any medium is permitted
without the express written consent of the publisher.
June 2000