Excel 同步功能

该文档是已过时的旧文档

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

In this article

使用 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


使用示例

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

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

  • Download on your computer the latest version
  • Open your file in Excel
  • Open the file "BananaSync.xlsm" in Excel
  • Go to the Developer Tab
  • Click on "Visual Basic"
  • Copy the content of the "BananaSync.xmls - Banana (Code)"
  • Paste the content in the Modules->Banana of your file.

Compatibility

Banana ExcelSync functions have been tested with Excel 2013 and 2016 for Windows.

Excel for Mac is not ready yet.

In Excel for Mac is not possible to call the http.
Any contribute to solve this problem is welcome.

Release History

  • 2014-07-24 First release
  • 2015-02-28 Updated for new version with new functionalities
  • 2015-05-12 Call to webserver now require v1
  • 2015-05-12 Development moved to github
  • 2015-05-25 Changed BAmoount function to use BClass
  • 2015-10-04 Added BDate function
  • 2015-11-12 Renamend ExcelSync
  • 2015-11-28
    • Added example for cost division
    • Started working on Mac support
  • 2016-04-26 Added BCellAmount
  • 2016-04-28 Fixes in some case rounding amount to zero
  • 2017-02-01 New Version 2 (Functions without the file parameters)

 

Share this article: Twitter | Facebook | LinkedIn | Email