Preparation was everything
What was the key to this sort of performance? Cooperation and preparation.

We're fortunate in having a Secretary of State's Office that, each election night since 1990, has been making available an electronic copy of its election results database. It also passes along a copy of the Maricopa County database as well. And we could have similar data from the state's second largest county if we had the room to print the results.

That's the good part. What we get is line after line of numerical data. It takes lots of electronic slicing and dicing to turn it into something readable.

That's where preparation comes in.

In previous elections, we've used macros written for our proprietary SII Coyote terminals to massage the raw data into readable results. We've had up to 21 terminals hammering away at the data -- which is impressive to look at, but I was convinced we could do even better with PCs.

We used our primary election in September to prove that PCs running spreadsheets written in Microsoft Excel 5.0 could crunch the numbers for us. But that was just child's play compared with what was to come.

The Republic's understanding newsroom managers let me drop all other projects to concentrate on the election, starting on Monday, Oct. 17. (In the next 3 weeks, I had three days off.)

As shown by the screen shots, the spreadsheets were straightforward -- rudimentary, even. Cell references were written to pair up the vote totals from the data files with the candidate's name. Other formulae calculated the percentage of the vote going to each candidate in a race.

The only real talents needed to produce the spreadsheets were a high boredom threshold and the capacity to be a methodical plodder.

Each spreadsheet had a companion macro to import the relevant results, then convert the results of the formulas to values and finally to export the results as monospaced text files that would be left on the network to be loaded into the front-end system via a separate PC. (Astute users of Microsoft Excel will note that the macros are written in Excel 4.0 macro language rather than Excel 5.0's Visual Basic. I just find the 4.0 macro language easier to read and document.)

The macros and spreadsheets worked so efficiently because we could manipulate the source data files so that only essential information was imported into each spreadsheet. This was crucial, considering that the data file on statewide races was more than 1400 lines long and the county data file exceeded 400 lines.

We were able to parse this mass of information into smaller files by making use of some of the most basic functions built into MS/DOS -- the ability to read a text file, locate lines matching specific criteria, then copy those lines into a separate text file. We also used DOS's ability to sort lines in a file depending on the numerical value at a specified location in each line.

(I'm not recommending that everyone run out and waste months learning the intricacies of DOS -- its days as an operating system are waning. However, those who have suffered through learning the ins and outs of its arcane command structure can accomplish feats of text manipulation that would take far longer in a word processor or spreadsheet.)

Bottom line for 21 days of work: 18 spreadsheets and 18 companion macros. The spreadsheets contained nearly 2800 formulas. The macros that made them work contained more than 2000 lines of code.

Printouts of the spreadsheets and macros made a stack one inch thick.

Putting it all together
The other key player in this endeavor was Bob Bundy, a member of our data and applications support team.

While I was sitting in my cubicle creating spreadsheets and macros, he was devising the system that would tie everything together.

Bob decided to use three networked '486 PCs running at 66 MHz. (The instability inherent in attempting to run multiple operations under Windows made him decide against trying to run all three processes on a single PC.)

There was a computer for each major task:

Once new results were in, that PC began downloading the files, then ran a series of batch files to parse them into more workable units. It also called in a piece of code Bob wrote to massage a file containing Xs and blank spaces representing which precincts in Maricopa County were reporting. It turned these into ones and zeroes and also generated each precinct number.

This file was picked off the network by staff artist Gus Walker, who imported it into a Macintosh map created with Atlas Pro software. This visual representation of which precincts had reported so far was then projected on a screen visible to most of the newsroom.

Our joint efforts created a system that ran virtually without need for human intervention. In the four minutes after each updated set of returns became available, it generated more than 4700 lines of text covering more than 600 races. (Because of the need for read-only, typeset and fax versions of the results, many races were covered up to three times.)

Two crises
All right, so Richard Nixon had six crises (seven, if you bother to count Watergate). We had two really close calls, and that was enough for us.

Crisis No. 1: In the test period prior to the primary election, the county data file came through single spaced. On election night, it was double spaced.

After a hectic -- and unsuccessful -- attempt to rewrite the spreadsheet processing county results, we had to abandon it. Fortunately, the newsroom was covering only about two dozen races in the primary and was able to create a template that was filled in by hand.

Additionally, Bob had the foresight to write his controlling program that ran the spreadsheets in a modular style, making it easy to disable any particular component.

So, our first question after the primary was, "Will the files the county sends on election match the test files it sends before the election?" Of course they will, we were assured.

As luck would have it, about one week after the primary, PC Magazine published an article explaining how to write simple filters in assembly language and compile them through the DOS Debug utility. One of the examples showed how to strip formfeed commands from a document. I gave a copy to Bob.

Sure enough, during our final testing the night before the general election, the county file -- which had been single spaced the previous week -- came through double spaced. A quick look at a hex dump of the file showed each line contained two carriage-return characters and a single line feed.

Bob quickly punched in the 23 lines of code from the magazine article, substituting the carriage-return character for the formfeed character. Then he compiled the program, added a line to our parsing program that funneled the county file through the filter, and our problem was solved.

Total time expended: 15 minutes.

Crisis No. 2: This one was my fault entirely. If we hadn't solved it, I'd be lucky to have a job at any newspaper west of Somalia.

Because of a recount in the Democratic primary for the U.S. Senate, both the state and county were late in building the sample data files we needed to test our spreadsheets. Rather than wait, I decided to modify a data file from the primary election to simulate what I thought the state file would look like. (In my defense, this simulation was based on a print out of the file the state planned to create.)

But -- even when a sample file became available, I failed to download it and test it against the spreadsheets that had already been written. Bad call! One hour before the returns started coming in, I found that the state's mainframe was delivering results sorted by candidate number rather than office identifier number.

This meant the vote totals for nearly all of the 160 candidates vying for 90 legislative seats were incorrect. Not only were these some of the most highly visible races being decided that night, there also were three versions of them -- one spreadsheet for read-only, one for typeset and one for the fax.

Panic time.

There was no way in the next 60 minutes that we could locate and correct the hundreds of spreadsheet cell references involved. Which doesn't mean that I didn't grab the printouts and start highlighting cells with a yellow marker.

More than anything, I didn't want to be alone with my thoughts at that point.

Even as I worked, I could tell something was trying to bubble up from my subconscious. In a few minutes I had it -- the arcane but beloved DOS Sort filter. We merely had to re-sort the data we downloaded from the state into the order we had been expecting in the first place.

We quickly added two lines of code to our parsing batch file, ran a couple of verification tests and the problem was solved. Total time expended: 20 minutes.

Several weeks after the election, it finally dawned on both of us that the same corrections could have been achieved via spreadsheet modifications as well.

In the first case, we could have loaded the double-spaced text into a new spreadsheet, run a macro that would delete every other line, then save it back into a file ready to be imported to the original spreadsheet.

Similarly, the re-sort accomplished in the batch file could be executed by another spreadsheet and macro -- only not as rapidly as it could be processed at the operating system level.

And from there on, it was smooth sailing. We processed more data and output more text faster than ever before -- and with an unprecedented degree of accuracy.

To top off the triumph, the next morning Republic Reporter Judy Nichols, who specializes in computer-assisted journalism, picked up nine-track tapes showing the results from every precinct in the county. From these, she extracted files on three key races: the gubernatorial contest, a ballot proposition to levy a cigarette tax of 40 cents a pack, and another proposition on freeway funding.

She relayed these to Gus Walker, who produced four-color, half-page maps showing how every precinct in the metropolitan area voted on these issues. We also uploaded the actual tallies to run with the maps.

This was really handy for me -- I could see that 32 other people in my neighborhood voted Libertarian for governor.

Valuable lessons
A number of lessons can be derived from our experience.

First, in the inescapable effort to tally election results, virtually every newsroom in the country can benefit from making greater use of computers -- PCs, Macs or proprietary front-end systems.

Your vote-counting officials -- be they state, county or municipal -- have the information you need in electronic form; you just have to persuade them to make it available to you. (It might prove helpful to invoke the line: "If a social backwater like Arizona can do this, then why can't etc., etc.")

Second, as Crisis No. 2 demonstrated, validation of your tallying procedure is vital. It must be built into the plan from the start. And the commitment to validate must be honored. It can't be postponed until the last minute.

Third (here you may refer to Crisis No. 1), no matter how carefully you have set up your procedures and validated your methods, you need to be prepared for the unexpected. Lots of computer knowledge can help; having a good fallback procedure ready to be rolled out is your best insurance.

Finally, there's no need to tie up your sharpest computer wizards for weeks on a project like this. I wrote all the spreadsheets and macros myself this time because we were crossing uncharted waters. However, a couple of intelligent and conscientious clerks could have done as well.

Save key people to set up the broad outlines of how election returns are to be processed and to handle the validation phase. You'll save yourself some money and use people more productively.

See you at the voting booth.

-- Bill Woodruff

Want to take Bill's spreadsheets and macros for a test drive? If you have Excel 5.0 for Windows, he'll be happy to send you a demonstration diskette. Contact him at Phoenix Newspapers Inc., 120 E. Van Buren St., Phoenix, Ariz. 85004-2227. Phone: (602) 271-8985; e-mail: batnerd@aol.com.

Curious about how many numbers Bill's spreadsheets actually crunched? And would you like to drool over those full-color precinct-by-precinct maps? A limited number of tearsheets is available for those who write or call.

From THE COLE PAPERS, January 1995, Copyright © 1995, All Rights Reserved.