Documentation Excel Add-in

Documentación •
En este artículo

Introduction

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:

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

The purpose of the header is to let you to choose which data to import from Banana Accounting and on which columns in the Excel file to display them.
You must manually set column by column indicating, for each of them, the data that you want to import and display. It is possible to use the columns from C to AZ.
 
Notes:
  • Do not add or delete rows in the header.
  • Do not add or delete columns before the column B.
  • From column C forward, it is possible to add or remove columns. Columns A (QueryColumn) and B (QueryOptions) must always exist.
  • Added columns can also be empty.
  • If columns from AA to AZ are used, plese re-enter the file name at least on the AA column, even if it is the same used in the previous column.

To better understand how exactly the header works and how to properly modify it, below there are some explanation about the most important things.


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.

 

 

 

Tell us how we can help you better
If the information on this page is not what you're looking for, is not clear enough, or is not up-to-date, let us know.

Share this article: Twitter | Facebook | LinkedIn | Email