Automatic bank reconciliation, step by step.

What is bank reconciliation?

It is a check between two records of the same money. On one side is the bank statement, which shows what actually came in and went out of the account. On the other side are the business's own records: the daily cash count, sales in the system, supplier invoices, the bills-to-pay notebook.

When both sides match, the balance you see in your spreadsheet is the real balance. When they don't, there is a reason, and reconciliation helps you find it. The most common reasons in a small business:

• a card sale that hasn't landed yet, because the card machine company pays out days later;
• a card fee or bank charge nobody recorded;
• a Pix instant payment received without the payer's name;
• a bill paid twice, or an account debited twice;
• an owner withdrawal taken from the business account without a note.

Done every week, the check catches these cases while it is still easy to remember what happened. It also makes your cash flow reliable, because it starts from checked numbers.

What is an OFX file and how do you use it?

OFX is a type of statement file that your bank lets you download from online banking. Instead of a PDF meant for reading, it carries the same statement lines in a format that software can read: date, description, amount, and whether it is money in or out.

To use it, the path is usually this:

1. In the business account's online banking, open the statement and pick the period.
2. Look for the option to export or download the statement and choose OFX. Some banks also offer CSV or Excel.
3. In your accounting or management software, use the import statement option and pick the file.

Menu names change from bank to bank. If your software doesn't accept OFX, download the statement as CSV or Excel, which any spreadsheet can open. What matters is to stop typing the statement by hand, because typing is where errors come from.

How do you reconcile your bank account in Excel?

You can start with a simple spreadsheet with two tabs:

• Statement: the lines that came from the bank, pasted or imported, with date, description and amount.
• Records: what the business recorded in the same period, with the same three columns.

Then add up each day's amounts on both tabs and compare. Excel has the SUMIF function, which adds only the rows that meet a condition, for example only the rows for one date. With it, a third tab shows the bank total and the recorded total side by side for each day. Any day where the two numbers differ is the day to check line by line.

If the bank gives you one file per month, keep them all in one folder. Excel's Get Data feature, part of Power Query, combines the text or CSV files in a folder into a single table. That way you stop copying and pasting statements every month.

This setup works well with a few dozen entries a week. Beyond that, checking by hand starts to take too long, and it makes sense to move to the automatic version.

How do you set up automatic bank reconciliation?

The automatic version has three parts, and each one removes a manual task:

1. Statement input. The statement arrives on its own, through a file downloaded on a routine or through the direct bank connection some systems offer.
2. Line matching. Rules set up once pair each bank line with the right entry: same amount, nearby date, similar description. A card machine payout, for example, is matched with the card sales from days before, with the fee already taken out.
3. Exceptions list. Whatever didn't match goes to a short list that a person handles: a Pix with no name, a new fee, a repeated payment.

Over time, each solved exception becomes a new rule, and the list gets shorter. The result can feed an automatic weekly report that reaches the owner with the checked balance and the items that need attention.

This applies to any industry. A street shop usually has many card and Pix sales. A restaurant or bakery also adds delivery apps, each with its own payout schedule.

Can AI do bank reconciliation?

Yes. AI helps with the lines that fixed rules can't handle. It can:

• read messy statement descriptions and tell what kind of expense each one is, such as rent, supplier or tax;
• read a photo of a receipt or invoice and turn it into an entry with date, amount and supplier;
• suggest the most likely match for an unidentified line, so a person only approves or corrects it;
• write the week's summary in plain language: what came in, what went out, and what is still unexplained.

The final decision stays with a person. AI gets things wrong when a description is ambiguous, so the right setup is for it to propose and for someone at the business or the accountant to confirm. See more examples in AI in finance and, for the part that goes to the accountant, in AI in accounting.

Where should your business start?

Start with the basics, which already handle most cases:

1. Separate the business account from the owner's personal account. Without that, no reconciliation adds up.
2. Pick a fixed day of the week for the check.
3. Download the statement as a file, OFX or CSV, instead of copying from the PDF.
4. Record every outgoing payment, including fees and withdrawals.
5. Build the exceptions list and clear it before the next week.

Once the routine is in place, you can automate one part at a time, starting with feeding the statement into a self-filling spreadsheet. If you want to know what to automate first in your case, take the free 3-minute assessment.