The steps below walk you through all the setup to run the Banana Office Add-ins for Microsoft Office 2016 or more recent.

Minimum requirements:

• Banana Accounting Plus or Banana Accounting 9.
• Microsoft Office 2016 or more recent (Word, Excel, PowerPoint, Outlook).

Get Banana Accounting

1. Download Banana Accounting for Windows or Mac.
2. Install it on your pc.

Activate Banana Accounting web server

1. Start Banana Accounting.
2. On Menu bar click Tools → Program options and select the Interface tab
3. Check the Start Web Server and Start Web Server with ssl options.
   
   
    
4. Click OK.

Load the Add-in

1. Open Excel.
2. Click on Insert tab.
3. Click on the Get Add-ins icon to open the Office Add-ins store.
   
   
    
4. In the Office store page search for "Banana" add-in.
   
   
    
5. Click on the Add button to add the Banana Accounting Excel Reports add-in.
6. As soon as the add-in is added in Excel, on the Home tab of the main ribbon is loaded the Banana Accounting add-in command.
   
   
    
7. Click on the Banana Accounting icon to use the add-in.

 

Once the add-in has been added from the Office store it is saved into My Add-ins section.
To load an add-in previously added from the office store:

1. Click on Insert tab.
2. Click on the My Add-ins icon.
   
   
    
3. Select the Banana Accounting add-in.
   
   
    
4. Click on the Add button.

 

Windows users

For Windows users, please also follow the Troubleshooting for Windows guide to complete the installation of the add-in.

macOS users

For macOS users, please also follow the Troubleshooting for macOS guide to complete the installation of the add-in.

 

 

With this add-in you can create Excel sheet that are filled with Banana Accounting data. Once you have added transactions to the accounting file you just need to click on the Update Button of the add-in and your spreadsheet content will be updated with the new data.
Your existing formatting and formula will be preserved.

1. Create an Excel sheet with headers information
   This information allows the add-in to retrieve data from Banana Accounting.
   There are information relative to the file, column and account or group to be retrieved.
   The add-in help you add the necessary information to retrieve the data.

2. Click on the Update button
   The add-in will retrieve the values from Banana Accounting software.
   It mantains the format or formula you enter.

Example of a Balance sheet report created with the Banana Accounting Excel add-in

In the example above we can see:

• The data part
  Here is where the data is synchronized, based on the QueryAccount and QueryColumns.
  • Accounting data (Green)
    Filled with the information coming from Banana Accounting
  • Header data (Yellow)
• QueryColumns (Red)
  The file name, columns names and type to retrieve.
  If the column is empty no data in this column will be retrieved. You can use the columns to enter formula.
• QueryAccounts (Orange)
  The accounts or groups to retrieve.
  If the row is empty no data in this row will be retrieved. You can use the row for entering formula o text that is not overwritten.

By clicking on the update button the Data part is updated with the new data of the accounting file, and all the previously settings like fonts, colors, formulas will remain.

Download and installation

See documentation on how to Download and install the Add-in

Example files:

• File Excel already with columns setup.
• Banana Accounting file used for the example excel file.

Add-in Command

As soon as the add-in is added in Excel, on the Home tab of the main ribbon is loaded the Banana Accounting add-in command.

Banana Accounting Add-in command

When the Banana Accounting button is clicked, it loads the start screen of the add-in. The start screen provides additional information describing the functionalities of the add-in.

Click on the Let's Begin! button to start using the add-in.

Banana Accounting Add-in start screen

Add-in general overview

The add-in is a task pane add-in type. This means that the add-in is loaded in a pane on the right side of the Excel worksheet.

It is composed by three tabs, each of them has one specific task:

• The Setup tab contains all the tools needed to add information to your sheet so that the add-in can fill the data part with the accounting data. Typically it is used every time you want to create something new, like for example the very first time you use this add-in.
• The Update tab is used to update the content of the Excel worksheet with the accounting data. It is used after the header section and some accounts/groups has been added.
• The Messages tab shows some messages about the add-in and the operations it does. For example when you update the sheet a message is displayed telling you that the update is completed.
• The Options tab is used to set some settings like the language and the server's Url.
   

Update of the worksheet

The Update tab is composed only of one button: Update current worksheet.

When clicked, this will start the updating process of the current Excel worksheet. Combining Header, QueryAccount and QueryOptions information, the add-in retrieves all the data directly from the Banana Accounting file and writes them in the Excel worksheet.

Retrieve data from Banana Accounting and update the worksheet

Setup of the worksheet

These features will add the necessary information to the current worksheet to retrieve data from Banana Accounting.

In the Setup tab there are four sections:

• Accounting file selection
• Set Header
• Set QueryColumns
• Set QueryAccounts

Setup of the worksheet tab

Select an opened Banana file

The first section of the setup page lists all the currently opened Banana Accounting files.
When the Refresh file list button is clicked, the add-in checks for all the opened Banana Accounting files and lists them.

Click the button, select a file, then go to the next setup section.

If you cannot see any file on the list and an error message is displayed, please follow the Troubleshooting for Windows or the Troubleshooting for macOS documentations.

Example of file selection

Set Header

The second section of the setup page inserts, on the top of the current worksheet, the header that allows the user to insert information that will be used by the add-in to retrieve data from the accounting file.

Add an header

The first step is to select from the list a type of header. There are two options:

• Predefined header with columns to insert an header with default values for columns and options
• Empty header to insert a blank header. In this case you must manually add columns and options values.

When the button Add Header is clicked, the selected type of header is inserted in the worksheet. It is then possible to modify it by changing the values of QueryColumns and QueryOptions.

Add header options

The second step is to define some options for the Currency, Header Left and Header Right values using the QueryOptions column. The options are:

• Repeat to repeat the values in each column
• Do not repeat to avoid repeated values. Only when the file name changes the values are entered again.

When the button Add options is clicked, the selected options will be inserted in the related cells.

Example of predefined header

Set QueryColumns

This section guides step by step the user to modify the header by adding QueryColumns to the worksheet.

The QueryColumns information allows the user to define exactly which data the add-in has to retrieve from the accounting file and in which column of the worksheet insert them.

Each QueryColumn consists of six information:

• The Column of the worksheet is used to define in which column of the worksheet all the QueryColumns values will be inserted.
• The File name is used to define the accounting file to use when retrieving data.
• The Type value is used to define the type of data of the accounting file.
• The Column value is used to define the accounting file data for the given type.
• The Segments (OPTIONAL) is used to have a more detailed classification of the costs. It is optional, if not specified none segments are added.
• The Periods (OPTIONAL) is used to define a period of the accounting. It is optional, if not specified all accounting period is automatically used.

When the button Add values to column is clicked, all the information are automatically added to the selected column of the worksheet.

You must repeat this process for each column you want to add to the worksheet.

Set QueryColumns section

Select a column of the worksheet

Use this to define in which column of the worksheet all the values of the QueryColumns are inserted. Possible values are:

• Current selected to use the colum of the cell selected on the worksheet (ex. if the cell D8 is selected, D column will be used).
• From C to Z.

The list shows only columns from C to Z, but it is also possible to use columns until AZ by adding them manually.

Select a filename

Use this to define the file name for a QueryColumn. When a file name is specified it is used until a new file name is inserted.

The possible values are:

• Current to use the selected file from the list on the top.
• Current (void) to use the previously inserted file but let the cell empty. It works only if in previous columns there is a specified file name.
• 1 previous year (p1) to use the previous year file of the last file inserted (example: if current year is "2019.ac2", p1 refers to "2018.ac2").
• 2 previous years (p2) to use two previous years file of the last file inserted(example: if current is "2019.ac2", p2 refers to "2017.ac2").
• 3 previous years (p3) to use three previous years file of the last file inserted(example: if current is "2019.ac2", p3 refers to "2016.ac2").

Filename selection

Notes:

• Remember to always open in Banana Accounting all the files specified in the header.
• The p1, p2 and p3 abbreviations always refer to the last file specified in the header.
• Use p1, p2 and p3 only if in the accounting file is used the File and accounting properties → Options → File from previous year.

Example of more file insertion

On the image above we can see there are three different files defined, each of them using different columns.

• The columns from C to G refer to the 2019.ac2 file.
• The columns from H to I refer to the 2018.ac2 file.
• The coumns from J to K refer to the previous year file of the 2018.ac2 file. This file must be specified in the File and accounting properties → Options → File from previous year of the 2018.ac2 file.

Select a Type and a Column value

Use Type and Column rows to define the data you want to retrieve from the accounting file.

• Type specify the type of data of the accounting file.
• Column specify the data of the accounting file for the given Type.

Enter the values using the add-in or writing them directly into the worksheet cells.

List of possible Type-Columns combinations (not Case-Sensitive) you must enter into the related Type and Column rows of the header:

• column
  • Group, Account, Description, Disable, FiscalNumber, BClass, Gr, Gr1, Gr2, Opening, Debit, Credit, Balance, Budget, BudgetDifference, Prior, PriorDifference, BudgetPrior, PeriodBegin, PeriodDebit, PeriodCredit, PeriodTotal, PeriodEnd, NamePrefix, FirstName, FamilyName, OrganisationName, Street, AddressExtra, POBox, PostalCode, Locality, Region, Country, CountryCode, Language, PhoneMain, PhoneMobile, Fax, EmailWork, Website, DateOfBirth, PaymentTermInDays, CreditLimit, MemberFee, BankName, BankIban, BankAccount, BankClearing, Code1
• current
  • amount, amountcurrency, balance, balancecurrency, bclass, credit, creditcurrency, debit, debitcurrency, enddate, opening, openingcurrency, periodstring, rowcount, startdate, total, totalcurrency
• budget
  • amount, amountcurrency, balance, balancecurrency, bclass, credit, creditcurrency, debit, debitcurrency, enddate, opening, openingcurrency, periodstring, rowcount, startdate, total, totalcurrency
• columnvat
  • Group, VatCode, Description, Gr, Gr1, IsDue, AmountType, VatRate, VatRateOnGross, VatPercentNonDeductible, VatAccount
• currentvat
  • taxable, amount, notdeductible, posted, rowcount
     

Below some examples of queries that can be entered in the header:

• Example 1: return from the Accounts table the value of the column description for the account specified in the QueryAccount column.
  Type: column
  Column: description
   
• Example2: return the amount of debit transactions for all the accounting period for the account specified in the QueryAccount column.
  Type: current
  Column: debit
   
• Example 3: return the opening + debit-credit from the 01.01 to 10.01.2022 for the account and segment specified in the QueryAccount column.
  Type: current
  Column: balance
  Segment: :S1
  Start date: 01.01.2022
  End date: 10.01.2022
   
• Example 4: return the difference between debit-credit for the 6th month for the account specified in the QueryAccount colum.
  Type: current
  Column: total
  Start date: M6
   
• Example 5: return the difference between debit-credit for the second quarter for the account specified in the QueryAccount column.
  Type: current
  Column: total
  Start date: Q2
   
• Example 6: return from the Vat Codes table the value of the column description for the vat code specified in the QueryAccount column.
  Type: columnvat
  Column: description
   
• Example 7: return the amount of the taxable column for the vat code specified in the QueryAccount column.
  Type: currentvat
  Column: taxable
   

Select a Segment (optional)

If the selected file has segments they will appear in the list.

Use this to define a segment to have a more detailed classification of the costs.

Select a period (optional)

Use this to define the accounting period that will be used to retrieve data from the accounting file.

Possible values are:

• All (void) to use all the accounting period.
• Custom date to specify a custom period (example: Start date "04.02.2022",  End date "12.03.2022").
• Month 1 (M1) ... Month 12 (M12) to specify a single month (example: M1 for 1st month, M2 for 2nd month, etc.).
• Quarter 1 (Q1) ... Quarter 4 (Q4) to specify a single quarter (example: Q1 for the 1st quarter, period from 01.01 to 31.03).
• Semester 1 (S1) ... Semester 2 (S2) to specify a single semester (example: S2 for the 2nd semester, period from 01.07 to 31.12).
• Year 1 (Y1) ... Year 10 (Y10) to specify a single year (example: Y1 for the 1st year).

Set QueryAccounts

This section provides to insert:

• QueryAccounts to specify all the desired accounts, groups, cost centers, segments or vat codes that will be used with the data specified in the header to retrieve the accounting data.
• QueryOptions (OPTIONAL) to specify an option for a specific QueryAccount. Just select a cell next to the account and insert the option. It is optional, if not specified none options are added.

Add accounts

When an option is selected, the add-in loads the appropriate check box list with all the elements taken from the selected accounting file. It is possible to choose between six options:

• Accounts to load a list of all the accounts/categories codes taken from the table Accounts/Category of the accounting file.
• Groups to load a list of all the groups codes taken from the table Accounts/Category of the accounting file.
• Cost centers to load a list of all the cost centers codes taken from the table Accounts/Category of the accounting file.
• Segments to load a list of all the segments codes taken from the table Accounts/Category of the accounting file.
• All to load a list of all the accounts/categories, groups, cost centers and segments codes taken from the table Accounts/Category of the accounting file.
• Vat codes to load a list of all the VAT codes taken from the table VAT codes of the accounting file.

Type of account selection

For example, choosing the All option, the add-in loads a list containing all the accounts, groups, cost centers and segments respecting the order in which they appear in the accounting file.

Example of accounts and groups selection

Select the desired elements and click the Add accounts button to add them to the worksheet under the QueryAccount (column A) starting from the selected cell. By default the add-in starts the insertion immediately after the QueryAccount title (row 16).

Add the selected accounts and groups to the Excel worksheet

Add option

The QueryOptions column is designed to add some options to the query. It is optional, if not specified no options are used.

The possible values are:

• invert to invert the sign of the current and budget balances.
• budget to get the budget balances of the selected QueryAccount.
• budgetinvert to get the budget balances of the selected QueryAccount and also to invert the sign.

QueryAccounts options selection

Header settings

Editable header parts

On the image above we highlighted in yellow all the header's parts that can be modified by adding information when creating a report.

Everything else will be automatically filled by the add-in when the Update current worksheet button is clicked.

Period Begin

A conversion of the start date to be easily read.
This is automatically filled for each column by the add-in when the worksheet is updated.

Period End

A conversion of the end date to be easily read.
This is automatically filled for each column by the add-in when the worksheet is updated.

Currency

The accounting basic currency.
This is automatically filled for each column by the add-in when the worksheet is updated.

Header Left

One of the information property of the accounting.
This is automatically filled for each column by the add-in when the worksheet is updated.

Header Right

One of the information property of the accounting.
This is automatically filled for each column by the add-in when the worksheet is updated.

QueryAccount

As already said, in this column are listed all the chosen accounts, each on a different row.

Instead of insert an account, is also possible to add a custom regroup using a particular accounting column.
The custom regroup QueryAccount syntax is $column=value, where:

• $ indicates that a custom regroup is used.
• column is the Xml name of the column. It can be a user created column (for example "Abc") or a column that already exists in the accounting (for example the "Gr").
• value indicates the regroup.

If we insert something like "$Abc=1" in the QueryAccount cell, this means that the add-in takes and sums together all the accounts/groups balances that have the 1 value in the "Abc" column of the accounting.

Messages

The Messages tab shows some information about the add-in and the operations that it does.

Example of messages

Settings

The Settings tab allows to change some settings of the add-in:

• the Server information allows to define the URL where Banana Accounting is hosted, to avoid to have Banana Accounting installed locally. By default it is defined the local Banana Accounting web server https://127.0.0.1:8089.
• the Connection token at the moment should be left empty.
• the Language to define the language of the Banana Excel Add-in. Available languages are english, french, german and italian.
• the Development is used only by developers for testing purposes, and users cannot access it.

Click on the Ok button and accept to reload the add-in in order to use the new settings. The settings are saved for future use of the add-in.

Settings tab

 

Release History

• 2017-06-12 First release
• 2017-07-07
  • Added Add-in Commands functionality.
  • Added a start screen that provides additional information describing the functionalities of the add-in.
  • Added the settings tab to allow the user to change the Port of the URL.
• 2017-09-29
  • Changed the name of the add-in to "Banana Accounting Excel Reports".
  • Changed some texts.
  • New add-in design.
  • Added new functionalities that allow the user to set and insert all the required information more easily.
  • Added localization language for english, french, german and italian.
• 2017-11-24
  • Added new functionality that allows to set the parameters for the connection.
• 2018-04-04
  • Settings options are now saved.
  • Changed the appearance of the error messages.
  • Changed some texts.
  • Other minor changes.

 

 

 

The steps below walk you through all the setup of the environment required to run the Banana Excel add-in from a local installation and not from the Office store.

In order to do that, you will have to:

• Download all the add-in files.
• Save them to specific folders on your computer.
• Add a trusted add-in catalog in Excel.
• Load the add-in in Excel.
• Set the server URL in the add-in.

Minimum requirements:

• Banana Accounting Plus or Banana Accounting 9.
• Microsoft Excel 2016 for Windows or macOS.

Get Banana Accounting Plus

Install Banana Accounting Plus for Windows or Mac on your pc.

Start Banana Accounting web server

In order to use the add-in you have to start the Banana web server first:

1. Start Banana Accounting Plus.
2. On menu bar click Tools > Program options and select the General tab.
3. Check the Start Web Server option. The web server with ssl is not needed.
4. Click Ok.

Download the add-in files

The next step is to download all the required files of the add-in.

• Download the BananaAccountingExcelAddin.zip file.
• Extract the content:
  • BananaAccountingExcelAddin: folder containing all the add-in files.
  • BananaAccountingExcelManifest: manifest file of the add-in.
    
    

Install the add-in files

The files extracted from the zip must be copied to specific directories.

Add-in files for Windows

Banana Accounting Plus:
On Windows you need to copy the BananaAccountingExcelAddin folder in the directory C:\Users\{user_name}\AppData\Roaming\Banana.ch\BananaPlus\10.0\WWW.

• In the search box insert %AppData% and press enter. The AppData\Roaming folder will open.
• Navigate through the folders Banana.ch\BananaPlus\10.0.
• If it doesn't exists yet, create a folder named WWW (all in capital letters).
• Copy in the WWW folder the BananaAccountingExcelAddin folder extracted from the zip file.

Banana Accounting 9:
On Windows you need to copy the BananaAccountingExcelAddin folder in the directory C:\Users\{user_name}\AppData\Local\Banana.ch\Banana\9.0\WWW.

• In the search box insert %LocalAppData% and press enter. The AppData\Local folder will open.
• Navigate through the folders Banana.ch\Banana\9.0.
• If it doesn't exists yet, create a folder named WWW (all in capital letters).
• Copy in the WWW folder the BananaAccountingExcelAddin folder extracted from the zip file.

Add-in files for macOS

Banana Accounting Plus:
On macOS you need to copy the BananaAccountingExcelAddin folder in the directory /Users/{user_name}/Library/Application Support/Banana.ch/BananaPlus/10.0/WWW.

• Open the Finder.
• From the menu select Go and then Go to folder.
• Insert here the path /Users/{user_name}/Library/Application Support/Banana.ch/BananaPlus/10.0 and click Go.
• If it doesn't exists yet, create a folder named WWW (all in capital letters).
• Copy in the WWW folder the BananaAccountingExcelAddin folder extracted from the zip file.

Banana Accounting 9:
On macOS you need to copy the BananaAccountingExcelAddin folder in the directory /Users/{user_name}/Library/Application Support/Banana.ch/Banana/9.0/WWW.

• Open the Finder.
• From the menu select Go and then Go to folder.
• Insert here the path /Users/{user_name}/Library/Application Support/Banana.ch/Banana/9.0 and click Go.
• If it doesn't exists yet, create a folder named WWW (all in capital letters).
• Copy in the WWW folder the BananaAccountingExcelAddin folder extracted from the zip file.

Install the Manifest file

Each Office add-in has its own manifest file. The manifest is an XML file that defines various settings, including description and links to all the add-in files.
Manifest file must be copied to a specific directory.

Manifest directory for Windows

On Windows you need to create a directory to save the manifest of the add-in.
The directory needs to be a shared directory.

1. Create a folder for the add-ins manifests on a network share:
   1. Create a folder on your local drive (for example, C:\Shared\OfficeManifest).
   2. Right click on the folder, select properties.
   3. Click on Sharing tab.
   4. Click on Advanced Sharing...
   5. Check the Share this folder box.
   6. Click Apply and then Ok.
   7. Copy here the BananaAccountingExcelManifest file extracted from the zip file.
      
      
       
2. Tell Excel to use the Manifests directory as trusted app catalog:
   1. Launch Excel and open a blank spreadsheet.
   2. Choose the File tab, and then choose Options.
   3. Choose Trust Center, and then choose the Trust Center Settings button.
      
      
       
   4. Choose Trusted Add-in Catalogs.
   5. In the Catalog URL box, enter the path to the network share you created, and then choose Add Catalog.
      To see the path of the network share folder: right click on the shared folder → Properties → Sharing → Network Path.
      You can copy the path from here.
      
      
       
   6. Select the Show in Menu check box, and then choose OK.
      A message appears to inform you that your settings will be applied the next time you start Office.
      
      
       
   7. Close Excel and restart it.

Manifest directory for macOS

On Mac you need to create a folder to save the manifest file of the add-in.

• Open Finder and from the menu select Go > Go to folder.
• Enter the filepath /Users/<username>/Library/Containers/com.microsoft.Excel/Data/Documents/wef
  If the wef folder doesn't exist on your computer, create it.
  Note: <username> is your name on the device.
• In the wef folder copy the BananaAccountingExcelManifest file extracted from the zip file.

Other filepaths based on the application:

• For Excel: /Users/<username>/Library/Containers/com.microsoft.Excel/Data/Documents/wef
• For Word: /Users/<username>/Library/Containers/com.microsoft.Word/Data/Documents/wef
• For PowerPoint: /Users/<username>/Library/Containers/com.microsoft.Powerpoint/Data/Documents/wef

Load the add-in in Excel

Once all the setup and installations are done, it is possible to run and use the add-in.

1. Open Microsoft Excel.
2. Click on Home tab.
3. Click on the Add-ins button.
4. Click on More Add-ins.
5. Click on the Shared folder.
   
   
    
6. Select the Banana Accounting Excel add-in.
7. Click Add.

The add-in is added

Set the server URL setting

In the add-in make sure to change the server URL to http://localhost:8081.

• Start Excel and the Banana Accounting add-in.
• Click on the Options tab of the Add-in.
• From the server information select Other.
• In the Server URL field,  insert http://localhost:8081
• Click OK to confirm and save the changes.
  
  

 

 

Other Resources

For more and detailed information about the developing of the Office Add-ins, please visit https://github.com/BananaAccounting/General/tree/master/OfficeAddIns.