Excel Banana Accounting Functions Add-in

The free Banana Accounting Functions add-in allows you to retrieve and view accounting data in Excel through the use of simple formulas.

The main benefits are as follows:

• Dynamically retrieve data from Banana Accounting.
• When the accounting file is changed you can instantly update the Excel sheet with the new values.
• You no longer have to rewrite data in Excel via import or copy-paste.
• Formulas are easy to use and allow you to create powerful spreadsheets in Excel to analyze and present accounting data.

To read and retrieve data from Banana Accounting, the add-in uses Banana Integrated Web Server with the API Version V2.

Prerequisites

To use the Banana Accounting Functions add-in you need to:

• Download and install Banana Accounting Plus (version 10.1.7 or higher).
• Have the Advanced plan of Banana Accounting Plus.
• Use Microsoft Excel for Windows or Mac (desktop version Microsoft 365, 2019 or newer).

 How to get started

To read and retrieve data from Banana Accounting, the add-in uses Banana Integrated Web Server with the API Version V2. So you have to configure the webserver in both Banana Accounting and indicate in the addin the connection parameters.

1. Download and install Banana Accounting Plus (version 10.1.7 or higher).
2. Configure the Banana Web Server.
   • Setup of the Banana Web Server ( Windows )
   • Setup of the Banana Web Server ( macOS )
3. Start Banana Accounting Plus and activate the Web Server.
4. Download the two sample accounting files and open them with Banana Accounting Plus.
   • [Download] current year accounting file (company-2024.ac2)
   • [Download] previous year accounting file (company-2023.ac2)
5. Download the already prepared Excel file and open it.
   • [Download] BananaFunction Excel file.
6. Install the add-in.
7. Check the add-in settings.
8. In yellow cells of the Start sheet enter the file names of the downloaded sample accounting files. In the other sheets you can see the data taken from the accounting.
9. When you update your accounting in Banana, re-enter the file name in yellow cells to recalculate all your formulas.

 Install the Excel add-in

1. Open Excel.
2. Make sure you're signed in to Office with your Microsoft user account.
   • Open Excel and, in the top right corner, click Sign in.
   • Type the email address and password you use with Office Microsoft user account.
3. Click on Insert > My Add-ins.
4. Click on Store.
5. In the store, search for "Banana".
6. Select the add-in Banana Accounting Functions and click Add.
7. The add-in is added the Home section in Excel.
   You can now find the add-in in the Insert > My Add-ins section.
   From there, if you want you can also remove it by clicking on the three dots in the upper right corner of the add-in and then on Remove.
   
   
    
8. Click on the add-in icon.
9. The add-in panel opens on the right side of Excel.
   
   
    

 Add-in settings

When you click on the Banana Accounting Functions add-in icon in Excel, a side panel opens. Here you can set some parameters to allow the add-in to connect to the Banana Web Server (make sure you configure the Web Server first).

The settings are the following:

• Server information
  Set the URL of the Banana Web Server:
  • On Windows, select http://localhost:8081.
  • On macOS, select https://127.0.0.1:8089.
• Access Token
  Enter the security password used when connecting to the Banana Web Server. It is necessary to set it first via Banana Web Server configuration (file httpconfig.ini > accessToken).
  • Setup of the Banana Web Server ( Windows )
  • Setup of the Banana Web Server ( macOS )

After you entered the web server URL and the password, click on "Test connection" to apply the changes and test the connection with the Banana Web Server.
In case of problems, see Error messages > Add-in errors for more information.

You can also change the language by selecting the one you prefer from English, Italian, French and German.

Once finished with the settings, you can also close the add-in panel. You do not need to keep it open to use the Banana Accounting Functions in Excel.

The Start sheet

The sample Excel file has a sheet named Start. It is used to enter the accounting files you want to retrieve data from.

How to use the Start sheet:

• In the yellow cells enter the names of the accounting files you want to retrieve data from.
  You can enter the current year file and the previous years files.
  To recalculate, re-enter the file name or double click on file name and press ENTER.
• As soon as you enter the file names, a function checks the connection to the files.
  The files must be open in Banana.
  If the connection is okay, the file names are displayed in cells called File0, File1, and File2. If not, see the section Error messages > Excel errors for more information.
• The cells File0, File1 and File2 are used as a reference for your accounting file names in all your formulas.
  This means that in formulas you can use File0, File1 and File2 as file names (File0 for current year, File1 for previous year, File2 for current year-2).

Add the Start sheet to a new Excel file

In case you do not want to use the sample file, you can also add the Start sheet to any Excel file:

• Create a new empty Excel file.
• Click on the add-in icon to open the side panel.
• Click Add Start sheet.
• The Start sheet is added to the Excel file you are currently working on.
  Note: if the Excel file already has a sheet named "Start", this will be replaced.

How to create your own Excel file

• Download the already prepared Excel file and save it under another name, or create a new empty Excel file and use the "Add Start sheet" command of the add-in to create the Start sheet.
• Open your accounting files in Banana.
• In the yellow cells of the Excel file ("Start" sheet), replace the sample file names with the names of your accounting files.
• Change the spreadsheets as needed.
• Recalculate Excel data: double-click on the yellow cells where you entered the file names and immediately press ENTER. This will recalculate all your formulas.

File Name

Almost all Banana Accounting Functions require the accounting file name as first parameter. It can be:

• A string with the full name of the file in quotation marks (e.g., "company-2024.ac2").
• A reference to a cell that contains the file name.

It is best to use the reference to a cell that contains the file name. This way you can use the same Excel file for different years as well. You will only have to enter the new file name in one cell, without change the name in each formula you entered.

The best way is the one used in the example file (Start sheet):

• The current year file name is taken as a reference from the cell called File0.
• The previous year file name is taken as a reference from the cell called File1.
• The file name of two previous years is taken as a reference from the cell called File2.

These cells called File0, File1, and File2 contain the function BA.FileName which checks the connection with a file:

• If the file is open in Banana and the add-in can connect with the web server, the function returns the name of the file. All the other functions will return values taken from accounting.
• If the file is not open in Banana or the add-in cannot connect with the web server, the function returns empty. All the other functions will not return any value (see the section Error messages > Excel errors for more information).

Period

Many functions use period as an optional parameter. It can be:

• An empty string or not present at all.
  The accounting period used will be the one defined in File > File and accounting properties > Accounting.
• A period with a start date and an end date in the form "yyyy-mm-dd/yyyy-mm-dd" (e.g.“2024-01-01/2024-01-31”).
  To create a period from two dates in Excel, we use the function BA.CreatePeriod.
• Abbreviation.
  Using an abbreviation you can use the same Excel with accounting files of different periods.
  • M + number of the month (e.g. "M1", "M2", ...).
  • Q + quarter number (e.g. "Q1", "Q2", ...).
  • Y + year number (e.g. "Y1", "Y2", ...).

Banana Accounting Functions

Banana Accounting Functions are formulas for use in Excel. The composition of the formulas is the following:

• Starts with "BA."
• Then the name of the function.
• Then the parameters of the function.
  Parameters can be entered as a reference to another cell or written manually between double quotes (e.g. "1000", "2024-01-01/2024-01-31", ...).
  The parameters in square brackets are optional.

When you start typing "=BA." in a cell, you see all the available Banana functions.

Select the function you want to use, enter the parameters required by the function, and finally press ENTER.

In the cell you will see the value returned by the function.

 BA.FunctionsVersion()

Returns the date of the current published version of the add-in.

 BA.FileName(fileName)

Returns the file name or an empty string if the specified file is incorrect or not found.

Parameters:

• fileName: name of the accounting file.

Examples:

• =BA.FileName(C3)
• =BA.FileName("company-2024.ac2")

You should use the cells that contain the result of this function as a parameter for the filename of all other functions that return accounting data.
If it returns an empty string, only a single call to the web server is made.

 BA.CreatePeriod(startDate, endDate)

Returns the period string according to the format used by Banana (yyyy-mm-dd/yyyyy-mm-dd).

Parameters:

• startDate: start date of the accounting period.
• endDate: end date of the accounting period.

The two dates must be a reference to cells that contain the dates.

Alternatively, you can use the Excel DATEVALUE function to convert a date that is stored as text to a serial number that Excel recognizes as a date.

Examples:

• =BA.CreatePeriod(D4, D5)
  Returns "2023-01-01/2023-12-31"
• =BA.CreatePeriod(DATEVALUE("01.01.2023"), DATEVALUE("31.12.2023"))
  Returns "2023-01-01/2023-12-31"

 BA.StartPeriod(fileName, [period])

Returns the date of the start period.

Parameters:

• fileName: name of the accounting file.
• period (optional): the period from which to take the start date.

Examples:

• =BA.StartPeriod(File0)
  Start date of the accounting period.
• =BA.StartPeriod(File0, "M1")
  Start date of the first month.
• =BA.StartPeriod(File0, "Q2")
  Start date of the second quarter.

 BA.EndPeriod(fileName, [period])

Returns the date of the end period.

Parameters:

• fileName: name of the accounting file.
• period (optional): the period from which to take the end date.

Examples:

• =BA.EndPeriod(File0)
  End date of the accounting period.
• =BA.EndPeriod(File0, "M1")
  End date of the first month.
• =BA.EndPeriod(File0, "Q2")
  End date of the second quarter.

 BA.Info(fileName, sectionXml, idXml)

Returns information regarding the file and file properties.
To see this information in Banana: menu Tools > File Info, Complete view.

Parameters:

• fileName: name of the accounting file.
• sectionXml: can be a value specified in the "Section Xml" column of the "Info file" table.
• idXml: can be a value specified in the "ID Xml" column of the "Info file" table.

Examples:

• =BA.Info(File0, "Base", "HeaderLeft")
• =BA.Info(File0, "Base", "HeaderRight")
• =BA.Info(File0, "AccountingDataBase", "Company")
• =BA.Info(File0, "AccountingDataBase", "OpeningDate")
• =BA.Info(File0, "AccountingDataBase", "BasicCurrency")

 BA.AccountDescription(fileName, account, [column])

Returns the description of the specified account or group in the Accounts table.

Parameters:

• fileName: name of the accounting file.
• account: account or group in the Accounts table.
• column (optional): you can return another column (name XML) instead of the Description column.

Examples:

• =BA.AccountDescription(File0, "1000")
  Description of account 1000
• =BA.AccountDescription(File0, "Gr=10")
  Description of group 10
• =BA.AccountDescription(File0, "1000", "Gr1")
  Content of column Gr1 of account 1000
• =BA.AccountDescription(File0, "1000", "Notes")
  Content of column Notes of account 1000

 BA.Amount(fileName, account, [period])

Returns the normalized amount based on the BClass.
Only work for double entry accounting only. For Income and expenses accounting use BA.Balance or BA.Total.

• for accounts of BClass 1 or 2 it return the balance (value at a specific instant).
• for accounts of BClass 3 or 4 it return the total (value for the duration).
• For accounts of BClass 2 and 4 the amount is inverted.

You can use this functions also with groups provided you assign a BClass also to a group.

Parameters:

• fileName: name of the accounting file.
• account: account, group, cost center or segment in the Accounts table.
• period (optional): the period.

Examples:

• =BA.Amount(File0, "1000")
• =BA.Amount(File0, "1000", "2024-01-01/2024-12-31")
• =BA.Amount(File0, "1000", "M1")

 BA.Balance(fileName, account, [period])

Returns the Balance at the end of the period of the indicated account, cost center, groups, segments
The BA.Balance result is the sum of the BA.Opening + BA.Total.
It is used for retrieving accounting data for the Balance Sheet accounts (Assets, Liabilities).

• Single account number ("1000").
• Several accounts summed toghether.
  Enter the accounts numbers separated by the character "|" ("1000|1001").
• You can specify normal accounts, cost centers or segments.
• You can also use wild cards and also use "Gr=" followed by the accounting group.
• For more information see the Javascript function description for currentBalance
• Example

Parameters:

• fileName: name of the accounting file.
• account: account, group, cost center or segment in the Accounts table.
• period (optional): the period.

Examples:

• =BA.Balance(File0, "1000")
  Balance of account 1000
• =BA.Balance(File0, "1000", "2020-01-01/2020-12-31")
  Balance of account 1000 using the period
• =BA.Balance(File0, "1000|1010")
  Balance of account 1000 and 1010 are summed together
• =BA.Balance(File0, "10*|20*")
  All account that start with 10 or with 20 are summed toghether
• =BA.Balance(File0, "Gr=10")
  Balance of group 10
• =BA.Balance(File0, "Gr=10|20")
  Balance of group 10 and 20 are summed together
• =BA.Balance(File0, ".P1")
  Balance of the cost center .P1
• =BA.Balance(File0, ";C01|;C02")
  Balance of cost center ;C01 and ;C02 are summed together
• =BA.Balance(File0, ":S1|S2")
  Segment :S1 and :S2
• =BA.Balance(File0, "1000:S1:T1")
  Balance of account 1000 with segment :S1 or ::T1
• =BA.Balance(File0, "1000:{}")
  Balance of account 1000 with segment not assigned
• =BA.Balance(File0, "1000:S1|S2:T1|T2")
  Balance of account 1000 with segment :S1 or ::S2 and ::T1 and ::T

 BA.Opening(fileName, account, [period])

Returns the Balance at the beginning of the period for specified account.

Parameters:

• fileName: name of the accounting file.
• account: account, group, cost center or segment in the Accounts table.
• period (optional): the period.

Examples:

• =BA.Opening(File0, "1000")
  Opening balance of account 1000
• =BA.Opening(File0, "1000", "M1")
  Opening balance of account 1000 at the beginning of the period M1
• =BA.Opening(File0, "1000", "Q2")
  Opening balance of account 1000 at the beginning of the period Q2

 BA.Total(fileName, account, [period])

Returns the movements for the period indicated ("Debit - Credit" difference).
Should be used to retrieve the data for the Profit and Loss accounts (Cost and Revenues).

Parameters:

• fileName: name of the accounting file.
• account: account, group, cost center or segment in the Accounts table.
• period (optional): the period.

Examples:

• =BA.Total(File0, "4100")
  Movements balance of account 4100
• =BA.Total(File0, "Gr=3", "M1")
  Movements balance of group 3 for the period M1
• =BA.Total(File0, "Gr=4", "Q2")
  Movements balance of group 4 for the period Q2

 BA.Interest(fileName, account, interestRate, [period])

Calculate the interest for the indicated account and the period.

Parameters:

• fileName: name of the accounting file.
• account: account can be any account as specified in BA.Balance.
• interestRate: the interest in percentage:
  • > 0 calculate the interest on the debit amounts
  • < 0 calculate the interest on the credit amount
• period (optional): the period.

Examples:

• =BA.Interest(File0, "1000", "5")
  Interest 5% of account 1000
• =BA.Interest(File0, "1000","5", "M1")
  Interest 5% of account 1000 for period M1
• =BA.Interest(File0, "2000", "-5")
  Interest -5% of account 2000
• =BA.Interest(File0, "2000", "-5", "M1")
  Interest -5% of account 2000 for period M1

 BA.VatBalance(fileName, vatCode, vatValue, [period])

Returns a value regarding the specified VatCode (or multiple VatCodes).

Parameters:

• fileName: name of the accounting file.
• vatCode: the VAT code.
• vatValue: can be "taxable", "amount", "notdeductible", "posted".
• period (optional): the period.

Examples:

• =BA.VatBalance(File0, "V10", "taxable")
• =BA.VatBalance(File0, "V10|V20", "posted")
• =BA.VatBalance(File1, "V10", "taxable")

 BA.VatDescription(fileName, vatCode, [column])

Returns the description of the specified VAT code in the VatCodes table.

Parameters:

• fileName: name of the accounting file.
• vatCode: the VAT code.
• column (optional): you can return another column (name XML) instead of the Description column.

Examples:

• =BA.VatDescription(File0, "V10")
  Description of VAT code V10
• =BA.VatDescription(File0, "V10", "VatRate")
  VAT rate of VAT code V10

 BA.BudgetAmount(fileName, account, [period])

Same as BA.Amount but uses budget data instead of accounting data.

 BA.BudgetBalance(fileName, account, [period])

Same as BA.Balance but uses budget data instead of accounting data.

 BA.BudgetOpening(fileName, account, [period])

Same as BA.Opening but uses budget data instead of accounting data.

 BA.BudgetTotal(fileName, account, [period])

Same as BA.Total but uses budget data instead of accounting data.

 BA.BudgetInterest(fileName, account, interestRate, [period])

Same as BA.Interest but uses budget data instead of accounting data.

 BA.CellValue(fileName, table, rowColumn, column)

Returns the content of a table cell as a text.

Parameters:

• fileName: name of the accounting file.
• table: the XML name of the table (Accounts, Categories, Transactions, Budget, Totals, VatCodes,...).
• rowColumn: the row of the table.
• column: the XML name of the column (Group, Account, Description, Notes,...).

Esempi:

• =BA.CellValue(File0, "Accounts", 2, "Description")
  Text cell of table Accounts, row 2, column Description
• =BA.CellValue(File0, "Accounts", "Account=1000", "Description")
  Text cell of table Accounts, row where the account is 1000, column Description
• =BA.CellValue(File0, "Accounts", "Group=10", "Description")
  Text cell of table Accounts, row where the group is 10, column Description

 BA.CellAmount(fileName, table, rowColumn, column)

Retrieve the content of a table cell as an amount.

Parameters:

• fileName: name of the accounting file.
• table: the XML name of the table (Accounts, Categories, Transactions, Budget, Totals, VatCodes,...).
• rowColumn: the row of the table.
• column: the XML name of the column (Group, Account, Description, Notes,...).

Examples:

• =BA.CellAmount(File0, "Accounts", 2, "Opening")
  Amount cell of table Accounts, row 2, column Opening
• =BA.CellAmount(File0, "Accounts", "Account=1000", "Balance")
  Amount cell of table Accounts, row where the account is 1000, column Balance
• =BA.CellAmount(File0, "Accounts", "Account="&$A4, "Balance")
  Amount cell of table Accounts, row where the account is the value $A4 (Excel cell reference), column Balance
• =BA.CellAmount(File0, "Accounts", "Group=10", "Balance")
  Amount cell of table Accounts, row where the group is 10, column Balance

 Error Messages

Excel error

When entering accounting file names in yellow cells, the following message may be displayed next to the yellow cell:

• Banana not open, file not open or WebServer not active.
  It means that the Banana Web Server cannot find the file you entered.
  • Make sure you enter the accounting file name correctly.
  • Make sure Banana Accounting+ is open.
  • Make sure to open the accounting file you entered.
  • Make sure the Banana Web Server is active.

Add-in errors

In some cases red messages may appear at the bottom of the add-in. The messages are the following:

• Download and install Banana Accounting+ (version 10.1.7 or later).
  It means that you cannot use the add-in with versions of Banana Accounting earlier than the one indicated.
  • Download and install the latest version of Banana Accounting Plus.
• Banana Web Server Connection Failed. Banana version not supported, Banana not open, Web Server not active or wrong add-in server settings.
  It means that the connection with the Banana Web Server is not working. The causes may be different as indicated.
  • Make sure you are using version 10.1.7 (or later) of Banana Accounting Plus. Download the latest version of Banana Accounting Plus.
  • Make sure that Banana Accounting Plus has been started.
  • Make sure that the Banana Web Server is active and correctly configured. See the Web Server configuration:
    • Setup of the Banana Web Server ( Windows )
    • Setup of the Banana Web Server ( macOS )
  • Make sure the file names entered in the yellow cells in Excel are correct.
  • Make sure the file you entered in Excel are open in Banana.
  • Make sure you have set the server information correctly in the add-in settings:
    • See Check add-in settings
• Unauthorized connection. Missing or wrong Access Token.
  It means the password for the access token is missing or is wrong, so the connection with the Banana Web Server cannot be established.
  • During the Banana Web Server configuration, define a password for the "accessToken" in the "httpconfig.ini" file. See the Web Server configuration:
    • Setup of the Banana Web Server ( Windows )
    • Setup of the Banana Web Server ( macOS )
  • In the add-in Settings > Access Token, you have to enter the same password.

Release History

• 2023-07-21 First release.
• 2023-09-20
  • Added a command to test the connection with the Banana Web Server.
  • Selecting a language now reloads the add-in panel immediately.
  • Added a command to create the Start sheet.