In this article
Introduction
This walkthrough provides step-by-step guidance for creating an Extension for printing a cash flow report.
As example we use the Cash Flow Report that is part of the Rapports comptables (OHADA - RDC) Extension, developed following the specifications for the OHADA-RDC in Africa (for more information, visit the GitHub documentation).
There are three basic steps in order to experiment with this Extension:
- Prepare for programming
- Create a JavaScript programming
- Install the Extension
- Run the Extension
Preparation work
Before you start programming, you need to define how the output should look like and how its calculations will be done.
With the following information the software developer will be able to program the software:
- Prepare an accounting file with:
- The accounting plan you will use as the base for the report.
The Cash flow report is related to a specific accounting plan, its accounting groups and its numbers. - The data that is necessary to test if the report is correct.
- Opening balances for all the accounts and groups that partecipate in the calculation.
Possibly use simple to understand amounts (10, 20, 100, 1000). - Transactions for all relevant accounts.
- Opening balances for all the accounts and groups that partecipate in the calculation.
- The accounting plan you will use as the base for the report.
- Decide how the report should look like.
If you aren't already in process of an example printout, use Excel and prepare an example of the desired printout.
On the left side ad a column with the name for each row.
See OHADA example. - Explain for each row how the calculation for the related row of the report should be done.
See OHADA example.- Specify the accounts or groups that will be summed
- Specify amounts that should be entered manually.
In case the information cannot be retrieved from the accounting data (lack of account), the programmer can easily create a dialog in order to enter the information manually.
How to specify calculations for programmers
In order to understand how to specify calculations for programmers, you need to know which amounts you can retrieve from the Accounts table of Banana Accounting.
You can retrieve amounts for both, accounts and groups, and specify the type of amount:
- opening: the amount at the beginning of the period (the Opening column of the Accounts table). Can be positive or negative.
- debit: the amount of debit transactions for the period (the Debit column of the Accounts table). Only positive values.
- credit: the amount of credit transactions for the period (the Credit column of the Accounts table). Only positive values.
- total: the difference between debit-credit for the period. Can be positive or negative.
- balance: the balance for the period (opening + total). Can be positive or negative.
The combination of account/group and amounts type can be used to specify the calculations for programmers:
- In case of accounts, indicate "account_number , amount_type" (i.e. "1000 , opening").
- In case of groups, indicate "Gr=group_number , amount_type" (i.e. "Gr=10 , total").
See the table below for more examples:
| Row name | Formula | Description |
|---|---|---|
| ZA | getAmount(Gr=BT, opening) | Takes the opening amount of the group BT |
| FA | + (-1) getAmount(Gr=134, total) + getAmount(6541, total) + getAmount(6542, total) - (-1)getAmount(7541, total) - (-1)getAmount(7542, total) + (-1)getAmount(Gr=136, total) + (-1)getAmount(Gr=TO, total) - getAmount(Gr=RP, total) - getAmount(Gr=RQ, total) - getAmount(Gr=RS, total) |
add (inverted) total amount of group 134 add total amount of account 6541 add total amount of account 6542 subtract (inverted) total amount of account 7541 subtract (inverted) total amount of account 7542 add (inverted) total amount of group 136 add (inverted) total amount of group TO subtract total amount of group RP subtract total amount of group RQ subtract total amount of group RS |
| FB | getAmount(488, total) | Takes the movement of the period (total) for the account 488 |
| FJ | + getAmount(826, credit) + getAmount(Gr=AS-1, credit) - getAmount(4856, debit) |
add credit amount of account 826 add credit amount of group AS-1 subtract debit amount of account 4856 |
Example of the "FA" row in the report with the sum of all the amounts:
Here is the Javascript code for the calculation of each line. For a better control there has been created a function for each row, that retrieves and sums the values for a specific period and then returns the amount to be printed.
function calculate_ZA(banDoc, startDate, endDate) {
/*
Gr=BT,opening - (- Gr=DT,opening)
*/
var grBT = getAmount(banDoc,'Gr=BT','opening',startDate,endDate);
var grDT = getAmount(banDoc,'Gr=DT','opening',startDate,endDate);
return Banana.SDecimal.subtract(grBT, Banana.SDecimal.invert(grDT));
}
function calculate_FA(banDoc, startDate, endDate) {
/*
+ (-Gr=134, total)
+ account 6541, total
+ account 6542, total
- (-account 7541, total)
- (-account 7542, total)
+ (-Gr=136, total)
+ (-Gr=TO, total)
- Gr=RP, total
- Gr=RQ, total
- Gr=RS, total
*/
var gr134 = getAmount(banDoc,'Gr=134','total',startDate,endDate);
var acc6541 = getAmount(banDoc,'6541','total',startDate,endDate);
var acc6542 = getAmount(banDoc,'6542','total',startDate,endDate);
var acc7541 = getAmount(banDoc,'7541','total',startDate,endDate);
var acc7542 = getAmount(banDoc,'7542','total',startDate,endDate);
var gr136 = getAmount(banDoc,'Gr=136','total',startDate,endDate);
var grTO = getAmount(banDoc,'Gr=TO','total',startDate,endDate);
var grRP = getAmount(banDoc,'Gr=RP','total',startDate,endDate);
var grRQ = getAmount(banDoc,'Gr=RQ','total',startDate,endDate);
var grRS = getAmount(banDoc,'Gr=RS','total',startDate,endDate);
var res = 0;
res = Banana.SDecimal.add(res, Banana.SDecimal.invert(gr134));
res = Banana.SDecimal.add(res,acc6541);
res = Banana.SDecimal.add(res,acc6542);
res = Banana.SDecimal.subtract(res, Banana.SDecimal.invert(acc7541));
res = Banana.SDecimal.subtract(res, Banana.SDecimal.invert(acc7542));
res = Banana.SDecimal.add(res, Banana.SDecimal.invert(gr136));
res = Banana.SDecimal.add(res, Banana.SDecimal.invert(grTO));
res = Banana.SDecimal.subtract(res,grRP);
res = Banana.SDecimal.subtract(res,grRQ);
res = Banana.SDecimal.subtract(res,grRS);
return res;
}
function calculate_FB(banDoc, startDate, endDate) {
/*
account 488, total
*/
return getAmount(banDoc,'488','total',startDate,endDate);
}
function calculate_FJ(banDoc, startDate, endDate) {
/*
+ account 826, credit
+ Gr=AS-1, credit
- 4856, debit
*/
var acc826 = getAmount(banDoc,'826','credit',startDate,endDate);
var grAS1 = getAmount(banDoc,'Gr=AS-1','credit',startDate,endDate);
var acc4856 = getAmount(banDoc,'4856','debit',startDate,endDate);
var res = 0;
res = Banana.SDecimal.add(res,acc826);
res = Banana.SDecimal.add(res,grAS1);
res = Banana.SDecimal.subtract(res,acc4856);
return res;
}
Create the JavaScript programming
The script retrieves all the required data from Banana Accounting files, makes some addition and subtraction operations, and presents the data in a table.
By looking a the source code for Cash Flow Report (OHADA - RDC) you will understand how the report is setup.
If you want to experiment with the script, copy and paste it on your text editor and save the file as .js (i.e. cashflow.js). Otherwise you can just install and run the app following the GitHub documentation.
Retrieve data from Banana
In order to build a cash flow it is required to get different data from the accounting. These data are values related to specific accounts or groups and to some columns of the table accounts.
But how to do that? How to retrieve a specific account value for a specific column of the accounts table and period?
Function currentBalance()
To retrieve the various amounts of the report, we use the currentBalance(account, startDate, endDate) function.
The function sums the amounts of opening, debit, credit, total and balance calculated based on the opening and all transactions for the given accounts/group and period.
To build the cash flow report we use this function for accounts and groups:
// example for account 1000
var currentBal = Banana.document.currentBalance('1000','2019-01-01','2019-12-31');
// example for group 10
var currentBal = Banana.document.currentBalance('Gr=10','2019-01-01','2019-12-31');
The parameters of the function are:
- account or group number (the group number is preceded by "Gr=")
- start date of the period we are intrested
- end date of the period we are intrested
The returned value of the currentBalance() function is an object, which has name:values pairs called properties. These properties are the values we need.
The object structure is like the following one:
{
"amount":"17570.00",
"amountCurrency":"",
"bClass":"1",
"balance":"17570.00",
"balanceCurrency":"",
"credit":"30.00",
"creditCurrency":"",
"debit":"16600.00",
"debitCurrency":"",
"opening":"1000.00",
"openingCurrency":"1000.00",
"rowCount":"6",
"total":"16570.00",
"totalCurrency":""
}
As you can see this object has many properties, but the cash flow report we want to build uses only four of them:
- opening the amount at the beginning of the period (the Opening column of the Accounts table). Can be positive or negative.
- debit the amount of debit transactions for the period (the Debit column of the Accounts table). Only positive values.
- credit the amount of credit transactions for the period (the Credit column of the Accounts table). Only positive values.
- total the difference between debit-credit for the period. Can be positive or negative.
- balance the balance for the period. (opening + total). Can be positive or negative.
For more information, see the documentation here.
Accessing Object Properties
Ok, now we have the object with all the properties. But how to get a single property value?
There are three methods for accessing the property of an object:
// method 1: objectName.property
var value = currentBal.debit; // returns 16600.00
// method 2: objectName["property"]
var value = currentBal["credit"]; // returns 30.00
// method 3: objectName[expression]
var x = "total";
var value = currentBal[x]; // returns 16570.00
It doesn't matter which method is used, the result does not change.
Calculate totals
The cash flow report requires to do addition and subtraction operations using some specific values retrieved from the accounting file.
To build all the various totals we encounter in the report we use the add(value1, value2) and the subtract(value1, value2) functions.
// example sum the amounts of accounts 6541 and 6542
var acc6541 = Banana.document.currentBalance('6541','2019-01-01','2019-12-31').total;
var acc6542 = Banana.document.currentBalance('6542','2019-01-01','2019-12-31').total;
var sum = Banana.SDecimal.add(acc6541, acc6542);
Previous year Banana document
To generate the report, the Extension retrieves data from the current year accounting file and from the previous year accounting file.
The current year accounting file is the one that is opened in Banana, the one that starts the execution of the Extension.
The previous year accounting file is not opened in Banana, it is just selected from the menu File -> File and accounting properties... -> Options tab -> File from previous year.
In order to retrieve data from the previous year we use the previousYear([nrYears]) function.
The function returns the previous year as a Banana.Document object. If the previous year is not defined or it is not found it returns null.
/* CURRENT year file: the opened document in Banana */
var current = Banana.document;
/* PREVIOUS year file: open a dialog window to select the previous year .ac2 file */
var previous = Banana.document.previousYear();
The object Banana.document represent the current document opened in the application.
The previous variable represent the defined previous year document.
Function getAmount()
We have added to the script a parameterized function that calls the currentBalance() and retrieves the value for the given parameters.
With this function it is possible to define which value to extract and from which Banana document file.
function getAmount(banDoc,account,property,startDate,endDate) {
var currentBal = banDoc.currentBalance(account,startDate,endDate);
var value = currentBal[property];
return value;
}
The parameters are:
- banDoc: the Banana document from which retrieve the data (see Open Banana document);
- account: the account or group;
- property: the property of the returned currentBalance() object (i.e. opening, debit, credit, total);
- startDate: the opening date of the accounting period;
- endDate: the closing date of the accounting period;
// retrieve from the current year Banana document the 6541 account's total value
var current6541 = getAmount(current,'6541','total','2019-01-01','2019-12-31');
// retrieve from the previous year Banana document the 6541 account's total value
var previous6541 = getAmount(previous,'6541','total','2018-01-01','2018-12-31');
The use of the function is the same, but the returned values are different: one returns the value of the current year and the other the value of the previous year.
The Dates
Dates that we use in the script are taken from the accounting file using the info(section, id) function.
These dates are retrieved from the Opening and Closing dates of the accounting file (File properties > Accounting Tab).
// Accounting period for the current year file
var currentStartDate = current.info("AccountingDataBase","OpeningDate");
var currentEndDate = current.info("AccountingDataBase","ClosureDate");
// Accounting period for the previous year file
var previousStartDate = previous.info("AccountingDataBase","OpeningDate");
var previousEndDate = previous.info("AccountingDataBase","ClosureDate");
Function toLocaleNumberFormat()
The function toLocaleNumberFormat is used to convert all the amount numbers to the local format.
Banana.Converter.toLocaleNumberFormat('16570.00'); // returns 16'570.00
Install and run the Extension
Visit the Install your Extension documentation to install and run the app.
Report example:
