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 PostsSee All
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