NetSuite Bank Integration and Bank Statement Automation

When reconciling bank accounts within NetSuite, bank statements need to be imported to match transactions against the general ledger. Until now, only a few file formats were supported, such as BAI2, CSV or OFX formats. In the newest release of NetSuite 2019.2, users can now download the Bank Statement Parsers SuiteApp which, using SuiteScripts will parse and import bank statements using any type of file. Furthermore, The Bank Connectivity SuiteApp will initiate this process automatically via SFTP.

We’re pretty excited about this and we know from talking to our customers that they are too. With the help of one of our consultants we talk you through the functionality.

1. Introduction

With the release of 2019.2, major enhancements and new features have been applied to NetSuite’s banking module specifically for bank statement processing.

The 3 stand-out changes are:

• New Advanced Banking Data Import
• Bank Statement Parser SuiteApp
• Bank Connectivity SuiteApp

2. New Advanced Banking Data Import

The most visually obvious change in NetSuite is on the Statement File Import page.

In 2019.2, NetSuite now offers two ways of importing bank statements. One is through the manual import method and the other way is by using a connectivity plug-in which directly integrates to the bank and retrieves statement files automatically.

2.1 Manual Import

In the manual import method, the overall process of uploading a bank statement is still similar to how it is done in the previous version. You get a copy of your account statement from your bank, you save it to your computer, then you upload it into NetSuite. With the new release, financial institution and format profile records are now introduced to support the new manual import method and it is in the format profile record where the statement parser is defined. The parsers available support the following bank statement formats:

• BAI2
• OFX/QFX
• MT940

You are still able to do the “old” manual import method using this new Statement File Import page. Notice in the screenshot above that there is an option for using the former method by ticking the 2nd option (Import with a default parser (CSV, OFX, QFX, BAI2, or CAMT.053)). However, this will be deprecated in the future so it is best to start migrating to the new parser plug-ins.

2.2 Connectivity Plug-In

A more automated option of importing bank statement is through the use of the connectivity plug-in.

On the 2nd tab of the Statement File Import page, you’ll find the options for setting up a connection with your bank to automatically pull the statement file from an sFTP server. On top of the new financial institution and format profile records, a connectivity plug-in will need to be configured.

3. Bank Statement Parser SuiteApp

Part of the 2019.2 release for banking is the Bank Statement Parser SuiteApp (ID: 293699) which contains the three parsers plug-ins supporting the following formats:

• BAI2
• OFX/QFX
• MT940

Previously, the parsers for CSV, OFX, QFX, BAI2, and CAMT.053 were all part of the core functionality. With the introduction of parser plug-ins, this provides users with the flexibility of developing their own parsers if the statements of their banks are not available in the 3 formats mentioned above.

4. Bank Connectivity SuiteApp

The Bank Connectivity SuiteApp (ID: 285204) contains a plug-in called sFTP Connectivity that will facilitate the connection between NetSuite and the bank’s sFTP server location where the bank statements will be picked up. The following information will be configured in the plug-in:

5. Examples

5.1 Bank Accounts in Chart Of Accounts

HSBC Trading Account 1

Bank account setup in NetSuite for 1024 HSBC Trading Account 1

HSBC Trading Account 2

Bank account setup in NetSuite for 1025 HSBC Trading Account 2

If you wish to use the “old” manual import method, you will need to map every bank account in NetSuite to the actual bank account number or IBAN identifier because you will no longer be able to select a bank account before you run the statement import. Instead, the system will look for the account number or IBAN identifier in the statement and relate it to the NetSuite bank account based on the mapping you have defined.

In the account record, under Bank Import Mapping tab, the following fields will need to be populated depending on the format of the bank statement.

For the purpose of this example, I have chosen the BAI2 format.

When running the “new” manual import method or using the connectivity plug-in, these mappings in the account record will be ignored. The ones in the financial institution and format profile records will be followed instead, which you are able to select in the Statement File Import page.

5.2 Financial Institution

A financial institution record in NetSuite will represent the banks where you have accounts. This will be the parent for the format profile record. You can create financial institution records by navigating to Setup > Accounting > Financial Institution. For this example, I have setup a record for HSBC.

5.3 Format Profile

In the Format Profile: Data Format Options and Mapping sub tab of the financial institution record, click on the Add button to create a format profile record. In that record, you are able to define the following:

Parser to be used for this bank’s statement.
Mapping of bank account numbers to the NetSuite bank accounts.

I have labelled it as HSBC Bank Accounts – BAI2 and I set it to use the BAI2 Plugin Implementation parser. I then mapped the 2 NetSuite bank accounts to the corresponding bank account numbers under the Account Mapping tab.

5.4 sFTP Connectivity Plug-in Configuration

Upon installing the Bank Connectivity SuiteApp, a plug-in implementation called sFTP Connectivity is created in your account. You can find this under Customization > Plug-ins > Plug-in Implementations.

If your implementation only requires 1 financial institution to be setup for direct bank statement integration, this one plugin should be enough. However, if you have more than 1 bank to setup, you will need to replicate this plug-in for each financial institution in scope.

To demonstrate the 2^nd scenario, I have downloaded the script file from this plug-in, uploaded it again in the File Cabinet as bc_connectivity_sftp_2.js, and used it to create another instance of the plug-in which I have labelled as HSBC sFTP Connectivity. The script file should be uploaded in the same File Cabinet folder where the bundle installed all of the related custom objects. For this case, it is under this path File Cabinet > SuiteBundles > Bundle 285204 > com.netsuite.bc > src > connectivity.

This will open the configuration page.

I have entered the sFTP server details found in the next section.

5.5 sFTP Server

The sFTP server was setup manually.

To access the server through an application in your computer, you can use Cyberduck which can be downloaded for free. Once installed, open the application and connect to the server by clicking the Open Connection button as shown below.

Select SFTP from the first drop-down field and set the Server, Username, and Password fields using the details in section 5.5 then click the connect button.

Once connected, you can now navigate and access the folders available and you can add, remove, or rename files from here. In this example, I have placed the test bank statement under the folder ftp/files. I gave the statement a file name of Bank_Statement_HSBC_BAI2_10282019.txt to follow the pattern configured in the plug-in.

Navigating inside the sFTP server using Cyberduck

5.6 Sample BAI2 Bank Statement

The test bank statement I used is in BAI2 format and contains transaction entries for the 2 bank accounts configured in section 5.1. Inside the file, you can see the financial institution and the bank account numbers as highlighted below.

6. Running the Bank Statement Import

Go to Transactions > Bank > Import Online Banking Data.

6.1 “Old” Manual Import

First, choose the bank statement file from your computer. Select the second option (Import with a default parser (CSV, OFX, QFX, BAI2, or CAMT.053)) then click import.

6.2 “New” Manual Import

First, choose the bank statement file from your computer. Select the first option (Select a Financial Institution) then set the financial institution and format profile. Lastly, click the import button.

6.3 Connectivity Plug-in

Switch to the Connectivity Plug-in tab and set the sFTP plugin, financial institution, and format profile to be used. Lastly, click the import button.

After clicking the import button, regardless of the method you chose to upload the statement, a pop-up message will give you a link to track the status of the upload as shown in the image above. The page to which this link points to is the same as when you navigate to Transactions > Bank > Import Online Banking Data > List.

In the case of an error, open the import details and check the failure reason field for details.

For more details about the error message, you check the following SuiteAnswers articles:

File Import Error Messages for Bank Statement Parsers (Answer Id: 87478)
Bank Connectivity SuiteApp Error Messages (Answer Id: 88164)