Wiki

Case Status
Register Log In

Wiki

 
Bank Statement Import Patterns…

Bank Statement Import Patterns Mapping

Importing of bank statement entries is documented in Monthly Import Bank Statement Data Procedure

When a bank transaction entry is imported, the Narrartive/Reference field can be used to identify and automatically link the transaction to a supplier or customer on the Insight database. This is done by looking for patterns in the reference field and mapping those to a known supplier or customer on the Insight Database

This pattern mapping can be setup and changed using the Tools -> Bank Import Patterns as shown below

    

 

Each Import pattern is setup as follows:-

1. Type

The Type is either OrgMatch or Remove

1.a Remove 

Will use the Reg Ex Pattern to remove the matching unwanted characters from the Narrative line.

Eg. "a*,VIA MOBILE - PYMT" will change 

'JOHN SMITH , DIRECTOR WAGE , VIA MOBILE - PYMT , FP 30/09/18 10 , 31234569137725000N

to 

'JOHN SMITH , DIRECTOR WAGE , FP 30/09/18 10 , 31234569137725000N

and  "a*, FP \d{2}[/]\d{2}[/]\d{2}[ ]\d{1,4} , \d{17}[aA-zA-Z]{1}" will change 

'JOHN SMITH , DIRECTOR WAGE , FP 30/09/18 10 , 31234569137725000N

to

'JOHN SMITH , DIRECTOR WAGE

1.b OrgMatch

Will use the Reg Ex Pattern to find the Org Name and match to the Insight Database.

Eg.

"(\b\d{4}[ ]\d{2}[a-zA-z]{3}\d{2} , POSH FINE , FOODS L)" Match to 

1782 16OCT14 , POSH FINE , FOODS L , DERBY GB

2. Branch.

For multi-branch systems you can configure a pattern to only match bank transaction entries for a specific branch, or you can select this pattern to apply to all branches. An example of where you might choose to have a different pattern for each branch is where a supplier provides goods or services at each branch and include the branch name in the reference field.

2. Reg Ex Pattern

Most references contain some text that doesn't change as well as other parts that changes, for example dates, transaction numbers. Regular Expressions are a flexible way to recognise patterns based on static text,  other information within the reference field.

The Regular Expression us used to compare the text in the reference field and identify a pattern to recognise. Some examples are given in the table below. 

See http://www.regexr.com/ to help create and test patterns

If you are not familiar or confident in creating these, we can help create these for you. Please email a sample of your bank statement to support@derbydatabases.com . Please identify which supplier or customer each entry should be allocated against.

3. Organisation

This is drop down list of all the suppliers & customers in the Insight Database and is used to signify which organisation a transaction should be linked when it matches the pattern 

4. Active

If ticked, the import bank process will use this pattern to match to a supplier or customers. It can be useful to keep any previous patterns for future use and mark them as Inactive by unticking this box.

Examples of Regular Expressions to Match to an Organisation:-

Sample Narrative Line Pattern Description
1782 16OCT14 , POSH FINE , FOODS L , DERBY GB (\b\d{4}[ ]\d{2}[a-zA-z]{3}\d{2} , POSH FINE , FOODS L)  
JOHN SMITH, WAGES, FP 09/10/14 10 , 58745253752420000N JOHN SMITH, WAGES, FP \d{2}[/]\d{2}[/]\d{2}[ , ]\d{2} , \d{17}[a-zA-Z{1}]  
MV- 12373363 -4567 MV- \d{8} -\d{4}  
1782 17OCT14 , 3663 , DERBY DATABASES LTD (\b\d{4}[ ]\d{2}[a-zA-z]{3}\d{2} , \d{4} , DERBY DATABASES LTD)  
EON , 013787516540 EON , \d{12}  
DERBY DATABASES , INSIGHT FEE DERBY DATABASES , INSIGHT FEE  
020366 25JAN 1234 (\b\d{6}[ ]\d{2}[a-zA-z]{3}[ ]\d{4})  
DERBY CITY COUNCIL, DERBY DATABASES, FP 20/10/14 30 , 12023747687654300N DERBY CITY COUNCIL, DERBY DATABASES, FP \d{2}[/]\d{2}[/]\d{2}[ ]\d{2} , \d{17}[aA-zA-Z]{1}  
1782 23OCT14 , DERBY DATABASES L, DERBY GB (\b\d{4}[ ]\d{2}[a-zA-z]{3}\d{2} , DERBY DATABASES L, DERBY GB)  
LEICESTER BRANCH , DERBY DATABASES, FP 24/10/14 30 , 16023641474081000N LEICESTER BRANCH , DERBY DATABASES, FP \d{2}[/]\d{2}[/]\d{2}[ ]\d{2} , \d{17}[a-zA-Z]{1}  
TALKTALK BUSINESS , 01304429/123 TALKTALK BUSINESS , \d{8}[/]\d{3}  
CALL REF.NO. 0111 , FROM A/C 12345678 CALL REF.NO. \d{4} , FROM A/C \d{8}  
DERBY CITY COUNCIL, DERBY DATABASES , FP 20/10/14 30 , 12012345678512000N (DERBY CITY COUNCIL,)(([^,]*), (FP \d{2}[/]\d{2}[/]\d{2}[ ]\d{2} , \d{17}[a-zA-Z]{1}))  
S/LINE H1234565 , INV 0012312323 S/LINE [a-zA-Z{1}]\d{7} , INV \d{10}  
CALL REF.NO. 0124 , MR JOHN SMITH, FP 30/10/14 10 , 29112345678955000N CALL REF.NO. \d{4} , (([^,]*), (FP \d{2}[/]\d{2}[/]\d{2}[ ]\d{2} , \d{17}[a-zA-Z]{1}))  
001782  (\b\d{0,6}) Matches 6 digits only. Eg. 123456 will match, but 123456 JAN12 won't mathc
     


Examples of Regular Expressions to remove unwanted characters :-

Sample Narrative Line Pattern Description
'JOHN SMITH, DIRECTOR WAGE , VIA MOBILE - PYMT , FP 30/09/17 10 , 62122321037593000N a*, VIA MOBILE - PYMT Will remove , VIA MOBILE - PYMT  leaving 'JOHN SMITH, DIRECTOR WAGE , FP 30/09/17 10 , 62122321037593000N
JOHN SMITH, WAGES, FP 09/10/14 10 , 58745253752420000N a*, FP \d{2}[/]\d{2}[/]\d{2}[ ]\d{1,4} , \d{17}[aA-zA-Z]{1} Will remove FP 09/10/14 10 , 58745253752420000N leaving  JOHN SMITH, WAGES 
MV- 12373363 -4567 MV- \d{8} -\d{4}  
1782 17OCT14 , 3663 , DERBY DATABASES LTD \b\d{4}[ ]\d{2}[a-zA-z]{3}\d{2} ,  Will remove 1782 17OCT14 , 3663 ,  leaving DERBY DATABASES LTD
'CALL REF.NO. 0098 , JOHN MSITH, FP 10/05/17 10 , 53092056774422000N \bCALL REF.NO. \d{4} , 

Will remove  CALL REF.NO. 0098 FROM 'CALL REF.NO. 0098 , JOHN MSMITH, FP 10/05/17 10 , 53092056774422000N' leaving  'JOHN MSMITH, FP 10/05/17 10 , 53092056774422000N'

JSMITH 04APR  ([0-3][0-9])(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)

Will remove  0APR from JSMITH 04APR leaving JSMITH