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:

• With an Import Extension of Banana Accounting 
  Import Extensions "transactions.simple" Income & Expenses for Digital Bank Statements
• By using an AI Assistant that transform the Bank CSV to the TSV "Transaction.Simple" format.

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.