Problem I. 398. (March 2016)

I. 398. There are some ATMs for cash withdrawal and deposit in the foyer of a bank. If someone wants to access the ATMs when the bank is closed, their bank card is scanned and, for security reasons, some data are logged upon entering or exiting the foyer. The tab-separated and UTF-8-encoded text file naplo.txt containing the log file of a certain day after banking hours can be downloaded from our web page.

Open the file naplo.txt in a spreadsheet application such that the first piece of data is put in cell A1, then save this sheet as i398 by using the default application file format.

The log file records every event (hour, minute), the type of the event (1, \(\displaystyle -1\), 2 and \(\displaystyle -2\) denote entering, exiting, cash deposit and cash withdrawal, respectively), the 5-digit card number, and the amount of money (if applicable).

Example:

You should solve the following tasks by using formulae, functions and references. Since the number of events is unknown in advance, your solution should be able to handle 150 possible events.

In your solution you can perform auxiliary computations in any number of columns if they contain some explanation. Answers to the following questions should be collected in a separate sheet.

– What is the total cash deposit and withdrawal based on the log file?

– Which bank card was used to withdraw the largest amount of money? If the maximum amount is not unique, give the card corresponding to the earliest event.

– What is the maximum number of people in the foyer at the same time? What is the earliest time such that this maximum occurred?

– How many different bank cards were used in the transactions?

– How many bank cards were used to perform cash deposit and also withdrawal?

– On a separate sheet and by using a nicely formatted bar chart with legends, display the number of transactions in each hour between 20:00 and 08:00.

The spreadsheet and a short documentation, containing the name and version number of the spreadsheet application, should be submitted in a compressed file i398.zip.

Downloadable file: naplo.txt.

(10 pont)

Deadline expired on April 11, 2016.

Statistics:

5 students sent a solution.
10 points:	Kovács 246 Benedek, Nagy Ábel, Olexó Gergely, Radnai Bálint, Szemerédi Levente.

Problems in Information Technology of KöMaL, March 2016