Duplicate Payment Testing with AI: Every AP Invoice, Plus the Three-Way Match
Duplicate payment testing is one of the oldest tests in internal audit, and one of the easiest to do badly.
An exact match on vendor, invoice number and amount finds the obvious pairs. It misses the invoice keyed as
A0071355 one week and A71355 the next, and the same invoice paid again under a second vendor record that
shares the first one's bank account. The three-way match has traps of its own: a PO line received in three
lots looks like a short receipt to a careless join, and a price 1.5% over the PO may be inside tolerance.
An AI tool can write and run these tests on the full AP population, not a sample. Two questions come first: what leaves your laptop, and how do you know the answer is right?
The rule: let the AI write the script, not read the data
Claude Code is Anthropic's AI tool that works on files on your own computer. It needs a paid Claude plan, and for company data your organization should approve the tool and the plan first.
The model that does the thinking runs on Anthropic's servers, so the rule is short: whatever Claude Code reads, it sends. If it opens your AP register, the rows go to the provider. If it writes a Python script that reads the register on your laptop and prints only counts and totals, then only the headers, the script and the counts are sent. The detail stays in a workbook on your disk.
This is a working habit, not a security boundary: printed rows and some error messages are sent too, and an approved script runs with your access. Work in a folder holding only the copies this job needs, use Manual mode, and read each permission prompt.
What you need
ap_invoices.xlsx: the invoice register, with vendor, invoice number, dates, amount, PO number, statuspo_lines.xlsxandgoods_receipts.xlsx: PO lines and the receipts against themvendor_master.xlsx: vendor ID, bank account, tax ID, created dateap_policy_extract.txt: price tolerance and the categories allowed without a POap_control_total.txt: the GL document count and net amount for the period
Step 1: Learn the files without reading the data
This folder holds our accounts payable data for an internal audit. Treat every file as company data: do not open the .xlsx files directly and do not print any data rows. Write and run a short Python script that shows, for each .xlsx file, the sheet names, column names, data types, row count and the date range of any date column. Then read the two .txt files. Tell me in plain words what each file is, which columns link them, and anything in the structure I should know before testing.
Structure alone tells you a lot. In the book's tested run on practice data, this step noticed that the register was pulled by invoice date but the GL total by posting date, that some PO lines had several receipts, and that two vendor records shared a tax ID.
Step 2: Tie the register to the general ledger
Every test after this depends on the population being complete.
Create a script ap_tests.py that writes ap_test_output.xlsx. Never print data rows. Compare the document count and net amount of ap_invoices.xlsx with the GL control total in ap_control_total.txt. If they differ, find the invoices that explain the difference (look at how each file defines its period) and show what, if anything, is left unexplained. Write a sheet Population_Recon ending with a one-line conclusion. Run the script and show me only that sheet.
A difference is often a cut-off item, such as an invoice dated in June and posted in July. A tie on net totals cannot rule out missing items that offset; if you will rely on the population, ask for the GL document list.
Step 3: Run four duplicate tests
Add duplicate tests to ap_tests.py, one sheet each, on invoices with a positive amount: DUP1_Exact: same vendor, same invoice number, same amount. DUP2_Near: same vendor and amount, where the invoice numbers match after upper-casing, removing anything that is not a letter or digit, and dropping leading zeros (e.g. INV-00123 and INV123). DUP3_Cross_Vendor: vendors in vendor_master.xlsx that share a bank account or a tax ID; then invoices for the same amount and normalized invoice number across those records. DUP4_Same_Amount: same vendor and amount, different invoice numbers. A pointer only. Give each set a Group number, show Status and Payment_Date, and note where a credit memo reverses an invoice. Run it and show me only rows, groups and total amount per sheet.
The normalization rule is a test-design decision; decide it and record it. The same-amount test will flag recurring charges such as monthly rent: explain those rows, don't report them. And a duplicate reversed by a credit memo before payment is the control working, not an exception.
Step 4: Test the three-way match
Read ap_policy_extract.txt for the rules. Add one sheet each, on invoices with a positive amount: TW1_No_PO: no PO number, in a category the policy does not exempt. TW2_Qty_Over_Received: quantity billed on each invoice above the total received for that PO line (add up all receipts for the line). TW3_Price_Over_Tol: unit price above the PO price by more than the policy tolerance, kept as a setting at the top of the script. TW4_PO_After_Invoice: PO dated after the invoice. TW5_Paid_Before_Receipt: paid before the last goods receipt for that PO line. Add Difference columns, a Summary sheet and a Parameters sheet (files and row counts, tolerance, exempt categories, versions, date run, blank Reviewed by). Show me only the Summary.
Be precise about wording. "Billed above received for that PO line" can be read per invoice or for all invoices on the line together. The book's two runs of the same prompt read it both ways and got different row counts; neither was wrong. Agree the definition and write it in the working paper.
The amount on each exception sheet is usually the whole invoice value. Report the exposure instead.
How to check the result
Fluent output is not evidence. These checks are:
- Control totals. Sum the register's amount column in Excel yourself and tie it to the GL figure, explaining the difference. Rows read should equal rows tested plus rows set aside (credit memos, negative amounts), and the sheets should add back.
- Re-perform an exception. Filter the register for one near-duplicate pair and confirm both invoices, the PO line and both payment dates. Check the shared bank account in the vendor master yourself.
- Re-perform a pass. Pick a PO line with several receipts that was not flagged and confirm the receipts add up to the quantity billed. Checking only what was found never catches what was wrongly left out.
- Re-run and compare.
Run ap_tests.py again so that it writes ap_test_rerun.xlsx instead (change nothing else). Then write and run compare_runs.py, which checks that every sheet except Parameters has identical rows in both files. Print only the comparison result.
An identical re-run shows the script is consistent. It does not show it is correct; the hand checks do that.
- Ask for assumptions. "List every assumption you made and everything you did not check." Which date, which price version, which currency: a script makes these choices silently.
File the script, the prompts, both workbooks and your re-performed items with a short note on population, tolerance, definitions and reviewer. That is what lets a reviewer, or next year's team, re-perform the work.
Limits
- A flag is not a finding. Vouch each one to the invoice copies, the receipt, the bank payment and, for a second vendor record, the vendor creation request. Words like fraud belong to a different engagement.
- The script tests only what is in the data. Approvals held outside the ERP, such as sign-off by legal on legal invoices, are not tested.
- A script can be precisely and consistently wrong. Read its rules in plain English against your test design.
- The script pattern reduces what is sent to the AI provider; it does not remove the need for approval, a clean working folder and careful permission prompts.
- The extract is only as good as how it was obtained. Record the report, parameters and who ran it.
From the book
This guide is a short version of one method in AI for Internal Auditors. Chapter 17 of AI for Internal Auditors walks through it end to end on practice files with an answer key.
More free guides
- Free guide · AI for Internal Auditors
Segregation of Duties Analysis with AI: User Access Review
Find SoD conflicts, mitigated cases and leavers with active accounts across a full ERP access listing using AI, with user data kept on your laptop.
Read the guide → - Free guide · AI for Internal Auditors
Journal Entry Testing with AI: Completeness and Risk Scoring
Roll the trial balance forward, score every manual journal against criteria you can defend, and keep GL data on your laptop. Prompts and checks.
Read the guide →