How to upload wire transfer payments from bank file statements
Bank file upload info:
Uploading bank statements is available in the Payments (Back office) page allowing for batch creating payments for allocation.
Bank costs can be accounted for automatically against a received payment.
File upload settings allow for high flexibility for supporting different file formats and layouts from various banks.
File upload settings are saved and referenced automatically on next upload from the same bank.
Changing stored bank file settings can be done when uploading bank statements, alternatively to change any stored bank file settings see Payment Bank File Settings (Administration).
Bank file statements can usually be downloaded from your bank website, accepted file types for upload are CSV, TAB, STA, TXT (if has a delimiter).
Contents:
For more information regarding wire transfers and payment allocations see Wire transfers and Payment allocations along with links for other related how to pages.
How-to video
tba
Instructions
Click ‘UPLOAD BANK STATEMENT’ in the Payments (Back office) screen to open the upload payments page.
Select Administration and file for upload
1. Select from the ‘Administration’ drop down the Administration the payments should apply to.
Note: Payment uploads are uploaded to a single Administration For payments applying to multiple Administrations, split the file to separate files for upload.
2. Click ‘SELECT THE FILE’ to open file browser to choose upload file. Accepted file types for upload are CSV, TAB, STA, TXT (if has a delimiter).
PDF and XLS/XLSX will not import correctly in most cases. This will show in the ‘File preview’ area of the upload screen as non-standard characters.
Note: One file can be uploaded at a time, selecting a different file will replace the previous.
Tip: After a file is selected it can be deleted or downloaded, using the delete and download icons.
Create bank file upload settings
Note: Previously created settings can be managed in the Payment Bank File Settings (Administration) page.
Bank file settings | Choose from an already created bank file setting or click ‘CREATE SETTINGS’ to show all available upload settings. To create new settings, clear the ‘Bank file settings' field. Note: When choosing an existing settings the ‘CREATE SETTINGS’ changes to ‘EDIT SETTINGS’ allowing edits to the setting. | |
Settings name | If creating new upload settings first enter the ‘Settings name’. This should describe the file upload, for example: 'Bank of America' | |
Matching text | Enter matching text (for file name) for the upload setting to automatically select when uploading similar files from the same bank. For example, adding ‘HSBC’ matching text would mean a future file named ‘HSBC_October’ would select the ‘HSBC' upload settings. |
File settings:
File delimiter | Choose the ‘File delimiter’ from the drop down. A file delimiter is used to define the end and the beginning of a new field, most CSV files will use comma delimiters to separate columns. To find the delimiter of a file open the file using Notepad, the delimiter symbol will be shown between each field. |
|
Has headers | Choose if the upload file contains a header row that should be excluded from upload. Choosing ‘Yes' results in the first row excluded from upload. See example files below: |
|
Decimal separator | Choose the ‘Decimal separator’ used to show the fraction of an amount, for example: 100.50 (for decimal point separator), 100,50 (for decimal comma separator). | |
Currency |
Note: Available currencies are defined in the Currencies (Administration) page. Ensure that exchange rates are set if allocating payments to invoices in different currencies from upload file, see Exchange rates (Administration) page. |
|
Payment amount settings:
Choose the amount column type for how the amount is shown in the file. Default = ‘Single column with Negative Or Positive Amounts’. Dependent on which type is chosen, extra columns will show.
Amount column type | Single column with Negative Or Positive Amounts: File has a single column for positive and negative payment amounts.
| |
Single column with text indicator: File has a single column for amounts and another column indicating if amount is positive or negative.
| ||
Two columns: File has two separate columns for positive and negative amounts.
|
Date settings:
Date column | Choose the column index number that contains the date for each payment. Note: To find the column index number use the ‘File preview’ area that shows the first row (or first two if ‘Has headers = Yes’). Look for where the date column is shown and enter the index number e.g. ‘Index 0’, 'Index 1' |
|
Date format | Choose the date format that is displayed in the upload file exactly as show, this includes and separators or spaces. dd/MM/yy = 01/01/20 dd-MM-yy = 01-01-20 dd MM yy = 01 01 20 d/M/yy = 1/1/20 M/d/yy = 3/22/20 MM/dd/yy = 03/11/22 MM-dd-yy = 03-22-20 M-d-yy = 3-22-20 MMM d, yyyy = Mar 22, 2020 MMMM d, yyyy = March 22, 2020 dd MMMM yyyy = 22 March 2020 dd-MMM-yyyy HH:mm = 22-Mar-2020 05:06 yy/MM/dd = 20/03/22 yyyy/MM/dd = 2020/03/22 |
|
The following characters can be used for the ‘Date format’ column: Note: Characters are case sensitive.
d - Represents the day of the month as a number from 1 through 31.
dd - Represents the day of the month as a number from 01 through 31.
ddd - Represents the abbreviated name of the day (Mon, Tues, Wed, etc.).
dddd - Represents the full name of the day (Monday, Tuesday, etc.).
h - 12-hour clock hour (e.g. 4).
hh - 12-hour clock, with a leading 0 (e.g. 06)
H - 24-hour clock hour (e.g. 15)
HH - 24-hour clock hour, with a leading 0 (e.g. 22)
m - Minutes
mm - Minutes with a leading zero
M - Month number (eg. 3)
MM - Month number with leading zero (eg. 04)
MMM - Abbreviated Month Name (e.g. Dec)
MMMM - Full month name (e.g. December)
y - Year, no leading zero (e.g. 2020 would be 20)
yy - Year, leading zero (e.g. 2020 would be 020)
yyy - Year, (e.g. 2020)
yyyy - Year, (e.g. 2020)
Payment description & Bank cost settings:
Payment description column | Choose the column index number that contains the description for each payment. This may be a reference column of a column that describes the payment row. Note: To find the column index number use the ‘File preview’ area that shows the first row (or first two if ‘Has headers = Yes’). Look for where the date column is shown and enter the index number e.g. ‘Index 0’, 'Index 1' | |
Bank cost text indicator | Enter the text indicator for payments that are banking fees related to a received payment. For example, ‘BANK FEE’ or ‘FEES’. Note: Any payments marked as bank costs will display in the ‘Cost’ column of the corresponding payment. The actual received payment amount will be increased by the cost amount, and can be used for reporting purposes. |
|
File preview and Save settings
The ‘File preview’ area previews the first row (or two rows if ‘Has headers’ = Yes) for the chosen file. The file preview should be used as a quick reference for which settings should be used to ensure payments are uploaded correctly.
After all settings are verified correct, click ‘SAVE SETTINGS’ to allow upload. Note: This will not result in payments uploaded, until the ‘OK’ button is clicked in the ‘Payments in the selected file’ section below.
Payments in the selected file
As settings are defined, the ‘Payments in the selected file’ updates automatically to give a summary and overview of the payments that will be added on upload.
Tip: If settings are incorrect, the columns will not display correctly. This is useful for defining the correct date column and format.
Click ‘OK’ to confirm displayed payments and upload to create payments in FBO One.
Note: All payment rows shown in this table will be created as unallocated payments.
By default, credit payments (negative payments) are hidden, click ‘Show all payments’ filter to show all payments.
Ensure all columns display correctly as expected before uploading payments.
Upload summary
After successful upload, added payments will show for allocation.
Info:
‘Payment uploads' filter is automatically set showing the created payments from uploaded file. Format = 'Date of upload | Administration | Settings name (Count for this date)’
Viewing negative payments can be done by unchecking the ‘Only payments received’ filter.
Duplicate payments will be skipped and are not created by FBO One. This allows uploading from the same file for adding new payments without creating duplicates of previous payments.