11th Nov 2019 •

NetSuite 2019.2 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 ImportOracle NetSuite

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.

 

 

 

 

Statement File Import – Manual Import

 

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.

 

 

Statement File Import – 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:

 

Configuration FieldsValues
URL
  • URL or IP address of the bank’s sFTP server where the statement will be picked up by NetSuite.
  • Provided by the bank.
Username
  • Username to be used when connecting to the sFTP server.
  • Provided by the bank.
Digital Certificate or Private Key ID (optional)
  • A digital certificate or private key may be used in place of a password to establish a connection to the financial institution’s server. Make sure that the certificate is prefixed with ‘custcertificate’ and the private key is prefixed with ‘custkey’.
  • Provided by the bank.
Password
  • Password to be used when connecting to the sFTP server.
  • Provided by the bank.
Host Key
  • Public key of the sFTP server. This should follow the type defined in the Host Key Type field.
  • Provided by the bank.
Host Key Type
  • The accepted types are RSA, DSA, pr ECDSA.
  • Provided by the bank.
Port
  • For sFTP connections, 22 is the more common port number used. Otherwise, the bank should provide the correct port to be used.
Download Filename Pattern
  • This is where you can define the file name that the plug-in will look for in the sFTP server. For example Bank_Statement_{DateToday}.txt.
  • You can use the following reserved words for defining the dates within the file name:
    • DateToday – this reserved word will be replaced with the date today.
    • DateLastBusinessDay – this reserve word will be replaced with the date of the previous business day.
    • DateYesterday – this reserved word will be replaced with the date yesterday.
    • DateLastMonthEnd – this reserved word will be replaced with the date of the previous month’s last day.
    • DateStartOfTheMonth – this reserved word will be replaced with the date of the current month’s first day.
  • Defined by the user configuring the plug-in.
  • The bank should comply to the set pattern.
Bank Server Time Zone (GMT)
  • Time zone of the bank server.
  • It is important to align to the time zone of the bank server so that the reserved word will be replaced with the correct date.
  • Provided by the bank.
Date Format for Reserved Word
  • This is where you define the date format that will be followed when the reserved word is replaced by the actual date.
  • A valid date format may have a combination of 2-3 ‘M’ characters, 2 ‘D’ characters, 2 or 4 ‘Y’ characters, and may include a ‘-‘ separator. For example, MMDDYYYY and MM-DD-YY are valid date formats while MMDDMMYYYY and MDMDYMYM are invalid date formats.
  • Defined by the user configuring the plug-in.
  • The bank should comply to the set format.
Download Directory
  • The exact folder within the sFTP server which will contain the bank statement.
  • Provided by the bank.
Download Timeout
  • Any value between 1-300.
  • There is no mention of what unit of time this pertains to in any of the documentations currently available.
  • Defined by the user configuring the plug-in

Configuration fields of the sFTP Connectivity plug-in

 

5 Examples

 

5.1       Bank Accounts in Chart Of Accounts

 

HSBC Trading Account 1

Account1024 HSBC Trading Account 1
SubsidiaryUnited Kingdom
CurrencyGBP
(BAI2) 02 Record – Originator Identification FieldHSBC
(BAI2) 03 Record – Customer Account Number40025001360469
Bank account setup in NetSuite for 1024 HSBC Trading Account 1

 

HSBC Trading Account 2

Account1025 HSBC Trading Account 2
SubsidiaryUnited Kingdom
CurrencyGBP
(BAI2) 02 Record – Originator Identification FieldHSBC
(BAI2) 03 Record – Customer Account Number40025001360470
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.

 

Bank Import Mapping tab on the account record

 

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.

 

Financial institution 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.

 

Format profile setup for HSBC’s financial institution record

 

 

For the example above, 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.

You can also further configure the format profile by mapping custom bank transaction codes to the corresponding bank data types under the Transaction Code Mapping subtab. You can also setup additional format specific configurations under the Parser Configuration subtab. However, for this example, we are not going to touch on this to keep things simple.

 

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 2nd 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.

 

File Cabinet folder to save additional sFTP connectivity scripts

 

To configure the plug-in, open it in view mode and click the configure button.

 

HSBC sFTP Connectivity plug-in implementation in view mode

 

This will open the configuration page as shown below.

 

HSBC sFTP Connectivity 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.

 

Opening a connection in Cyberduck

 

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.

 

Entering sFTP server details and login credentials

 

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.

 

Contents of the sample BAI2 bank statement

 

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.

 

Import bank statement manually using the new method

 

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.

Imported Statement List page

 

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

 

Import 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)

 


Back to news

listen . transform . together

Find out how your company could benefit from our proven experience and commitment towards creating positive change. We’re great listeners and firmly believe that business and system transformation should be a collaborative process between ourselves and our clients. We can assist you through the full life cycle; from choosing the right software solutions, through to implementation and support.

 

WANT TO KNOW MORE?

Get in touch and we’ll talk you through the rest.


I understand and accept the terms and privacy policy
I agree to receive other communications from Elevate2