.TITLE PortaCalc - A Public Domain 3D Spreadsheet .PAPER SIZE 80,80 .SPACING 1 ^^ .C PortaCalc - A New (Free) DEC Spreadsheet .SKIP 2 .C by .C Glenn C. Everhart .SKIP 1 .P People wanting to use a spreadsheet on their PDP11 or VAX based systems now have a new choice. In the past, the only spreadsheets available either were very costly (some over $4000) or ran on microprocessors (possibly emulated under RSX or VMS). .P Now there is a spreadsheet program, written in FORTRAN 66 for portability, which has been submitted to the DECUS Program Library for use with PDP11 and VAX, which has all the functionality of the commercial products, but is available essentially free and in complete source form. It runs under RSX, or on VAX VMS in native mode. Also, PortaCalc is now known to work under RSTS in RSX mode. This system was christened PortaCalc because its source language is chosen to make it easy to port to new systems and its use of DEC peculiarities has been minimized. (Porting it from RSX to VMS took half an hour.) .P The PortaCalc program took about 2 weeks to write, debug, and polish in its initial version since it is based on a program called Calc obtained from the DECUS library which was extended to handle the requirements of a full spreadsheet. It has undergone many enhancements and speedups since then to make it more powerful and useful. Since the major part of the computing engine was in place, only multi-argument functions, command parsing, and display needed to be added. Also, since Calc is a Fortran 66 program, it is itself rather portable, and its capabilities have been retained and extended. (This means that PortaCalc has some really unusual abilities like working in non decimal radices - such as hex - or extending formulas infinitely by reading them off separate files where they won't fit in the space provided in the sheet itself.) Since originally put together, PortaCalc has been given most of the attributes of a simple programming language too, and a crude database handling capability. Access to multiple other saved sheets is added also, so that a "summary" sheet can easily be built to take some or all of its inputs from other saved spreadsheets. This may be done in the usual "add in place" form, or may give totally random access to other sheets. Thus, PortaCalc is now a fully 3 dimensional program. Of course, inputs directly from database files are also permitted, though with limitations. .P The command structure of PortaCalc is not like that of VisiCalc, SuperCalc, or other spreadsheets. Mostly, this comes from the desire to read in commands with simple Fortran READ statements which assume a RETURN after the text is entered. Thus, the commands are structured as _#_, where the verbs are short (1 or 2 letters generally). The commands do make sense, however, and there are several online help pages to summarize them should you forget their syntax. .P In the VAX and PC versions, some extra front-end terminal processing permits an "Enter-mostly" mode option too, which is similar in style to most commercial micro sheets, though not in detail. Unknown commands are also possible to map onto the known commands of your choice, making a most protean user interface. .P Since most PortaCalc users will have VT100 type terminals, the latest PortaCalc has been set up to allow use of alternate keypad keys for user-tailorable commands. A default set of these has been added, together with multiple HELP pages including a keypad diagram. Functions such as scrolling, an ENTER mode, recalculation, moving/copying cells, insertion/deletion of rows/columns, and changing widths of screen are included. While the resulting command language is not identical to commercial sheets, it is similar enough in general form that learning is not a barrier. The online HELP has been greatly augmented as well, so that the user need not refer as often to the manual. The manual is now structured to permit its use as a VMS help library, keeping all documentation on use online. .P PortaCalc is similar to many other spreadsheets in that it displays a matrix of cells on a screen (up to 132 columns wide) which map onto a larger number of cells kept in the computer. At each location, there is a number and a formula, either of which may be displayed. Display formats can be chosen from the entire FORTRAN repertoire and individual columns may be set to different widths. Each cell may have a different format for its output, and the default format may also be changed. Unlike some sheets, PortaCalc allows multiple equations in each formula. (You can even comment the equations.) Any cell can refer to a command file which can be used to add user defined functions, using any cells and having 27 accumulators available which can handle integer data, floating point data, or multiple precision data of up to 20 digits width. (You can use a built-in interactive calculator at any point while working on a sheet also, and return to the screen display after, if you need to make further computations outside of sheet context). Maximum sheet size is determined by parameters set at compile time so that it depends solely on memory available. On PDP11 a 10000 by 10000 sheet is generally used at our site (nobody has needed a larger one yet). On VAX, the size is essentially unlimited. The spreadsheet is somewhat slower than some commercial ones, faster than many others, due to the way it computes formulas. Various recalculation modes can be used to reduce computation overhead where it is not needed. The entire sheet can be driven from command files, which are able to prompt the user for terminal inputs, perform conditional tests and looping, and control inputs. There is even interaction with the sheet so that a computation in any cell can be used to control the looping in a command file. There is a data extraction command in the embedded calculator which can query sequential files to dynamically extract numbers or formulas from predefined files which can be created and maintained with normal editors. Thus, PortaCalc is designed to be used with command files as well as from terminals. A knowledgeable user can program PortaCalc for a variety of applications. .P Where a fancy computation is already handled by another program, PortaCalc can logically "connect" that program to itself via a pair of mailboxes and special commands and functions, so that it becomes a computation organizer as well as a math engine on its own. .P PortaCalc is unique in that every cell on the screen can (if desired) become a separate window onto the physical sheet. It is up to the user to determine what should be mapped. A pair of "origin reset" commands act as a fast scrolling operation for contiguous parts of sheets. The mappings set up onto the screen can be used in computation, so that they define a projection onto the physical sheet which can be summed over, averaged, etc. .P The PortaCalc sheet as supplied to DECUS presently supports VT100 terminals (with or without AVO), or VT52 terminals. Also there are versions of the UVT100 subroutine (which is the only screen access routine) for Datamedia Elite 1500 terminals and for Datamedia Colorscan 10 terminals. Additional support has been written for Televideo 925's. The DECUS version will be updated periodically to provide these terminal support packages, but using any of the existing versions, it is a 30 minute job to make a version for your terminal. All that is needed is direct cursor addressing, plus a way to erase the screen and erase a line. (On ADDS Consuls, for example, the line erase must be done by writing spaces. Since this is all done within one routine, even such behavior can be handled easily.) The program is also designed to be easy to recustomize for terminals with more (or less) than 24 lines. Versions for RSX11M, RSX11M+, POS, and VMS are easy to generate with supplied command files, and all documentation is machine readable. Also supplied, in the hope that it will be helpful, is a document describing known features of the Fortran used (Fortran IV Plus) not part of the ANSI 66 standard. It is not complete, but will give a good start to people modifying the package for their own machines. .P Due to PDP11 space limitations, PortaCalc does not have built in graphics. Rather, there is a separate program which reads saved sheets and can produce histograms or scatter plots of any parts of a sheet by name. Since one can save or restore whole or partial sheets, or restore partial sheets to different locations, these save files will normally be made anyway to simplify merging different sheets. The graphics utility can access these files. It, too, is available in source. The sheets may access data stored in sequential files and pull in numbers or formulas so stored, and human-readable text files may also be read into a screen and manipulated, providing a full integration with word processing functions and a way to access reports from other programs without the usual painful step of converting the reports to a DIF (Data Interchange Format) type file first. This feature is unusual (possibly unique) in spreadsheets. (A DIF read/write utility is however provided.) The screen can be saved as a normal ASCII file, of course, at any time and that can be easily included in documents. .P The following are two pictures generated by PortaCalc of its screen and including the row/column labels. These may be suppressed if they are not wanted. The VT52 type cursor is added in by hand; the VT100 reverse video effect cannot be reproduced this way. .SKIP 2 .TEST PAGE 40 .LITERAL Demonstration of Appearance of a Typical VT52 Screen ROW/COL A= 1 B= 2 C= 3 D= 4 E= 5 1> General Priv. Inst. Expansion 100.000 100.000 100.000 100.00 2> SVC insts expansion 3000.000 200.000 3000.000 200.00 3> Interval Timer Service 500.000 25.000 500.000 25.00 4> Virtual Interval Timer Service 500.000 25.000 500.000 25.00 21> Virtual Timer Updates/sec 20.000 20.000 20.000 20.00 22> SVC's per Second 200.000 200.000 200.000 200.00 23> Average MIPs of MCF CPU 3.000 3.000 3.000 3.00 24> Time to init paging 0.050 0.050 0.050 0.05 25> Minimum Runtime Quantum 0.100 0.000 0.100 0.00 26> Total Inst Ovhd Time/Sec 3.158 0.602 0.608 0.09 27> Total I/O Time > 0.390 0.390 0.390 0.39 28> Total Wait Time 0.077 0.003 0.077 0.00 29> Total Time 3.625 0.995 1.075 0.49 5> Protected Memory References 500.000 25.000 500.000 25.00 6> I/O Interrupt Svc. 2000.000 500.000 2000.000 500.00 7> I/O Startup Overhead 6000.000 1100.000 6000.000 1100.00 8> Non-SVC Sensitive Insts 100.000 25.000 100.000 25.00 9> VMM scheduling ovhd 2.000E+04 1.000E+04 2.000E+04 1.000E+0 10> Time in Sched Wait Q 0.010 0.000 0.010 0.00 11> Device Wait Time 0.010 0.000 0.010 0.00 B 27> B12+B16*(B14+B15+2*B24) FIG. 1 - VT52 Screen, Numeric (default) Display .END LITERAL .TEST PAGE 40 .LITERAL Demonstration of Appearance of a Typical VT52 Screen ROW/COL A= 1 B= 2 C= 3 D= 4 E= 5 1> General Priv. Inst. Expansion 100. 100. 100. 100. 2> SVC insts expansion 3000. 200. 3000. 200. 3> Interval Timer Service 500. 25. 500. 25. 4> Virtual Interval Timer Service 500. 25. 500. 25. 21> Virtual Timer Updates/sec 20. 20. 20. 20. 22> SVC's per Second 200. 200. 200. 200. 23> Average MIPs of MCF CPU 3.0 3.0 3.0 3.0 24> Time to init paging .05 .05 .05 .05 25> Minimum Runtime Quantum .1 0. .1 0. 26> Total Inst Ovhd Time/Sec (B17*B8+B1(C17*C8+C1(D17*D8+D1(E17*E8+ 27> Total I/O Time >12+B16*(BC12+C16*(CD12+D16*(DE12+E16* 28> Total Wait Time B11+B13+B1C11+C13+C1D11+D13+D1E11+E13+ 29> Total Time B27+B28+B2C27+C28+C2D27+D28+D2E27+E28+ 5> Protected Memory References 500. 25. 500. 25. 6> I/O Interrupt Svc. 2000. 500. 2000. 500. 7> I/O Startup Overhead 6000. 1100. 6000. 1100. 8> Non-SVC Sensitive Insts 100. 25. 100. 25. 9> VMM scheduling ovhd 20000. 10000. 20000. 10000. 10> Time in Sched Wait Q .01 0. .01 0. 11> Device Wait Time .01 0. .01 0. B 27> B12+B16*(B14+B15+2*B24) Fig. 2 - VT52 Screen. Formula Display .END LITERAL .P Note that on the VT52, the ">" character indicates the cursor. On a VT100, reverse video is used instead, and the entire cell is shown in reverse mode. The formula of the current cell is always exhibited at the bottom of the screen if any exists. .P The documentation supplied is sufficient to use the sheet. However, a calculator from DECUS was used as the computing engine (which made writing the rest a 2 week job). Its documentation is also included, integrated with the documents for the spreadsheet. Full "scientific calculator" functions exist, plus some statistical ones and a full set of financial and logical ones. .P The PortaCalc program is already in use by DECUS staff internally, and is available shortly from the library as DECUS #11-SP-47. Cost for an order is about $150 for the special collection tape containing all code plus some other goodies and an RSX (RSTS) task image and VMS object libraries for a "default" version for VT100. Earlier versions will appear on DECUS RSX and VAX SIG tapes for Fall '85, but users wanting the full version must order it from DECUS. There are of course NO license restrictions. If you have RSX and desire file security, you may obtain the special collection and use the secure virtual disk package included therein to handle the security needs separately from this package; that driver is included in the special collection tape version of DECUS PortaCalc. VAX users desiring the same protection are advised to call Midcom (213-516-9590) and to ask about the encrypting virtual disk package they have. It's well worth their price. .SKIP 3 .C Future Plans .P PortaCalc is now at least as powerful as anything commercially available on the PDP11 market (though its details differ and in individual areas it may be more or less powerful than the competition). It is faster than some commercial sheets (especially on VAX) as well. Since versions are on RSX and VAX SIG tapes, in the library, and in use at multiple local sites, it is being used widely enough to get bugs out and to bring up its performance. The current version (V19-04C) is in the DECUS library, which will remain the source for the latest version. Plans for the public domain version involve basically bug fixes and possibly a few more documents. It will be maintained by periodic updates if bugs are found, though, like commercial products, it is a working program for which the lack of formal maintenance should not be a concern. The author does try to respond to trouble reports or suggestions, though not to crank calls (which have so far been of the form "but I have to learn a whole other command language from SxxxxCalc!"). .P In the future, the VAX version will be enhanced. The thought is that one or more DBMS interfaces will be added. A Datatrieve interface and a generic connect - to - anything interface has already been added. (The RIM DBMS is one of the "anythings" that can be used, but it's easier to connect to it via mailboxes than to write a special purpose RIM command link at present.) Also added is a spawn command-line, and a link to the companion Desktop Calendar program for scheduling and meeting management (DECUS #11-597, DTC) so that the month, day, or week at a glance functions can be integrated with spreadsheets for report generation more easily than at present. Finally, some major statistical enhancements are done. This involved adding numerous matrix and statistics functions to operate on areas of a sheet taken as vectors or matrices (matrix equation solving for eigenvalues, matrix inversion, math, transpose, determinant, and statistic functions like correlation coefficient; fast move/copy of matrices; thresholding functions; and more, user specifiable) to perform operations, plus some command(s) to allow expressions to be evaluated by varying one or more accumulators to seek a goal state by multidimensional searching. This will have trivial (in the math sense) special cases like solving mortgage equations or other one dimensional problems for unknowns given knowns. But it will be really useful to an analyst. They are currently there mainly to provide a full powered tool for everyone who, like me, couldn't afford to pay outrageous prices for this sort of thing for a PDP11 or VAX, and to make that tool available widely enough that anyone who now buys a commercial spread sheet for their PDP11 or VAX ought to have his head examined. .P As an added note, there is an 8088 version available now with the full capabilities of the VAX version (except for its' matrix size, which is 18000 by 18000 instead of 32000 by 32000) available for MSDOS machines and for Amiga, from Glenn Everhart, 25 Sleigh Ride Rd., Glen Mills, PA 19342. All the matrix algebra, statistics, and business functions mentioned plus lots more are included, and special speedup optimizations exist in the 8088 flavor to compensate for the basic slowness of that processor. (The PDP11 and VAX are fast enough that these were not productive in those systems, so they don't have the optimizations.) Once you get a copy, the building license fee is $45.00, so if you're in a big company and want to have lots of spreadsheets on MSDOS machines (practically ANY MSDOS machines), order now.