Xero:Import Xero Receipts/Payments and Allocate/Match to Invoices in Excel

Import Xero Sales Receipts/Payments

A more recent version, not shown on any of the screenshots/video below, has an additional tag in the Header section of the template to set the Process Type to "R" for Receipts or "P" for Payments.

PostTrans imports Sales Receipts, or Payments, in two different modes, each discussed below with demonstration videos:

1) Manual Allocation Mode

Match by:MANUAL, Manual Allocation

See example "Import and Match Sales Receipts/Payments"

Pressing Extract button will populate the Excel sheet with all Outstanding Invoices in Xero. The template columns can be rearranged, additional functionality added or items removed to for fill your needs.

TLRcMatch column is populated with the Invoice number, and when Import is pressed the sheet is scanned for payment values. If this cell contains a value then, a Sales Receipt is imported for the value and entered reference. If just a reference is entered then the full value of the outstanding invoice is allocated, plus multiple invoices can be allocated to single payments.

Overpayment

If allowed by the "Tolerance" setting (cell D7), set to "Allow Overpayments" then any values over the invoice value will be imported as an overpayment in Xero against the customer account, for later allocation.

Another sheet "OverPayment Alloc" will read all overpayments using the Extract button. Outstanding Invoices are then matched to overpayments. Import is then pressed to import these allocations.

Sheet "Import Overpayments" separately imports overpayments against customer accounts if needed.

Please see the demo video below for more details, and the overpayments further down the page.

Demonstration Video - Manual Entry

Download Sales Receipt Import Template

To use the template, with Xero, install PostTrans. Download

Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price

Watch the On-line Training course on how to use and customise the templates.

2) Automatic Allocation Mode

Match by not set to:MANUAL, Manual Entry

See example "Import and Match Sales Receipts/Payments from CSV file"

In this mode, Xero Sales Receipts/Payment are imported into Xero, and are matched to Outstanding Sales Invoice by a matching value in TLRcMatch Column.   The value in the TLRcMatch can be matched by Invoice Number, Reference, or Oldest matching value on account ledger.

This is therefore ideal for users who want to import Invoice from an external system, and at a later date import the Sales Receipts, matched on a matching value on the original invoice.

In the above example we can see we can allocate one payment to multiple invoices simply by using the same Payment Ref. See video below for demo.

Debtors List

To aid diagnosis, if a reference does not match PostTrans will optionally open a new sheet showing the outstanding transactions found in Xero and all references on those transactions. This can also be seen by pressing the Extract button on the Sales Receipt template.

Overpayment

If allowed by the "Tolerance" setting (cell D7), set to "Allow Overpayments" then any values over the invoice value will be imported as an overpayment in Xero against the customer account, for later allocation.

Another sheet "OverPayment Alloc" will read all overpayments using the Extract button. Outstanding Invoices are then matched to overpayments. Import is then pressed to import these allocations.

Demonstration Video - from File

The video shows how to use PostTrans Pro for Xero, to import Sales Receipts from within Excel, which relate to existing Sales Invoices in Xero. These are allocated to the invoice by a matching unique reference, which is in the Invoice data. In our example the Invoice Number.

This would be used, in a scenario where the Invoice data has originated from an external source, website or bespoke program. And at a later date the receipt information is generated by same program, which needs matching off against the invoices in the accounts.

Demonstration Video - Overpayments and Auto-allocation

The video shows PostTrans for Xero importing Xero Receipts/Payments and Overpayments with auto allocation into Xero in Excel save a lot of time and money.

Download Sales Receipt Import Template

To use the template, with Xero, install PostTrans. Download

Trial with Sage demo company. Purchase a month subscription for Live Sage Company. Price

Watch the On-line Training course on how to use and customise the templates.

Requirements

• Requires PostTrans Pro.
• The invoice and payment must use the same currency.
• Payments cannot be over-allocated.
• Optionally, only match when the values agree.

Available Tags

Req = Field is required    EOF = End of file, Stop when no more values    

Head  A header tag can be used in header section, before row 30, of the transaction template.  Or in the rows data of data, below row 30, of the template like a traditional CSV importer.

With data type Yes/No fields, if cell starts with 'y' or 'Y' is taken as TRUE

Where ^ denotes a In Cell Code Search, delete ^ at end of tag, to turn off code search

All Text fields are trimmed (spaces before) by default. Add ' to the end of the tag name to stop this.

Related Example Sheets

You can easily access the example sheets by pressing Help button, on the PostTrans Toolbar to access these related examples, or downloading them from the links below.

Use these examples with PostTrans for Xero. Download PostTrans and try it with a Xero demo company. See Xero pricing or follow the transaction training.

EXCEL · XLSX

Sales receipts & payments

Import receipts and payments, then match them to invoices.

Download workbook ↓ xeroTransReceipts.xlsx

Inside this workbook

• Extract all outstanding invoices, and allocate any overpayments to them
• Import and Match Sales Receipts/Payments (optionally create overpayments)
• Import Overpayments to Customer Account
• Import and Match Sales Receipts/Payments from CSV file (optionally create overpayments)