Put each month's opening and closing balance in a small table, then compare each opening with the previous closing. When they are equal, the months join up. The first month where they differ tells you a file is missing, duplicated or incomplete just before that point.
What does a continuous chain look like?
A bank balance flows without breaks, so what one statement ends with is where the next begins. If April closes at ₹52,300, May must open at ₹52,300. Anything else means money moved that your files do not show. This is a simple test that can save you from analysing incomplete data.
How do you set the grid up?
Use a sheet with Month in column A, Opening in B, Closing in C, and Gap in D. Type the opening and closing balances from the top and bottom of each statement. If you only have a Balance column, you can read them from your data with formulas, as described next.
| Month | Opening | Closing | Gap check |
|---|---|---|---|
| 2025-04 | 48,000 | 52,300 | start |
| 2025-05 | 52,300 | 61,150 | OK |
| 2025-06 | 58,650 | 64,000 | Gap of -2,500 |
| 2025-07 | 64,000 | 70,200 | OK |
These figures are invented. June opens at 58,650, while May closed at 61,150, so something worth ₹2,500 is not in the files, or May was cut short. The gap sits before June, so look at the end of May and the start of June.
How is this different from checking inside one month?
The chain test compares month to month. A second test works inside each statement: opening balance plus credits minus debits should equal that statement's own closing balance. Passing the chain test while failing this one means a row is missing within a month. Failing the chain test while passing this one points to a file missing between months. Running both tells you where to look.
Invented example: June's file shows opening ₹58,650, credits of ₹20,000 and debits of ₹14,650, so its closing should be 58,650 + 20,000 - 14,650 = ₹64,000, which is what the table shows. June is internally fine; the gap lies between May and June.
Which formulas do the checking?
- Gap amount, in D3 and down: =B3-C2
- Plain verdict: =IF(ROUND(B3-C2,2)=0,"OK","Gap")
- First gap month: =IFERROR(INDEX($A$3:$A$14,MATCH("Gap",$D$3:$D$14,0)),"No gap")
ROUND to two decimals avoids false alarms from paise. Add a conditional format on column D, with a rule that colours any cell equal to "Gap" red, so the problem jumps out. Fill the formulas to twelve rows for a full year.
Can you read the balances from your transaction list?
Yes, if the sheet has a Balance column, sorted oldest to newest and a Month column such as 2025-05. For the closing balance of the month in A3:
=LOOKUP(2,1/($F$2:$F$2000=A3),$E$2:$E$2000)
This returns the last balance in a month. Here F holds the month label and E the balance. For the opening balance, take the first row's balance and undo its movement: =INDEX($E$2:$E$2000,MATCH(A3,$F$2:$F$2000,0))+INDEX($C$2:$C$2000,MATCH(A3,$F$2:$F$2000,0))-INDEX($D$2:$D$2000,MATCH(A3,$F$2:$F$2000,0)), with C as debit and D as credit. The balance after the first row, plus what that row took out, minus what it put in, is the balance before it.
Until the missing file turns up, mark the affected month as incomplete in your table and keep it out of any yearly total you plan to share or rely on. A visible gap is far better than a total that quietly looks complete.
What do you do when you find a gap?
- Check that you have a file for every month in the range, including the one just before the gap.
- Look for a statement that starts or ends a few days short.
- Check whether the same month is loaded twice, which makes the next opening look wrong.
- Download the missing period from your bank's statement section, or contact the bank if it is not offered.
Once the chain is clean, a tool like SpendixAI can carry on from there, with batch statement imports and an import history showing what has been loaded.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


