Using formulas in the Budget table

Neste artigo

The addition of Javascript formulas in the Budget table of Banana Accounting Plus, opens up several possibilities.

The use of formulas is available in Banana Accounting Plus with the Advanced plan only.
See all the advantages of the Advanced plan.

Formula column

The Formula column allows you to enter calculation formulas. The estimate amounts can thus be calculated on the basis of other values of the same budgeting (see Examples of formulas).

Indicate the cost of goods based as a percentage of sales.
Increase your sales budget based as a percentage of growth.
At the end of the year, calculate the depreciation based on the value of the investments made.
Quarterly, calculate interest on the bank account based on actual usage (days / 365).
Monthly, calculate interest on the bank account based on actual usage (days / 365).

If you enter a value in the Formula column, the Amount column is calculated by the program based on that formula.

Formula Begin Column

In the Formula Begin column you can enter any formula, that is pre-pended to Formula and resolved (see documentation of the table Budget):

It is useful to specify a variable name and the assignment operator, so that you can easily change amounts:

Instead of writing in the Formula "Price= 100", you can write
Formula Begin "Price="
In Formula "100" , so you can easily see the amount and change it

Formulas in Description column

You can also enter formula in the column text, so that the text is completed automatically when you request an account card (see documentation of the table Budget).

The Formula in the Description should be entered within curly brackets preceded by the dollar sign.

Assuming that in the formula you have defined the variable Price "Price= 100"
In the Description text you can use the variable price
"Sell of goods at price ${Price}" will results in the account card in
"Sell of goods at price 100"

Within the Description column you can use any Javascript formula, but you should avoid use assignment or other calculations, and use only the Description to for retrieving values.

Example files

For examples of the formulas, refer to the following explanations:

Examples of Budget formulas

Calculation formulas in Javascript

The formula must be expressed in the Javascript language (not to be confused with the Java language).
If there is a formula (or any text), the value in the Amount column is set according to the formula result.
You can use all the functions of the Javascript language, plus the APIs provided by Banana.

Decimal separator

In JavaScript only the point "." is used as a decimal separator
If you use a different separator, the one used for numbers in the local format, the number is likely to be truncated.

Amount = result of the last instruction

In Javascript the semicolon ";" is used to separate expressions.
If the Javascript formula contains multiple expressions separated by ";" the value of the Amount column will be the result of the last executed expression.

10*3 //30 will be returned
If there is a sequence of several operations separated by a semicolon ";", the last operation will be resumed.
10*3;7; //7 will be returned
If there is a return, the value is resumed after the return.
return 10; // 10 will be returned.

Variables

Javascript variables are the most powerful elements of programming, as they let you give a name to a value stored in the computer's memory, so you can easily save, reuse, and update that value in your formulas.
Variables do not exist in Excel formulas, but are similar to the name of the cells, with the difference that the name can be freely assigned.

Javascript variables, budget formulas

What is a variable?

A variable is like a labeled box where you store a value. For example:

price = 10

This means you’re giving the name price to the value 10. You can then use "price" in other calculations:

total = price * 5

This will multiply the value of "price" (which is 10) by 5 and store the result in a new variable "total".

You can also reassign the value of a variable later:

price = 20

Variable initialization

To create a variable, just write a name, an equal sign (=), and a value.

There are two different ways to create and initialize a variable, and they behave slightly differently:

Variable with the word "var"
- ```
var price = 10
```
- This creates the variable "price" and the value 10 is assigned to it.
- The value will not appear in the Amount column. The cell looks empty.
- The value will be used and displayed only when you refer to "price" in another row.
Variable without "var"
- ```
price = 10
```
- This also creates the variable "price" and the value 10 is assigned to it.
- The value appears directly in the Amount column of the same row.
- Useful when you want to both define the variable and see its value immediately.

You can define and use variables directly within the rows. By entering the variable name in the formula, the saved value is taken over.

Nullish coalescing operator "??"

Sometimes, you want to give a variable a value only if it doesn’t already have one.
You can do this using a special symbol called the Nullish coalescing operator "??", in this case the value specified after the operator "??" is used only if the price has not been already assigned.

price = price ?? 10;

This means: “If price doesn’t already have a value, set it to 10. Otherwise, keep the current value.”

Before using "??", make sure the variable exists. You can write:

var price
price = price ?? 10

Or simply:

var price = price ?? 10

The Nullish coalescing operator is useful for repetitions, due that it allows to assign an initial value, the first time only. So you can than increment the value and by the next repetition the value will not be assigned again.

Note: The Nullish coalescing assignment operator "??=" is not supprted.

Objects

Javascipt objects are variables that allow you to save multiple values, each indicated with a property.
The prices object is created below, indicating the curly brackets. To access and save the values, instead use the square brackets or indicate in the name of the property after the name of the object.

prices = {}
prices['car'] = 10;
prices.car = 10;
prices['computer'] = 20;
prices.computer = 20;

Array

Javascript Arrays are created using square brackets and also to access them.
The first element of the array has index 0.

costs = [1,2,3];
costs[0]=3;
result = prices['car'] - costs[0];

Repetition and variables

For more information on the calculation sequence, see Planning Logic.

All rows (including those created for repetitions) are sorted by date. If there are rows with the same date, the order is that of insertion in the table.
After the sorting, the rows (including the formulas) are recalculated.
In the formulas, you therefore only have access to the budget data up to that date.
A variable must be defined in a row that has the date preceding the row where it is used.
If the budget entry has a repetition the variable will be reassigned each time.
sum = 10;
If you want to calculate the grand total instead.
- In an initial line create the variable with value zero.
  sum = 0;
- In the line that repeats in the sum also include the previous value.
  sum = sum + 10;
  or use the "+="
  sum += 10
Use the Nullish coalescing operator "??" for assigning a value to a variable that is not yet initialized.

Automatic variables

budgetCurrent
It is a table that contains the budget rows after the repetitions creation.
These are used to record values in conjunction with the JRepeatNumber.
DEBUG is a variable that can be "true" or "false".
If "true", in the messages, all the results of the formulas are being displayed.
row
Is a Javascript object that refers to the current row.
- The values of the cells can be retrieved with the value function ("columnNameXml").
  row.value("date") returns to the date of the transaction.
- row.value ("JRepeatNumber") returns the progressive of the repetition.
  The first repetition is 0.
_totalPrice
This is the value of the Quantity column multiplied by the UnitPrice column.
Equivalent to the formula "row.value('Quantity')*row.value('UnitPrice')"

Budget Functions

In addition to the budget API defined in the accounting class API, there are specific functions.

budgetGetPeriod(tDate, period)

This function is used in combination with the use of repetition.
When repetitions are indicated, it is advisable to refer to a calculation period and not to a precise date.

Parameter tDate.
The date to which the calculation of the period refers. Usually the date of the recording line.
Period parameter.
Abbreviations
- For the current
  - "BC" current bi-monthly (2 months)
  - "DC" current day
  - "MC" current month
  - "QC" current trimester
  - "SC" current half-year
  - "YC" current year.
  - "WC" current week
- For previous
  - "BP" previous bi-monthly
  - "DP" previous day
  - "MP" previous month
  - "QP" previous quarter
  - "SP" previous shalf-year
  - "YP" previous year
  - "WP" previous week
Returned values.
An object composed of two dates
- startDate
- endDate

// example
t = BudgetGetPeriod ('2015-01-01', 'MP') returns
t.startDate // 2014-12-01
t.endDate // 2014-12-31

Specific budget functions

The following are similar to those available with Banana.document, but can be used without indicating the object Banana.document.

To be taken into account:

Instead of the startDate parameter, you can use one of the abbreviations "MC", "MP", explained in the budgetGetPeriod.
If you use an abbreviation, the function calculates the start and end date of the period, based on the date of the current registration.
It makes sense to use the end date only if it is earlier than the row date.
If it is equal or higher, it has no effect because the values after the current row are not yet available, because they have not been processed.
If the registration row date is April 15th:
- budgetBalance("1000","MP") the balance of the 1000 account returns at the end of March.
- budgetBalance("1000","MC") returns the balance at the current time is the same as budgetBalance("1000").
- budgetTotal("1000","QP") returns total of the movement for the previous quarter.
- budgetTotal("1000","QC") returns the total of the movement for the previous quarter, up to the current date.

budgetBalance(account, startDate, endDate, extraParam)

The balance up to the current row.

budgetBalance('1000', 'MP'); //returns the balance of 1000 at the end of the previous month

budgetOpening(account, startDate, endDate, extraParam)

The balance at the beginning of the period.

budgetTotal(account, startDate, endDate, extraParam)

The difference between the debit and the credit movement of the period.

budgetTotal('1000', 'MC'); //returns the total movement of the 1000 account for the current month

budgetInterest( account, interest, startDate, endDate, extraParam)

Calculates the interest on an account, for the period indicated (at the maximum the current date)
If you use it to calculate interest on an account at the end-of-period, the row where the formula is shown should always be the last one for this date.

Account parameter
This is the account number on whose movements interest will be calculated
Interest parameter,
Indicates the interest rate in percent.
- Positive (2.5, 4, 10) calculates the interest on the account's debit movement
- Negative (-2.5, -4, -10) calculates the interest on the account's credit movement.

credit(amount)

If the amount parameter is negative returns the amount as a positive
credit(-100) // returns 100
If the amount parameter is positive, returns 0 (zero)
credit(100) // returns 0

This function is useful in conjunction with the other budgetBalance functions to work only on the balances you need.
If you want to calculate the percentage on sales, using this function is easier.
credit(budgetTotal('1000')) // enter the value only if it is a negative.

debit(amount)

If the amount parameter is positive, returns the amount
debit(100) // returns 100
If the amount parameter is negative, returns 0 (zero)
debit(-100) // returns 0

Useful if you have to make calculations using only the debit amount and avoid using the credit amount

include

Includes and executes a Javascript file, with the possibility to create its own functions and variables that can be recalled in the script.

Include "file:test.js"
Executes the contents of the indicated file. The name refers to the file on which one is working.
Include "documents:test.js"
Executes the contents of the text document contained in the documents table.
This has to be a file of the "text/javascript" type.

Functions for multi-currency accounting

They can also be used for accounting without multi-currency, in this case the account is always in basic currency.

budgetBalanceCurrency(account, startDate, endDate, extraParam)

The balance in the account currency up to the current line.

budgetExchangeDifference (account, [date, exchangeRate])

This formula recalls the Banana.document.budgetExchangeDifference function.

budgetOpeningCurrency(account, startDate, endDate, extraParam)

The balance in the account currency at the beginning of the period.

budgetTotalCurrency(account, startDate, endDate, extraParam)

Starting with version 9.01, the following functions are also included : budgetCreditCurrency, budgetDebitCurrency.

User defined functions

The user can define personal function with the JavaScript language, and recall them in the formulas.
It is possible to define functions:

Directly in a formula
In a JavaScript coded attachment; this attachment must have as row id the "_budget.js" name
In a text of the Documents table, that needs to be included with the Include command

Note:
In the user functions it is currently not possible to use functions that access current accounting data (currentBalance, ...).

function Taxcalculation(profit)
{
   var percentage = 10;
   if (profit > 50000)
      percentage = 10;
   else if (profit > 100000)
      percentage =20;
   return profit * percentage / 100;
}

Banana Accounting Plus GPT Budget formula creator

The GPT budget formula creator is only experimental: it might be subject to changes and improvements.

This Banana Accounting Plus GPT was created using ChatGPT and is designed to generate calculation formulas to be used in the Budget table of Banana Accounting Plus.

Requirements

To use this GPT, you must have a ChatGPT Plus subscription. Without it, you can try the service for free for up to ten requests. After that, you’ll need to either upgrade to ChatGPT Plus or wait some hours before making additional requests.

How it works

Open the Banana GPT from this link:
- https://chatgpt.com/g/g-67efab70619c81918bab4f7bdf67a803-budget-formula-creator-gpt-banana-accounting
In+the Message field at the bottom, enter a sentence describing what formula you want to create. Explain what your purpose is. Generally, more specific requests will yield better results.
GPT will process your request and return the corresponding result.
Based on the input provided to the GPT, examples and text strings are generated containing the formulas.
Copy the generated formulas.
Paste the copied formulas into the Formula column of the Budget table in Banana Accounting Plus.

Formula column

Formula Begin Column

Formulas in Description column

Example files

Calculation formulas in Javascript

Decimal separator

Amount = result of the last instruction

Variables

What is a variable?

Variable initialization

Nullish coalescing operator "??"

Objects

Array

Repetition and variables

Automatic variables

Budget Functions

budgetGetPeriod(tDate, period)

Specific budget functions

budgetBalance(account, startDate, endDate, extraParam)

budgetOpening(account, startDate, endDate, extraParam)

budgetTotal(account, startDate, endDate, extraParam)

budgetInterest( account, interest, startDate, endDate, extraParam)

credit(amount)

debit(amount)

include

Functions for multi-currency accounting

budgetBalanceCurrency(account, startDate, endDate, extraParam)

budgetExchangeDifference (account, [date, exchangeRate])

budgetOpeningCurrency(account, startDate, endDate, extraParam)

budgetTotalCurrency(account, startDate, endDate, extraParam)

User defined functions

Banana Accounting Plus GPT Budget formula creator

Requirements

How it works

Help us improve the documentation

Choose your region