Here are the steps to update the Online Payment Transaction report into the format that can be uploaded into Xero:
Preparing the upload file
- Download Bambora Statement from All Transaction Detail V3 (CSV only) under view>reports
- Remove the totals from the bottom line of csv file
- Copy sales lines downloaded and place these at the bottom
- Of the new copied lines, move the ‘Fees’ to the ‘Amount’ column and make them a negative figure
- Under Channel and Name of Card mark these as “Bambora Fee”
- Make all Disbursements a negative figure
- Under the channel put “Online Sales” for all sales so the channel column reads either Online Sales, Disbursement or Bambora Fee.
Now the file is ready for uploading to xero.
Upload the file to Xero
When uploading the file you can map the following files to Xero columns:
- Channel = Description
- Amount = Transaction Amount
- Settlement Date = Transaction Date
- CustRef = Reference
- Name on Card = Payee
Xero will import the above columns.
Now that the transaction has been updated into Xero you can use the Xero bank reconciliation screen to match the invoice in Xero with the Net and fee transactions from the Bambora Transaction history report.