Purpose

The purpose of this article is to outline the 365 Payments Reconciliation Report. The 365 Payments Reconciliation Report is a comprehensive export that details all transactions processed via 365 Payments in AU, UK and EU. It allows customers to reconcile their sales, fees, taxes, and net deposits, ensuring transparency and accuracy in payment flows.

• The report can be scheduled.
• It can be exported as an Excel file.
• This report is available under the Financial heading Tab.
  
  
  • Please Note: If this report is not showing under your Reports List, it must first be enabled by 365 Support. Please contact support to have this report enabled.

Report Format

• Org and Location: Identifies the organization and specific site (e.g., SampleCompany, Retail Site A).
• Card Type: Specifies the payment method (e.g., Visa, Mastercard).
• ADM Txn Date and Processor Txn Date: Timestamps for when the transaction occurred and was processed.
• Currency: Currency in which the payment was processed.
• Type: "captured" represent the individual transactions, while "booked" lines aggregate the net payouts.
• Amounts: Breaks down the original sale amount, amount after fees and taxes, fees, and taxes.
  • Please Note: For "booked" entries, the "Amt after Fees and Taxes" represents the net amount deposited to the customer's bank account. “Booked” amounts are tallied for all locations under the Org.
• Trans Id and balXferId: Trans Id is 365's unique transaction ID for an individual charge; balXferId is the identifier for balance transfers (deposits).
• Trxn Status: Status such as "Approved".
  
  

Example Report Excerpt

Payout Schedule and Settlement Details

• Payout Period: Weekly.
• Deposit Timing: Deposits are sent on Wednesdays and cover all transactions processed from the previous Tuesday to Monday.
• Settlement Delay: There is a standard 2-day delay on settlement to account for processing and risk holds.
  
  • Deposits appear as "booked" entries in the report, with negative amounts representing the net transfer to the customer's account.
    
    

Process

1. Log into ADM.
   
   
2. Navigate to Reports > Financial.
   
   
3. Search for 365Payments Reconciliation Report in the reports list search bar.
   
   
4. Filter by date range (e.g., 01/06/25 to 04/09/25) and export as .xlsx for reconciliation.
   • This can be done by the date of the actual sale or by the date the transaction was approved by the card brands (typically 2 business days after the sale).
     • Note: Adding 2 days before and after the desired timeframe may assist in this search result.
       
       
5. Select Data Type - Processor Txn Date.
   
   
6. Select all active locations.
   • Note: Booked entries are only shown as a cumulative amount by Org.
7. Click ‘Run report’. Allow multiple minutes for the report to run, depending on timerange and number of Locations.
   
   
8. Click ‘Export to Excel’.
   
   
9. Open the report in Excel.
   1. To the right of the column for ‘Processor Txn Date’ (column F), add 3 new blank columns.
      • The Excel shortcut to add columns is Ctrl Shift ++ Or Alt I C
        
   2. Select the column for ‘Processor Txn Date’ (Column F) and select the Data menu/tab from the top of the page.
      
      
   3. Select ‘Text to Columns’ from the Data menu.
      
   4. Leave ‘Delimited’ as selected and click Next (if using the web version of Excel, it will ask you to Select file type - select Delimited then click Next)
      
   5. Select Delimiters of ‘Space’ and ‘Other’ - use a value of '/' as the Other/Custom delimiter.
      
   6. Select ‘Finish’ (if using a Web app, click apply and paste)
      
      
   7. A pop up may appear asking if you wish to replace data - select ‘OK’
      
   8. After confirming you should see columns with day, month, and year separated out for sorting purposes.
      • Rename column F(Day), column G (Month), Column H (Year), and Column I (Time).
        
        
   9. Delete the first row with the date range description.
      
   10. Select ‘Sort’ from the Data menu.
       
   11. Sort by ‘Year’, ‘Month’, ‘Day’ and ‘Time’ by adding each level.
       
   12. All transactions that are prior to the latest disbursement date (‘Booked’ row) until the previous disbursement date will equal the disbursements.
       
       
   13. To split by Location create a new column (T) and name it '365 payments'.
       
       
   14. Copy the disbursement amount (after fees) and copy against all transactions that make up that disbursement.
       
       
   15. Remove the individual disbursement lines (Booked). Do this by filtering on '365 payments', select ‘Booked’. This should bring up the disbursement row to delete.
       
       
   16. Any transactions left that haven't yet been paid out should be deleted. These will be included in the next disbursement.
       
       
   17. Select the Insert Menu/Tab and select Insert Pivot Table. Select the data range to include all rows and columns in the data.
       
       
   18. In the pivot table fields:
       • Select 'Location' to row
       • Select '365 payments' to column
       • Select 'Amt after fees & taxes' to values
         • NOTE: when selecting the fields from the list, they may populate in different areas, drag and drop them to the correct fields as noted.
           
   19. Finalize the PivotTable to show weekly disbursements split by location.