ExcelSync functions

Example costs divided among co-owners or customers

The ExcelSync functions are used to retrieve from Banana in Excel the current accounts balances.
The costs are then divided among customers using normal Excel Formulas.
You could use the example to create a division of the apartment costs.

Example with the current and last year difference

In this example we take the data from the two years and create a graphic.

Example with segments subdivision

The amount of the segments are diveded also by segments.

Introduction to Banana ExcelSync User defined functions

Introduction

ExcelSync are Excel User defined functions that allow to synchronize in real time your Excel spreadsheet with the data from Banana Accounting.
You update you accounting file, adding new transactions, and instantly you get your Excel Sheets updated.

Excel has the ability to integrate documents and data that are made available throught the internet protocol. Banana includes a web server, and a RESTful API, that can be accessed through http protocol. ExcelSync uses the Banana integrated web server to retrive data on real time.

Using Excel formula

Banana ExcelSync are functions, with the name that start witht the "B", that you can use within the cell to retrive accounting data.
Here some example:

// return the opening balance of the account 1000 for all the period
=BOpening("1000")  
// return the description of the account 1000
=BAccountDescription("1000")
// return the end balance of the group 10
=BBalance("Gr=10")
// return the opening of the account 5000 for the period 3. month 
=BOpening("2000", "2017-03-01/2017-03-31")
// return the total debit minus credit of the account 5000 for 3. month of the year
=BTotal("5000", "M3")
// return the total debit minus credit of the group 50 for 3. quarter of the year
=BTotal("Gr=50", "Q3")

The advantage of the Excel Sync functions :

You can dynamically retrieve the take data from Banana Accounting.
No more need to retype data in Excel (or import, copy and paste)
When the accounting file is changed, the spreadsheet is populated with the new values
Easy to use formulas that let you calculate values for periods and create powerful spreadsheets for evaluating, presenting accounting data or creating graphics.

Tecnical details

Banana ExcelSync are Excel User defined functions (UDF), small Visual Basic Programs that extend Excel allowing to insert formula within the cell.

Banana ExcelSync requires a recent version of Excel, and due to the Excel Mac limitations works only on Windows versions.
In order to use the ExcelSync UDF you need an Excel file with the extension *.xlsm.
The Banana ExcelSync UDF are provided according the Apache License (open source software. See: /www.apache.org/licenses/LICENSE-2.0
Development and latest version of the function are available on github.com/BananaAccounting/General/
Banana ExcelSync UDF make use of the Banana web server.
You can extend the ExcelSync by adding other functionalities.

For more information on the formula used see the Banana API regarding the Accounting functions

Using the examples

Download the Excel spreadsheet with examples files.
Unzip the content
Start Banana Accounting
Activate the Webserver (Tools -> Program options -> Interface -> Start web server)
Open the Banana accounting files "company_2019.ac2" and "company_2020.ac2"
Open the "BananaSync.xlsm" file and activate the Macro
If the macro are automatically disabled by Excel you should change your macro security setting
Eventually follow this instructions to show the developer tab in the ribbon
Recalculate the Spreadsheet with the Macro “RecalculateAll” (Ctrl+R)

Excel does not react

If you open a file and Banana or the Banana Web Server are not running, Excel will wait until it can contact the Banana Web server.

Start the Banana and the Banana Web Server.

How to create your spreadsheet

Save as the "BananaSync.xlsm" file with another name
Open your accounting files in Banana Accounting
In your Excel spreadsheet, replace the file name (yellow highlighted cells) with your accounting file name
Change the spreadsheet according to your needs
Recalculate with the "Recalculate" button or the "Ctrl+R" shortcut

Functions use

Argument file name

Most ExcelSync functions require, as first parameters, a name of a Banana Accounting file.

The file must be openened in Banana.
You use only the file name without the directory.

DO NOT use the file name directly in the fuctions. Instead use a reference to a cell, that contains the file name.

You can use the same spreadsheed also for differrent years. You only need to change the file name in on cell.
If Banana Accounting is not open of the Banana webserver is not active you don't have to wait.

The best way is the one used in the example file.

The file name of the current year is taken from the cell named "File0" .
The cell File0 contains a function =BFileName(DisableConnection).
This function checks if the file is open in Banana.
- If the file is not open the content of the cell is set to an empty string.
  The other Banana Sync functions will not make any call to Banana, to retrive data.
- If the file is open it will insert the name of the file.
The cell B6 contain the name of the file to be used. Insert the file name in cell B6.
=BFileNameF(File0, DisableConnection).
The file name of the current year is taken from the cell named "File0" .
The file name of the last year is taken from the cell named "File1" .

Argument period

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

An empty string. The start and end date of the accounting are used.
A start date and end date in the form of yyyy-mm-dd/yyyy-mm-dd
example “2015-01-01/2015-01-31”
In order to create a period from two Excel dates use the function BCreatePeriod.
An abbreviation
With the abbreviation you can easily use the same spreadsheet for accounting file of different periods.
The start and the end date will be determined based on the date of the accounting file
- M + the month number M1, M2, ..
- Q + the quarter number Q1, Q2,
- Y + the year number Y1, Y2, ....

BananaSync Functions description

Most function are available

Without the parameter FileName.
In this case the File0 (Current Year) is used
With the paramenter FileName.
- The function is the same but end with "F"

BAccountDescription(account[, column]) and BAccountDescriptionF(fileName, account[, column])

Retrieve the account description of the specified account or group.
With argument column you can indicate to retrieve another column instead of the Description column.
Examples:

=BAccountDescription("1000")           // Description of account 1000 current year
=BAccountDescription("Gr=10")          // Description of Group 10 current year
=BAccountDescription("1000", "Gr1")    // Contet of column "Gr1" relative to the account 1000 current year
// Last year
=BAccountDescriptionF(File1, "1000") // Desctiption of account 1000 
=BAccountDescriptionF(File1, "Gr=10")       // Description of Group 10

BAmount(account, [,period ]) and BAmountF(fileName, account, [,period ])

Retrieve the normalized amount based on the BClass.
Only work for double entry accounting only. For Income and expenses accounting use BBalance or BTotal.

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.

BBalance( account [, period]) and BBalanceF(fileName account [, period])

Retrieve the Balance at the end of the period of the indicate account, cost center, groups, segments
The BBalance result is the sum of the BOpening + BTotal
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

BBalance( "1000")              // Balance of account 1000  
BBalance( "1000|1010")         // Balance of account 1000 and 1010 are summed together
BBalance( "10*|20*")           // All account that start with 10 or with 20 are summed toghether
BBalance( "Gr=10")             // Group 10
BBalance( "Gr=10| Gr=20")      // Group 10 or  29
BBalance( ".P1")               // Cost center .P1
BBalance( ";C01|;C02")         // Cost center ;C01 and C2
BBalance( ":S1|S2")            // Segment :S1  and :S2
BBalance( "1000:S1:T1")        // Account 1000 with segment :S1 or ::T1
BBalance( "1000:{}")           // Account 1000 with segment not assigned 
BBalance( "1000:S1|S2:T1|T2")  // Account 1000 with segment :S1 or ::S2 and ::T1 and ::T
BBalance( "1000&&JCC1=P1")     // Account 1000 and cost center .P1
// Last year 
BBalanceF(File1, "1000")        // Balance of account 1000 (last year)
BBalanceF(File1, "1000|1010")   // Balance of account 1000 and 1010 are summed together (last year)

BBalanceGet( account, cmd, valueName [,period ]) and BBalanceGetF(fileName, account, cmd, valueName [,period ])

This function allows to easily access all other data made available by the REST API as “balance”, “budget”
Examples:

=BAmount( “1000”, “balance”, “currencyamount”)
=BAmount( “1000”, “balance”, “count”)
=BAmount( “1000”, “balance”, “debit”)
// Last year
=BAmount( File0, “1000”, “budget”, “debit”)

BBudgetAmount(account [, period]) and BBudgetAmountF(fileName account [, period])

Same as BAmount but use the budget data instead of the accounting data.

BBudgetBalance(account [, period]) and BBudgetBalanceF(fileName account [, period])

Same as BBalance but use the budget data instead of the accounting data.

BBudgetInterest( account, interestRate [, period]) and BBudgetInterestF(filename, account, interestRate [, period])

Same as BInterest but use the budget data instead of the accounting data.

BBudgetOpening(account [, period]) and BBudgetOpeningF(fileName account [, period])

Same as the BOpening but use the budget data instead of the accounting data.

BBudgetTotal(account [, period]) and BBudgetTotalF(fileName account [, period])

Same as the BTotal but use the budget data instead of the accounting data.

BCellAmount( table, rowColumn, column) and BCellAmountF(fileName, table, rowColumn, column)

Retrieve the content of a table cell as an amount.
Examples:

=BCellAmount(“Accounts”, 2, “Opening”)
=BCellAmount(“Accounts”, “Account=1000”, “Balance”)
=BCellAmount(“Accounts”, “Group=10”, “Balance”)
// Last year
=BCellAmountF(File1, “Accounts”, 2, “Opening”)

BCellValue( table, rowColumn, column) and BCellValueF(fileName, table, rowColumn, column)

Retrieve the content of a table cell as a text.
Examples:

=BCellValue(“Accounts”, 2, “Description”)
=BCellValue(“Accounts”, “Account=1000”, “Description”)
=BCellValue(“Accounts”, “Group=10”, “Description”)
// Last year
=BCellValueF(File1, “Accounts”, 2, “Description”)

BCreatePeriod( startDate, endDate)

Take two cell dates and create a string period
=BCreatePeriod(D4, D5)

BDate(isoDate)

Convert an Iso Date to an Excel date.

BFileName(fileName [, disable connection])

Return the FileName or an empty string if there is no connection with the web server or if the file is not correct.
If the value of disableConnection is not void the function returns an empty string.
Use the cells that contain the result of this function as the file name parameter when using the other functions. If Banana is not open only one query is made and Excel will not wait for a long time.

BFunctionsVersion()

Return the version of the function in the date format.

BInfo( sectionXml, idXml) and BInfoF(fileName, sectionXml, idXml)

Retrieve information regarding the file properties.
Examples:

=BInfo(“Base”, “HeaderLeft”)
=BInfo(“Base”, “DateLastSaved”)
=BInfo(“AccountingDataBase”, “OpeningDate”)
=BInfo(“AccountingDataBase”, “BasicCurrency”)
// Last year
=BInfoF( File1, “Base”, “HeaderLeft”)

BInterest( account, interestRate [, period]) and BInterestF(filename, account, interestRate [, period])

Calculate the interest for this account for the specified period

account can be any account as specified in BBalance

interestRate in percentage

> 0 calculate the interest on the debit amounts
< 0 calculate the interest on the credit amount

BOpening( account [period]) and BOpeningF(filename, account [period])

Retrieve the Balance for balance of period start for the indicated account.

BQuery(fileName, query)

Return the result of a free defined query.
Examples:

=BQuery(File0;"startperiod?M1”)
=BQuery(File0;"startperiod?M1”)

BTotal( account [,period]) and BTotalF(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).

BVatBalance( vatCode, vatValue [, period]) and BVatBalanceF(filename, vatCode, vatValue [, period])

Return a value regarding the specified VatCode (or multiple VatCodes).
“vatValue” can be “taxable”, “amount”, “notdeductible”, “posted”
Examples:

=BVatBalance(“V10”, “taxable”)
=BVatBalance(“V10|V20”, “posted”)
//Last year
=BVatBalanceF( File0, “V10”, “taxable”)

Additional function explanation

The retrieve the exact content of the cells

If you wanto to retrive the content of a cell you can use:

BCellValue
The content of a cell, useful for text.
BCellAmount
The content of a cell is converted to a number so that you can use it for calculation.
With this you will retrive the exact content of a column "Balance" for the row where Account is 1000.
If the Balance is credit the amount is negative.

=BCellAmount(File0, “Accounts”, “Account=1000”, “Balance”)

Accounting Period calculation

You have different formula that allow to retrieve the amount.

BBalance.
This is equivalent to the above. It retrieve the Balance of the whole accounting period.
But BBalance allow you to use also a period.
As a period you can use the date being, date end of an abbreviation. M3 means the first month of the accounting period.
If you use abbreviation instead of date your sheet will automatically adapt to file of different year.

BBalance( "1000")  //Balance end of year
BBalance( "1000", "2017-03-01", "2017-03-31")  //Balance end of March
BBalance( "1000", M3);   // Balance and of March if accounting period start on 1. of January

BTotal
It retrieve the total movement (Debit - Credit) for the period.
Use BTotal to the amount for income and expenss account.
Cedit amounts are retrieved as negative numbers.

BTotal( "1000")  //Total movement end of year
BBalance( "1000", "2017-03-01", "2017-03-31")  //Total end of March
BBalance( "1000", M3);   // Total and of March if accounting period start on 1. of January

BAmount
BAmount put the sign in positive based on the BClass of the account.
The amount retrieved depend on the BClass of the account or the group.
For Balance accounts (bclass 1 and 1) retrieve the Balance.
For Income and expenses accounts (bclass 3 and 4) retrieve the Total.
It also invert the sign in case of BClass 2 and 4.
So if you use BAmount for the Account revenues (BClass 4) you will have the total sales for the period in positive.

Your are free to use the most appropriate function.

BAccountDescription.
It is the same as GetCellValue but it deal automatically with accounts or groups.
Is usefull to retrieve the description of an account or group, in combination with BBalance, BTotal or BAmount.

=BAccountDescription( "1000") //Retrieve the column Description of the account 1000
=BAccountDescription( "1000", "Notes") //Retrieve the column Notes of the account 1000
=BAccountDescription( "Gr=10") //Retrieve the column Descrition of the group 10

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 files 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”.
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.

Use a new version of the Banana functions