This article is relevant if you desire to build custom parsings or bank imports from Financial Institutions into NetSuite.

Background

In my 2022 article, Sort Out NetSuite’s Banking Import Options Including a How To for Custom Plug-In Approaches, I offered an orientation about the many options to help retrieve and import bank or financial institution data into NetSuite.

In our experience working with clients, we have found that the supplied parsers and files offered by banks may not always conform to so-called “standards”.  Thus, we need to roll up our sleeves to get to work and solve the technical concerns that invariably seem to come forth when working in this area of the NetSuite platform.

In this article, illuminating the connectivity and parsing particulars can help the NetSuite technologist navigate the complexity to produce a stable solution.

NetSuite Financial Institution Connectivity

The connectivity guidance offered by NetSuite effectively assumes that you have set up your Financial Institution profiles to store information in custom tables that are then used to gather account-level mapping information between the Financial Institution and NetSuite GL accounts. Those accounts are then used as a map to help retrieve respective bank data offered by the institution.

Since each institution supplying data can offer anything it wants, it is important to determine the technology plumbing constraints before trying to work with Connectivity plugins. We suggest building a “Hello World” approach to connect and confirm assumptions.  Note, the most popular technique for passing data is via sFTP. However, readers should prepare for the real concern too often presented about NetSuite IP Restrictions for sFTP Services.  This plugin aims to retrieve the remotely supplied data to package it for parsing.

Thus, the Financial Institution Connectivity layer implemented via a custom NetSuite plugin supplies data to a custom NetSuite Financial Institution Parser plugin.  These two plugin technologies help produce the overall solution.

NetSuite Financial Institution Parsers

The data supplied by the bank typically is in either common CSV or other more arcane file formats. We often see the BAI2 file format when helping our clients. See the BAI website here and the related file specifications.

The BAI2 file format is a more demanding structure to work with. NetSuite provides parsers via the Bank Statement Parser SuiteApp (at this writing, the bundle number is 293699). The parser supports CSV, BAI2, OFX/QFX, MT940, and CAMT053 formats.

In our experience, when working with a new institution, we always start with the hope that the NetSuite Bank Statement Parser SuiteApp gets the job done.  But we find that because banks don’t carefully conform to standards, we may need to build our own parser. Unfortunately, NetSuite locks up the code for the supplied parsers, making it more demanding to produce what may be a simple programming refinement. Yet, the open-source community offers up algorithms that can be helpful when you have to take matters into your own hands.  Use your search engine to help find those algorithms.

Example NetSuite Financial Institution CSV Parser

To help NetSuite developers understand better what is needed, I will supply a (modified) version of CSV parser work that Matthew M., Senior Technical Analyst, produced for a client. Here, we leveraged our Prolecto Record Import Export (RIEM) framework as we have powerful tools to transform data into JSON to make it easy in JavaScript to do the work needed to map the data.

NetSuite’s Intelligent Transaction (Auto) Matching is one topical area that comes up when importing this type of bank data.  We have found that there are times when the provided tools do not quite give us the data rule matching we need.  It’s in this parsing layer that can get control over the supplied data and refine it to help NetSuite’s rule engine do its matching work.

The following SuiteSuite 2.0 assumes that the Connectivity plugin did its work to retrieve the data.

Example SuiteScript 2.0 Financial Institution CSV Parser

/*
 * in gratitude to Matthew M., Senior Technical Analyst, Prolecto Resources
 * @NApiVersion 2.x
 * @NScriptType fiParserPlugin
 */

define(['N/query','N/url','/.bundle/413713/riem/PRI_RIEM_Common','N/format'],
  function (query, url,riemCommon,format)
  {
    function getConfigurationPageUrl(context){
      //Adding configuration options in UI is Not in use.
      return;
    }
    function parseData(context) {
      var logTitle='parseData';
      var configurationId = context.pluginConfiguration.getConfigurationFieldValue({fieldName: "configuration_id"});
      log.debug(logTitle,'configurationId: ' + configurationId);

      //the assumption is that the connectivity plug-in did the work to return the data to the parser from this final call
      //context.returnAccountRequestsJSON({accountsJson: context.accountRequestsJSON});

      //use the power of Prolecto RIEM parser to work with CSV data; so much easier
      var recordData = riemCommon.parseFile(context.inputData.getContents(),null,riemCommon.IMPORT_TYPE.COMMA_SEPARATED,null,null,null);

      //get the account id.  The assumption is that there's a single account inside the file
      log.debug(logTitle,'accountId: '+ recordData[0]["accountnumber"]);

      //work with NetSuite's Match Data account (header) and transaction (detail) record format
      //this plug-in appears to be the only way to push data into the target structure 
      var account = context.createAccountData({
        accountId: recordData[0]["accountnumber"],

        /* sample structure below
        account: '5430001',
        dataAsOfDate: '2019-12-19',
        openingBalance: 100.0,
        closingBalance: 120.0,
        currentBalance: 125.0,
        dueBalance: 10.0
        */
      });

      //loop through the data to build out transaction records
      //if needed, to help the Automated Reconciliation Rules engine match, we can manipulate data here
      for(var i = 0; i < recordData.length; i++){
        var date = recordData[i]["date"];
        var dateObj = format.parse({value: date, type: format.Type.DATE});
        var creditAmount = recordData[i]["creditamount"];
        var debitAmount = recordData[i]["debitamount"];
        var amount = creditAmount == '0' ? debitAmount : creditAmount;

        account.createNewTransaction({
          id: recordData[i]["reference"],
          date: formatDate(dateObj),
          transactionTypeCode: recordData[i]["description"],
          payee: recordData[i]["payerpayeename"],
          memo: recordData[i]["memo"],
          amount: amount,
          
          //other data structures below for reference
          //currency: 'USD',
          //uniqueId: recordData[i]["transactionid"],
          //transactionStatus: 'Cancelled',
          //customerReferenceId: '34',
          //customerName: 'Amit',
          //invoiceReferenceIds: ['100', '101],
          //billedTaxAmount: 15.0,
          //localChargeAmount: 100.0,
          //localTaxAmount: 17.0,
          //currencyExchangeRate: 1.13,
          //expenseCode: '4'
        });
      }
    }
    function getStandardTransactionCodes(context) {
      /* not used
          context.createNewStandardTransactionCode({
          transactionCode: 'CHARGE',
          transactionType: 'PAYMENT'
      });
      */
    }
    function getExpenseCodes(context) {
      /* not used
          context.createNewExpenseCode({
          code: 'CC',
          description: 'Customer Credit'
      });*/
    }
    function formatDate(date) {
      var d = new Date(date),
        month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        year = d.getFullYear();

      if (month.length < 2)
          month = '0' + month;
      if (day.length < 2)
          day = '0' + day;

      return [year, month, day].join('-');
    }

    return {
      getConfigurationPageUrl: getConfigurationPageUrl,
      parseData: parseData,
      getStandardTransactionCodes: getStandardTransactionCodes,
      getExpenseCodes: getExpenseCodes
    }
  });

Implementing Bank Connections and Imports

During development, it’s important to see if our NetSuite’s parsing programs work as expected. We generally start with the manual file import process before we produce the automated import.  This article, Gain Access to NetSuite’s Bank Import History Detail Page, can help with the process.

This type of software development is less intuitive than most NetSuite programming tasks. Given NetSuite’s Parser SuiteApp is locked up, in due course, I can imagine producing a framework to help ease the crafting of these specialized Financial institution programs in a much easier fashion than how they have been presented by NetSuite.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. Should you need a custom NetSuite Financial Institution (bank) integration, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube