7 January 2013

First client

31 December 2019

Last software update

< 2 hours

Support response

StatementReader is used by forensic accountants, bookkeepers, insolvency investigators, solicitors, auditors and business review professionals to stop wasting time and talent converting bank statements into Excel

This website uses cookies to enhance the user experience.  Click here for our cookie statement and here for our privacy policy.

All Rights Reserved 2012-2020 Suave Software Limited. London, England, United Kingdom. Email Us

  • LinkedIn Social Icon
  • Twitter Social Icon

Balance checks using SUM

Balance checks are used to validate that the data exported from bank statements is correct. They are quick and easy to apply, although there are a couple of points that you need to be careful of:

- Is the balance calculated before or after the transaction on the same line?

- Are there spaces or text within the debit, credit of balance columns?

- Does the page date order match the transaction date order (in other words, does the balance flow from one page to the next?

The above points will become clear as soon as you start to copy the balance check formula across many transactions, and you will need to modify the formula accordingly.

Start in a separate column. The first row should equal the opening balance.

Then, use this format to construct the balance check:

=SUM( previous calculated balance , - transaction debit , transaction credit )

Finally, copy the formula down to all rows with a transaction.

You can then check the new calculated balance with the balance read from the page. For many transactions, you can use the following formula in a separate column to only display warnings where there is a difference between the calculated balance and the extracted balance.

=IF ( ABS( calculated balance - extracted balance ) >0.01 , "WARNING" , "" )

#Formula #Excel #All