Checking Totals Between Two Excel Reports With SQL

Two reports claim to show the same thing and disagree. Finance says March revenue was 1,204,310; the sales dashboard export says 1,198,775. Someone has to find out why. In Excel this means two pivots, a column of subtraction and a day of scrolling. SQL does it in layers: totals, groups, rows, each narrowing the search. This post builds that reconciliation, with a full outer join as its core and a method for classifying what it finds. It is a spoke of doing proper analysis on Excel data with SQL.

Bring both to the same shape

Reconciliation compares like with like. Before any query, write down for each report: what one row represents (an order line, an invoice, a day), which period it covers and how that period is cut (order date or ship date; local time or UTC), what is included (returns, tax, shipping, canceled orders), the currency and the sign convention (are refunds negative?), and the date it was extracted. Differences in these definitions explain most gaps and are not errors in either report.

Load each as a table with the same column names for the key and the measure:

import duckdb
import pandas as pd

a = pd.read_excel("finance.xlsx", sheet_name="March", dtype={"doc_no": str})
b = pd.read_excel("sales_export.xlsx", sheet_name="Orders", dtype={"doc_no": str})

Normalize the key the same way on both sides: trim, uppercase, same padding (joining two Excel sheets with SQL lists the key problems). Choose the grain; if one report is by invoice and the other by order line, aggregate the finer one to the coarser before comparing.

Sample, reduced to a key and an amount:

Level 1: grand totals

SELECT (SELECT SUM(amount) FROM a) AS finance_total,
       (SELECT SUM(amount) FROM b) AS sales_total,
       (SELECT SUM(amount) FROM a) - (SELECT SUM(amount) FROM b) AS difference;

Finance totals 580.00 (100 + 250 + 80 + 60 + 90) and sales totals 515.01 (100 + 250 + 80.01 + 40 + 45), a difference of 64.99. If the difference is zero, stop; if it is small, you may be done after checking rounding; if it is large, descend. Also compare the row counts and the number of distinct keys, which tell you whether the gap is in amounts or in coverage.

Level 2: totals by group

Choose a dimension that both reports share, such as region, product or day, and compare:

SELECT COALESCE(x.region, y.region) AS region,
       COALESCE(x.total, 0) AS finance,
       COALESCE(y.total, 0) AS sales,
       COALESCE(x.total, 0) - COALESCE(y.total, 0) AS diff
FROM (SELECT region, SUM(amount) AS total FROM a GROUP BY region) x
FULL OUTER JOIN (SELECT region, SUM(amount) AS total FROM b GROUP BY region) y
  ON x.region = y.region
ORDER BY ABS(COALESCE(x.total, 0) - COALESCE(y.total, 0)) DESC;

The full outer join keeps a region that exists in one report only. Sorting by the absolute difference puts the guilty group first. Often one group or one day carries nearly the whole gap, and the search is over before it starts. If the gap is spread everywhere, suspect a definition: tax, a rate, a cut-off.

Level 3: rows

When a group is isolated, or no dimension helps, compare row by row on the key:

SELECT COALESCE(a.doc_no, b.doc_no) AS doc_no,
       a.amount AS finance,
       b.amount AS sales,
       COALESCE(a.amount, 0) - COALESCE(b.amount, 0) AS diff,
       CASE
         WHEN a.doc_no IS NULL THEN 'only in sales'
         WHEN b.doc_no IS NULL THEN 'only in finance'
         WHEN ABS(a.amount - b.amount) <= 0.05 THEN 'rounding'
         ELSE 'amount differs'
       END AS status
FROM a
FULL OUTER JOIN b USING (doc_no)
WHERE a.doc_no IS NULL OR b.doc_no IS NULL OR a.amount <> b.amount
ORDER BY ABS(COALESCE(a.amount, 0) - COALESCE(b.amount, 0)) DESC;

doc_no | finance | sales | diff  | status
-------+---------+-------+-------+----------------
D6     | 90.00   | 45.00 | 45.00 | amount differs
D4     | 60.00   | NULL  | 60.00 | only in finance
D5     | NULL    | 40.00 | -40.00| only in sales
D3     | 80.00   | 80.01 | -0.01 | rounding

The differences add to 45 + 60 minus 40 minus 0.01 = 64.99, which equals the total difference. That tie-out is the proof that the row list explains the gap. If the rows do not add up to the total difference, something is not in the comparison: a duplicate key that the join multiplied, a null key, a row filtered out earlier. Check keys for uniqueness in each table first, because a duplicate on one side distorts the join.

Tolerance and rounding

Amounts computed in different systems differ by cents because of rounding order (round each line then sum, or sum then round) and floating-point arithmetic. Decide a tolerance before comparing, such as 0.05 per row, and separate rounding differences from real ones. Do not compare floating-point numbers with =. Use decimal types, or compare the absolute difference with the tolerance. Also check whether the total of rounding differences is itself material: a thousand rows each off by 0.01 add up to 10.00.

Classify the differences

Each difference belongs to a cause. A short list covers most:

Add a cause column to the difference table and fill it, with a person's help where the data cannot say. A difference with a cause is an explanation; a difference without one is an open item. The aim is not always zero difference but zero unexplained difference. Reports built for different purposes legitimately disagree, and the reconciliation is the bridge between them: finance total, plus these items, equals sales total.

Present the bridge

A reconciliation reads best as a bridge from one number to the other:

SELECT status, COUNT(*) AS rows, SUM(COALESCE(a.amount, 0) - COALESCE(b.amount, 0)) AS amount
FROM a FULL OUTER JOIN b USING (doc_no)
WHERE a.doc_no IS NULL OR b.doc_no IS NULL OR a.amount <> b.amount
GROUP BY status;

Output: finance 580.00, then only in finance +60.00, only in sales -40.00, amount differs +45.00, rounding -0.01, to sales 515.01. Anyone reading it sees what the gap is made of. Put the bridge on the first page of the reconciliation workbook and the row detail on the second.

Keep it

Save the queries and the difference table with the two source files (or their checksums), the extraction dates and the name of the person who did the work. Next month, rerun the same queries on the new files. The recurring causes (a feed that is always a day late) become known items, and new ones stand out. If the reconciliation is a monthly control, make it a script that produces the bridge and flags a gap above a threshold (checking totals is part of testing a generated Excel report).

Pitfalls

• Comparing different periods. Make sure the date range is the same, including the boundary day.

• Joining on a non-unique key. Check uniqueness before and the row counts after.

• Ignoring nulls. a.amount <> b.amount is null when either is null, and the row drops out of the WHERE. The explicit IS NULL checks above avoid it.

• Comparing text and number keys. Cast both to text.

• Stopping at the total. Two reports can agree in total and disagree in every row, with errors that cancel.

When the two reports should not be expected to agree

They measure different things. Bookings and revenue, orders and shipments. A bridge explains the difference, and the difference is not an error.

One is a forecast or a snapshot. A figure frozen at the end of the month will differ from a live one.

Definitions have not been agreed. Reconciling to the cent is pointless until the business agrees what the number means.

The effort exceeds the value. A difference of 0.01 percent in a management report may not be worth a day.

See DuckDB's page on joins and PostgreSQL's on numeric types, which explains why decimals are exact and floats are not. To convert a small result to JSON, use the CSV to JSON converter.