Import Income & Expenses transactions in CSV format

This "Transactions.simple" file format is used to import transaction data from an account statement or bank statement, with amounts entered as positive or negative values.

Use with Import into accounting

You can import the file by using:

• Actions menu > Import into accounting
• Import into the Transactions table
• The file type to use is "Income & Expenses transactions".

Options

• Import using clipboard data uses the contents of the clipboard instead of a file.
• Autocomplete values: Some transaction fields are completed automatically (see "Importing transactions for multi-currency Double-entry accounting").
  Once the import is complete, the contra account must be entered manually.
• Unicode (UTF-8): The file content is encoded in Unicode UTF-8, which supports any character set.

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

• Tab-separated values (TSV).
  • Column headers and data must use the tab character as the separator "\t".
  • Each line (after a "\n") is a new record.
• Column headers
  • The first line of the file contains the column headers.
  • They specify the names of the columns.
  • Column names are case-sensitive.
• The transaction data lines follow the header.
• Transaction data in column format
  • Tab-separated values following the headers.
  • Date columns should use the format "yyyy-mm-dd".
  • Amounts should use the standard JavaScript decimal number format:
    • The decimal separator is a point ".".
    • No thousands separators.
    • Numeric characters only, for example "12345678.90".
    • Negative numbers use a minus sign "-" before the number (e.g. "-100.00").

Columns in "transactions.simple" 

• Required columns
  • Date
    The date of the transaction.
    The date should use the format "yyyy-mm-dd" (e.g. "2024-12-31").
  • Description
    A brief description of the transaction.
  • Income 
    The income amount. 
    For double-entry accounting, this is the debit amount; if negative, it is treated as a credit amount. 
    The amount should use a simple numeric format, with the point "." as the decimal separator.
• Optional predefined columns:
  When present, the import function uses their contents.
  • Expenses
    The expense amount.
    For double-entry accounting, this is the credit amount.
    The expense can also be specified as a negative number in the "Income" column.
  • DocInvoice
    The invoice number.
  • ContraAccount
    The account number (debit/credit) or category.
    Enter square brackets [] to keep the field empty.
  • Account
    The transaction account when the file contains transactions for multiple accounts.
    Enter square brackets [] to keep the field empty.
  • VatCode
    The VAT code to use.
    The VatCode should be defined in the VatCodes table.
    Precede it with the minus sign "-" if the transaction is a correction of an existing transaction.
  • IsDetail
    For composed transactions, an "S" identifies a counterpart transaction and a "D" identifies a detail transaction.

Transform a bank statement file 

Banks provide CSV or Excel files containing transactions. 
These files can be transformed in different ways:

• With a Banana Accounting Import Extension 
  Import Extensions "transactions.simple" Income & Expenses for Digital Bank Statements
• By using an AI assistant that transforms a bank CSV file into the TSV "transactions.simple" format.

Information for an AI assistant transforming bank files

When converting data from a bank statement to the "transactions.simple" file format, it is important to analyze the bank statement carefully.

A bank statement file contains a series of transactions corresponding to the income and expenses recorded by the bank. 
The structure of a bank statement CSV file usually consists of different parts:

• Optional information about the account and account holder.
  • Unstructured data at the beginning of the file.
• The column headers.
  • A line containing the column names. 
    For example: "Date,Description,Income,Expense"
  • Column names are usually in the user's language and not in English.
  • Column names are separated by a character, usually a comma "," or a semicolon ";".
  • Column names often use similar terminology but are usually in a language specific to the customer. For example:
    • German: "Datum,Buchungstext,Einnahmen,Ausgaben"
• The transaction movements.
  • Multiple lines, each containing a single movement.
  • Dates may use a specific format.
  • Numbers may use 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 names for columns containing income and expense amounts. 

• Double-entry column naming convention:
  Debit and Credit usually refer to the bank's point of view: "We have debited you" and "We have credited you".
  • A debit for the customer is an expense (credit amount).
  • A credit for the customer is income (debit amount). 

If necessary, the AI assistant should ask the user for information about:

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