Developer info for importing data in Banana Accounting

Banana Accounting is a an accounting software that run on the client computer and it is not a cloud solution and there is not an internet service that is used to add data to the accounting.

When integrating data there are two choices:

Import the file in Banana Accounting

You can import in Banana Accounting by using one of the predefined formats or by using an Import  extension.

Complete and modify imported transactions

After any data is imported in the accounting file the user can add more information, modify the existing one or also undo the import. 
That makes very easy to test import but also to automate tasks. You don't have to define all possible cases. The user can take care of exceptions and changes.

Create an export file from your application

This is the most current use case, where your application directly create and export file that can be easily imported in Banana Accounting. 

By providing a Banana Accounting export file, your application can be appealing to a great number of Banana Accounting users. You can focus on enhancing you application and don't need to dedicate time to develop an accounting application.

Create an Import Extensions for converting from other formats

Most software have the choice to export data in a predefined format and don't give you the chance to create an export format specific to Banana Accounting. By creating an Import Extension the imported file will be converted to the Banana Accounting format during the Import Accounting process.

This is a best solution when you are using a standard software solution and you need to integrate data in Banana Accounting or in case the customer need a customized solution, for example for automating some aspects. 
For this case you can:

Use free version for test purpose

For the development and testing the import you can download the Banana Accounting software version available on our web site. You can use all functionalities and save up to 70 transactions. This version is normally sufficient for the development and testing process.

In case you are an independent software developer and need full functionalities we can provide a time-limited full license.
Request must come with a link to the developer web site.

Cloud integration

Banana Accounting is a client solution that runs on the user computer. The accounting file  is saved on the client computer.

There is not an API that allow to integrate data from other cloud services.
If you need to integrate data from a cloud solution:

  • Export the data in a local file.
  • Import the data in Banana Accounting as explained above.

Community

If you are a software developer that provide solutions that integrate or extend Banana Accounting we invite you to also look at the benefits available with our Community.

 

 

 

 

 

 

Import "Text file with columns header"

The Banana import format is of type "Text file with column headers".

  • Fields and column name separator is to be tab separated "\t"
  • Each line (after a "\n") is a new record
  • Character code preferably UTF8 or else the local one.
  • The first line contains the columns header name
    • You can use any column name existing on the table
    • Names are case sensitive and must correspond to the Banana NameXml (English) of the column. You can find the NameXml of a column by clicking on the header of the column and moving under Settings, like in the example:

    XmlName

  • Starting from line 2 it contains the data to be imported
    • The format for the Date fields is yyyy-mm-dd
    • The decimal separator is the decimal point "."
    • Amount should not have any thousand separator

File format example:

Date	Doc	Description	AccountDebit	AccountCredit	Amount
					
2023-10-12	1	Description 01	1020	2800	2000.00
2023-10-19	2	Description 02	6580	1000	300.00
2023-10-12	3	Description 03	1000	2800	3240.00
2023-10-19	4	Description 04	6570	1020	700.00

Import Accounts

For creating new accounts, customers, suppliers, cost centers. See Chart of accounts documentation for the list of columns available.

Menu Actions->Import into accounting->Accounts

The type of file to be used is a "TXT with headers.

  • You can use any column name existing on the table
    • Account.
      The account number.
    • Description
      A brief text, organization or customer name
    • BClass
      Required (1,2,3,4).
    • Gr1
      Obligatory. It is also used to order the data when it is imported.
    • Address fields.
  • Fields header in the first line of the file.
    Fields names are case sensitive and must correspond to the NameXml (English) that you find in the Setting Tab of the Columns setup.
  • Fields header and field data must use the tab character as separator "\t"
  • Each line (after a "\n") is a new record
  • The format for the Date fields is yyyy-mm-dd
  • Character code preferably UTF8 or else the local one.

When importing the user can choose to import only the new lines.

Import Double-entry transactions in CSV format

For what is concerning the specifics of the import of Double-entry see the explanations for Import Double-entry accounting transactions.

Menu Actions → Import into accounting

The type of file to be used is a "Text file with column headers".

File format and Main columns for import

For a double entry accounting files. 

  • You can use any column that is available in the Transactions table
    (See: Transactions Table double entry) and you can see in the Column Setup list of columns.
  • Main columns for double entry accounting
    • Date of the transaction (2014-12-31).
    • Description a brief text.
    • AccountDebit the account number of the customer or the general account for customers.
    • AccountCredit the account number of the revenue account.
    • Amount the amount of the accounting currency.
    • VatCode the vat code that should be used.
    • AmountCurrency if multi-currency the amount of the invoice in original currency and currency of the AccountDebit.
  • Fields header in the first line of the file.
    Fields names are case sensitive and must correspond to the NameXml (English) that you find in the Setting Tab of the Columns setup.
  • Fields header and field data must use the tab character as separator "\t"
  • Each line (after a "\n") is a new record
  • The format for the Date fields is yyyy-mm-dd
  • Character code preferably UTF8 or else the local one.

Example file Double-entry format

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

Double entry trans. excel example

Options

  • Import using clipboard data will use the content of the clipboard instead of the file
  • Autocomplete values: Some fields of the transactions are automatically completed (see "Importing transactions for multi-currency Double-entry accounting").
  • Unicode (utf-8) The content of the file is in Unicode utf-8 (it supports any character set). 

Importing other transaction's columns

You can import any other field that is defined in the Transactions table.
There are other values that we suggest to import if available:

  • DateDocument the date of the original document (for example the date of the invoice).
  • DocInvoice the invoice number.
  • DocOriginal the document number for example the invoice number.
  • DocLink the address of the file that links to a scanned document (pdf, jpg, ..).
    You can use a relative path name to the accounting file.
  • DateExpiration due date of the invoice.
  • ExternalReference an information that help to identify the transactions as unique.
    It will be used in future implementation of Banana (in conjunction with the date) to warn a user that the transaction has already been imported.
    This should be an external Reference generated by the software that creates the transactions to be imported.
    We suggest to use a name of the program and a number that is unique for the accounting period.
    For example "invoice-2013-00001.001" with year, invoice number and a progressive number that is relative to the invoice in case it will be necessary to have more transaction lines for the same invoice.

Importing transactions for multicurrency Double-entry accounting

By importing multicurrency data there can be rounding or calculation differences due to different development tools used. To avoid such differences you should provide only certain fields and while importing the program will calculate the field values that are missing (with the option "Autocomplete values") .

  • If you provide only "AmountCurrency" the program will use the default exchange rate and will calculate the "Amount".
  • In order to avoid error provide always the "ExchangeCurrency"
  • If you provide the "AmountCurrency"  and the "ExchangeRate" and the "Amount" are 0 or not present the program will calculate the exchange rate based on the column "Amount" and "AmountCurrency".

Importing Invoice data

The data of your invoice software can be imported in Banana.
There are two ways to do so:

  1. Let your invoice software generate a file for Banana as indicated in the "Import Double-entry transactions in txt format".
  2. Use the data of the export format of your existing invoicing software .
    In order to import this data from a proprietary format into Banana you need to create a Javascript Banana Extension  that translates the data into a format acceptable for Banana.
    The script program takes as input the content of a file and creates an output that is a tab separated text file with columns headers.
    See also repository on Github.

Invoices on multiple rows

Most invoices have different items that need to be registered in different revenue accounts or that have different VAT percentages.
In this case, for each invoice you need to have many import rows.
Date, DateDocument, DocInvoice have always the same values.

  • The first row you have the
    • AccountDebit the customer account number
    • AccountCredit is void.
    • Amount the total amount of the invoice. The amount due from the Customer.
    • VatCode is void
  • For each item with a different revenue account or Vat percentage you should have an additional row
    • AccountDebit is void
    • AccountCredit the revenue account for this item
    • Amount the total amount to be registered on this account.
      If you have a VatCode it could be convenient to use the amount without VAT.
    • VatCode the VatCode that applies to this item.
      If the Amount is Net of VAT you should define a VAT Code that is calculated on the net Value.

Group transactions by invoice number

If the imported data contains the "DocInvoice" columns, when Banana displays you a second DialogBox, you can choose to have Banana group the transactions by DocInvoice.
In this case Banana automatically creates, if necessary, a transaction for rounding differences.

Use Cost center instead for customer account

If you want to keep track of the invoices registered but do not want them to be recorded on individual accounts you can use the Cost center (CC3).  See also Client/Suppliers register.

Import Income & Expenses transactions in CSV format

This file format "Transaction.simple" is used to import transactions data from an account statement or bank statement with the amounts in plus or minus.

Use in Import into accounting

You can import the file by using:

  • Menu Actions->Import into accounting
  • Import to Transaction table
  • The type of file to be used is a "Income & Expenses transactions".

Options

  • Import using clipboard data will use the content of the clipboard instead of the file
  • Autocomplete values: Some fields of the transactions are automatically completed (see "Importing transactions for multicurrency Double-entry accounting").
    Once the import is done, the contra account will have to be entered manually.
  • Unicode (utf-8) The content of the file is in Unicode utf-8 (it supports any character set).

File format "transactions.simple" (Income & Expenses)

  • 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
  • Columns header
    • The first line of the file contains the Columns headers.
    • They specify the name of the column.
    • Column names are case sensitive.
  • After the header come the lines with the transactions data.
  • 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").

Columns "transactions.simple" 

  • 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

 

Transform a bank statement file 

Banks makes available CSV or Excel file with the transactions. 
This file can be transformed  in different way:

Information for AI assistant transforming Bank files

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 Bank statement file contains a series of transactions that correspond to the movements, income and expenses, registered by the bank. 
The structure of a Bank Statement CSV is usually composed of different part:

  • 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 usually in the user language and not in English.
    • 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). 

If necessary the AI Assistant should ask the user information regarding:

  • The thousand and decimal separator for amounts. 
    "thousand separator ', decimal "."
  • The character separator for date or the format used by date.
    For example: dd.mm.yyyy.
  • The meaning of a particular column name.