Part I – Bank Statement Data Import Options
In the financial services world, reconciliations are a foundational aspect of accounting. Historically, they’re also one of the most time-consuming. Luckily for NetSuite users, there are bank reconciliations features that can make the process easier, faster and more accurate.
NetSuite Bank Statement Import Options
When it comes to getting bank statement files and data into NetSuite, there are multiple options to consider. Most importantly, placing available bank statement data into NetSuite enables organizations to utilize NetSuite-based reconciliations features, as well as other functionalities available within the platform.
NetSuite offers multiple methods for organizations to import their bank statement data:
- Manual Imports
- NetSuite Default Parsers
- Bank Statement Parser SuiteApp
- Auto Bank Statement Import (ABSI) SuiteApp
- Bank Feeds
Manual Imports (NetSuite Default Parsers)
To import Bank Statement Data within NetSuite, navigate to Transactions > Bank > Banking Import History > Upload File.
Out of the box, NetSuite offers ready-to-go parsers for CSV, BAI2, OFX, QFX, and CAMT.053 formats. The two most widely used formats are CSV and BAI2.
CSV Imports Using Default Parser & Template
For CSV manual imports using NetSuite’s default parsers, there is a downloadable template on the Bank Statement Upload page (this template must be used as-is when importing CSV statements using the ‘Import with Default Parser’ option).
1-Banking Upload Page
Once in the template, you’ll populate the following columns with information from your bank statement:
- Date (MM/DD/YYYY)*
- Payer/Payee Name
- Transaction ID
- Transaction Type*
- NS Internal Customer ID
- NS Customer Name
- Invoice Number(s)
*Denotes mandatory data
Using this option, bank statement information needs to be pasted into this downloaded CSV template and formatted as specified to be successfully loaded into NetSuite.
Additionally, when using the default parser for CSV, you’ll need to create individual CSV files and run imports for each unique bank account, as the CSV template doesn’t have ability to identify or separate individual account numbers. When uploading, you will be asked to select which GL Bank Account to assign the statement data to.
Another challenging aspect of this process is assigning Transaction Type during the import. Depending on your bank, you may have references or codes to specify the transaction type.
For the CSV default parser, you’ll need to assign one of the following values to each transaction row to ensure that the import is processed without error:
BAI2 Import Using Default Parser
The BAI2 format is a standardized set of codes that were developed by the Bank Administration Institution as a cash management reporting standard. BAI2 files are text files, so they can be processed by NetSuite’s default parsers. As a result, it’s more efficient to download your statement information in BAI2 if possible, as you’ll be able to import multiple accounts within the same statement file.
When loading BAI2 statement data using NetSuite’s default parser, you will need to include the unique Account and Routing numbers for each bank account included in the import. To do this, you will access the chart of accounts (COA) for your accounting system within NetSuite
Adding Account and Routing Numbers to Your NetSuite Chart of Accounts
Once in your NetSuite chart of accounts, you will see a subtab labeled Bank Import Mapping, with 02 and 03 record fields under the BAI2 section. Populate these fields with the corresponding account and routing numbers for each bank account in your import:
02 Record – Originator Identification Field: Bank Routing Number
03 Record – Customer Account Number: Bank Account Number
2- Bank Import Mapping Subtab on Account Record
While importing your statement data, there may be instances when your financial institution uses a custom transaction code that NetSuite’s default parser doesn’t recognize; this can cause processing errors that will be specifically labeled in the error log on the Banking Import History page.
If you find yourself repeatedly running into these errors, you can create a specific Financial Institution record and Format Profile for the bank causing the issue. You can do this using the Bank Statement Parsers SuiteApp.
Bank Statement Parser SuiteApp
The Bank Statement Parsers SuiteApp provides standard plug-ins that can help minimize formatting adjustments and process bank statement imports more efficiently. The tool still requires manual imports, but if the Financial Institution and Format Profiles are set up properly, it can successfully identify multiple accounts within a single file upload.
This will save your team a substantial amount of time by reducing individual CSV imports and the need for formatting within the default parser template. It will also reduce or eliminate errors since you can add and map any custom transaction codes that are included in the statement file.
To utilize the Bank Statement Parsers SuiteApp, you need to first install the bundle. The bundle is free to all NetSuite instances and can be downloaded through your Search and Install Bundles page (SuiteBundle ID: 293699)
3- Bank Statement Parser SuiteApp – SuiteBundle 293699
Once the bundle is installed, you’ll need to create a Financial Institution and Format Profile. These will be selected when importing the statement file through the Banking Upload Statement page.
You can create a new Financial Institution by navigating to Setup > Accounting > Financial Institution > List: New Financial Institution.
Give the Financial Institution a name that matches or can easily identify the bank for which you are creating the record. Once you’ve selected a name, save the changes. After you’ve successfully created the Financial Institution, you’ll need to create a Format Profile: Configuration record–this is where you’ll add specifics for formatting future statement files. To do this, navigate to the Format Profile: Configuration subtab within your newly created Financial Institution and click “Add”.
4-Creating a Format Profile Configuration Record for a Financial Institution
Once you’re in the correct Format Profile: Configuration record, you’ll need to select and add information for several fields and subtabs.
The initial page will ask you to provide a name and select a transaction parser, e.g. CSV or BAI2.
For profile naming, it is best to provide the name of the institution, with additional clarification on format and connectivity type.
After assigning a name, and selecting the Connectivity method and Transaction Parser option, you’ll need to go through and populate following subtabs:
- Connectivity Configuration*
- Account Linking
- Code Type Mapping
- Parser Configuration
*This won’t apply since Connectivity Method is manual import
Account linking is where all the individual account numbers for the financial institution (or any bank accounts included) in the bank statement import will need to be added and mapped to their corresponding GL account.
A potential efficiency gain for organizations using CSV imports is using Format Profiles. Here, you can incorporate multiple accounts in the account linking subtab to enable import processing for these accounts within the same CSV import statement file. s mentioned previously, you can’t do using the default parser for CSV imports.
For BAI2 statement files, one import thing to note, is that, if using a Financial Institution > Format Profile configuration records for loading statements, the account numbers and linking will need to be added here. NetSuite won’t be looking the Account > Bank Import Mapping 02 and 03 record fields for the GL account mapping information, previously mentioned in the Default Parser section.
Code Type Mapping
Code type mapping is where you would need to insert the transaction code values that are included in the bank statement for NetSuite to appropriately assign and assume types for.
Along with the code value, indicate additional information so NetSuite will know how to successfully process the transaction codes, such as transaction type (from the dropdown list), whether it’s a Debit or Credit code, and whether you want to completely exclude a specific transaction type or code from the related bank statement uploads.
An important consideration for BAI2 statements is that most codes are standard and won’t need to be added individually to the parser configuration. You would only need to add code values for code types that aren’t recognized in NetSuite’s default parser. If you aren’t aware of which codes aren’t included, you can request a list of BAI2 codes and their definitions from your bank and compare to the NetSuite list of supported BAI2 codes in SuiteAnswer 84310.
Parser Configuration is where you’ll add any specifics to the formatting of the statement to enable NetSuite to interpret the file.
For CSV Parser types, you would insert the related column headers of your statement file to the NetSuite field assignments so NetSuite will know how to map the various values when importing. There is also a Formatting Preferences subtab, to identify various formats within statement file such as Date format, currency format, etc.
5-Parser Configuration for CSV Parser Type
Format Profiles can take a bit of time to setup and establish, but once they’re created correctly, they can save your team an immense amount of time when importing statement files.
Once configured, to import a bank statement file using a Financial Institution and Format Profile, Navigate to Transactions > Bank > Banking Import History > Upload File click Select a Financial Institution under import options and chooses the appropriate record for Financial Institution and Format Profile, on the Upload page (see below example).
6-Importing statements using a Financial Institution and Format Profile
Auto Bank Statement Import (ABSI)
Auto Bank Statement Import (ABSI) is a great SuiteApp to automate bank statement importing, utilizing a SFTP integration between NetSuite and your Bank. Auto Bank Statement Import is available and free for download on the SuiteApp Marketplace.
Once installed, you’ll be able to select an SFTP connectivity method when creating a Format Profile under a Financial Institution record, which will enable population of the Connectivity Configuration with fields to populate with credentials to establish a connection with your bank’s hosted SFTP server.
7-Select SFTP Connectivity under ‘Connectivity Method’ dropdown
When establishing a connection with the SFTP server, you’ll need to fill out below field. The dependency here is that your bank has provided a hosted SFTP server and enabled service to provide bank statement information on a schedule. This is a common service but can come with implementation fees or on-going service fees. Setup and logistics for this need to be coordinated with your bank.
Connectivity Configuration Fields for SFTP Connectivity Method
- *URL: Enter the URL for the connecting financial institution.
- Digital Certificate or Private Key ID: Select a Digital Certificate or Private Key ID. A digital certificate or private key may be used in place of a password to establish a connection to the financial institution’s server.
- *Password: Enter your password for online banking. The URL field must have a value first before you can enter your online banking password.
- *Host Key: Enter the host key of the financial institution’s SFTP server. The host key is required even if Digital Certificate or Private key ID is supplied instead of a password.
- *Host Key Type: Enter the host key type for this configuration. The host key type must be any of the following: RSA, DSA, or ECDSA.
- *Port: Enter the port number that will connect to the remote account. Port number value must range from 0 to 65535.*To be provided by Financial Institution
8-Connectivity Configuration Subtab to connect to SFTP Server from NetSuite
Once the correct credentials are entered, choose Submit, and validate your connection. If the connection is successful, NetSuite will pull any available statement files from the folder and attempt to import. This record will also automatically schedule a daily sweep of the Bank’s SFTP server to import any available statement information for an automated import feed of bank data.
Similar to configuring a Format Profile for manual imports, additional tabs of the Format Profile Configuration will need to be completed, including Accounting Linking, Transaction Codes and Parser Configuration.
Auto Bank Statement Import (ABSI) and it’s a great option for organizations looking to automate bank statement importing, while relying on secure and scalable underlying technology with SSH File Transfer Protocol (sFTP).
Another method for acquiring bank data into NetSuite is Bank Feeds. Bank Feeds isn’t an import option but relies on web scraping technology to pull bank data from a provided login, which it then posts as bank data within NetSuite. Bank Feeds is also available as a free application on the SuiteApp Marketplace.
Once installed, Bank Feeds will be available for setup as a Financial Institution within your Financial Institution List.
9-Bank Feeds Financial Institution Record
Within the Format Profiles, you’ll see following options:
- Connect to Accounts in Other Countries
- Connect to Europe Accounts
- Connect to Non-PSD2 Europe Accounts
- Link Bank Accounts
There is documentation within NetSuite SuiteAnswers that provides a list of available and supported financial institutions. If supported, U.S. financial institutions can be found under the Link Bank Accounts option.
Within the Format Profile Link Bank Accounts and under the Connectivity Configuration option, click Connect New Institution to prompt new window to find and login to various bank portals.
10-Bank Feeds ‘Connect New Institution’ Window
Bank Feeds is an easy setup method of connecting to financial institutions for automated bank data. It also doesn’t require any SFTP or server setup for integration which can avoid bank fees and logistics. Although it’s an option worth exploring, there are some technical limitations with Bank Feeds, including the reliability and consistency of the data, as well as connectivity and scalability issues (it maxes out around 15-20 accounts).
NetSuite offers users multiple options for importing your bank statement data and integrating that data with its other banking features, such as reconciliation.
For any questions or if you’d like any further information or support to improve your NetSuite banking strategy or NetSuite ERP capabilities, fill out our form below.