AAH, Phoenix and Alliance Invoices to Excel for Pharmacies

UK community pharmacies can turn AAH, Phoenix and Alliance Healthcare invoice PDFs into a reusable Excel workbook by extracting one row per product line: invoice number, date, branch or account, wholesaler, PIP code, product description, pack size, quantity, unit cost, VAT treatment, credit or return flags, and controlled-drug indicators where the invoice shows them.

That workbook is the bridge between supplier documents and the monthly close. Xero, QuickBooks or Sage may only need a summarised purchase posting by branch, VAT treatment or cost-of-goods category, but the line-level sheet remains the audit trail for supplier discrepancies, VAT review, PMR comparison, FP34 questions and margin work.

The important point is not that every wholesaler invoice looks the same. It does not. AAH, Phoenix and Alliance Healthcare each have their own layout, account references, line descriptions and document conventions. The useful control is that the extracted output should look the same each month, so the bookkeeper can review one consistent community pharmacy wholesaler invoice Excel file instead of reinterpreting each PDF from scratch.

For a UK pharmacy bookkeeper, the practical job is pharmacy wholesaler invoices to Excel, not generic invoice OCR. The output has to preserve pharmacy-specific identifiers such as PIP code and pack size, expose line-level VAT treatment, keep credits and returns visible, and leave enough context for the finance team to decide whether the invoice should feed bookkeeping, margin review or a supplier query.

That is where invoice extraction into Excel fits naturally. With Invoice Data Extraction, the user uploads the wholesaler invoice PDFs, describes the required columns in a prompt, and downloads the structured result as Excel, CSV or JSON. The prompt can ask for the same output schema across AAH, Phoenix, Alliance and short-line supplier files, which is more useful to the accountant than copying each supplier's document layout into a separate spreadsheet.

Define the workbook before extracting the PDFs

Start with the spreadsheet you want at the end of the process. If the workbook columns are vague, the extraction will produce a tidy-looking file that still leaves the bookkeeper making judgement calls line by line.

For UK pharmacy supplier invoice PDFs, the core workbook usually needs these fields:

• Invoice number
• Invoice date
• Supplier or wholesaler
• Branch, store, account or customer reference
• Product description
• PIP code
• Pack size
• Quantity supplied or credited
• Unit price
• Net line amount
• VAT rate or VAT treatment
• VAT amount where shown
• Gross line amount where shown
• Credit, return or adjustment flag
• Original invoice reference where a credit note provides it
• Delivery, parcel or tote reference where useful
• Controlled-drug, specials or fridge-line indicator where the source document exposes it

The schema is easier to review if the columns are grouped by the job they serve:

• Document control: supplier, invoice number, invoice date, branch or account, and original invoice reference. These fields tie each line back to the purchase ledger, supplier statement and branch month-end file.
• Product identity: product description, PIP code, pack size and quantity. These fields preserve the product context needed for PMR, stock and margin matching.
• Financial values: unit price, net line amount, VAT treatment, VAT amount and gross amount. These fields support COGS posting, VAT review and supplier price checks.
• Exception flags: credit or return flag, CD or specials indicator, and delivery or parcel reference. These fields keep compliance-sensitive and adjustment lines visible instead of hiding them in invoice totals.

Those columns are not just labels for tidy data. They are controls. The invoice number and date anchor the transaction in the purchase ledger. The branch or account reference stops a small multiple from mixing one shop's COGS into another's. The PIP code gives the purchase line a pharmacy-specific product identifier. The pack size keeps "28" and "84" from being treated as equivalent simply because the description looks similar. VAT treatment tells the bookkeeper whether the line can be posted confidently or needs review.

Some fields should be treated as conditional. A controlled-drug marker, specials indicator, parcel reference or delivery reference is useful when the invoice carries it, but it should not be invented when the source document is silent. The workbook can include the columns and leave them blank where not present. That is better than forcing a false value into a compliance-sensitive field.

The same discipline applies to short-line and secondary suppliers. A smaller pharmacy supplier may not use the same layout as AAH, Phoenix or Alliance Healthcare, but if the output still has supplier, invoice number, PIP code, description, pack, quantity, cost and VAT treatment, the month-end process remains comparable.

Normalise AAH, Phoenix and Alliance into the same rows

The monthly source pack usually starts with the supplier documents themselves: AAH invoice PDFs, Phoenix Healthcare Distribution invoices, Alliance Healthcare documents and any secondary supplier files for items sourced outside the main wholesaler arrangement. Some documents may come from portals, some may arrive as PDFs, and some may be downloaded by branch or account.

Treat those files as inputs, not as spreadsheet designs. If the AAH layout places one value in a different visual position from the Phoenix layout, the output column should not change. The bookkeeper still needs supplier, invoice number, branch, PIP code, description, pack size, quantity, unit cost, VAT treatment and credit or return status in the same places every month.

A prompt-based extraction workflow is useful because the prompt can describe the desired finance output rather than the supplier's page design. For example, the instruction can ask for one row per invoice line, to keep product lines separate from credits or returns, and to include controlled-drug, specials or delivery references only where visible on the invoice. Invoice Data Extraction supports that style of natural-language extraction prompt and returns the result as Excel, CSV or JSON, so the same schema can be used across the main wholesalers without building a separate template for each one.

Portal CSVs can still be helpful where they exist and contain the fields needed for the job. They should not be assumed to replace the invoice PDF in every case. The article's workflow is deliberately document-first because pharmacy accountants often need the full line context, not only a supplier summary, and because the exact export granularity can vary by supplier, portal and account setup.

The normalised rows should look consistent even when the source cue differs:

• AAH invoice PDF: if the product line shows supplier description, PIP code, pack, quantity, unit cost and VAT value, the workbook records one AAH row with the account or branch reference, PIP, pack, quantity, unit cost, VAT treatment and any credit or CD flag visible on the document.
• Phoenix invoice PDF: if the line appears as a Phoenix product or credit line with its own invoice and account references, the workbook uses the same columns and marks a credit or return flag where the line reverses a prior purchase.
• Alliance Healthcare document: if the invoice or portal document carries Alliance account context, line descriptions, parcel details or delivery references, the workbook keeps those references only where they help trace the product line.

These are representative output patterns, not reproductions of customer invoices. The point is the control design: the supplier name changes, but the workbook row still tells the accountant what was bought, for which branch, at what cost, under which VAT treatment, and with which identifiers available for review.

This is also why regional pharmacy invoice workflows should not be collapsed into one global pattern. The UK AAH, Phoenix and Alliance process is built around UK pharmacy wholesaler invoices, PIP codes, NHS reimbursement context and UK VAT review. A separate workflow such as pharma distributor invoice extraction for India may share the same idea of structured line extraction, but the identifiers, tax rules and stock-control priorities are different.

Preserve PIP, pack size and PMR matching context

PIP code is one of the fields that makes a pharmacy wholesaler invoice different from a general supplier invoice. It gives the purchase line a trade-product reference that can be used later in stock, PMR or margin work. If the extraction drops the PIP code, the accountant is left matching on description alone, which is weaker and slower.

Pack size matters for the same reason. A product description can look nearly identical while the supplied pack size changes the economics of the line. If the workbook separates description, PIP code, pack size, quantity and unit cost, the reviewer can see whether a margin difference is caused by price, volume, pack presentation or an actual mismatch.

The invoice workbook is still only one side of the reconciliation. Comparing purchase cost against the PMR or Drug Tariff requires the pharmacy's own PMR export, the relevant product or dm+d reference data, and a matching rule that the accountant trusts. Extraction can give you the purchase-cost file in a usable form; it does not, by itself, prove that a dispensed item and a supplier invoice line are the same product.

Credits and returns need the same care. A credit note should not be flattened into a normal positive purchase line. It should carry its own flag, amount direction, VAT treatment and original invoice reference where the document provides one. Substitutions and unavailable-line adjustments should also remain visible, because they can explain why the invoice cost does not line up neatly with what the PMR or buying report expected.

For margin review, the cleanest file is one that preserves rather than interprets. Keep the PIP code, pack size, product description, quantity, unit cost, VAT treatment and credit status intact. Then use the pharmacy's reference data and PMR export to decide what can be matched automatically and what needs human review.

Keep VAT treatment at line level

A pharmacy wholesaler invoice should not be treated as if every line carries the same VAT treatment. The same monthly supplier pack may include prescription-dispensing stock, OTC medicines, sundries, beauty items, delivery charges, credits and returns. The invoice extraction needs to expose the VAT treatment line by line so the bookkeeper can review it before posting.

In practice, the reviewer wants to see whether the supplier document treats a line as NHS prescription-side stock, OTC or sundry goods, delivery or another charge type, and whether the line carries zero-rated or 20% standard-rated treatment on the invoice. The workbook should preserve that source treatment so the pharmacy can post or query it deliberately. It should not silently turn a mixed invoice into one VAT code.

At minimum, the workbook should separate net amount, VAT rate or treatment, VAT amount where shown, and gross amount. If the invoice displays a zero-rated line, a standard-rated line and a credit note, those values should remain distinct in the extracted file. The reviewer can then decide how the amounts map into the pharmacy's own VAT codes and COGS categories.

This matters for Xero, QuickBooks and Sage because the ledger posting may be summarised. A pharmacy might post one supplier invoice with separate COGS lines for prescription stock, OTC or sundries, rather than importing every product line into the accounts. That summary is only defensible if the underlying spreadsheet keeps the VAT evidence visible.

Credits and returns need their VAT treatment preserved separately. If a credit reverses a standard-rated OTC item, it should not be netted invisibly against zero-rated stock. If a return refers back to an original invoice, keep that reference where the document shows it so the adjustment can be traced.

Unclear VAT lines should be marked for review rather than forced into a default. The extraction job is to present what the supplier document says in a structured way. The pharmacy's VAT policy, accountant or adviser decides the final treatment where the document is ambiguous or where the line sits outside the normal pattern.

Post COGS into Xero, QuickBooks or Sage without losing the audit trail

The extracted workbook does not have to become the ledger entry line for line. Many pharmacy bookkeepers will post a summarised purchase invoice into Xero, QuickBooks or Sage, split by branch, VAT treatment and COGS category, while retaining the full workbook for review. That is often cleaner than filling the accounting system with hundreds of product rows that are better analysed in Excel.

A practical posting file might group by supplier, branch, invoice number, VAT code and COGS category. Where the accounting setup supports it, tracking categories or dimensions can carry the branch or store. Product-level references such as PIP code may belong in the retained workbook rather than the purchase ledger, unless the finance team has a deliberate item-code structure.

For Xero, the common pattern is to prepare an importable purchase file or summarised bill with the right supplier, invoice date, due date, net amount, VAT amount and account codes. QuickBooks and Sage can follow the same accounting logic even if their import format differs. The control is that the total posted to the ledger ties back to the extracted line-level workbook.

One practical posting model is:

• Branch, supplier, invoice number and invoice date: create the supplier bill or purchase entry for the relevant branch or tracking category. In QuickBooks or Sage, use the equivalent class, department, project or branch dimension if the file is split by site.
• Zero-rated prescription-side stock lines where supported by the source and policy: post to the pharmacy's prescription or NHS dispensing COGS account with the appropriate VAT code, while keeping the underlying line file because the ledger may only carry a summary.
• Standard-rated OTC, sundries or similar lines: post separately from prescription stock if management accounts need Rx and OTC visibility.
• Credits, returns and supplier adjustments: use a negative line or credit note linked to the original invoice where possible, and retain the original invoice reference with the supplier document in the evidence pack.

This is where UK pharmacy differs from general accounts payable. Broader pharmacy invoice processing controls cover duplicate invoices, approval, payment status and supplier records, but the UK wholesaler workbook also needs PIP, pack size, VAT treatment, credits, branch references and margin-review context.

Invoice Data Extraction can produce Excel, CSV or JSON outputs, so the same extraction run can serve two purposes: a human-readable workbook for the pharmacy accountant and a structured file that can be shaped for import preparation. The product does not remove the need for a chart-of-accounts decision, but it reduces the manual keying needed to get the invoice lines into a form the finance process can use.

Use the workbook for margin, FP34 and supplier discrepancy checks

Purchase-cost visibility matters because pharmacy margin is not only a bookkeeping detail. Community Pharmacy England says the 2025/26 CPCF includes GBP900 million to be delivered as retained buying margin and Category M, and that DHSC uses Category M Drug Tariff reimbursement price adjustments to deliver that target.

That does not mean the invoice workbook calculates reimbursement by itself. It means the purchase-cost side needs to be clean enough to compare against the pharmacy's other records. The extracted invoice rows show what the wholesaler charged for each product line. The PMR export, Drug Tariff reference data, dm+d mapping and FP34 data come from other systems or sources.

For a specialist pharmacy accountant, the useful analysis starts when those sources can be lined up. A PIP-coded purchase line can be compared with the pharmacy's PMR or stock data where the identifiers and pack sizes support a reliable match. A supplier discrepancy can be investigated where the invoice quantity, unit cost or credit note does not match what the pharmacy expected. A margin variance can be explained by purchase price, reimbursement price, pack-size mismatch, missing credit, substitution or timing.

The reimbursement-side companion is the NHSBSA FP34 Schedule of Payments to Excel workflow. The FP34 file helps explain what the pharmacy was paid. The wholesaler invoice workbook helps explain what the pharmacy paid for stock. The two should not be merged casually, but they do belong in the same month-end evidence pack when the finance team is reviewing margin.

The same file also supports supplier follow-up. If a line appears at an unexpected cost, the workbook has invoice number, supplier, product description, PIP code, pack size, quantity and VAT treatment in one row. That gives the pharmacy or accountant a precise query, rather than a vague complaint about the total invoice value.

Build the monthly workflow so exceptions stand out

The repeatable process is simple, but it has to be followed in the same order each month. Collect the AAH, Phoenix, Alliance and secondary supplier documents for the period. Run the extraction into the agreed workbook schema. Review exceptions before posting. Prepare the accounting import or summarised purchase entry. Retain the line-level workbook with the supplier PDFs and any PMR, Drug Tariff or FP34 files used for review.

The exception review is where the workflow earns its keep. Look for missing PIP codes, pack sizes that do not match the expected product, unexpected VAT treatment, negative credits that cannot be linked back to an original invoice, duplicate invoice numbers, missing branch or account references, and controlled-drug or specials flags that need follow-up. Schedule 2 and Schedule 3 lines flagged here feed a separate compliance step: cross-referencing wholesaler CD lines to the pharmacy's CD register so the running balance and inspection evidence pack stay in step with what the supplier delivered. These are the problems that disappear when the invoice is keyed only as one gross supplier total.

For a single branch, the workbook may be enough to support the monthly close and supplier checks. For a small multiple, the same schema becomes more important because branch or account coding lets the finance team compare COGS, VAT and margin patterns across stores without rebuilding the analysis for each location.

The structure is similar to other UK supplier-invoice workflows. A convenience-retail process such as UK wholesaler invoice bookkeeping also depends on turning supplier documents into usable purchase data, but the pharmacy version has its own fields: PIP code, pack size, VAT treatment, PMR matching context, Drug Tariff review and controlled-drug indicators where shown.

Once the schema is fixed, each month becomes a controlled routine rather than a fresh spreadsheet exercise. The finance team still reviews judgement-heavy lines, but the extraction step gives them the same evidence pack every time: product-level purchase cost, VAT treatment, supplier reference, branch context and the exception flags that deserve attention.