Excel 同步功能

在此文中

使用 Excel同步功能，使您的会计数据也同样可在 Excel 表格中被使用。不需要复制和粘贴或导出和导入。

在您添加了新的发生业务之后，在Excel表格中的数据也会同步进行更新和计算。此功能仅在Windows操作系统下可用，在Apple/Mac电脑上暂不可用。

成本分摊在共同所有者或客户之间

Excel 同步功能用来检索在 Excel中的Banana会计数据和当前账户余额。
费用由使用正常Excel公式的客户分摊。
您可以使用该示例来创建公寓费用的划分。

当年与去年差额的示例

在此示例中，我们从设定的两年中提取数据并创建了一个图形。

分部细分的示例

分部的金额也由分部来细分。

Banana财务会计软件Excel同步用户定义功能介绍

介绍

Excel同步是Excel用户定义的功能，其实现了在Banana财务会计软件内的数据与在Excel表格文件中的数据同步更新，计算的功能。
在Banana财务会计软件中，如果您更新了会计文件，添加了新的发生业务，那么在Excel表格中的数据也会同步进行更新和计算。

Excel能够整合通过互联网协议提供的文档和数据。 Banana 包含一个网络服务器, 和一个 RESTful API, 其可通过http协议进行访问。Excel同步功能使用Banana软件集成网络服务器实时检索数据。

使用Excel公式

Banana Excel 同步功能的名字是以 "B" 开头的, 您可以在单元格中使用来检索会计数据。
请看示例:

// 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")

Excel 同步功能的优点:

您可以动态检索从Banana财务会计文件中提取的数据；
不需要在Excel中重新输入数据 (或者导入，复制和粘贴)；
在会计文件数据发生变化的时候，Excel表格内的数据是与其同步更新的；
用于计算期间值的公式简便易使用，可以创建强大的电子表格来用于评估，呈现会计数据或创建图形。

技术方面的细节

Banana Excel同步是Excel用户定义的功能, 小的Visual Basic程序，其扩展Excel允许在单元格中插入公式。

Banana Excel同步功能需要使用新版本的Excel,并且只适用于Windows版本。
为了能够使用Excel同步用户定义功能，您需要一个扩展名为 *.xlsm 的Excel文件。
Banana Excel同步用户定义功能是根据Apache许可证来提供的 (开放资源软件。请您查看: /www.apache.org/licenses/LICENSE-2.0)。
查看最新版本的功能: github.com/BananaAccounting/General/。
Banana Excel同步用户定义功能使用 Banana 网络服务器。
您可以通过添加其它功能来扩展Excel同步。

有关使用公式的更多信息，请您查看关于会计功能的Banana API。

使用示例

下载 Excel 电子表格示例文件
解压内容
开启 Banana财务会计软件
激活网络服务器 (工具栏 -> 基本设置 -> 界面 -> 启动web服务器)
打开Banana财务会计文件 "company_2019.ac2" 和 "company_2020.ac2"
打开“BananaSync.xlsm”文件并激活宏(Macro)
如果宏被Excel自动禁用，您应该更改宏安全设置
最后按照说明显示功能区中的开发人员选项卡
使用宏的"重新计算全部"来重新计算电子表格 (Ctrl+R)

Excel 没有反应

如果您打开了一个文件，Banana财务会计软件或Banana网络服务器没有运行的话，那么Excel将等待至联系到Banana网络服务器为止。

启动Banana财务会计软件和Banana网络服务器。

如何创建您的电子表格

以新的名字另存文件"BananaSync.xlsm"
在Banana财务会计软件中打开您的会计文件
在您的Excel电子表格中，用您的会计文件名替换该文件名（黄色突出显示的单元格）
根据您的需要更改电子表格
使用“重新计算”按钮或“Ctrl + R”快捷方式重新计算

功能使用

参数文件名

作为第一个参数，大多数的Excel同步功能需要一个Banana财务会计文件的名称。

该文件必须是在Banana财务会计软件中被打开的。
您只使用没有目录的文件名。

不要在函数中直接使用文件名。请在单元格中使用包含文件名的参数。

您也可以在不同的年份使用相同的电子表格。您只需要更改单元格中的文件名。
如果Banana财务会计软件没有打开Banana网络服务器，也没有激活的话，您无需等待。

最好的方法是在示例文件中使用的方法。

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