/xenix: A Simple Accounting System
©1987 - Richard A. Bilancia - All Rights Reserved
to be published first in the July 1987 issue of UNIX/World

Those of you that read my column regularly know that I am an accountant. Because of that background, a long time ago I built simple accounting system using XENIX relational database tools. This month I’d like to share that simple accounting system with you, fully describe how to use it and completely analyze each line of code in the system.

My simple accounting system is written in the Bourne shell programming language (the same interactive command interpreter that you use to execute simple XENIX commands) and uses the following XENIX relational database tools:

          awk    pattern scanning & processing language
          cat    catenate & print
          echo   echo arguments
          join   relational database operator
          lpr    line printer spooler
          pr     print file
          rm     remove (unlink) files
          sed    stream editor
          sort   sort or merge files

Some Background.
But before we start, what exactly is a relational database system? In the early 1970’s Dr. E. F. Codd of IBM proposed an alternative to the traditional hierarchical & network database systems. His main reasoning for describing this new database alternative was two-fold. First, for the first time there could be a solid theoretical mathematically based foundation for data base design. In addition, it was Dr. Codd’s contention that system development would be substantially easier using a relational system because the programmer could be relieved from the burdensome coding normally required for both the hierarchical and network systems. In February of 1982, Dr. Codd published a complete outline of his ideas in "Relational Database: A Practical Foundation for Productivity," in Communications of the ACM. If you have the time, I strongly recommend that you read the article.

If you’ve done any development on your XENIX system you’ve probably used most of the tools listed above, with the possible exceptions being ’awk’ and ’join.’ While ’awk’ is described in the manuals as a pattern scanning and recognition language, it can better be described as a report writing program. But even that description only touches upon it power and potential. After reading this article you may want more information about ’awk.’ If so, you can find a detailed description in Appendix A.3 of the "Text Processing Guide" of your XENIX reference manuals as well as Dale Dougherty’s 3 part tutorial concluding in this issue of UNIX/World.

The other tool, ’join’ is simply a program that matches records based upon common fields.

You may have also already discovered that XENIX (and UNIX) rely quite heavily on the use of field separators, with the tab character being the most common field separator. Conforming with that standard practice, the accounting system below uses the tab character as its field separator.

The simple accounting system presented here has only two procedures "CLOSE_MONTH" and "POST_ENTRIES." However, the user must also be able to use either of the XENIX editors, ’ed’ or ’vi,’ in order to create the various files.

Installing the system.
The system is very easy to install and to use. Only three data files and two program files need to be created. In order to install the system simply follow these steps:

1. Using either of the XENIX editors, ’ed’ or ’vi,’ enter and save the two programs POST_ENTRIES and CLOSE_MONTH exactly as shown in figures A and B. Wherever you see the characters ’<tab>’ simply type a single tab character using the tab key. After creating and saving the files type the following shell commands to make the files executable:

chmod 755 POST_ENTRIES
chmod 755 CLOSE_MONTH

2. Next you will need to create a file called CHART_OF_ACCOUNTS. This file has three fields, the account number, a category code, and the account description. Use one of the XENIX editors to create this file separating each field in each record with a single tab character. I recommend that the account number be a three or four digit number while the category code should be an A, L, I or E symbolizing an asset account, a liability account, an income account, or an expense account. A suggested chart of accounts for personal use appears in figure C.

3. Finally you’ll need to create a file that contains the opening balances for each asset and liability account. Each record in this file should have three fields likewise separated by a single tab character. The fields are, the account number, the opening balance amount (preceded by a minus sign if a credit number), and the Julian date of the balance (i.e., the date the books were last closed). Name the file BALANCES and be sure that the total of column two equals zero and the file is in ascending sequence by account number. You can use the ’sort’ command to sequence the file by typing the following command:

sort -o BALANCES BALANCES

Using the system.
Using the system is just as easy! Just follow these steps:

1. Each month you will need to create a file of accounting entries (or journal vouchers) named ENTRIES. Each record in this file should have four fields with each field separated by a single tab character. The fields are, the account number, the entry amount (preceded by a minus sign if a credit number), the Julian date of the transaction, and finally a description of the transaction. Be sure that the total of column two (the entry amount) equals zero and the file is in ascending sequence by account number.

2. In order to post these entries to the previous balances just type "POST_ENTRIES" and the program created above will be executed. This will result in a report named "Trial Balance Report" and a file titled "TRIAL_BALANCE" that will be used in the final posting process. If any errors are detected while reviewing the output of this program, simply make changes to either the "BALANCES" or "ENTRIES" file and rerun this program. It can be rerun as many times as you wish.

3. When the output of the previous step is completely correct the accounting month can be closed-out by typing "CLOSE_MONTH". Be careful before executing this program as the previous month’s BALANCES file will be automatically deleted.

That’s all there is to it!

Understanding the Code: "POST_ENTRIES"
lines 1-3: These are comment lines. The pound sign prevents the remainder of the line from being executed.

lines 4-5: An ’awk’ program. The portion enclosed within the single quotes is the actual program. The ’BEGIN {FS=OFS="\t"}’ is executed on at the beginning of the processing and sets the field separator and the output field separator to the tab character.

The portion of line 5 enclosed within braces instructs the ’awk’ program to print the contents of the first, second and third columns of the input file, followed by the text in double quotes, to the standard output. The input is taken from the file "BALANCES" and the output is diverted through a pipe to the commands on line 6.

line 6: The ’cat’ command is used to concatenate the contents of the "ENTRIES" file to the end of the output of the previous pipe and redirected through a pipe to the ’sort’ program. The ’sort’ program uses the ’-t’ option to set the tab character as the field separator and uses columns 1 and 3 as the major and minor sort fields. The output is then diverted through a pipe to the commands on line 7.

line 7: The ’join’ command is used to match on the first columns of the output of the previous pipe’ and the file "CHART_OF_ACCOUNTS" to create the file "TRIAL_BALANCE". The ’-a1’ option ensures that every line of file 1 (the output of the previous pipe indicated by the solitary ’-’) is written to the output file. The ’-t’ option once again sets the field separator as the tab character.

lines 8-9: The ’echo’ command is used to create the column headings for the trial balance report that is being created in the file "TB".

line 10: The ’cat’ command is used to append the contents of the file "TRIAL_BALANCE" created on line 7 above to the file "TB".

line 11: The ’echo’ command is used to append a line of dashes (to signify that a total follows) to the file "TB".

lines 12-14: An ’awk’ program to computed the sum of all transactions in the "TRIAL_BALANCE" file and append that number to "TB". Line 12 once again sets the field separators. Line 13 adds the values of column 2 in every record to a variable named "total". And in line 14 the instruction within the braces following the word "END" send a tab character followed by the value of "total" when the input file is exhausted to the standard output which is finally diverted to the file "TB".

line 15: Uses the ’pr’ and ’lpr’ commands piped together to send the contents of "TB" to the printer as a report. The ’-h’ option is used to add a description to the standard ’pr’ header.

line 16: Deletes the "TB" file using the ’rm’ command.

Understanding the Code: "CLOSE_MONTH"
lines 1-3: These are the comment lines.

line 4: The ’echo’ command is used to display a message on the screen that indicates the user should enter a date.

line 5: The ’read’ command of the Bourne shell programming language is used to retrieve the characters entered by the user, and stores that response in a shell variable named ’date.’

line 6: The stream editor program ’sed’ is now used to insert the value stored in the variable ’date’ in the beginning of every record in the file "TRIAL_BALANCE". Note that the ’sed’ program is contained within the double quotes (to allow the ’$’ to be interpreted by the ’shell’), and the output is redirected through a pipe to the command on the next line. The ’sed’ program is actually a substitute command, as indicated by the ’s’, and is to substitute at the beginning of every line, as indicated by the ’^’, the value in the variable ’date’ immediately followed by a tab character. Note the convention of preceding a shell variable with a ’$’ to indicate the value of the variable name immediately following.

lines 7-11: The remaining lines are another ’awk’ program that takes as its input the ’piped’ output from line 6 and sends its output to replace the file "BALANCES" with the new updates balances for each account. The ’awk’ program creates and uses two new variables -- ’prev’ to check for a break or change in the account number, and ’amt’ to store the accumulated net balance in each account. Line 7 once again sets the field separators. In line 8 if the account number in column 2 is not equal to the previous account number stored in the variable ’prev’ then print a record with three fields, the previous account number ’prev’, the accumulated amount in ’amt’, and the date from column 1. Also on the same condition in line 9, reinitialize the value in the variable ’amt’ to zero. For every input record, line 10 sets the value in the variable ’prev’ with the account number from column 2, and adds the amount from column 3 to the variable ’amt’. And lastly after the input file has been completely exhausted, line 11 prints a final record with the remaining values in ’prev’ and ’amt’ appending the value stored in the shell variable ’date’.

FIGURE ’A’ - POST_ENTRIES

1 # ’POST_ENTRIES’

3 #

4 awk ’BEGIN {FS=OFS="\t"}

5 {print $1,$2,$3,"OPENING BALANCE"}’ < BALANCES |\

6 cat - ENTRIES | sort -t’\t’ +0 +2 |\

7 join -a1 -t’<tab>’ - CHART_OF_ACCOUNTS > TRIAL_BALANCE

8 /bin/echo "ACCOUNT\tAMOUNT\tDATE\tEXPLANATION\tCATEGORY\tDESCRIPTION" > TB

9 cat TRIAL_BALANCE >> TB

10 /bin/echo "\t--------" >> TB

11 awk ’BEGIN {FS=OFS="\t"}

12 {total += $2}

13 END {print "\t", total}’ < TRIAL_BALANCE >> TB

14 pr -h "Trial Balance Report" < TB | lpr

15 rm TB

FIGURE ’B’ - CLOSE_MONTH

1 # ’CLOSE_MONTH’

3 #

4 echo ’Enter the closing date (e.g., 861231) ’

5 read date

6 sed "s/^/$date<tab>/" < TRIAL_BALANCE |\

7 awk ’BEGIN {OFS=FS="\t"}

8 prev != $2 {print prev, amt, $1}

9 prev != $2 {amt = 0}

10 {prev = $2; amt += $3}

11 END {print prev, amt, "’$date’"}’ > BALANCES

FIGURE ’C’ - CHART_OF_ACCOUNTS

1000 A CASH IN CURRENCY 6050 E Electricity

1100 A CHECKING A/C 6080 E Snow Removal

1150 A CASH FUND A/C 6110 E Retirement Insurance

1300 A A/R - MISCELLANEOUS 6120 E Homeowner’s Insurance

1350 A CASH VALUE OF INSURANCE 6130 E Auto Insurance

1400 A I.R.A. 6140 E Life Insurance

1500 A LAND 6210 E Medical: Doctors

1510 A BUILDINGS 6220 E Medical: Dental

1520 A Improvements 6230 E Medical: Drugs

1530 A Landscaping 6310 E Real Estate Taxes

1549 A Unrealized Gain on House 6320 E Auto Licenses

1600 A Furniture & Fixtures 6330 E Sales & Use Tax

1705 A Auto #1 6340 E Federal Income Tax W/H

1710 A Auto #2 6350 E State Tax W/H

1910 A Retirement Contributions 6360 E F.I.C.A. TAXES

1960 A Jewelry 6410 E Mortgage Interest

2000 L Personal Loan 6430 E Credit Card Interest Exp.

2010 L Loan - Auto #1 6440 E Other Interest Exp.

2020 L Loan - Auto #2 6530 E Contributions

2100 L Accounts Payable 6710 E Subscriptions

2510 L Mortgage Payable 6750 E Business Expenses

3900 L Deferred Capital Gains 7010 E Food & Groceries

3999 L Net Worth 7020 E Alcoholic Beverages

4010 I Gross Salary 7030 E Milk

4101 I Dividends 7040 E Entertainment

4121 I Interest Income 7050 E Clothing

4205 I Gifts received 7060 E Auto Parts & Repairs

4299 I Miscellaneous Income 7070 E Cleaning & Laundry

4310 I Equity Inc/[loss] 7080 E Postage

4410 I Capital Gains 7090 E Gasoline

4490 I Federal Tax Refunds 7100 E Gift Expense

6010 E Telephone 7110 E Haircuts

6020 E Water & Sewer 7500 E Travel & Vacation

6030 E Trash Collection 7600 E Education

6040 E Natural Gas 7999 E Miscellaneous

I hope that you’ve found my example interesting!