Before asking where the money went, check that your spreadsheet contains the money that actually moved. A bank reconciliation in Excel starts with a simple control: opening balance + signed movements = closing balance for one account and one period.
BankSync removes repeated export work for supported UK accounts. Reconciliation tells you whether the resulting dataset is fit for your report. A successful feed run is useful evidence, but it does not prove that you chose the right period or imported every historical record you expected.
Illustrative statement reconciliation
| Control | GBP |
|---|---|
| Opening statement balance | £2,400 |
| Posted credits | £3,100 |
| Posted debits | −£2,650 |
| Calculated closing balance | £2,850 |
| Closing statement balance | £2,850 |
| Unexplained difference | £0 |
Fix the scope before comparing numbers
Choose one account, one currency and the statement's exact opening and closing dates. Use the same balance basis on both sides. Available balance can reflect holds or facilities that do not appear in a posted-transaction total, so it is not automatically interchangeable with the statement's closing balance.
Keep the bank statement as the comparison source. Record its account reference and period in a Checks tab, using only the identifying detail your reviewers need. Preserve the original statement separately rather than treating the imported sheet as evidence of its own completeness.
Build the Excel check
Connect and map
Save the workbook in OneDrive, connect Microsoft Excel in BankSync, and send a Transactions feed to its own worksheet. Include date, amount, account, currency, status and source identity where available.
Verify the sign
Find a known deposit and withdrawal. Normalise a separate calculation column to positive credits and negative debits without overwriting the source amount.
Apply the statement period
Filter the account and currency, then include the transactions falling in the statement period on the relevant posting basis.
Calculate the expected close
Add the signed movement total to the opening statement balance. Compare with the closing statement balance.
Investigate the difference
Keep a list of exceptions with a reason, owner and resolution. Recalculate only after the underlying issue is understood.
A formula you can inspect
Suppose an Excel table called Tx contains Amount, Date, Account and Currency. On Checks, B1 is the start date, B2 is the end date, B3 the account name and B4 the opening balance. For an example using the same transaction-date basis as the statement, calculate:
=B4+SUMIFS(Tx[Amount],Tx[Date],">="&B1,Tx[Date],"<"&B2+1,Tx[Account],B3,Tx[Currency],"GBP")
The end test includes the whole final day even if the field contains a time. If the source date is purchase date but the statement uses posting date, resolve that difference before treating the formula as a reconciliation. See Microsoft's SUMIFS reference for the range-and-criteria syntax.
When the closing balance does not agree
| Symptom | Likely question | Useful check |
|---|---|---|
| Difference equals one purchase | Missing, duplicated or outside cutoff? | Trace that record in bank, feed and workbook |
| Recent purchases absent | Still pending? | Compare posted records, not only the banking-app activity list |
| Older month incomplete | History unavailable from the connection? | Check earliest source record against the statement |
| Every debit seems reversed | Sign convention wrong? | Test a known withdrawal |
| Difference after a restart | Overlapping imports or feeds? | Inspect source IDs and import origin |
Keep pending entries out of a posted reconciliation
BankSync's UK transaction feeds normally exclude pending entries. If you enable them, a pending item and its posted version can arrive as separate rows; the original pending row is not automatically removed. A simple sum of both will overstate movement.
For the statement check, use the final posted set. Maintain a separate pending view if you need near-term spending awareness. A pending purchase is not proof that a final record has been missed.
Do not force a zero difference
A £25 gap is an investigation, not permission to insert a balancing expense. If history is incomplete, obtain the missing statement period and establish a controlled import boundary. If two feeds overlap in the same destination, fix their scope before deleting suspected duplicates. Equal amounts and merchant names alone do not prove two rows are duplicates.
For repeated normal syncs, BankSync uses source transaction identity to avoid repeat delivery. Starting from the beginning or mixing independent import routes deserves separate review. Preserve a copy of the workbook before a major history repair.
Finish with a repeatable close
Record the cutoff, statement balance, calculated balance, difference and review date. Check the source data is current and save the explanation for every resolved exception. Then use the reconciled period in spending charts or the 13-week cash-flow forecast.
Reviewed: 1 November 2026. Product references: transaction sync, UK connection history, and Excel integration.