Lab4, due Tuesday, Feb 7

You are processing information for the "Lots Of Savings Earned" (LOSE) bank. In a spreadsheet you find the names of customers and their beginning balances. Their account number is the spreadsheet row they are in. Their last name is in the first column; first name is in the second column. Their account balance is in the third column.

Another spreadsheet holds the daily transactions for the accounts. The transactions include deposits, withdrawals, adding on interest, or making a transfer to another account. In this spreadsheet

1. The account number to perform the transaction on is found in column one. (A zero account number in column one signifies the end of the transaction data.)
2. The amount of the transaction is in column two.
3. The kind of transaction ("D" for deposit, "W" for withdrawal, "I" for interest, "T" for transfer) is found in column three.
4. If it is a TRANSFER transaction, then the amount of money is transferred from the account number in column one and transferred to the account number found in column four.

You will process the transactions by creating a final balance amount in column four. This will be the beginning balance and all adjustments made by processing the transactions. If a transaction would cause an account to become overdrawn, do NOT process that transaction and place the word OVERDRAWN in column five. This could occur from either an attempted withdrawal or transfer of insufficient funds.

After processing the transactions, display the number of accounts that become overdrawn in a MsgBox.

Sample data (in the accounts spreadsheet):
Mouse   Mickey   100
Mouse   Minnie   500
Spider   Sammy   200


Sample data (in the commands spreadsheet):
2   150   D
1   500   W
3   100   T   2
2   50   I
9   250   D
8   100   T   2
2   100   T   9
1   100   D
0

For the sample data, your program produces the following results (in the spreadsheet):
Mouse   Mickey   100   200   OVERDRAWN
Mouse   Minnie   500   800
Spider   Sammy   200   100


In a MsgBox, you will display:
The number of overdrawn accounts is: 1

Assignment Notes

-- Don't worry about formatting the spreadsheet. The above example uses whole numbers. Use Cdbl to convert money amounts for computing accuracy.

-- A file, lab4.vbs, is provided. It opens an excel spreadsheet in a subroutine, terminates an excel spreadsheet in a subroutine, and another subroutine reads one transaction data and returns the values back for you to use to process.

-- The sample excel file, data4Accounts.xls, shows how the account data will look. The sample excel file, data4Commands.xls shows how the transaction data will look. The real data may be different from the sample data. Your program should be written to the specifications, not the numbers.

-- You must use subroutines or functions for your computing. The code found at the top of the program should be declarations, but computation should be done in subprograms. Think about tasks. Describe the solution to the problem in English. For example, needing the data for one command became the subroutine setOneCommand.

-- You must do error checking for a bad transaction code and for a bad account number. Do NOT process any bad transactions. Note that in the sample data, the three transactions containing account eight or nine are ignored.