How to upload wire transfer payments from bank file statements

Upload bank file statements to FBO One to bulk add received wire transfer payments for allocation to orders and invoices.

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.

  • 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

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.

Setting

Description

Example

Setting

Description

Example

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.

 

Create new settings:

Edit existing settings:

File settings:

Setting

Description

Example

Setting

Description

Example

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

  • Choose the amount currency for the upload file from the drop down.

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.

Setting

Description

Example

Setting

Description

Example

Amount column type

Single column with Negative Or Positive Amounts: File has a single column for positive and negative payment amounts.

  • Amount column: Choose the column index number that contains the payment amount for each payment.

Single column with text indicator: File has a single column for amounts and another column indicating if amount is positive or negative.

  • Text indicator for Paid in: Enter the text indicator for payments that are paid in (positive amounts received). For example ‘Credit’ or ‘Received’.

    • Text indicator column: Choose the column index number that contains the text indicator for paid in. If text indicator is not found for payments, they are considered as 'paid out' (negative payments).

    • Amount column: Choose the column index number that contains the payment amount for each payment.

Two columns: File has two separate columns for positive and negative amounts.

  • Paid out column: Choose the column index number that contains payments paid out (negative payments).

    • Paid in column: Choose the column index number that contains payments paid in (positive payments).

Date settings:

Setting

Description

Example

Setting

Description

Example

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:

Setting

Description

Example

Setting

Description

Example

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.