Retrieving data from other programs

One time data importing from other programs using Excel

If you are starting to work with Banana for the first time and you need to import your data from another software, it is probably faster to rely on Excel to copy and paste data.

1. With your old accounting software, export the data in a format that can be read by Excel (CSV, txt, ...)
2. Import the data into Excel (see below) 
3. In Excel, organize the columns in the same order as they appear in the Banana Transactions table.
   Instead of changing column layouts, it may be more convenient to add columns next to existing ones and copy the data into them, or to use formulas that take the data and convert it. Using formulas you can remove spaces, change text or convert amounts and dates.
4. Open your new accounting file in Banana Accounting.
5. In Excel, select the data to be copied and use the Copy command (Ctrl+C or Copy from Edit in Menu).
6. Go in Banana and execute the Paste command ((Ctrl+V or Paste from Edit in the menu).

This method is useful to transfer data from MS-Money, Intuit Quicken and Quickbooks, and from most other accounting programs.

Importing bank transactions

In this case, it is useful to use one of the import functions already available.

• See: Import transactions

If there is no import extension suitable for your case, you can also use the 'one time' import mode indicated above.

Example of CSV bank file import via EXCEL

If you need to import bank data and there is no import extension, you can also proceed via Excel, following the example explained here.

The aim is to load the data into Excel and transform it exactly as it is arranged in the Banana Transactions table, then select and copy and paste it.

1. Create a new empty file in Excel.

• Go to "Data"
• From Test/CSV
• Select the file to import
• Adjust the parameters that Excel proposes.
• If necessary, use the 'Transform Data' button at the bottom to make further modifications.
• Confirm with "Charge"

   2. To the right of the data, add column headings, as they are in the Transactions table of the accounting.

• Date
• Doc
• Description
• Debit A/C
• Credit A/C
• Amount

  3.  In the first line of data, add the formulas for taking and converting bank data.

• Date         
  (resumes the desired date)       
  =C2
• Doc        
  May be left blank, but could be numbered.
• Description
  (resumes the desired description)       
  =B2
• CtDebit        
  Add the bank account number, for example "1010",  but only if the amount is positive.        
  =IF(E2>0;"1010","")
• CtCredit      
  Add the Bank account number, for example "1010", only if the amount is negative.         
  =IF(E2<0;"1010","")
• Amount  
  Take the value in negative.
  =ABS(E2)
• Copy the formulas created in all subsequent rows where there is data.
• Select the data to be copied into Excel and issue the Copy command.
• Move to the Transactions table of the accounting file, to the first empty row at the end of the table in the date column.
• Give the command "paste".

Multi-currency accounting columns

If the accounting is multi-currency add the columns as well.

• Currency initial
• Amount in base currency.           
  If you have the amount in currency and in base currency, the exchange value is calculated by Banana Accounting.

Further advice

• If import columns do not contain thousand separators, Excel may not convert them into numbers. In this case, select the entire column and use the "Replace" command and then insert the thousands separator.
• If the data is sorted in reverse order from the way you keep it in Banana, before proceeding with the mapping of the fields in the Date column, click the little arrow on the right and sort from oldest to newest.
• You can add the columns you need at will.
• With Excel's Office Scripts you can possibly also create a macro to automate the file conversion.

Recurring data import from other programs

See: Import into accounting

It may also be necessary to import data regularly:

• To import monthly transaction from your salaries software.
• To import the invoices issued with a invoicing software.
• To import the payments made with your invoicing software.
• To import your sales transactions from your online shop.
• To import data from another accounting software.
• To import data from another cashbook management software (for example a restaurants or a hotel).

In these cases you can proceed in two different ways:

1. Make sure that your application exports the data in a txt (TSV) file format specifically set to be imported in Banana.
   For more information please consult the technical information on how to create a file to be imported in Banana;
2. Create an import script that will transform data exported from another program so that they can be imported in Banana;
   • Check whether there is an extension already prepared.
     See: Import extensions.
   • Create an Import Extension.
     See: Develop an Import Extension