top of page

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" , "" )

Recent Posts

See All

Improving Excel with Python (May 2022)

Revisited starter script from January 2021: Split Excel file into separate files Excel is essential, and Python is the future - forcing ourselves to practice the latter by automating some of the commo

Message us or

Call us on +44 (0)20 3287 8283

Mon to Fri: 8am-8pm

Weekends: 10am-6pm

bottom of page