Bank reconciliations can be processed for small businesses and personal checking accounts and savings accounts, or for corporate accounting requirements.

The main two reasons for performing it are the fraud detection and annual audit for your business.

Bank reconciliation is the process of comparing your internal records to those of your bank, to see if there are any discrepancies between these two sets of records for your cash transactions. The ending balance of your data of the cash records is known as the book balance, while the bank’s version is called the bank balance. It happens for there to be differences between the two balances, which you should track down and adjust in your own records. If you were to ignore these differences, there would eventually be substantial variances between the amount of cash that you think you have and the amount the bank says you actually have in an account. The result could be an overdrawn bank account, bounced checks or transactions, overdraft fees, etc.

If we break down the process into steps, ticking off the transactions that match is the domain where BitCurb Crunch engine helps.

Our engine can be used in of transaction matching processes, i.e. bank reconciliation, statement reconciliation, credit card reconciliation, sub ledger reconciliation, intercompany reconciliation and so on.

A non-exhaustive list of the challenges in the process of bank reconciliation could be:

  • Automating the matching process
  • Matching big volumes of data
  • Matching specific  transactions that form 1 to M (many) relationship and especially matching these transactions within given variance
  • Matching transactions based on complex correlation between a number of properties for the target records

Let’s review a case where we have 2 company accounts and we want to reconcile payments made through them with the internal records we made for the very same transactions.

The bank data comes in swift format and in our case MT940 format. Part of it is extracted and presented in tabular format below:

The internal data could be anything from excel spreadsheets to accounting software data export.

Below is the corresponding set of data that we keep internally:

All transaction properties that present bank accounts and transactions references have been deliberately changed.

We have two bank accounts: GB21ABCY99999999999999 and GB21ABCY99999999999998, and from the bank report we see the payment we made through GB21ABCY99999999999998 has been reported to have transfer fees.

BitCurb Crunch rules must have defined few properties that filtering rules don’t have:

  • Direction
  • Type
  • Variance
  • Crunch Field – that is the field that is used for comparison and calculating the difference if such exists.  Even if this field is not part of the

To match the transaction from the two different sources we will have next attributes defined:

  • Direction: $1 to $2 , meaning we match two sources of data against each other
  • Type: we will have two 1:M rules and one 1:1 rule
  • Variance: we will have one rule with variance and two without
  • Crunch Field – Amount column

Below is the list of the rules that reconcile the data above:

  • $1.AccountIdentification == $2.AccountIdentification AND $1.TransactionValueDate == $2.TransactionValueDate AND $1.TransactionType == $2.TransactionType AND FIND($2.AccountServingInstitution, $1.AccountServingInstitution) >= 1

 

1:M (One To Many) rule. We have made two bank payments, but recorded internally only one payment internally, representing the total amount

  • $1.AccountIdentification == $2.AccountIdentification AND $1.TransactionValueDate == $2.TransactionValueDate AND $1.TransactionType == $2.TransactionType AND $1.AccountServingInstitution == $2.AccountServingInstitution

 

1:M (One To Many) rule. We have made one bank payment and respectively recorded internally one payment. But the bank payment has additional taxes for the transfer, which we didn’t log internally. We only know that the % of taxes is less than 20 quids, hence we define Variance condition for this rule, saying Variance < 20.00

 

  • $2.AccountServingInstitution == $1.AccountServingInstitution AND $2.Amount == $1.Amount

1:1 (One To One) rule. No variance is applied, since we recorded internally the amounts of the bank transfers we have made

 

We can select to run the rules one after another, one at a time or to define template and add them to it and run the template which will imply to executing all the rules in the order they are assigned in the template.

Please, refer the documentation of the BitCurb Crunch Engine for details about structure of the messages exchanged between consumer and BitCurb.

Rules can be executed consequentially in different request, but also can be bound to template and run at once through this template.

Find below a screenshot for each of the rules.

As you can see the rules may contain different structure rows definition that applies to the context of the rule. It is not necessary to define all the fields from your source that you are trying to reconcile.

Once defined, the rules can be assigned in a template. Order matters, since it defines the sequence in which the rules are executed. You cannot mix filtering with matching rules in one template.

Template ID is the first column in the list of the defined template. It could be used as input parameter for executing the Crunch operation.

API BitCurb

BitCurb Crunch Engine works with JSON, so the source of the data is irrelevant for the engine.

There are two options when you have more than one rule that you want to execute against raw source data.

  • Execute the rule one after another and process the result for every rule in the caller before applying next one. This will involve multiple request-response operations against the BitCurb api endpoint. If this is the case, the consumer should take care of processing result and defining if one item can be part of more than one result group.
  • Execute all the rules at once and return a subset as result in one request-response operation. In this case one item can’t be part in more than one result, which may potentially be the case if you run the rules one at a time.

In order to achieve this, you need to define reusable template in your account, create rules and then attach the rules to this template. Bulk rules execution is currently supported only through referencing template ID in your request.

To be able to work with the API your account must be assigned in licence and your connection settings must be set up in Administration module, Client Profile page.

Please, note that if you are consuming the API through client tool (any tailor developed program or tool), the Client Secret might need to be encoded. That is also valid for your password, when you acquire token. In our code samples available on GitHub, the values for the settings must be provided in the app.config as it is.

JSON that is submitted must have unique identifiers for each data row that is processed and the result will refer these identifiers, rather than containing full copy of the data. It is up to the consumer of the API to handle the result by the Ids.

Here is the JSON that is submitted for reconciliation to our Crunch Engine:

{
   "$1":[
      {
         "Id":8,
         "CrunchValue":3854.11,
         "Fields":{
            "AccountIdentification":"GB21ABCY99999999999999",
            "TransactionValueDate":"170425",
            "TransactionType":"D",
            "Amount":3854.11,
            "AccountServingInstitution":"969989937331,969989937499",
            "ReceivingAccountIdentification":"GB21ABCY11111111111111"
         }
      },
      {
         "Id":9,
         "CrunchValue":3775.0,
         "Fields":{
            "AccountIdentification":"GB21ABCY99999999999998",
            "TransactionValueDate":"170502",
            "TransactionType":"D",
            "Amount":3775.0,
            "AccountServingInstitution":"969989913557",
            "ReceivingAccountIdentification":"GB21ABCY11111111111112"
         }
      },
      {
         "Id":10,
         "CrunchValue":114.33,
         "Fields":{
            "AccountIdentification":"GB21ABCY99999999999999",
            "TransactionValueDate":"170502",
            "TransactionType":"D",
            "Amount":114.33,
            "AccountServingInstitution":"999992144444",
            "ReceivingAccountIdentification":"GB21ABCY11111111111113"
         }
      },
      {
         "Id":11,
         "CrunchValue":56.23,
         "Fields":{
            "AccountIdentification":"GB21ABCY99999999999999",
            "TransactionValueDate":"170502",
            "TransactionType":"D",
            "Amount":56.23,
            "AccountServingInstitution":"999992144445",
            "ReceivingAccountIdentification":"GB21ABCY11111111111114"
         }
      }
   ],
   "$2":[
      {
         "Id":1,
         "CrunchValue":1416.88,
         "Fields":{
            "TransactionReferenceNumber":"20170425-23659542",
            "AccountIdentification":"GB21ABCY99999999999999",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170425",
            "TransactionEntryDate":"0425",
            "TransactionType":"D",
            "Amount":1416.88,
            "TransactionInformation":"invoice number 1112 March Customer X",
            "AccountServingInstitution":"969989937331",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":"GB21ABCY11111111111111"
         }
      },
      {
         "Id":2,
         "CrunchValue":2437.23,
         "Fields":{
            "TransactionReferenceNumber":"20170425-23659542",
            "AccountIdentification":"GB21ABCY99999999999999",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170425",
            "TransactionEntryDate":"0425",
            "TransactionType":"D",
            "Amount":2437.23,
            "TransactionInformation":"invoice number 1113 April Customer X",
            "AccountServingInstitution":"969989937499",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":"GB21ABCY11111111111111"
         }
      },
      {
         "Id":3,
         "CrunchValue":3775.0,
         "Fields":{
            "TransactionReferenceNumber":"20170502-23659667",
            "AccountIdentification":"GB21ABCY99999999999998",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170502",
            "TransactionEntryDate":"0502",
            "TransactionType":"D",
            "Amount":3775.0,
            "TransactionInformation":"INVOICE NUMBER INV 11111",
            "AccountServingInstitution":"969989913557",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":"GB21ABCY11111111111112"
         }
      },
      {
         "Id":4,
         "CrunchValue":16.89,
         "Fields":{
            "TransactionReferenceNumber":"20170502-23659667",
            "AccountIdentification":"GB21ABCY99999999999998",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170502",
            "TransactionEntryDate":"0502",
            "TransactionType":"D",
            "Amount":16.89,
            "TransactionInformation":"Tax outgoing payment",
            "AccountServingInstitution":"969989913557",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":""
         }
      },
      {
         "Id":5,
         "CrunchValue":5.0,
         "Fields":{
            "TransactionReferenceNumber":"20170502-23659667",
            "AccountIdentification":"GB21ABCC99999999999998",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170502",
            "TransactionEntryDate":"0502",
            "TransactionType":"D",
            "Amount":5.0,
            "TransactionInformation":"Recipient Fee",
            "AccountServingInstitution":"999992140322",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":""
         }
      },
      {
         "Id":6,
         "CrunchValue":114.33,
         "Fields":{
            "TransactionReferenceNumber":"20170502-23456789",
            "AccountIdentification":"GB21ABCY99999999999999",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170503",
            "TransactionEntryDate":"0503",
            "TransactionType":"D",
            "Amount":114.33,
            "TransactionInformation":"insurance fee policy number 123456789",
            "AccountServingInstitution":"999992144444",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":"GB21ABCY11111111111113"
         }
      },
      {
         "Id":7,
         "CrunchValue":56.23,
         "Fields":{
            "TransactionReferenceNumber":"20170502-23456789",
            "AccountIdentification":"GB21ABCY99999999999999",
            "OpeningBalanceType":"C",
            "Currency":"GBP",
            "TransactionValueDate":"170503",
            "TransactionEntryDate":"0503",
            "TransactionType":"D",
            "Amount":56.23,
            "TransactionInformation":"",
            "AccountServingInstitution":"999992144445",
            "ClosingBalanceType":"C",
            "ReceivingAccountIdentification":"GB21ABCY11111111111114"
         }
      }
   ],
   "TemplateId":19
}

As you can see $1 side in this case is the internal records sheet (“Internal Records”). It contains four transactions and each of them has ID, which is unique to not only all other transactions in $1 side, but to all transactions from $2 side.

$2 side represent the bank records sheet (“Bank Swift MT 940”). It contains seven transactions. Each of the transactions has unique ID value, which identifies the row in the data sets that are reconciled.

You can notice that it is not mandatory both sides to have the same structure. Items in side $2 have columns, different from items in side $1. Both sides though share the same columns, which participate in rules’ body definitions.

After you execute a rule you receive a JSON result which you can consume according your business process.

Here is the response in text file:

{
   "Groups":[
      {
         "Identifier":"2705be57-4d82-4526-a116-928a03bf1722",
         "Elements":[
            8,
            1,
            2
         ]
      },
      {
         "Identifier":"02dca6f3-650d-4bc0-95ef-df0ce9a2a635",
         "Elements":[
            9,
            3,
            4
         ],
         "Variance":16.89
      },
      {
         "Identifier":"59a506f5-ce15-4f67-9d36-19a5f8a7e304",
         "Elements":[
            10,
            6
         ]
      },
      {
         "Identifier":"009ba390-48fd-4d41-881d-6f1dc3fafa7b",
         "Elements":[
            11,
            7
         ]
      }
   ]
}

The response contains a number of uniquely identified results groups, containing the IDs of the transactions that are part of the groups.

Let’s review each of the result groups:

  • “Identifier”:”2705be57-4d82-4526-a116-928a03bf1722″,  “Elements”:[8,1,2]

The result group is comprised of 3 elements, following the rule 1:M

$1.AccountIdentification == $2.AccountIdentification AND $1.TransactionValueDate == $2.TransactionValueDate AND $1.TransactionType == $2.TransactionType AND FIND($2.AccountServingInstitution, $1.AccountServingInstitution) >= 1

  • “Identifier”:”02dca6f3-650d-4bc0-95ef-df0ce9a2a635″,  “Elements”:[9,3,4], “Variance”:16.89

The result group is comprised of 3 elements and there is variance item with value 16.89. The group is result of the execution of our second rule:

$1.AccountIdentification == $2.AccountIdentification AND $1.TransactionValueDate == $2.TransactionValueDate AND $1.TransactionType == $2.TransactionType AND $1.AccountServingInstitution == $2.AccountServingInstitution

Please note, it is responsibility of the consumer of the API to properly handle the variance item attributes, since the crunch engine returns only the value of the variance. It usually business specific, which attributes shall the difference item inherit.

Also, note the Credit or Debit type of your transactions is not counted during processing. The values that needs to be passed for processing should be negative or positive values representing your own business logic for Credit or Debit. Calculated variance is always absolute value, so it is consumer’s responsibility to define negative or positive (Credit or Debit) based on the other items participating in the group.

  • “Identifier”:”59a506f5-ce15-4f67-9d36-19a5f8a7e304″,   “Elements”:[10,6]

The group is result of execution of the last rule in our template:

$2.AccountServingInstitution == $1.AccountServingInstitution AND $2.Amount == $1.Amount

Records have the same payment reference number and same amounts.

  • “Identifier”:”009ba390-48fd-4d41-881d-6f1dc3fafa7b”,    “Elements”:[11,7]

The group is result of execution of the last rule in our template:

$2.AccountServingInstitution == $1.AccountServingInstitution AND $2.Amount == $1.Amount

Records have the same payment reference number and same amounts.

If you compare all items participating in groups, you will notice that there is only 1 outstanding item – this with ID 5. Along with the variance value of 16.89, the reconciliation gives us 2 outstanding items that would need to be investigated for discrepancies and eventually reported in the internal records.

If you want to track which are rules that created the result groups, please refer our report knowledge based article for details.

You can also download our code samples from our GitHub repository.