Documentation Excel Add-in

Cette documentation est dépassée

La documentation la plus complète et actualisée est celle de Banana Comptabilité Plus : Essayez-le maintenant

In this article

Introduction

With this Add-in you can create Excel sheets 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 helps 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 Show Taskpane add-in command.


    Banana Accounting Add-in command

    When the Show Taskpane 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

    Security alert messages for Windows users

    In order to properly establish a connection between the add-in and Banana Accounting web server, it is required to accept the Banana security certificate.

    After the Let's Begin! button has been clicked, securities alert dialogs like the following appear:

    • The first security alert message is the following one, click on Yes to proceed:

       
    • The second security alert message is the following one, click on Yes to proceed:

       

    If the user clicks Yes, a connection between the add-in and the Banana Accounting web server is estabilished, and then it is possible to use the add-in. Otherwise, if the user clicks No, the add-in is loaded but none connection is established, and the add-in will not work.

    If for some reason the security alert messages above do not appear, try to see the troubleshooting documentation.

     

    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 has been added.
    • The Logs tab it’s just a place where are displayed 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.


    Banana Accounting task pane add-in

    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 the Header, QueryAccount and QueryOptions, the add-in retrieves all the data directly from the Banana Accounting 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 information to the current worksheet necessary 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 file. Just select the needed one and go to the next setup section.

    If for some reason an accounting file is opened in Banana Accounting after the add-in is loaded, then this file doesn’t appear in the list. In this case just click on Refresh list button in order to recheck all the opened documents and recreate the list.


    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

    When the button Add Header is clicked, the selected type of header is inserted in the worksheet. It is then possible to modify by setting the QueryColumns and changing 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 inserted again.

    When the button Add options is clicked, the selected options will be inserted in the respective 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 Accounting filename is used to define the Accounting file to use when retrieving data.
      • The Type value is used to define the type of data.
      • The Column value is used to define the data for the given type.
      • The Segments (OPTIONAL) is used to have a more detailed classification of the costs (this is optional, if not specified none segments will be added).
      • The Periods (OPTIONAL) is used to define a period of the accounting (this is optional, if not specified all accounting period will be automatically used).

      When the button Add values to column is clicked, all the information will be added automatically to the selected column of 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).
      • C ... Z

      Remember that it is possible to use the columns from C to ZZ, even if not all appear in the list.

      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 selected to use the selected file on the top of the add-in.
      • 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


      Example of more file insertion

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

      • Columns from C to G refer to the 2019.ac2 file
      • Columns from H to I refer to the 2018.ac2 file
      • Coumns from J to K refer to the 2017.ac2 file (p1 is the previous file of the last file inserted, in this case the 2018.ac2)

      Select a Type and a Column value

      Use them to define the data you want to retrieve from the accounting file.

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

      The table below indicates for each Type of data which Column can be specified and so retrieved from the accounting (Not Case-Sensitive).

      Possible Type-Column combinations
      Type Column
      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

       

      In the table below there are some examples of queries that can be used in the header to retrieve data from Banana Accounting:

      Examples of queries
      Type Column Segment Start date End date

      RESULT

      column description       Return from the Accounts table the value of the column description for the account specified in the QueryAccount column
      current debit       Return the amount of debit transactions for all the accounting period for the account specified in the QueryAccount column
      current balance :S1 01.01.2019 10.01.2019 Return the opening + debit-credit from the 01.01.2019 to 10.01.2019 for the account and segment specified in the QueryAccount column
      current total   M6   Return the difference between debit-credit for the 6th month for the account specified in the QueryAccount column
      current total   Q2   Return the difference between debit-credit for the second quarter for the account specified in the QueryAccount column
      budget opening   M12   Return the amount at the begining for the 12th month for the account specified in the QueryAccount column
      columnvat description       Return from the Vat Codes table the value of the column description for the vat code specified in the QueryAccount column
      currentvat taxable       Return the amount of the taxable column for the vat code specified in the QueryAccount column

      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 Start date and End date (example: Start date "04.02.2019",  End date "12.03.2019").
      • 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 (this is optional, if not specified none options will be 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
      • Groups to load a list of all the groups codes taken from the table Accounts/Category of the accounting
      • Cost centers to load a list of all the cost centers codes taken from the table Accounts/Category of the accounting
      • Segments to load a list of all the segments codes taken from the table Accounts/Category of the accounting
      • 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
      • Vat codes to load a list of all the VAT codes taken from the table VAT codes of the accounting


      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

      After all desired elements has been checked, by clicking the Add accounts button will add them to the Excel worksheet under the QueryAccount 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 that will retrieve the data from Banana Accounting. It is optional. If not used no options will be used.

      The possible values are:

      • invert to invert the sign of the current or budget balances.
      • budget to get the budget balances (even if in the header are specified to use current balances).
      • budgetinvert to get the budget balances (even if in the header are specified to use current balances) 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.

      Logs

      The Logs tab it’s just a text area where are displayed some messages about the add-in and the operations that it does.


      Example of logs messages

      Settings

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

      • the server URL 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 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.


      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.

       

       

      Partager cet article: Twitter | Facebook | LinkedIn | Email