# Vendor Statement PDF to Excel: Open Invoices Listed

- Author: FastBankConvert (https://www.fastbankconvert.com)
- Published: 2026-09-02
- Updated: 2026-09-02
- Category: Other financial documents
- Canonical: https://www.fastbankconvert.com/blog/vendor-statement-pdf-to-excel

> Turn a vendor statement PDF to Excel: open invoices, credits and payments, aging buckets, and a sheet you can compare with your own records.
To turn a vendor statement PDF to Excel, put each listed invoice, credit note and payment on its own row with number, date, type and a signed amount. Rebuild the aging buckets from the dates, then line the rows up against your payables ledger by invoice number, so every difference between the supplier's balance and yours has a named cause.

## Vendor statement layouts: open item versus balance forward

Vendor statements come in two main layouts. An open item statement lists only the invoices and credits that are still unpaid. A balance forward statement starts from the previous balance and lists everything that happened in the period. Identify which one you have first, because it decides what each row means and how the total is built.

### Open item statements

An open item statement is a to-do list from the supplier's side: every document they think you still owe on, with its date, number and remaining amount. Paid invoices drop off. Partly paid invoices show the remaining balance. The total at the bottom is the sum of the rows, so a conversion is complete when the rows add up to that total.

### Balance forward statements

A balance forward statement reads more like a bank statement. It opens with the balance carried from last period, lists new invoices, payments received and credits issued, and closes with a new balance. Here the check is a roll-forward: opening balance plus invoices, minus payments and credits, equals closing balance. A paid invoice from earlier in the month still appears, alongside the payment that cleared it.

### Hybrid layouts

Many suppliers print a mix: open items, plus a few lines of recent payments not yet matched to invoices, plus an aging summary box. Treat each block as rows with a Type column, and use the summary box as a check rather than as data.

## Open invoices, credits and payments as vendor statement rows

Every vendor statement line becomes a row with the same columns: document date, document number, type (invoice, credit or payment), your PO or reference number if printed, the original amount, and the amount still open. Store invoices as positive and credits and payments as negative, so a plain sum gives the balance you owe.

### Signs that stay consistent

Suppliers print signs in different ways. Some use a minus sign, some use brackets, some put credits in a separate column, and some print every amount as positive and rely on a CR suffix. Convert all of them to one rule before you sum anything. Find and Replace can strip brackets or a CR suffix, but only after you have marked those rows as negative in a helper column.

### Document numbers as text

Invoice numbers often have leading zeros or mixed letters and digits. Format the column as text before importing so 000418 stays 000418. Your ledger stores the number exactly as the supplier printed it, and a match on a number that lost its zeros fails silently.

### Keeping the statement's own details

Add columns for the supplier name, the statement date and the account number the supplier uses for you. They are the same on every row of one statement, but repeating them lets you stack statements from several suppliers into one sheet. If you keep this data outside Excel, a structured format such as JSON holds the same fields well; the guide on [bank statement data in JSON](/blog/bank-statement-to-json) shows a layout for header and line fields that adapts to supplier statements.

## How do aging buckets on a vendor statement work?

Aging buckets group open amounts by how old they are on the statement date, usually Current, 1 to 30, 31 to 60, 61 to 90 and over 90 days. Suppliers count age from either the invoice date or the due date, and the statement rarely says which. Rebuild them in Excel and compare with the printed aging box.

### Rebuilding the buckets

Add a column for days old: the statement date minus the invoice date. Then a bucket column with a nested IF or a lookup against a small table of thresholds. A pivot table on bucket and amount gives the aging summary. If your totals per bucket match the supplier's box, they age from invoice date. If they are shifted by one bucket for some invoices, the supplier probably ages from due date; recompute with due date and the match should appear.

### Why the buckets matter

The aging tells you which balances need attention first. An invoice in the over-90 bucket on the supplier's statement but absent from your ledger is either lost, disputed or already paid without being applied. Each of those calls for a different action, and all of them get harder the older the invoice is.

### Credits in the buckets

Unapplied credits and payments also land in buckets, as negative amounts. A credit sitting in the over-90 bucket usually means the supplier owes you an offset you never used. That is money worth claiming.

## Reconciling a vendor statement against your payables ledger

Reconciling means placing the supplier's list beside your own open payables for that supplier and explaining every line that differs. Put both on one sheet with a shared key, usually the invoice number, then pull each side's amount next to the other. The difference column should end up fully explained, line by line.

### Worked example

The statement and ledger below are a made-up example dated March 31, 2026.

| Document | Date | Type | Vendor says open | Our ledger open | Difference | Cause |
| --- | --- | --- | --- | --- | --- | --- |
| INV-1042 | 2026-01-12 | Invoice | 1,250.00 | 0.00 | 1,250.00 | Paid by us March 30, not yet received |
| INV-1088 | 2026-02-05 | Invoice | 830.00 | 0.00 | 830.00 | Paid; vendor lists payment separately |
| PMT | 2026-03-28 | Payment | -830.00 | 0.00 | -830.00 | Vendor's unapplied payment for INV-1088 |
| INV-1101 | 2026-02-20 | Invoice | 415.50 | 415.50 | 0.00 | Agrees |
| CM-207 | 2026-03-02 | Credit | -120.00 | 0.00 | -120.00 | Credit note not entered in our ledger |
| INV-1123 | 2026-03-14 | Invoice | 2,040.00 | 2,400.00 | -360.00 | Our entry transposed digits |

The supplier's total is 3,585.50 and our ledger shows 2,815.50, a gap of 770.00. The causes account for it exactly: 1,250.00 in transit, less the 120.00 credit we missed, less the 360.00 keying error. The INV-1088 pair cancels out.

### Steps to build it

1. Convert the supplier statement into rows with document number and signed open amount.
2. Export your open payables for that supplier with the same two fields.
3. Stack the two lists, or use a lookup from one to the other on document number.
4. Add a difference column and sort by its absolute value, largest first.
5. Write a cause next to every non-zero difference, and confirm the causes add up to the total gap.

### Who fixes what

Some causes are yours to fix, such as the missed credit note and the keying error. Some fix themselves, such as a payment in transit. Some need the supplier, such as a payment they hold but have not applied. Sorting the cause column groups the actions.

## Why doesn't the vendor statement balance match your ledger?

A vendor statement balance and your ledger disagree for a short list of reasons: timing, missing documents, keying errors and unapplied payments. Timing is the most common and needs no action. The others need a correction on one side, so each must be found and named, not netted away as a single unexplained figure.

### Timing differences

A payment you sent on the 30th that the supplier has not received by the statement date on the 31st, or an invoice dated the 31st that you have not received yet. Both resolve themselves within days. Note them and check that they have cleared on next month's statement.

### Missing documents

An invoice on their statement and not in your ledger may never have reached you, or it may be sitting unapproved in someone's inbox. A credit note on their side and not on yours is the reverse. Ask for copies of anything you do not hold.

### Unapplied payments and misallocation

A payment the supplier received but did not apply to a specific invoice leaves both the invoice and the payment open on their statement. The total is still correct, but the aging looks worse than it is. A payment applied to the wrong invoice causes two mismatches that cancel; look for pairs of equal and opposite differences.

## Vendor statement traps: duplicate invoice numbers, currencies and partial payments

A few edge cases break a vendor reconciliation that otherwise works: the same invoice number reused across years, statements in a currency different from your ledger, partial payments that leave a remainder open, and a vendor who also sends you money. Handle them before you trust the difference column.

### Reused invoice numbers

Some suppliers restart numbering every year, so INV-0042 can exist twice. Match on invoice number plus invoice date, or number plus amount, to keep two different documents apart.

### Foreign currency

When the supplier bills in euros and your ledger records the converted dollar value, the open amounts will never match exactly. Reconcile in the supplier's currency if your ledger stores it; otherwise expect small exchange differences and set a tolerance.

### Partial payments

A partly paid invoice shows its remaining open amount on the supplier's statement, but your ledger may show the original amount with a separate payment line. Compare open amounts on both sides, not original amounts.

### Related documents

Supplier statements often sit alongside staff spending and property charges in the same payables review. The [expense report PDF to Excel guide](/blog/expense-report-pdf-to-excel) covers employee claims, and the [rent roll PDF to Excel guide](/blog/rent-roll-pdf-to-excel) covers tenant-by-tenant property figures, both in the same one-row-per-item form used here.

### The payment proof

When a supplier says they never received a payment, the bank statement is the evidence. FastBankConvert is being built to turn bank statement PDFs into Excel, CSV or JSON, so the outgoing payment can be found and matched quickly. It works on bank statements, not supplier statements, and it is not open yet; [Get started](/get-started) puts you on the list.