How to import Cashmanager transactions into XPA
You can already export your client’s annual accounts from their Cashmanager RURAL database by using the Export - Accountants function.
However, with a bit of data manipulation, you can also export transactions for a specific period i.e. monthly or two monthly.
Before you start, check the following:
Transactions are dated correctly
Transactions are transferred out of Cashmanager RURAL based on the transaction date.
Bank Accounts are balanced
Unpresented income or expenses should not be exported. Make sure all transactions are reconciled to the bank account(s) for the date range you have selected.
Export transactions from Cashmanager
View the Analysis by Code report.
Click on Reports - Analysis by Code. Set the following filters in the report:
- Select the transaction date range.
- Tick Accountant’s Codes.
- Click Update Report to display the settings.
- Click on Export - Save as CSV.
Save the file to a location on your PC and then open it.
NOTE: Any Cashmanager codes that are attached to transactions in the period selected, and that haven’t been mapped to your Chart of Accounts will appear at the top of the report. You should align these codes before continuing.
Customise the Cashmanager .csv file
Change the format for the Date column to dd/mm/yyyy.
Insert a column to the right of Inc.Amount and name it XPA Amount (column G).
Cashmanager displays expense amounts as negative and income as positive.
Because XPA requires the opposite in the export file, enter the following formula =(“Inc.Amount cell”)*-1 into column G, adjacent to the first transaction in the list. Copy the formula to the bottom of the list.
Create a column for XPA Quantity and repeat step 2 for quantities.
Insert opening and closing bank balances for all bank accounts recorded in Cashmanager at the bottom of the transaction list, as shown below.
Each account should be recorded separately and the Reconciliation report can be used as a guide for the opening and closing bank balance for the period.
NOTE: The opening balance is negative when in funds and positive when in overdraft while the closing balance is positive when in funds and negative when in overdraft.
- AutoSum the XPA Amount column and ensure it totals to zero.
If it doesn’t, check that there aren’t any transactions dated prior to the period you’re preparing, in the Reconciliation report. This would be a cause for the XPA Amount total to be unbalanced.
Once the XPA Amount column totals to zero, the data is ready to be transferred to the XPA .csv template.
Create the .csv file for XPA
- Create an XPA template in Excel for your practice to use. It needs to look as below:
Save a copy of the template file with a name of your choosing. The copy will contain information for the business you’re processing and the period covered.
Copy the following columns from the Analysis by Code file to the corresponding columns in the XPA file.
Analysis by Code file XPA file ExportCode Account Code TransactionDate Transaction Date OtherParty Description Reference Reference XPA Amount Amount XPA Quantity Quantity
So that the XPA file can be imported successfully, ensure the file includes the following:
- Column A displays B beside each transaction date
- Column A displays X in the final row
- Column F displays 0 (zero) in the final row adjacent to X in column A
Import into XPA
Open the XPA file.
Click on Reporting and Account Code Translate.
Click on the Import tab.
Load the XPA .csv file you saved earlier.
NOTE: You need to change the file type to ‘All Files’ to select the .csv file.
Select the following options:
Apply Account Tax Rates
Apply Cash In/Cash Out
Post All Entries
Set the dates for the import.
Click on Import.
- As you have used the Import Type GENERIC, the following message will appear. Click OK.
If codes exist in the import file but not XPA or if there are un-coded entries, a related message will appear.
Cancel the import and check the coding in the CSV file. Then try importing it again.
Click Post in the following box, to post the entries you have just imported.
If you want to retain the .csv import file as a reference in the future, click No.
If you click Yes, the file will be deleted from its saved location.
We would like to thank the team at Graham Brown & Co Ltd for their contribution to this topic.