1. What is Bank Reconciliation?
Bank reconciliation (or bank conciliation) is the process of comparing, transaction by transaction, the bank statement against the company's accounting records to confirm they match.
The goal is to identify:
- Transactions recorded in accounting but not yet reflected in the bank (outstanding cheques, transfers in transit)
- Bank transactions not recorded in accounting (bank charges, interest, direct debits)
- Posting errors (wrong amount, duplicates)
- Fraud or unauthorised transactions
How often should you reconcile?
The minimum recommended frequency is monthly. Companies with high transaction volumes should consider weekly reconciliation. The more frequent it is, the easier it is to detect and correct discrepancies before they accumulate.
2. Preparing the Data for Reconciliation
To perform bank reconciliation in Excel you need two data sets:
Bank Statement
Exported from the bank as PDF → converted to Excel with Bank2PDF
- ✓ Date of each transaction
- ✓ Transaction description
- ✓ Amount (debit/credit)
- ✓ Running balance
Accounting Entries
Exported from accounting software (PHC, Primavera, Sage)
- ✓ Posting date
- ✓ Description / document
- ✓ Accounting debit/credit
- ✓ Bank account (12xx)
Getting the bank statement in Excel
The first step — and often the most laborious — is converting the bank statement PDF to Excel. Bank2PDF does this automatically for any Portuguese bank:
- Download the PDF statement from online banking (CGD, BCP, Santander, Novo Banco, BPI, etc.)
- Upload the PDF at bank2pdf.com
- Receive the Excel with columns Date, Description, Debit, Credit, Balance
Exporting accounting entries
In PHC: Accounting → Journal → Listing → Export Excel, filtering by bank account (e.g. 1211 — BCP Current Account).
In Primavera: Accounting → Enquiries → Account Statement → Export, filtering the corresponding bank account.
In Sage: Accounting → Account Statement → Export CSV.
3. Structure of the Reconciliation Excel File
The reconciliation file should have at least 3 sheets:
| Sheet | Contents | Source |
|---|---|---|
| Bank_Statement | Bank statement transactions | Bank2PDF → Excel |
| Accounting | Bank account entries | PHC / Primavera / Sage |
| Reconciliation | Cross-reference and differences | Formulas (VLOOKUP / Power Query) |
Columns in the Reconciliation sheet
The reconciliation sheet should have these columns:
| A — Data | B — Descrição banco | C — Valor banco | D — Valor contab. | E — Diferença | F — Estado |
|---|
Column E (Difference) is simply =C2-D2. If it equals 0, the transaction is reconciled. Column F (Status) uses a conditional formula to show "OK", "Pending" or "Difference".
4. VLOOKUP to Cross-Reference Transactions
The VLOOKUP formula (PROCV in Portuguese) is the classic tool for cross-referencing bank statement transactions against accounting entries. The logic is: for each bank transaction, find the matching entry in the accounting records.
Reconciliation key
The challenge in bank reconciliation is that the bank and accounting records rarely have the same description. The best cross-reference key is a combination of date + amount:
=TEXTO(A2,"DD-MM-AAAA")&"|"&TEXTO(C2-D2,"0,00")
This formula creates a key like 15-03-2025|-1250.00 (date + net amount). Do the same on the Accounting sheet. Then use VLOOKUP to cross-reference:
=SEERRO(PROCV(F2,Contabilidade!F:G,2,0),"Não encontrado")
Tip: use MATCH + INDEX instead of VLOOKUP
VLOOKUP only looks from left to right. For greater flexibility, use =INDEX(Accounting!G:G,MATCH(F2,Accounting!F:F,0)) — this works regardless of column order.
Highlight differences automatically
Use conditional formatting to highlight unreconciled rows. Select column E (Difference) and apply:
- Green if E = 0 (reconciled)
- Red if E ≠ 0 (difference)
- Yellow if D = "Not found" (no match)
5. Automate with Power Query
Power Query (available in Excel 2016+ and Microsoft 365) is the most efficient way to automate bank reconciliation. Instead of copying and pasting data every month, Power Query imports and transforms the data automatically.
Setting up Power Query for reconciliation
Import the bank statement
Excel → Data → Get Data → From File → From Excel. Select the Excel file exported by Bank2PDF. Power Query loads the sheet with the transactions.
Import the accounting entries
Repeat the process for the file exported from PHC/Primavera/Sage. You will have two separate queries.
Create a key column in both queries
In each query, add a custom column with the date+amount key: = Text.From([Date]) & "|" & Text.From([Amount])
Merge the two queries
Data → Combine Queries → Merge. Select both queries, the key column from each, and the join type: Left Outer (to keep all bank transactions, even those without a match).
Automatic status column
Add a conditional column: if Accounting_Amount = null → "To post"; if Bank_Amount ≠ Accounting_Amount → "Difference"; else → "OK".
Load to sheet and refresh
Load the result to an Excel sheet. Next month, simply update the source files and click Refresh All — Power Query reruns the entire process automatically.
Result: reconciliation in 2 minutes per month
With Power Query configured, the monthly reconciliation comes down to: (1) convert the statement PDF to Excel with Bank2PDF, (2) export entries from PHC/Primavera, (3) click "Refresh All" in Excel. All cross-referencing is done automatically.
6. Handling Differences
After cross-referencing, differences will appear. The most common ones and how to resolve them:
Transactions in transit (timing differences)
These are transactions already posted in accounting but not yet reflected in the bank (e.g. a cheque issued but not yet cashed), or vice-versa. No immediate correction needed — mark them as "In transit" and check the following month whether they have been processed.
Unrecorded bank charges and fees
The bank charges maintenance fees, transfer fees, etc., which are often not posted in accounting. Identify these transactions in the statement, create the corresponding entries (Bank Charges account — 69x) and post them.
Duplicate entries
If the same transaction appears twice in accounting, the balance will not match. Use Power Query to group by key and identify duplicates — any key with count > 1 is suspicious. Verify and reverse the duplicate entry in the accounting software.
Incorrect amounts
An entry with the wrong amount (e.g. €1,250 instead of €1,500) generates an exact difference of €250. Identify the source document, correct the entry and record the difference with a corrective document.
7. Reconciliation in PHC, Primavera and Sage
Portuguese accounting software packages have native bank reconciliation modules that automate part of the process described above.
PHC CS — Treasury Module
In PHC, go to Treasury → Bank Reconciliation. Import the bank statement (Bank2PDF Excel) and the system automatically cross-references it against bank account entries. Unreconciled transactions are flagged in red. From PHC 23.x, the module supports direct CSV import.
Primavera BSS — Automatic Reconciliation
In Primavera ERP, go to Treasury → Banks → Bank Reconciliation. Import the statement and Primavera cross-references by date and amount. Partial reconciliations (same amount, different dates) are flagged separately for manual review.
Sage 50c — Bank Statement
In Sage, go to Accounting → Bank Reconciliation. The process is similar: import statement, select account, and Sage flags matching transactions. Unreconciled items remain visible for manual posting.
Detailed import guide
For step-by-step instructions on importing statements into PHC, Primavera and Sage, see our article: How to Import Bank Statements into Accounting Software.
8. Frequently Asked Questions
What is the difference between bank reconciliation and an account statement?
VLOOKUP does not work when values have decimal places — why?
=ROUND(C2,2). Alternatively, use a text key with TEXT(C2,"0.00") which formats the number as a string. Can I use Google Sheets instead of Excel for reconciliation?
How do I convert a bank statement PDF to Excel quickly?
The closing balance does not match after reconciling all transactions — what should I do?
Start your reconciliation now
Convert your bank statement PDF to Excel in seconds — ready for VLOOKUP, Power Query and all Portuguese ERPs.
Free Trial — 5 PagesNo credit card. CGD, BCP, Santander, Novo Banco, BPI and more.