Excel functions

This documentation is outdated

The most complete and up-to-date documentation is the one of Banana Accounting Plus: Try it now

In this article

Disclaimer

This functionality is in development, the specifications may be changed at any time, with or without notice. This functionality can be removed in future products or subject to additional costs.

This functionality is only available within the Accounting Experimental Version.

Excel functions are provided according the Apache License (open source softwar)
See: /www.apache.org/licenses/LICENSE-2.0

Link Excel cell content to banana accounting data

Thank to this function you will be abel to write formula within Excel that take data directly from Banana accounting.

  • No more need to retype data in Excel (or import, copy and paste)
  • When accounting is changed, the spredsheet is populated with the new values
  • Easy to use formulas that let you calculate values for periods and create powerfull spreadsheet for evaluating, presenting accounting data or create graphics.

Banana Web Server

This functions use the Banana Web Server in order to retrieve data in real time.

Form more information regarding the possibility are available:

This is a very early release. We invite to save immediately after you have inserted data in your accounting file.

How to see the example

  1. Download the Excel spreadsheet with examples files.
  2. Unzip the content
  3. Install Banana Accounting Experimental Version and insert the same License Key as for Banana7.
    If you do not insert a valid license key the program will operate in Starter edition mode.
  4. Activate the Webserver (Tools->Option->WebServer)
  5. Open the accounting file Banana "company_2014.ac2" and "company_2015.ac2"
  6. Open the file "BananaFunctions.xlsm" and activate the Macro.
    I the macro are automatically disabled by Excel you should change your macro security setting.
    Eventulaly follow this instructions to show the the developer tab in the ribbon
  7. Recalculate the Spreadsheet with the Macro “RecalculateAll”  (ctrl+r)

How to create the your spreadsheet

  • Save as the file "BananaFunctions.xlsm" with another name
  • Open in Banana your accounting files
  • Replace in the spreadsheet the file name (yallow) with your accounting file name
  • Change the spreadsheet accordint to your needs
  • Recalculate with the Button "Recalculate" or shortcut "Ctrl+R"

 

Functions use

Argument file name

Most functions use the argument file name.

Instead of passing the file name as a string , you should use the content of a cell that contain the formula =BFileName(“myfile.ac2”). BFileName return an empty string in case Banana is not started or server not activated.
Successive functions call will not wait and Excel will not stuck waiting for the connections.

Argument period

Many function use the optional argument period. This can be:

  • An empty string. The start and end date of the accounting is used.
  • A date start and date end in the form or yyyy-mm-dd/yyyy-mm-dd
    example “2015-01-01/2015-01-31”
    To create a period from two excel date use the function BCreatePeriod.
  • An abbreviation (M1, M2, Q1, Q2, Y1) indicating the month, quarter or year of the accounting.

You can use the function BCreatePeriod to create a period string based on two cell date.

Functions description

  • BBalance(fileName account [, period])
    Retrieve the Balance at the end of the period of the indicate account.  
    The BBalance result is the sum of the BOpening + BTotal
    It is used for retrieving accounting data for the Balance Sheet accounts (Assets, Lialabilities)
    • Account can be an account number or a string containing multiple accounts separated by the character “|”.
      You can specify normal account, cost center 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
      “1000” “1000|1001” “10*|20*”  “Gr=10” “Gr=10|Gr=20” “Gr=1*”
      ".P1" ";C01|,C02",":S1|:S2"
      "1000:S1"
  • BOpening(filename, account [period])
    Retrieve the Balance for balance of period start for the indicated account.
  • BTotal(filename, account [,period])
    Retrieve the movement for the period.
    Should be used to retrieve the data for the Profit and Loss accounts (Cost and Revenues).
  • BBalanceBudget, BOpeningBudget, TotalBudget.
    Same as the above functions but use the budget data instead of the accounting data.
  • BAmount(fileName, account, cmd, valueName [,period ])
    This function  allow to easily access all other data made available by the REST API “balance”, “budget”
    Examples:
    =BAmount( FName, “1000”, “balance”, “currencyamount”)
    =BAmount( FName, “1000”, “balance”, “count”)
    =BAmount( FName, “1000”, “balance”, “debit”)
    =BAmount( FName, “1000”, “budget”, “debit”)
  • BVatBalance(filename, vatCode, vatValue [, period])
    Return a value regarding the specified VatCode (or multiple VatCodes).
    “vatValue” can be “taxable”, “amount”, “notdeductible”, “posted”
    Examples:
    =BVatBalance( FName, “V10”, “taxable”)
    =BVatBalance( FName, “V10|V20”, “posted”)
  • BCellValue(fileName, table, rowColumn, column)
    Retrieve the content of a table cell.
    Examples:
    =BCellValue(FName, “Accounts”, 2, “Description”)
    =BCellValue(FName, “Accounts”, “Account=1000”, “Description”)
    =BCellValue(FName, “Accounts”, “Group=10”, “Description”)
  • BCreatePeriod( startDate, endDate)
    Take two cell date nd create a string period
    =BCreatePeriod(D4, D5)
  • BFileName(fileName)
    Return the FileName or an empty string if there is no connection with the web server or the file is not correct.
  • BInfo(fileName, sectionXml, idXml)
    Retrieve information regarding the file properties.
    Examples:
    =BInfo( FName, “Base”, “HeaderLeft”)
    =BInfo( FName, “Base”, “DateLastSaved”)
    =BInfo( FName, “AccountingDataBase”, “OpeningDate”)
    =BInfo( FName, “AccountingDataBase”, “BasicCurrency”)
  • BQuery(fileName, query)
    Return the result of a free defined query.
    Examples:
    =BQuery(FName;"startperiod?M1”)
    =BQuery(FName;"startperiod?M1”)
  • BFunctionVersion()
    Return the version of the function in the date format.

Recalculate

The automatic recalculation does not update the data from the accounting file.
In order to have the data updated it is necessary to call the macro RecalculateAll() that call the method Application.CalculateFullRebuild

The example file contain a button “Recalculate” that call the macro RecalculateAll

Banana host name and port

Web server data is retrieved from “localhost:8081”

You can specify a different host by entering a value in a cell named “BananaHostName”

Modify the functions or add your owns

Functions are defined in the Visual Basic module “Banana”.
We may update this module and add new functions.

If you add your function it would be better to add to your module.

To access the Visual Basic Macro Functionalities you should activate the macro.

In order to see and edit the functions your nedd to show the Developer tab in the Excel ribbon.

Release History

  • 2014-07-248 First release