Banana.Document (Accounting)

Banana.Document is the interface to a document in Banana Accounting.

Banana.Document has base methods, that apply to all documents, see Banana.Document (Base), and method that are specific to the type of documents. This page describes the methods for the accounting files.

In the table documents of the example file there are also some example code.

Methods for accounting's files

This method are available only if the Document is of type Accounting

accountDescription(account [,column])

Return the Description of the specified account.

  • Account can be an account or a Group (Gr=)
  • Column can be an alternative column name to retrieve.
var descriptionAccount = Banana.document.accountDescription('1000');
var descriptionGroup = Banana.document.accountDescription('Gr=10');
var gr = Banana.document.accountDescription('1000','Gr');

accountsReport([startDate, endDate])

Return the account report for the specified period. Start and end date can be a string in form 'YYYY-MM-DD' or a date object.

var report = Banana.document.accountsReport();
var report = Banana.document.accountsReport('2017-01-01', '2017-03-31');

budgetBalance(account [, startDate, endDate, function(rowObj, rowNr, table)])

Sum the amounts of opening, debit, credit, total and balance for all budget transactions for this accounts .

var budget = Banana.document.budgetBalance('1000');

See for more detail the function currentBalance.

budgetCard(account [, startDate, endDate, function(rowObj, rowNr, table)])

Return for the given account and period a Table object with the budget account card.

var card = Banana.document.budgetCard('1000');

For more ditails see currentCard.

budgetExchangeDifference( account, [date, exchangeRate])

Return the unrealized exchange rate Profit or Loss fo the account at the specified date. 

  • account must be a valid account number not in base currency
  • date
    • a date that is used to calculate the balance
    • if empty calculate up to the end of the period
  • exchangeRate
    • if empty use the historic exchange rate for the specified date or the current if not a valid exchange rate for the date are found.
    • if "current" use the current exchange
    • if number for example "1.95" use the specified exchange rate.
  • Return value
    • Positive number (debit) are exchange rate Loss.
    • Negative number (credit) are exchange rate Profit.
    • empty if no difference or if the account has not been found or not a multicurrency accounting file.
// unrealized exchange rate profit or loss for the account 1000
// last balance and current exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000');

// at the end of Semptember and hystoric exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000', "2017-09-31");

// at the end of Semptember and current exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000', "2017-09-31", "current");

// at the end of Semptember and specified exchange rate
var exchangeRateDifference = Banana.document.budgetExchangeRateDifference('1000', "2017-09-31", "1.65");

Available from Banana version 8.05

budgetInterest( account, interestRate, [startDate, endDate, , function(rowObj, rowNr, table)])

Return the calculated interest for the budget transactions. 

For more information see the currentInterest and current Balance

// calculate the interest debit for the whole period
var interestDebit = Banana.document.budgetInterest('1000','5.75');

// calculate the interest credit for the whole period
var interestDebit = Banana.document.budgetInterest('1000','-4.75');

currentBalance(account [, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the amounts of opening, debit, credit, total and balance for all transactions for this accounts.

The calculations are perfermed by traversing by creating a journal (see journal() function) with all the transactions , and selecting the transctions with the parameters specified.
The computation is usually very fast. But if you  have a file with many transactions especially the first query could take some time. 

var currentBal = Banana.document.currentBalance('1000','','');
var openingBalance = currentBal.opening;
var endBalance = currentBal.balance;
  • Return value
    Is an object that has
    • opening the amount at the begining of the period (all transactions before)
    • debit the amount of debit transactions for the period
    • credit the amount of credit transactions for the period
    • total the difference between debit-credit for the period
    • balance opening + debit-credit for the period
    • amount it the "normalized" amount based on the bclass of the account or group.
      If there are multiple accounts or groups, takes the first BClass of the first.
      • for BClass 1 or 2 it return the balance (value at a specific instant).
      • for BClass 3 or 4 it return the total (value for the duration).
      • For BClass 2 and 4 the amount is inverted.
    • openingCurrency the amount at the begining of the period in the account currency
    • debitCurrency the amount of debit transactions for the period in the account currency
    • creditCurrency the amount of credit transactions for the period in the account currency
    • totalCurrency the difference between debit-credit for the period in the account currency
    • balanceCurrency opening + debit-credit for the period in the account currency
    • rowCount the number of lines that have bben found and used for this computation
    • bclass (double entry accounting only) is the bclass of the account or group used to express the amount.
      The bclass is the value entered in the columns bclass.
      It is taken in consideration the first account or group specified. If for examble you query two account that first that has bclass 2 and the second that has bclass 1. The bclass would be 2.
      The bclass is assigned by following this steps. :
      • The bclass of the specified account or group.
      • The blcass of the partent group, for the same section.
      • The blcass of the section.
  • Account
    • can be an account id, a cost center or a segment.
    • can be a cobination of account and segments, separeted by the semicolon ":"
      In this case it returns all the transactions that have the indicated account and segments
      • 1000:A1:B1
    • can be differents accounts and multiple segments separated by the "|"
      In this case it include all transactions that have the one of the specified accounts and one of the specified segments
      • 1000|1001
      • 1000|1001:A1:B1
      • 1000|1001:A1|A2:B1
      • can be a wildCardMatching
        Wildcards can be used for accounts, segments, Groups or BClass and in combination
        • ?  Matches any single character.
        • *  Matches zero or more of any characters
        • [...] Set of charachtes
        • "100?" match "1001, 1002, 1003, 100A, ...)
        • "100*" Matches all accounts starting with 100
        • "100*|200*:A?" Matches all accounts starting with 100 or 200 and with segments with A and of two charachters.
        • "[1234]000" Matches "1000 2000 3000 4000"
    • Can be a group or a BClass.
      It include all the transactions where the account used belong to a specified Group o r BClass.
      It is also possible to use wildcards.
      The program first create a list of accounts and then use the account list.
      Do non mix mix groups relative to normal accounts, with groups relative to cost center or segments. Calculation could provide unexpected results.
      • BClass (for the double entry accounting only)
        • BClass=1
        • BClass=1|2
      • Gr for groups that are in Accounts table.
        • Gr=100
        • Gr=10*
        • Gr=100|101|102
      • GrC for group that are in the Category table of the income and expenses accounting type.
        • GrC=300
        • GrC=300|301
    • Contra Accounts or other fields selection
      Transactions are included only if they have also a value corresponding
      After the "&&" you can insert a field name of the table journal.
      • 1000&&JContraAccount=2000 return all transctions of the account 1000 that have a contra account 2000.
        As per accounts you can specify multiple contra accounts, BClass=, Gr= with also wildcards.
      • 1000&&JCC1=P1|P2 will use only transactions on account 1000 and that also have the CC1=.P1 or .P2
  • StartDate
    • is a string in form 'YYYY-MM-DD' or a date object.
    • If startDate is empty the accounting start date is taken.
  • End date:
    • is a string in form 'YYYY-MM-DD' or a date object.
    • If endDate is empty the accounting end date is taken.
  • function(rowObj, rowNr, table)
    This fuction will be called for each row of the selected account.
    The function should return true if you want this row to be included in the calculation.
function exec( string) {
    // We retrive the total sales (account 4000) only for the cost center P1
    var balanceData = Banana.document.currentBalance('4000','', '', onlyCostCenter);
    // sales is a revenue so is negative and we invert the value
    var salesCC1 = -balanceData.total;
    // display the information
    Banana.Ui.showText("Sales of Project P1: " + salesCC1);
}

// this function return true only if the row has the cost center code  "P1"
function onlyCostCenter( row, rowNr, table){
   if(row.value('JCC1') === 'P1') {
      return true;
   }
   return false;
}

Examples

Banana.document.currentBalance("1000")              // Account 1000 
Banana.document.currentBalance("1000|1010")         // Account 1000 or  1010 
Banana.document.currentBalance("10*|20*")           // All account that start with 10 or with 20
Banana.document.currentBalance("Gr=10")             // Group 10
Banana.document.currentBalance("Gr=10| Gr=20")      // Group 10 or  29
Banana.document.currentBalance(".P1")               // Cost center .P1
Banana.document.currentBalance(";C01|;C02")         // Cost center ;C01 and C2
Banana.document.currentBalance(":S1|S2")            // Segment :S1  and :S2
Banana.document.currentBalance("1000:S1:T1")        // Account 1000 with segment :S1 or ::T1
Banana.document.currentBalance("1000:{}")           // Account 1000 with segment not assigned 
Banana.document.currentBalance("1000:S1|S2:T1|T2")  // Account 1000 with segment :S1 or ::S2 and ::T1 
                                                    //   or ::T
Banana.document.currentBalance("1000&&JCC1=P1")     // Account 1000 and cost center .P1

currentCard(account [, startDate, endDate, function(rowObj, rowNr, table)])

Return for the given account and period a Table object with the transactions for this account.

Row are sorted by JDate

parameters:

  • account can be any accounts, cost center or segment as specifiend in currentBalance.
  • startDate any date or symbol as specifiend in currentBalance.
  • endDate any date or symbol as specifiend in currentBalance.

Return columns the same as for the Journal() function.

var transactions = Banana.document.currentCard('1000','2015-01-01','2015-12-31');

currentInterest( account, interestRate, [startDate, endDate, , function(rowObj, rowNr, table)])

Return the calculated interest on the specified account.
Interest is calculate on the effective number o days for 365 days in the years.

  • account is the account or group (same as in the function currentBalance)
  • interestRate. In percentage "5", "3.25". Decimal separator must be a "." 
    • If positive it calculate the interst on the debit amount.
    • If negative it calculate the interest on the credit amounts.
  • startDate, endDate, function see the currentBalance description.
    If no parameters are specified it calculate the interest for the whole year.
// calculate the interest debit for the whole period
var interestDebit = Banana.document.currentInterest('1000','5.75');

// calculate the interest credit for the whole period
var interestDebit = Banana.document.currentInterest('1000','-4.75');

exchangeRate( currency, [date])

Return the exchange rate that convert the amount in currency in basic currency.

The exchange rate is retrieved from the Currency table, already considering the multiplier.

  • If no date is specified the exchange rate without date is used.
  • If a date is specified it retrieve the exchange rate with the date minor or equal the specified date.

invoicesCustomers()

Return a table with the customers invoices from the transaction table. A customer group must be defined and invoices must be numbered using the column DocInvoice.

invoicesSuppliers()

Return a table with the suppliers invoices from the transaction table. A supplier group must be defined and invoices must be numbered using the column DocInvoice.

journal([originType = ORIGINTYPE_NONE, int accountType = ACCOUNTTYPE_NONE])

Return for the given parameters a Table object with all the amount registered on the accounts.
The journal contain a row for each account used.

  • originType specify the row to be filtered for
    Can be on of
    • ORIGINTYPE_NONE no filter is applied and all rows are returned (current and budget)
    • ORIGINTYPE_CURRENT only the normal transactions are returned
    • ORIGINTYPE_BUDGET only the budget transactions are returned
  • accountType specify the row to be filtered for
    • ACCOUNTTYPE_NONE no filter is applyied and all rows are returned
    • ACCOUNTTYPE_NORMAL only rows for normal accounts are returned
    • ACCOUNTTYPE_CC1 only rows for Cost Center 1 are returned
    • ACCOUNTTYPE_CC2 only rows for Cost Center 2 are returned
    • ACCOUNTTYPE_CC3 only rows for Cost Center 1 are returned
    • ACCOUNTTYPE_CC Cost Center rows are returned same as using (ACCOUNTTYPE_CC1 | ACCOUNTTYPE_CC2 | ACCOUNTTYPE_CC3)
// get all transactions for normal accounts
var journal = Banana.document.journal(Banana.document.ORIGINTYPE_CURRENT, Banana.document.ACCOUNTTYPE_NORMAL );

For each account used in the transaction table (AccountDebit, AccountCredit, CC1, CC2, CC3) the program generate a Journal row with the JAccount column set with the specific account. 
For a double entry account transaction that use AccountDebit, AccountCredit, AccountVat, CC1, CC2, CC3 the Journal will contain 6 rows. If the transaction has only AccountDebit and AccountCredit only 2 rows will be generated.

The column JAmount  contains the exact amount registered on the specific account. 

The returned table has all the columns of the transaction's table plus the following columns.

The return columns are:

  • JDate the date of the transction.
  • JDescription the transaction's description.
  • JRowOrigin the row number in the transaction's table (rows begin from 0).
  • JTableOrigin the source table.
  • JRepeatNumber the progressive number of the repetition of budget transactions.
  • JAccount the account for this line.
    There is one row for each account (AccountDebit, AccountCredit, AccountVat, CC1, CC2, CC3).
  • JAccountDescription the Description for this account.
  • JAccountClass the BClass number for this account.
  • JAccountGr the Gr for this account.
  • JAccountGrDescription the Gr for this account.
  • JAccountGrPath the whole Gr path.
  • JAccountCurrency the currency of thi account.
  • JAccountType as defined above (ACCOUNTTYPE_NORMAL, ACCOUNTTYPE_CC1, ...)
  • JAmount the amount in basic currency registered on the account (positive is debit, negative is credit).
  • JAmountAccountCurrency the amount in the account currency (positive i debit, regative is credit).
  • JTransactionCurrency the transaction's currency.
  • JAmountTransactionCurrency the amount in transaction's currency.
    For account with currency not in tranasctions currency the exchange rate of the transaction is used.
  • JTransactionCurrencyConversionRate is the conversion rate to obtain amounts in transaction's currency. 
    Multiply the transcation's amount in basic currency with the JTransactionCurrencyConversionRate  and you will have the amount converted in transaction's currency.
    The conversion rate has 12 significant decimal so only by very large conversion should be have conversion differences.
  • JVatIsVatOperation true if this row has a Vat code.
  • JVatCodeWithoutSign the Vat code without the evetually preceeding '-'.  For example "-V10" becomomes "V10".
  • JVatCodeDescription the Description for this Vat code.
  • JVatCodeWithMinus true if the Vat code is preceeded by "-".
  • JVatCodeNegative true if the Vat amount is negative (deductible).
  • JVatTaxable the amount VatTaxable with the sign that follow the JVatCodeNegative
  • VatTwinAccount the account where the net amount (without VAT) is being registered . 
    In case of a transaction where the Gross amount is CHF 1100, then the VAT is CHF 100 and the net amount is CHF 1000. The VatTwin account will be the account where the CHF 1000 is being registered.
    We use the name Twin for the fact that the VatTwinAccount follows the sign of the VatAccount.
    If the Vat amount is registered in debit, the VatTwinAccount will be the AccountDebit.
    If the Vat amount is registered in credit, the VatTwinAccount will be the AccountCredit.
  • JContraAccount the contra account.
    The contra account is deducted based on the other accounts and the sequence in the transactions table.
  • JContraAccountType one of the following value:
    • CONTRAACCOUNTTYPE_NONE  for no contra account
    • CONTRAACCOUNTTYPE_DIRECT when there is on the same line credit and debit accounts.
    • CONTRAACCOUNTTYPE_MULTIPLEFIRST the first line of a transaction on more accounts. 
      The first transactions after a line with debit and credit accounts or with a different date.
    • CONTRAACCOUNTTYPE_MULTIPLEFOLLOW the second or following line of a MULTIPLEFIRST with the same date.
    • CONTRAACCOUNTTYPE_VAT  the line for the Vat Account
  • JContraAccountGroup the line number corresponding to the row number of the CONTRAACCOUNTTYPE_MULTIPLEFIRST
  • JCC1 the CC1 without the preceeding sign
  • JCC2 the CC2 without the preceeding sign
  • JCC3 the CC3 without the preceeding sign
  • JSegment1 .. JSegment10 the segment relative to the account
  • JDebitAmount the amount debit in basic currency
  • JCreditAmount the amount credit in basic currency
  • JDebitAmountAccountCurrency the amount debit in account currency
  • JCreditAmountAccountCurrency the amount credit in account currency
  • JBalance the balance amount (for account card) in basic currency
  • JBalanceAccountCurrency the balance amount (for account card) in account currency
  • JOriginType as defined above
    • ORIGINTYPE_CURRENT
    • ORIGINTYPE_BUDGET
  • JOperationType on of
    • OPERATIONTYPE_NONE = 0
    • OPERATIONTYPE_OPENING = 1
      The row is generated from the opening balance
    • OPERATIONTYPE_CARRYFORWARD = 2
      The row is used from the account card and is the balance of the account at this moment.
    • OPERATIONTYPE_TRANSACTION = 3
      The row is generated from the Transactions table if it is ORIGINTYPE_CURRENT
      or from the budget table if the row is ORIGINTYPE_BUDGET
    •  OPERATIONTYPE_INVOICESETTLEMENT = 21

       

journalCustomersSuppliers([originType = ORIGINTYPE_NONE, int accountType = ACCOUNTTYPE_NONE])

Same as journal with additional settlements rows for closing invoices and the following columns:

  • JInvoiceDocType
  • JInvoiceAccountId
  • JInvoiceCurrency
  • JInvoiceStatus
  • JInvoiceDueDate
  • JInvoiceDaysPastDue
  • JInvoiceLastReminder
  • JInvoiceLastReminderDate
  • JInvoiceIssueDate
  • JInvoiceExpectedDate
  • JInvoicePaymentDate
  • JInvoiceDuePeriod
  • JInvoiceRowCustomer (1=Customer, 2=Supplier)

previousYear([nrYears])

Return the previous year as a Banana.Document object. If the previoius yeas is not defined or it is not foud it return null.

  • nrYears is the number of years to go back, default is one.
var previousYearDoc = Banana.document.previousYear();
var previousTwoYearDoc = Banana.document.previousYear(2);

projectionBalance(account, projectionStartDate [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as currentBalance but use the budget data starting from the projectionStartDate.

This functions calculate a projection of the end of year result (or specified period) combining the current data and the budget data for the period non yet booked.

if projectionStartDate is empty the result will be the same as currentBalance.

If you have already booked the 1. semester and would like to have a projection up to the end of the year

// We have booked the 1. semester and would like to have
// a projection up to the end of the yer
var cashProjection = Banana.document.projectionBalance('1000','2015-07-01');
var cashEnd = projection.balance;
var salesProjection = Banana.document.projectionBalance('3000','2015-07-01').total;
var salesForYear = -salesProjection.total;

projectionCard(account, projectionStartDate [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as currentCard but use the budget data starting from the projectionStartDate.

If projectionStart date is empty result will be the same s currentCard.

var transactions = Banana.document.projectionCard('1000','2015-01-01','','');

endPeriod([period])

Return the end date in the form of 'YYYY-MM-DD'.

The endDate and startDate functions are used to retrieve the date of the accounting, so that you can create scripts that works on file of different years.

var dateEnd = Banana.document.endPeriod();
var dateStartFebruary = Banana.document.endPeriod('2M');
  • Period:
    • If period is not present the return value is the end date of the accounting.
    • The period is added the starting account date, and than is returned the last date of the period..
    • Period (for example 2M = 2 months) is a number followed by one of the following charachters
      • D for days
      • M for months
      • Q for quarters
      • S for semesters
      • Y for years
    • Assuming that the Start accounting date is 2015-01-01
      • 1M return 2015-01-02
      • 2M return 2015-02-28
      • 2Q return 2015-06-30
      • 2S return 2015-12-31
      • 2Y return 2016-12-31

See also startDate.

startPeriod ([period])

Return the end date in the form of 'YYYY-MM-DD'.

The endPeriod and startPeriod functions are used to retrieve the date of the accounting, so that you can create scripts that works on file of different years.

var dateStart = Banana.document.endPeriod();
var dateStart3Q = Banana.document.endPeriod('3Q');
  • Period:
    If period is not present return the start date.
    • Period (for example 2M = 2 months) is a number followed by one of the following charachters
      • D is for Days
      • M for Months
      • Q for Quorters
      • S for Semester
      • Y for year
    • With 1 the starting date of the accounting is returned.
    • Assuming that the Start accounting date is 2015-01-01
      • 1M return 2015-01-01
      • 2M return 2015-02-01
      • 2Q return 2015-04-01 
      • 2S return 2015-07-01
      • 2Y return 2016-01-01

See also endDate.

vatBudgetBalance(vatCode[, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the vat amounts for the specified vat code and period, using the Budget data.

var vatTotal = Banana.document.vatBudgetBalance('V15');

vatCurrentCard(vatCode[, startDate, endDate, function(rowObj, rowNr, table) ])

Retrieve the transactions relative to the specified VatCode.

var vatTransactions = Banana.document.vatCurrentCard('V15');

vatCurrentBalance(vatCode[, startDate, endDate, function(rowObj, rowNr, table) ])

Sum the vat amounts for the specified vat code and period.
For more info see :

Example: 

var currentVat = Banana.document.vatCurrentBalance('V15','','');
var vatTaxable = currentVat.vatTaxable;
var vatPosted = currentVat.vatPosted;
  • Return value:
    Is an object that has
    • vatTaxable the amount of the taxable column
      (the sign is the same as the vatAmount)
    • vatAmount the amount of vat
    • vatNotDeductible the amount not deductible
    • vatPosted VatAmount - VatNotDeductible
    • rowCount the number of lines that have bben found and used for this computation
  • VatCode
    One or more VatCode defined in the tabel Vat Codes.
    Multiple vat code can be separated by "|" for example "V10|V20", or you can use vildcard "V*".

vatProjectionBalance(vatCode, projectionStartDate, [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as vatCurrenBalance but use the budget data starting from the projectionStartDate.

var projectionVat = Banana.document.vatProjectionBalance('V15','','');
var vatTaxable = projectionVat.vatTaxable;
var vatPosted = projectionVat.vatPosted;

vatProjectiontCard(vatCode, projectionStartDate, [, startDate, endDate, function(rowObj, rowNr, table) ])

Same as vatCurrentCard but use the budget data starting from the projectionStartDate.

var vatTransactions = Banana.document.vatProjectiontCard('V15','2015-01-01','','');

vatReport([startDate, endDate])

Return the vat report for the specified period.

Start and end date are strings in form 'YYYY-MM-DD' or a date object. If startDate is empty the accounting start date is taken. If endDate is empty the accounting end date is taken.

var vatReport = Banana.document.vatReport('','');

 

 

Help us improve the documentation

We welcome feedback on how to improve this page.

Tell us what theme needs a better explanation or how to clarify a topic.

Share this article: Twitter | Facebook | Email