Run a Benford's Law Check on Excel Data

In many naturally occurring sets of numbers — invoice amounts, expense claims, populations, river lengths — the leading digit is 1 about 30% of the time and 9 less than 5% of the time. That pattern is Benford's law, and auditors use it as a screening test: data that departs from it markedly is worth a closer look, because people who invent or adjust numbers tend to spread digits more evenly or cluster them below approval limits. It does not prove anything on its own; it tells you where to look. This page runs the test on an expense ledger with pandas. It is part of Validating Excel Data with Python.

Prerequisites

pip install pandas scipy openpyxl xlsxwriter

expense_claims.xlsx has a year of expense claims: claim ID, employee, category and amount, from a couple of pounds for a coffee to several thousand for equipment. Claims of 5,000 or more need a director's approval. That rule is the kind of thing Benford analysis is good at exposing.

Why the leading digit is not uniform

Benford's law says the probability that the first significant digit is d is log₁₀(1 + 1/d). It holds for data that spans several orders of magnitude and arises from multiplying or growing processes: going from 1,000 to 2,000 needs a 100% increase, from 9,000 to 10,000 only 11%, so values spend longer with a leading 1. It does not apply to data with a narrow range (heights of adults), assigned numbers (phone numbers, invoice numbers) or values bounded by rules (a fixed fee, a price list).

Step 1 — Extract the first significant digit

The first significant digit of 0.045 is 4, and of 1,250.00 it is 1. Converting to a string and taking the first character gets both wrong: "0" for the first, and for negative numbers "-". Use logarithms instead, after taking the absolute value:

import numpy as np
import pandas as pd

claims = pd.read_excel("expense_claims.xlsx")
amount = claims["Amount"].abs()

def first_digit(x):
    x = x[x > 0]
    return (x / 10 ** np.floor(np.log10(x))).astype(int)

print(first_digit(pd.Series([0.045, 1250.00, 9.99, 10.0, 999_999.0])).tolist())

Floating-point quirks can make x / 10**k land a hair below an integer; astype(int) truncates, so for values like 1000.0 check the result is 1, as above.

Standard practice is to leave out small amounts, below 10, because they are dominated by fixed prices and rounding rather than by the processes Benford describes:

tested = claims[amount >= 10].copy()
tested["D1"] = first_digit(tested["Amount"].abs())
print(f"{len(claims)} claims, {len(tested)} tested (amount >= 10)")

Step 2 — Observed against expected

Count the first digits, turn them into proportions, and set them beside the Benford expectation:

digits = np.arange(1, 10)
expected = np.log10(1 + 1 / digits)
observed = tested["D1"].value_counts(normalize=True).reindex(digits, fill_value=0).to_numpy()

table = pd.DataFrame({"Digit": digits, "Count": tested["D1"].value_counts().reindex(digits, fill_value=0).to_numpy(),
                      "Observed %": observed * 100, "Expected %": expected * 100})
table["Difference"] = table["Observed %"] - table["Expected %"]
print(table.round(2).to_string(index=False))

Step 3 — Measure conformity

Two measures are used. The mean absolute deviation (MAD) is the average absolute gap between observed and expected proportions; Mark Nigrini's widely used thresholds for the first-digit test are below 0.006 for close conformity, up to 0.012 acceptable, up to 0.015 marginal, and above that nonconformity. The chi-square test gives a p-value, but with thousands of rows it flags even trivial differences, which is why MAD is preferred for large ledgers:

from scipy.stats import chisquare

mad = np.abs(observed - expected).mean()
verdict = ("close conformity" if mad < 0.006 else "acceptable" if mad < 0.012
           else "marginal" if mad < 0.015 else "nonconformity")
counts = table["Count"].to_numpy()
chi2, p = chisquare(counts, f_exp=expected * counts.sum())
print(f"MAD {mad:.4f} ({verdict}); chi-square {chi2:.1f}, p = {p:.2g}")

The two measures disagree here, and the disagreement is the lesson. By MAD the ledger is in close conformity: averaged over nine digits, the deviations are small. The chi-square test, with over 4,000 claims, says the pattern is very unlikely to be chance (p ≈ 0.005). Look at the table instead of either number: eight digits sit within about one percentage point of Benford, and one, digit 4, is two points high, about 85 more claims than expected. A targeted manipulation affects a narrow band of amounts, and an average over all digits dilutes it. Always read the per-digit differences, not just the summary statistic.

Step 4 — Drill into the digit that stands out

A test result is only useful if it leads to transactions. Here digit 4 is over-represented; the next question is where, and a first-two-digits breakdown sharpens it. The two-digit test uses 90 bins, 10 to 99, with expected share log₁₀(1 + 1/dd):

x = tested["Amount"].abs()
tested["D12"] = (x / 10 ** (np.floor(np.log10(x)) - 1)).astype(int)
two = tested["D12"].value_counts(normalize=True).rename("Observed")
two = two.to_frame().assign(Expected=np.log10(1 + 1 / two.index))
two["Excess"] = (two["Observed"] - two["Expected"]) * 100
print(two.sort_values("Excess", ascending=False).head(4).round(3).to_string())

The spike is at 47, 48 and 49: amounts from 4,700 to 4,999, just under the 5,000 approval limit. Now look at who submits them:

near_limit = tested[tested["Amount"].between(4700, 4999.99)]
print(near_limit.groupby("Employee").size().sort_values(ascending=False).head(5).to_string())

Three employees account for most of the claims just below the limit. That is not proof of anything — some roles legitimately buy equipment in that price range — but it is a specific, short list for someone to review, which is the whole purpose of the test.

Two companion tests are cheap to add. The duplicate amounts test lists the exact amounts that occur most often; a single figure repeated dozens of times is either a standard charge, which is fine, or a claim being resubmitted. The last two digits test checks rounding: invented numbers end in 00 or 50 far more often than real receipts do.

print(tested["Amount"].value_counts().head(3).to_string())
cents = (tested["Amount"] * 100).round().astype(int) % 100
print(f"amounts ending .00: {(cents == 0).mean():.1%} (about 1% expected for real receipts)")

Step 5 — Write the results with a chart

The workbook holds the digit table with a column chart of observed against expected, the two-digit table, and the claims behind the finding:

with pd.ExcelWriter("benford_check.xlsx", engine="xlsxwriter") as xw:
    out = table.assign(**{"Observed %": table["Observed %"] / 100, "Expected %": table["Expected %"] / 100,
                          "Difference": table["Difference"] / 100})
    out.to_excel(xw, sheet_name="First digit", index=False)
    ws = xw.sheets["First digit"]
    pct = xw.book.add_format({"num_format": "0.0%"})
    ws.set_column(2, 4, 12, pct)
    ws.write(11, 0, f"MAD {mad:.4f}: {verdict}")
    chart = xw.book.add_chart({"type": "column"})
    chart.add_series({"name": "Observed", "categories": ["First digit", 1, 0, 9, 0], "values": ["First digit", 1, 2, 9, 2]})
    line = xw.book.add_chart({"type": "line"})
    line.add_series({"name": "Benford expected", "categories": ["First digit", 1, 0, 9, 0],
                     "values": ["First digit", 1, 3, 9, 3], "marker": {"type": "circle"}})
    chart.combine(line)
    chart.set_y_axis({"num_format": "0%"})
    chart.set_title({"name": "First digits against Benford's law"})
    ws.insert_chart("G2", chart)
    two.sort_values("Excess", ascending=False).round(4).to_excel(xw, sheet_name="First two digits")
    near_limit.sort_values(["Employee", "Amount"]).to_excel(xw, sheet_name="Claims 4,700-4,999", index=False)
print("written")

Showing the expected values as a line over observed columns makes a spike obvious without any statistics.

Common pitfalls

• Taking the first character of a string. It fails for decimals below 1 and negative numbers; use the logarithm.
• Testing data Benford does not describe. Narrow ranges, assigned numbers and price lists will "fail" for innocent reasons.
• Including small amounts. Below 10, fixed prices dominate; exclude them and say so.
• Relying on chi-square alone with big data. Tiny differences become "significant"; use MAD for large ledgers.
• Treating a result as proof. Nonconformity says where to look. The explanation is in the transactions.
• Too few rows. With a few hundred values, random variation swamps the pattern; the test needs thousands.

Conclusion

Extract the first significant digit with logarithms after taking absolute values, exclude amounts below 10, and compare the observed digit shares with log₁₀(1 + 1/d). Measure conformity with the mean absolute deviation against Nigrini's thresholds, use chi-square with care on large data, then drill in with the first-two-digits test to specific amount ranges and the people or suppliers behind them. Write the tables, a chart of observed against expected and the transactions for review to Excel.

Frequently asked questions

What is Benford's law? An observation that in many real-world datasets the leading digit d occurs with probability log10(1 + 1/d): about 30% of values start with 1 and under 5% with 9. It applies to data spanning several orders of magnitude.

How do I get the first digit of a number in pandas? Take the absolute value, then divide by 10 raised to the floor of its base-10 logarithm and convert to int. That handles decimals below 1 and large numbers, which string slicing does not.

What MAD value indicates nonconformity? For the first-digit test, Nigrini's thresholds are below 0.006 close conformity, 0.006 to 0.012 acceptable, 0.012 to 0.015 marginal, and above 0.015 nonconformity.

Does failing a Benford test mean there is fraud? No. It means the data does not follow the expected pattern, which can have innocent causes such as a price list or a policy threshold. It identifies where to look more closely.

Related

• Up one level: Validating Excel Data with Python — checks to run before trusting a sheet.
• Find Gaps in a Numbered Sequence with pandas — another audit test.
• Profile an Excel Dataset with pandas — a first look at any file.
• Detect Outliers in Excel Data with pandas — unusual values, rather than unusual digits.
• Add a Combo Chart with a Secondary Axis in openpyxl — column-and-line charts.