An import extension that is used to import a bank account statements, takes as input a bank statements in digital format and convert into a "transactions.simple" format that is accepted as input from Banana Accounting. 

See also:

• Extensions for importing transactions from CSV file

transactions.simple converted to the accounting file

Using the "transactions.simple" has the advantage that the program automatically convert the data to the specific accounting you are using. So the format is adequate to be used for double entry, multi-currency or Income & expenses.

The program automatically convert the data to the specific accounting file format.

• Cash Manager and  Income & Expenses
  • Income Amounts goes in the column Income
  • Expense Amounts or negative Income Amounts goes in the column Expenses.
• Double entry accounting
  • Income Amounts goes in the column Amount. 
    Bank Account goes in the Account Debit Column
  • Expense Amounts or negative Income Amounts goes in the column Amount as positive values.
    Bank Account goes in the Account Credit Column 

Digital Bank Statement

A digital bank statement is a file containing the transactions of a bank account within a specific period. 

Bank statements have usually this elements:

• Information regarding the accounts.
  • Account id
  • Account holder
• Period information.
  • Start Date
  • End Date
  • Begin Balance
  • End Balance
• Transactions with all for each movement a least the following information.
  • Date
  • Description of the transactions
  • Income amount, positive for the customer
  • Outcome amount , negative for the customer
  • Amount an amount that contain income as positive number and negative numbers as expenses.  

Digital Bank Customer Statements Formats

Banks can make their bank statements available in different formats.

• ISO 20022 Bank Statements Format  camt.052, camt.053, camt.054.
  It is an XML file that can contain multiple bank statements.
  Each country can have a specific implementation of this format.
  Banana Accounting provides extensions to read such file.
• ISO MT940.
  Is a text based format.
• CSV (Comma separated file format) containing bank statements transactions.
  Banks, within online application, usually allows customer to export the transaction in a CSV or Excel file.

Bank CSV Bank Statements

Banks usually let you export the bank statement in a CSV format, but the columns header, columns sequence and format of the data usually differs. 

When converting the data from a Bank Statement to a "transactions.simple" file format it is important to analyze with attention the bank statement:

The structure of a Bank Statement CSV is usually this:

• Optional Information regarding the account and holder.
  • Unstructured data at the begin of the file.
• The columns headers.
  • A line with the name of the columns. 
    For examples "Date,Description,Income,Expense"
  • Columns name are separated by a character, usually a comma "," or a semicolon ";".
  • The columns name use similar naming, but are also usually in a language specific to the customer. For example:
    • German "Datum,Buchungstext,Einnahmen, Ausgaben“
• The transactions movements.
  • Multiple lines each one containing a single movement.
  • Date may be expressed in a specific format.
  • Number may be expressed in a specific localized format.
  • For example
    • "31.12.2024,"Payment to Albert",,"1'344,22",
    • "31.12.2024,"Income from Albert","344,22",
• Other optional information.
  • Unstructured data at the end of the file. 

Banks use different naming for columns containing the income and expenses amount. 

• Double entry column's naming convention:
  Debit and Credit is usually referred to the bank point of view. "We have debited you", "We have credited you".
  • Debit for the costumer is an Expense (credit amount).
  • Credit for the costumer is an Income (debit amount). 

In the example the values are written in an excel document.

Import Extension for transactions.simple

The import Extension takes a 

Extensions attributes

The Extensions attributes should contains the following lines:

//@task = import.transaction
//@outputformat = transactions.simple

JavaScript code 

The function takes the input data (e.g. csv file) and converts it to a "transactions.simple" tabulator separated values (tsv file).

• An exec(data)  
  • It take as parameter the CSV bank statement.
  • It process the input data and convert to transactions.simple data structure.
  • It returns the data.
• Mapping the CSV columns to the transactions.simple naming
  When mapping column, the Date column must always be on first position. 
  For example:
  • "Datum" to  "Date"
  • "Buchungstext" to "Description"
  • "Einnahmen" to "Income"
  • "Ausgaben" to "Expenses"
• Converting the date value to transactions.simple format yyyy-mm-dd.
  • To convert a date, use the "Banana.Converter.toInternalDateFormat(date, input_format)"
    • Date "31.12.2024," to "2024-12-31"
    • Example: Banana.Converter.toInternalDateFormat("31.12.2024", "dd-mm-yyyy") returns "2024-12-31"
• Converting the numeric value to transactions.simple format
  • To convert a numeric value, use the "Banana.Converter.toInternalNumberFormat(numeric_value, input_decimal_separator)"
    • Numeric "344,22" to "344.22"
    • Example: "Banana.Converter.toInternalNumberFormat("123,45", ",")" returns "123.45"
  • For all numeric operations, use the Banana.SDecimal class.
    • To convert the sign of a numeric value, use the "Banana.SDecimal.invert(numeric_value)"
      Example: Banana.SDecimal.invert("123.45") returns "-123.45"
    • To verify the when a numeric value is positive or negative, use the "Banana.SDecimal.sign(numeric_value)"
      Example: Banana.SDecimal.sign("-123.45") returns "-1".
• Creating the output in the transactions.simple format, containing:
  • The columns header
  • The transactions data

"transactions.simple" format (Income & Expenses)

The import extension takes in input the bank statement and convert to a "transactions.simple" format with the following characteristics: 

• Tabulator Separated Values (tsv).
  • Column headers and data must use the tab character as separator "\t"
  • Each line (after a "\n") is a new record
• The first line of the file contains the Columns headers.
  After the header come the lines with the transactions data.
• Columns header
  They specify the name of the column.
  • Column names are case sensitive.
  • Required Columns
    • Date
      The date of the transaction.
      Date should be in format "yyyy-mm-dd" (e.g. "2024-12-31").
    • Description
      A a brief text of the transaction.
    • Income 
      The income amount. 
      For double-entry accounting, the the amount in debit, if negative is considered in credit. 
      The amount should be in simple numeric format, with the point "." as a decimal separator.
  • Optional predefined columns:
    When present the import function will use the content.
    • Expenses
      The outcome amount.
      For double entry the amount is in credit.
      The Expense can also be specified as a negative number in the column "Income".
    • DocInvoice
      The invoice number.
    • ContraAccount
      The account number (debit/credit) or category.
      Enter square brackets [] to keep the field empty.
    • Account
      The account of the transaction when the file contains the movements of multiple accounts.
      Enter square brackets [] to keep the field empty.
    • VatCode
      The VAT code that should be used.
      The VatCode should be defined in the VatCodes Table.
      Precede with the minus "-" sign if the Transaction is a correction of an existing transactions.
    • IsDetail
      For composed transactions a "S" identifies a counterpart transaction and a "D" a detail transactions
• Transactions data in column format
  • Tabulator separated values following the headers.
  • Date columns should be in the format "yyyy-mm-dd".
  • Amount should be in a standard JavaScript decimal number format:
    • Decimal separator is the point "."
    • No thousands separators
    • Only numeric characters "12345678.90"
    • Negative number with a minus sign "-" preceding the numbers (e.g. "-100.00").

Example Extension

This extension example return a predefined data content, does not convert the data.

// @api = 1.0
// @id = ch.banana.scripts.import.example
// @description = Import Example bank (*.csv)
// @task = import.transactions
// @doctype = nodocument
// @publisher = Banana.ch SA
// @pubdate = 2024-06-21
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputfilefilter = Text files (*.txt *.csv);;All files (*.*)
// @inputfilefilter.de = Text (*.txt *.csv);;Alle Dateien (*.*)

/**
* Parse the data and return the data to be imported as a tab separated file.
*/
function exec(inText) {
  
  // parse the inText and set to outText
  // in the return text the data is tab separated
  
  var outText = "";
  outText += "Date\tDescription\tIncome\tExpenses\n";
  outText += "2015-01-01\tIncome text\t100.25\t\n";
  outText += "2015-01-02\tExpense text\t\t73.50\n";  
  
  return outText;
}

Banks Statement in Excel Format

If your bank makes available the statements in Excel format, proceed in the following way:

• Open the statement file in excel.
• Select and Copy to the clipboard content of the statement.
• When Importing in Accounting, tell Banana to use the Clipboard as input.

You can create an Import Extension for CSV that use the content of the file you copy to a clipboard.

Introduction

This page provides you with step-by-step guidance for creating a file based Javascript extension to import a typical bank statement in CSV format.

You find simple examples in the Repository China/ImportExtensions. All the examples have also their own test.

The steps in order to experiment with import Banana Extension are the following:

1. Create a CSV file example
2. Create the import file based javascript extension
3. Install the extension
4. Run the extension

Create the CSV file

For test purpose we create a file in CSV format using the format specified import income & expenses transactions in CSV format.

Copy the following CSV example, paste it on your text editor and save it as csv_example.csv:

"Date","Description","Income","Expenses"
"2019-01-01","Income transaction text","100.00",""
"2019-02-02","Expense transaction text","","200.00"

• First line is the fields header. Fields names are case sensitive and must correspond to the NameXml (English) of the columns in Banana Accounting.
• Fields names and data values are between double quotes.
• Fields and values are separated with a comma
• Each line is a new record
• The format for the Date fields is yyyy-mm-dd

Create the file based javascript import extension

Copy the following JavaScript code, paste it on your text editor and save it as import_transaction_example.js:

// @id = ch.banana.app.importtransactionexample
// @api = 1.0
// @pubdate = 2018-10-30
// @publisher = Banana.ch SA
// @description = Example Import Transactions (*.csv)
// @doctype = *
// @docproperties =
// @task = import.transactions
// @outputformat = transactions.simple
// @inputdatasource = openfiledialog
// @inputencoding = latin1
// @inputfilefilter = Text files (*.txt *.csv);;All files (*.*)

/* CSV file example:
"Date","Description","Income","Expenses"
"2019-01-01","Income transaction text","100.00",""
"2019-02-02","Expense transaction text","","200.00"
*/

// Parse the data and return the data to be imported as a tab separated file.
function exec(inText) {
   // Convert a csv file to an array of array.
   // Parameters are: text to convert, values separator, delimiter for text values
   var csvFile = Banana.Converter.csvToArray(inText, ',', '"');
   
   // Converts a table (array of array) to a tsv file (tabulator separated values)
   var tsvFile = Banana.Converter.arrayToTsv(csvFile);
   
   // Return the converted tsv file
   return tsvFile;
}

When it is used transaction.simple as @outputformat attribute in the script, it's important that CSV file includes "Income" and "Expenses" fields.

Install the Extension

For the installation of the file based extension, see First File Based Extension > Install the Extension.

Run the import Extension

To run an import Extension follow the steps below:

1. Open an accounting file in Banana Accounting.
2. In Banana select from the menu Actions the command Import to accounting...
3. From the import type selection select Transactions.
4. From the list select the Example Import Transactions (*.csv) extension.
5. Click on Browse and look for the csv_example.csv file, then click to Open.
   
   
    
6. Click Ok to begin the import process.
7. On the dialog window select a Destination account and click on Ok to import the data.
   
   
    

The data from the CSV file are imported into the Transactions table of your accounting file like the following examples.

• For a Double-Entry accounting:
  
  
  
  You can now replace all the [CA] values with the appropriate contra-account, so that the Credit transactions will be balanced with the Debit transactions.
   
• For an Income & Expenses accounting:
  
  
  
  For each transaction you can now enter an income or expense category, as defined in the Categories table.

More about Import Extensions

• Import Extensions
• Import into accounting
• Import transactions

This guide explains how to create your own import CSV extension, starting from the Banana Accounting parametrizable import template.

For more information see the import as "transactions.simple" .

Requirements

To create your import extension, you need:

• A CSV file: Ensure you can open the file to review its contents.
• The template code: Use import.csvstatement.parametrizable.template.js as your base.
• Adaptable parameters: Update the parameter values in the JavaScript code based on your CSV file structure.

Extension parameters

At the beginning of the JavaScript code, locate the getConversionParamUser() function. This is where you change parameter values based on your CSV file.

//This function defines the parameters specific for the CSV Transactions file to be imported
function getConversionParamUser(convertionParam) {
    
    // The following variables need to be set according to the specific 
    // CSV file that will be imported
    
    // Column separator character 
    // Use '\t' for tab separated columns. 
    // Use the '\t' when processing CSV copied from Excel to the clipboard
    convertionParam.column_separator = ';';

    // Text delimiter character for string
    convertionParam.text_delimiter = '"';

    // Decimal separator charachter used for amounts
    convertionParam.amounts_decimal_separator = '.';

    // Line number where the column header starts (with the columns name)
    // First line is 0
    convertionParam.header_line_start = 0;

    // Line number where data starts 
    // Usually header_line_start + 1
    convertionParam.data_line_start = 1;

    // Column name header for the date transaction 
    convertionParam.column_date_name = '';

    // Date format for column containing dates 
    // For example 'dd.mm.yyyy', 'mm/dd/yyyy', 'dd.mm.yy', 'yyyy-mm-dd'
    convertionParam.date_format = '';

    // Column name for the column description 
    convertionParam.column_description_name = '';

    // Column name for the income amount 
    convertionParam.column_income_name = '';

    // Column name for the expenses/outcome amounts 
    convertionParam.column_expenses_name = '';
    
    // Column name for the external reference identification number of the transaction
    convertionParam.column_external_reference_name = '';
}

Parameter Descriptions

The parameters are in an object called convertionParam and are as follows:

• convertionParam.column_separator
  Parameter used to specify the separator character used in the CSV file to separate the columns (e.g., ;).
• convertionParam.text_delimiter
  Parameter used to specify the text delimiter character used in the CSV file to delimit the text (e.g., ").
• convertionParam.amounts_decimal_separator
  Parameter used to specify the amounts decimal separator used in the CSV file to indicate the decimals of the amounts (e.g, .).
• convertionParam.header_line_start
  Parameter used to specify at which row of the CSV file is the header with the column titles.
  Zero-based index (start counting from 0) of the row containing column titles (e.g., 0 for the first row).
• convertionParam.data_line_start
  Parameter used to specify at which row of the CSV file start the transactions rows.
  Zero-based index (start counting from 0) of the first row containing transaction data (e.g., 1 if it follows the header).
• convertionParam.column_date_name
  Parameter used to specify the name for the column with the date of the transaction used in the CSV file.
• convertionParam.date_format
  Parameter used to specify the format of the date used in the CSV file (e.g., dd.mm.yyyy for 31.12.2024).
• convertionParam.column_description_name
  Parameter used to specify the name for the column with the description of the transaction used in the CSV file.
• convertionParam.column_income_name
  Parameter used to specify the name for the column with the income amount of the transaction used in the CSV file.
• convertionParam.column_expenses_name
  Parameter used to specify the name for the column with the expenses amount of the transaction used in the CSV file (only if using two-column format).
• convertionParam.column_external_reference_name
  Parameter used to specify the name for the column with the identification number of the transaction used in the CSV file.

CSV amount columns format

The CSV file can have two format for amount columns:

• one-column amounts: income is positive, expenses are negative.
  In this case use only the convertionParam.column_income_name to define the column name used for the amounts.
• two-column amounts: income and expense amounts have separate columns.
  In this case use both convertionParam.column_income_name for income amounts and convertionParam.column_expenses_name for expenses amounts.

Example

Sample CSV file contents:

xxxx;;;;;
;;;;;
;;;;;
Datum;Buchungstext;Betrag;Saldo;Valuta;Id
03.01.2024;"aaa";127.2;42282.99;03.01.2024;1
04.01.2024;"bbb";-165.75;42117.24;04.01.2024;2
05.01.2024;"ccc";90.05;42207.29;05.01.2024;3

CSV file structure:

• ; is used as column separator.
• " is used as text delimiter (e.g., "aaa").
• . is used as amounts decimal separator (e.g., 127.2).
• 3 is the row where the header line starts (start counting from 0).
• 4 is the row where the data start (start counting from 0).
• Datum is used as column name for the date.
• dd.mm.yyyy is used as date format (e.g., 03.01.2024).
• Buchungstext is used as column name for the description.
• Betrag is used as column name for the amounts (one-column format: income amounts are positive, expenses amounts are negative).
• Id is used as column name for the external reference identification numbers (e.g., 1,2,3).

Updated parameters:

// example with setting for the CSV file above
function getConversionParamUser(convertionParam) {
    convertionParam.column_separator = ';';
    convertionParam.text_delimiter = '"';
    convertionParam.amounts_decimal_separator = '.';
    convertionParam.header_line_start = 3;
    convertionParam.data_line_start = 4;
    convertionParam.column_date_name = 'Datum';
    convertionParam.date_format = 'dd.mm.yyyy';
    convertionParam.column_description_name = 'Buchungstext';
    convertionParam.column_income_name = 'Betrag';
    convertionParam.column_expenses_name = '';
    convertionParam.column_external_reference_name = 'Id';
}

How to Create and Run the Parameterizable CSV Import Extension

There are two methods to create an import extension:

• As an Embedded Extension.
• As a File Based Extension.

Embedded Extension

For detailed instructions on creating and running embedded extensions, refer to the First Embedded Extension documentation.

Follow these steps to create and run the extension:

1. Creating the Extension
   • Add the extension to the Documents table.
   • Copy and paste the template code from import.csvstatement.parametrizable.template.js.
2. Customizing the Extension
   • Adjust the parameters within the getConversionParamUser() function.
   • Open the CSV file to inspect its content and edit the parameter values based on the CSV structure.
3. Running the Extension
   • Save the accounting file.
   • Run the extension from the Documents table by clicking the run icon in the Attachments cell containing your code.
   • Select the CSV file to import.
   • In the Import Transactions dialog, choose the destination account and confirm with OK.
   • The transactions will be imported and displayed in the Transactions table.

File-Based Extension

For detailed instructions on creating and running file based extensions, refer to the First File Based Extension documentation.

Follow these steps to create and run the extension:

1. Creating the Extension
   • Copy and paste the template code from import.csvstatement.parametrizable.template.js.
   • Save the file as .js.
2. Customizing the Extension
   • Adjust the parameters within the getConversionParamUser() function.
   • Open the CSV file to inspect its content and edit the parameter values based on the CSV structure.
3. Installing the Extension
   • Install the extension from the menu: Extensions > Manage Extensions > Add from file.
4. Running the Extension
   • Run the extension from the menu: Actions > Import to accounting > Import: Transactions.
   • In the Import to accounting dialog, select the "Parameterizable Import CSV" extension and the CSV file to import.
   • In the Import Transactions dialog, choose the destination account and confirm with OK.
   • The transactions will be imported and displayed in the Transactions table.