Use Excel to Compute COBOL Results
A Teaching Tip by Bob Grauer

Does Excel belong in a COBOL class? We think it does because it is a fantastic tool with which to compute anticipated output, the first step in developing any COBOL program. The first thing I do as an instructor in assigning a project, is to require students to submit the anticipated output based on the sample data supplied with the program. They can do this via pencil and paper. They can also do it in Excel depending on how much of the application they remember. But it's easy. In essence you open a data file in Excel (be sure to specify all files as the file type) and the Input Wizard will convert the txt file to a workbook. From there you can use formulas as appropriate to get the output.

Figure 1 applies this approach to Project 3-6 in the third edition of our book, COBOL: From Micro to Mainframe. The project is a simple heading, detail, and total line program, typical of early assignments in a COBOL course. The program is to read a file of records, do basic calculations for each record, and display a total line at the end. The project is set in the context of an inventory example where each record contains the beginning inventory, quantity shipped, quantity received, and unit price. The requirements of the program are to compute the ending inventory and the value of that inventory. Figure 1a displays the input file, Figure 1b illustrates one step of the Excel Input Wizard, and Figure 1c shows the resulting spreadsheet.


Figure 1a Data File (Previous / Next)


Figure 1b Input Wizard (Previous / Next)


Figute 1c Completed Spreadsheet (Previous / Next)


Figure 1: Use Excel to Determine Anticipated Output

As indicated, the first step in the assignment is for the student to read the processing specifications and to compute the intended results. The typical solution is traditional, with pencil and paper, but you can facilitate the process with an Excel worksheet. Regardless, however, of how the student determines the anticipated output, computation of the output does several things:

  1. It confirms for both student and instructor the desired result of the COBOL program. The student knows the intended result and the instructor has an invaluable aid in grading the projects.
  2. It eliminates any ambiguities that might exist in the specifications and/or clarifies the intended result of more complicated specifications.
  3. It reinforces material learned in an earlier class; moreover the concept is applicable to programming in any language.

Once the class is introduced to Excel, the next question is inevitably "If I can do this in 10 minutes in Excel, why do I need two weeks to write a COBOL program?" The answer in a word is the I/O capability that is built into COBOL to process large files, which far exceeds the capability of a spreadsheet. Of course not every project is amenable to a solution by Excel, but you will be pleasantly surprised at how many fit. More advanced projects will require the Excel IF, VLOOKUP, and SUBTOTAL functions, but students enjoy this review.

I would love to hear from you regarding your thoughts on this approach and/or your teaching tips and suggestions.

Bob Grauer


© 1999-2000 by Prentice-Hall, Inc.
A Pearson Company
Upper Saddle River, New Jersey 07458
Legal Notice