When Sage 50 exports one transaction CSV and your processor sends a settlement or payout report, the rows almost never line up on first pass. Sage 50 records bookkeeping entries. The processor report records payment events. When you need to reconcile Sage 50 CSV exports processing reports, the mismatch usually starts because the two files are describing the same money at different levels.
That is why a row-by-row lookup fails so quickly. The processor may show one sale, one fee, one refund, and one net settlement inside the same batch. Sage 50 may show a sales posting, a fee expense, a refund posting, and one bank or clearing movement across different dates or references. Neither file has to be wrong for the comparison to break.
The fix is not another formula. The fix is to decide what each file is proving, then compare the two at the same level.
Why the files disagree before you compare them
Most failed reconciliations start with the wrong assumption: that Sage 50 and the processor should share one obvious key and one obvious total.
They usually do not.
Sage 50 exports are built around nominal postings, account codes, audit-trail references, and posting dates. Processing reports are built around gateway transaction IDs, settlement dates, batch IDs, fees, chargebacks, and net cash movement. Those structures overlap, but they are not identical.
Here is what that looks like in practice:
| Sage 50 export field | Processor report field | What it actually helps you prove |
|---|---|---|
| Posting date | Transaction date or settlement date | Whether a timing gap is expected |
| Reference or details | Order ID, invoice number, payment reference, transaction ID | Whether two records point to the same activity |
| Nominal code or account | Transaction type or fee category | Whether the posting went to the right bucket |
| Debit and credit amount | Gross, fee, refund, dispute, net | Whether you are comparing gross to gross or net to net |
| Bank or clearing account line | Batch ID, payout ID, settlement ID | Whether the cash movement itself was posted correctly |
The dangerous part is that the numbers can look close enough to tempt you into forcing a match. A settlement total might be one figure lower than sales because fees and refunds were deducted before payout. A posting date in Sage may be one day later because the book entry follows the bank date, not the original card transaction date. If you do not separate those differences early, every later exception list becomes noise.
Choose one reconciliation scope first
Before you compare any rows, choose the scope of the reconciliation. This matters more than the formula you use.
There are only three scopes that tend to work cleanly:
- One settlement or payout batch.
- One bank statement period.
- One month-end close period where Sage and the processor are both being reviewed on the same calendar.
Do not compare a full Sage month to one processor batch. Do not compare processor transaction dates to Sage bank postings if the problem you are solving is a net settlement that hit cash on a later day. Scope drift is why many reconciliations produce dozens of false mismatches before the real problem has even shown up.
For a clean pass, pull these files:
| File | Minimum columns to keep | Why you need it |
|---|---|---|
| Sage 50 transaction or audit-trail CSV | Date, reference, details, nominal code or account, debit, credit, net if available | Shows what was actually posted in the books |
| Processor transaction or settlement report | Transaction date, settlement date, transaction ID, order or invoice reference, type, gross, fee, refund or dispute amount, net, batch or payout ID | Shows what activity created the cash movement |
| Bank statement CSV if cash proof matters | Date, description, amount, reference | Confirms whether the settlement landed as expected |
If the bank movement is the piece that keeps failing, the grouped approach in compare two bank statement CSV files without formulas is closer to the real job than trying to debug one more lookup column. You are proving one grouped movement against another, then drilling into what sits inside each group.
Find the bridge key before you compare amounts
The biggest mistake in this kind of work is comparing amounts before you know which field can connect the files.
Sometimes the bridge key is obvious. Often it is not.
What you are looking for is the best available field that survives export on both sides:
| Candidate bridge key | Use it when | It fails when |
|---|---|---|
| Invoice number or order reference | The processor carries the same commercial reference that Sage stores in details or reference fields | The processor only stores its own transaction ID |
| Gateway transaction ID | Sage postings include the processor reference in the memo, details, or reference field | Sage only stores the customer or invoice reference |
| Batch, payout, or settlement ID | Sage posts processor cash through a clearing or bank line tied to the settlement | You try to use it to prove individual sales |
| Composite key: amount + type + date window | No single shared key exists, but timing and value patterns are stable | Multiple same-value transactions happen on the same day |
Notice what is missing from that list: nominal codes. An account code tells you where Sage posted the line. It does not tell you which processor event the line belongs to.
If you cannot find one shared key, build the bridge in layers:
- Match settlement totals or batch totals first.
- Isolate Sage lines into sales, fees, refunds, chargebacks, and bank or clearing movements.
- Then match detail records inside each bucket with the best reference still available.
That approach is slower than a perfect one-field match, but it is reliable. It stops you from comparing unrelated rows only because the amounts look similar.
Separate gross activity from settlement cash
Gross activity and settlement cash are not the same number. They should not be forced to behave like the same number.
A processor report might show this for one settlement:
| Processor activity | Amount |
|---|---|
| Gross card sales | 12,000.00 |
| Processing fees | -360.00 |
| Refunds | -500.00 |
| Chargeback | -140.00 |
| Net settlement | 11,000.00 |
Sage 50 might show this in the same period:
| Sage posting category | Amount |
|---|---|
| Sales income | 12,000.00 |
| Merchant fees expense | -360.00 |
| Customer refunds | -500.00 |
| Chargeback or adjustment account | -140.00 |
| Bank or clearing movement | 11,000.00 |
If you compare sales income to the net settlement, you will invent a 1,000.00 difference that is not unexplained at all. It is already explained by fees, refunds, and the chargeback.
This is the structure that actually works:
| Layer | Compare this | Against this | Question it answers |
|---|---|---|---|
| Sales layer | Gross processor sales | Sage sales postings | Were the underlying sales recorded? |
| Deductions layer | Processor fees, refunds, disputes | Sage expense, refund, and adjustment postings | Were the reductions posted correctly? |
| Cash layer | Net settlement or payout | Sage bank or clearing line, and bank statement if needed | Did the money movement land correctly? |
Once you split the work this way, the reconciliation becomes much easier to defend. You are no longer asking one total to prove three different things at once.
A workflow that works when no shared reference exists
This is the workflow to use when the processor report and Sage 50 export do not share one clean match field.
1. Export the smallest complete slice
Pick one settlement, one batch, or one statement period. Export Sage 50 lines for that same scope. Do not pull a larger range "for safety." A wider file gives you more unrelated rows and weaker matches.
2. Classify the processor rows by type
Split the processor file into sales, fees, refunds, chargebacks, and net settlements. Keep the original export unchanged. Work from a copy if you need helper columns.
At this point you should be able to answer one basic question: what should the net settlement equal after all deductions?
3. Classify the Sage rows by posting purpose
Use nominal codes, account names, and descriptions to separate the Sage export into the same buckets:
- Revenue or sales
- Merchant fees
- Refunds or credit notes
- Chargebacks or adjustments
- Bank or clearing lines
Do not start by matching individual rows. Start by proving that each bucket exists and that its total is plausible.
4. Build summary totals before detail matching
Create one short summary like this:
| Category | Processor total | Sage total | Status |
|---|---|---|---|
| Gross sales | 12,000.00 | 12,000.00 | Matched |
| Fees | -360.00 | -360.00 | Matched |
| Refunds | -500.00 | -250.00 | Difference remains |
| Chargebacks | -140.00 | 0.00 | Missing in Sage |
| Net settlement | 11,000.00 | 11,000.00 | Matched |
This table tells you where to spend your time. If gross sales and net settlement both match, but refunds and chargebacks do not, the problem is not the whole reconciliation. It is those categories.
5. Only then drill into unmatched detail
For the unmatched bucket, try the best available bridge in this order:
- Shared invoice, order, or payment reference.
- Shared processor transaction ID in Sage details.
- Same signed amount and same transaction type within a short date window.
- Settlement-level grouping if Sage posts summaries instead of detail.
This is also where CSV quirks matter. Sage may export one reference field while the processor report uses another. Leading zeros may disappear if someone opened the file carelessly. Dates may be shown as posting dates in Sage and settlement dates in the processor. Those are not minor formatting details. They decide whether a valid row looks unmatched.
The same pattern shows up in other accounting-export comparisons too. The workflow in reconciling a client bank CSV against a QuickBooks ledger is solving the same structural problem: two financial files, different reference logic, and a need to separate real exceptions from false ones.
What the remaining exceptions usually mean
After the buckets are separated and the summary totals are built, most remaining exceptions fall into a small set of patterns.
| What you see | What it usually means | What to check next |
|---|---|---|
| Net settlement matches, but sales do not | Sales were posted net in Sage or posted to the wrong period | Check whether the bank movement was booked straight to revenue |
| Sales match, but cash is lower | Fees, refunds, reserves, or chargebacks are missing from Sage | Filter non-sale processor rows and trace those postings |
| Same amount, different date | Settlement timing difference | Compare processor settlement date to Sage posting date |
| Many processor rows to one Sage line | Sage was posted in batch summary form | Match at settlement or daily summary level first |
| One-sided unmatched records | Wrong reference field or truncated ID | Compare alternate reference columns before changing amounts |
| Small recurring difference each period | Repeating fee, reserve hold, or rounding treatment | Look for one category that is always posted separately |
This is where people often overreact and start editing source files. Do not do that. Once you change the export manually, you weaken the audit trail and make the next mismatch harder to explain. Keep the raw files intact. Add your classification and matching logic around them.
What a finished reconciliation should show
A finished reconciliation is not a workbook full of highlighted rows. It is a short explanation that another person can review and follow.
The output should make these points clear:
| Output section | What it proves |
|---|---|
| Scope summary | Which settlement, period, or batch the reconciliation covers |
| Match key logic | Which fields were used to connect the files |
| Category totals | Whether sales, fees, refunds, chargebacks, and cash agree at summary level |
| Exceptions list | Which records are still missing, duplicated, mistimed, or posted to the wrong bucket |
| Required corrections | What has to be changed in Sage or explained to close the gap |
If you cannot state those points plainly, the reconciliation is not finished yet.
The goal is not to make every row line up in one grid. The goal is to prove where the money moved, which deductions explain the difference between gross and net, and which records still require action. Once you do that, Sage 50 and the processor report stop looking contradictory. They become two views of the same activity.
