Transaction summary, automated and comprehensive in Excel

Automated summary reports are generated in a consistent and robust Excel template for you to explore and share with colleagues and clients. Bank statements for multiple accounts can be extracted together quickly using ‘folder mode’ to process all files from each bank in one click.  Then, multiple output files can be merged together for a combined summary highlighting round/recurring/reversed transactions with key words/days/months. This analysis delivers a dynamic insight into several key areas of the banking period, including the dependency on certain customers or suppliers, without the need to first specify which third parties should be analysed, or the associated acronyms used by the bank.

Once the scanned bank statements have been processed, StatementReader will automatically create a summary report in Microsoft Excel that will analyse the following:

 

Keywords

Each unique word in each description cell is counted, and the associated transaction amount is accumulated to present two tables. The first table shows the keywords that appear the most number of times and the second shows the keywords that has the greatest monetary total. For each keyword, the the earliest and latest date on which it appeared is also shown.

 

Days

Whilst analysing the daily totals for a company in a state of increasing or decreasing operations, importance can be incorrectly placed on the days at the beginning or the end of the period. Therefore, the StatementReader summary uses a rolling average of daily totals over a three month period to identify which days have extreme activity, and may require further analysis.

 

Months

The months with the most and least activity, by number of transactions and total amount flowing through the bank account, is concisely summarised in four tables.  

 

Round amounts

One table shows the largest transactions that appear as a round amount. This is defined as a number greater than 1,000 that has no remainder when divided by 100.

 

Recurring amounts

The StatementReader summary shows the amounts that appear many times, together with the description of the first transaction, and the period over which the transactions occurred. This information is sorted by most occurrences and largest total amounts in two separate tables.

 

Level of materiality

Some calculations are used to outline the materiality level of the transactions analysed.

 

User defined search

Once all transactions are in Excel, you can use 'advanced filter' feature in Excel available on the 'data' menu to only show certain transactions, or copy them elsewhere.