Here are the steps that you need to start using the Reconcile Account Statement/Match Bank Data
- Set up your bank account - List > Accounting >Accounts (check the USE MATCH BANK DATA AND RECONCILE ACCOUNT STATEMENT PAGES box)
- Manually creating of import file - Transactions > Bank > Banking Import History > Upload file (click on the template, this will be your format when you create your bank import file)
- Uploading your bank file - Transactions > Bank > Banking Import History > Upload file
- Matching your bank data - Transactions > Bank > Match Bank Data (match the bank entry with its corresponding transaction in netsuite/book entry)
- Reconciliation - Transactions > Bank > Reconcile Account Statement (reconcile the entries you have matched in # 4.)
- Check the reconciliation report - Reports > Banking/Budgeting > Reconciliation
You can also use this article from suite answer as you guide in preparing your CSV/ bank file.
Preparing CSV Files for Import
Statement files in CSV format must adhere to the provided CSV template and constraints. The file must be UTF-8 encoded.
Download the CSV template from Transactions > Bank > Banking Import History > Upload File, or create the file with the required columns and specifications.
If you are using Excel, ensure the file is UTF-8 encoded. Go to File > Save As, and then select CSV UTF-8 (Comma delimited) (*.csv) from the file format list.
The CSV file can only contain transactions that belong to a single account.
To ensure successful import, the column headings must be in the first row in the specified order as follows: Date (MM/DD/YYYY), Payor/Payee Name, Transaction ID, Transaction Type, Amount, Memo, NS Internal Customer ID, NS Customer Name, Invoice Number(s).
The following column data is required:
The Memo field is optional. The memo must not exceed 4000 characters and must not include any commas, semicolons or underscores.
*Date (MM/DD/YYYY) – The transaction date. Content must be in the format of MM/DD/YYYY. If you are using Excel, ensure that the date does not get reformatted.
Payor/Payee Name– Payor or Payee name. There is a limit of 70 characters.
*Transaction ID – Unique Bank Transaction or Reference ID. This can also be populated with the check number. There is a limit of 90 characters.
*Transaction Type – Values in this column must be one of the following transaction types, all uppercase: CREDIT, DEBIT, CHECK, PAYMENT, ACH, INTEREST, DEPOSIT, TRANSFER, FEE, OTHER.
*Amount – The amount column must be a single column. If your file contains CREDIT and DEBIT columns, combine them into a single amount column. Positive amounts are CREDITS. Negative amounts are DEBITS, and must begin with a minus sign or be in parentheses.
The amount column must not contain currency symbols or commas instead of decimal places. The amount currency should be in the same currency as the selected account.
NS Internal Customer ID - (Optional) The NetSuite internal customer ID. Content must be numeric. There is a limit of 12 characters.
NS Customer Name - (Optional) If the specified customer ID does not match any data, NetSuite uses the value to look up the customer ID. Content is free-form text. There is a limit of 512 characters.
Invoice Number(s) - (Optional) A comma-separated list of invoice numbers in a single field, with no spaces between numbers (for example, 132,133,134). If you use a script or comma-separated file instead of the CSV template in a spreadsheet, put quotation marks around multiple invoice numbers (for example, “132,133,134”). For each invoice number in the list, there is a limit of 128 characters. If the imported file specifies multiple invoices for a transaction, they must all belong to same customer and AR account.
* Mandatory columns that needs to be filled up, the rest with no * can be left empty but do not remove these on the template.
If you exceed the character limit for any field, the import will fail. If you use multibyte characters, the character limit may become less.
If present, remove the following:
Empty columns (containing no data) between other filled columns.
Empty rows (containing no data) between other filled rows.
Columns containing opening and closing balances.
Any columns not specified by the template.
Bank account number.
Above is the sample of the file upload file , the columns with data are the ones that are important in the file,
Date - you must follow the format set (mm/dd/yyyy) sometimes when the system will error you out because of the date, you can just put an apostrophe at the start of the date , like this one '04/06/2021 then enter, do that to the rest of the date lines
Transaction id - you can just enter any number, in my case I just number it from 1- 6 and so on
Transaction type- if it is addition to your cash = credit , reduction to cash = debit and should have a negative sign before the amount ex. -500
Memo - this can just be copied on the file from your bank
The rest of the fields although empty, should not be removed/deleted from the template.