Import Extensions "transactions.simple" Income & Expenses for Digital Bank Statements

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