Dans cet article
This page explains:
- How to create a Banana Accounting file with double registration without VAT starting from Excel data using the Excel Office Script module.
- How to use the http POST protocol with Banana APIs to transfer the current and budget transactions, the chart of accounts into Banana Accounting Plus from the Excel file.
- How to create financial statement and income statement reports in Banana with this data.
Prerequisites
For use these functions you need to have:
- Download and install Banana Accounting Plus.
- Have the Advanced plan of Banana Accounting Plus.
- Have Excel for Windows (version 2210 or higher) or Excel for Mac.
- Use OneDrive for Business.
- Configure the webserver in Banana Accounting Plus based on your operating system.
Note
- The entered budget and transaction data must be considered over the period defined in the FileInfo in order to be calculated and evaluated
Steps to Integrate Excel Office Script
After checking the prerequisites for this use case, you can follow the next steps:
- Download the example Excel with data.
- Create a new Office Script file in Excel, copy and paste the example code into the code editor in Office Script.
- Open Banana Accounting Plus.
- Make sure that the Webserver is working.
- Save and Run the code in Office Script.
- See the results in the Banana Accounting Plus.
The Office Script will do the following:
- Create a Banana Accounting Json Document Change Object
- For each Excel Sheet:
- Read the content of the Excel Sheet.
- Add the data to the Json Document Change Object.
- Send the Json Document Change Object to the Banana Web Server using the http POST method.
The Banana Accounting software will create a new file and display within the software.
The structure of the Excel file
The file is divided into several sheets, each corresponding to the data structure and Table in the Banana Accounting file.
- FileInfo: Banana Accounting File Properties.
- Accounts: Accounts Table.
- Transactions: Transactions Table.
- Budget: Budget Table.
All the data you enter into Excel sheets are used to create a basic Json structure (documentchange) that must pass into Banana Accounting Plus.
FileInfo
In this sheet, you will enter basic business data. This information is essential to ensure that all entries are consistent and properly contextualized. Here you will be able to specify:
- Company: The company name, that is the name and legal form.
- Opening and closure date: The date the accounts were opened and closed, that are related to period that you want evalueted your accounting.
- Basis currency: The currency to be used.
Accounts
This sheet is dedicated to creating the chart of accounts. Here you will be able to define:
- The account names and codes.
- The type of account.
- The groupings required for summing account groups.
- For example, you will be able to sum all accounts receivable to get the total assets, thus facilitating the preparation of the fiancial statement and income statement.
The columns of the sheet Accounts in Excel file refer the same table in Banana Accounting Plus.
The accounting sheet contains the main data for constructing the chart of accounts based on the document change structure and later creating the Financial statement and Income Statement reports, with the same columns that find in Banana Accounting Plus:
Transactions
In this sheet, you will record:
- All accounting transactions for the current year.
The entries will include key data such as:
- Date: The date of the transaction.
- Description: Transaction description.
- AccountDebit: Debit account.
- AccountCredit: Credit account.
- Amount: Amount of your transaction.
This will enable you to keep track of financial transactions in a detailed and organized manner.
The sheets of Transactions and Budget have the same columns, but refer to different table.
To understand and learn more about the use of these columns you can look at this link Transactions.
Budget
The entries will include key data such as:
- Date: The date of the budget transaction.
- Description: Budget transaction description.
- AccountDebit: Debit account.
- AccountCredit: Credit account.
- Amount: Amount of your budget transaction.
Finally, the Budget sheet is dedicated to planning future expenses.
Here you will be able:
- To enter the projected expenses you intend to incur, taking into account the closing date indicated on the FileInfo sheet.
- Monitor and manage the company budget proactively.
These data even though they have the same columns as transactions will be recorded in the Budget table for future forecasting.
For further study: Budget Table
Script code in Office Script
The code is structured as:
- The function main.
- An Office Script for Excel must include a main function with the ExcelScript.Workbook as its first parameter.
- When you execute a function, the Office Script calls the main function by providing the workbook as first parameter.
- ExcelScript.Workbook should always be first parameter.
- The function is defined async because the script needs to interact with APIs (forsend data).
Using async allows you to handle these calls without blocking the execution of the rest of the script.
- Initialization in the jsonData variable based JSON who are structured as DocumentChange that have an array of dataUnits objects defined by the following properties:
- FileInfo.
- Transactions.
- Budget.
- Accounts.
- For each sheet:
- There is the initialization of the parameters of the range that are used to read the cells.
- There are the reading the rows containing the data needed to build the DocumentChange and inserting them into the rows object array of variable jsonData.
- To send the data contained in the jsonData variable to Banana Accounting Plus using the function createAc2(jsonData: JSON).
- The body of request:
- jsonData
represents the data of Documentchange that need to create a file accounting in Banana Accounting. - fileType
that define the which type of accounting you want to create in Banana Accounting.- docGroup: if you set the number 100 you create a file with double-entry accounting.
- docApp: if you set the number 100 you create a file without VAT.
- decimals: is the number of decimal digits (default value is 2).
- jsonData
- HTTP method:
- POST.
- Header:
- 'Content-Type': 'application/json'.
- Is defined to Async:
- The script needs to interact with APIs.
- Errors:
- To verify the success of the Banana API call, I print an error message if a problem occurs.
- Endpoint:
- http://<_PASSWORD>/v2/doc?show&acstkn=<_LOCALHOST+ >
- The _PASSWORD variable must be replaced with your own token password used to configure the Banana Accounting Plus webserver as described in Integrated Web Server
- The _LOCALHOST variable must be replaced with your compatible with your operating system if it is Windows or if is Mac, have a different localhost.
- The body of request:
- Formatting the date with the function formatDateToYyyyMmDd(date: number | string | number | boolean): string
- date: Office Script when reading a cell, there are different types of data and it is not possible to impose which input format arrives for reading, so to avoid possible errors it has been integrated as possible data: boolean, number and string.
- The date is first verified as a number, because at the time of reading the cell the data is in number and subsequently if it is a number it is converted to a string with a calculation because the format being read is a number and the destination format in Banana accepts the string format.
Note
- You can add rows to create more records or change data of row without modifying the code.
- If you move, delete, or add new columns, you must update the column references in the code accordingly.
- Adding Rows:
You can add a row in the Transactions sheet (or any other sheet) following the existing column order without any code changes.
Adding Columns:
If you add a column in the Transactions sheet to include a new field, you must adapt the code to reflect this change in the document structure.
The structure of Json data that rapresent the Document Change Object
The Office Scipt will generate a DocumentChange Json object with the following properties:
- FileInfo: refers to the FileInfo table of Banana Accounting.
- Accounts: refers to the Accounts table of Banana Accounting.
- Transactions: refers to the Transactions table of Banana Accounting.
- Budget: refers to the Budget table of Banana Accounting.
These properties of the DocumentChange object are technically called nameXml and refer to the data structured, that in this is the tables of Banana Accounting.
These properties are directly connected to the structure of the Banana Accounting and are used to pass data to the corresponding table.
For further details and references, please see the Structure of DocumentChange and DocumentChange JSON.
Code
async function main(workbook: ExcelScript.Workbook) {
// Initialize JSON structure.
// The JSON is used by the documentChange to add rows in Transactions, Budget and Accounts table.
// The data of property FileInfo in JSON is related of the properties in the Accounting File
// https://www.banana.ch/doc/en/node/9841#example_adding_a_row+ var jsonData = {
"format": "documentChange",
"error": "",
"data": [
{
"document": {
"dataUnits": [
{
"nameXml": "FileInfo",
"data": {
"rowLists": [
{
"nameXml": "Base",
"rows": []
}
]
}
},
{
"data": {
"rowLists": [
{
"rows": []
}
]
},
"nameXml": "Transactions"
},
{
"data": {
"rowLists": [
{
"rows": []
}
]
},
"nameXml": "Budget"
},
{
"data": {
"rowLists": [
{
"rows": []
}
]
},
"nameXml": "Accounts"
}
]
}
}
]
};
//FILE INFO
// Get the specific sheet
const sheetFileInfo = workbook.getWorksheet("FileInfo");
// Get the range of cells used in the sheet
const usedRangeFileInfo = sheetFileInfo.getUsedRange();
const columnRangeFileInfo = sheetFileInfo.getUsedRange().getRow(0).getColumnCount();
// Get the last used row of the sheet
const lastRowFileInfo = usedRangeFileInfo.getLastRow().getRowIndex();
// Get data range of File Info
const companyRange = sheetFileInfo.getRange(`A2:A${lastRowFileInfo + 1}`).getValues();
const openingDateRange = sheetFileInfo.getRange(`B2:B${lastRowFileInfo + 1}`).getValues();
const closingRange = sheetFileInfo.getRange(`C2:C${lastRowFileInfo + 1}`).getValues();
const basicCurrencyRange = sheetFileInfo.getRange(`D2:D${lastRowFileInfo + 1}`).getValues();
// iteration on each row of the columns.
let companyValue = companyRange[0][0];
let openingDateValue = openingDateRange[0][0];
let closureDateValue = closingRange[0][0];
let basisCurrencyValue = basicCurrencyRange[0][0];
jsonData.data[0].document.dataUnits[0].data.rowLists[0].rows.push({
"fields": {
"SectionXml": "AccountingDataBase",
"IdXml": "Company",
"ValueXml": companyValue.toString()
},
"operation": {
"name": "modify"
}
});
jsonData.data[0].document.dataUnits[0].data.rowLists[0].rows.push({
"fields": {
"SectionXml": "AccountingDataBase",
"IdXml": "OpeningDate",
"ValueXml": formatDateToYyyyMmDd(openingDateValue)
},
"operation": {
"name": "modify"
}
});
jsonData.data[0].document.dataUnits[0].data.rowLists[0].rows.push({
"fields": {
"SectionXml": "AccountingDataBase",
"IdXml": "ClosureDate",
"ValueXml": formatDateToYyyyMmDd(closureDateValue)
},
"operation": {
"name": "modify"
}
});
jsonData.data[0].document.dataUnits[0].data.rowLists[0].rows.push({
"fields": {
"SectionXml": "AccountingDataBase",
"IdXml": "BasicCurrency",
"ValueXml": basisCurrencyValue.toLocaleString()
},
"operation": {
"name": "modify"
}
});
// TRANSACTIONS
// Get the specific sheet
const sheetTransactions = workbook.getWorksheet("Transactions");
// Get the range of cells used in the sheet
const usedRangeTransactions = sheetTransactions.getUsedRange();
// Get the last used row of the sheet
const lastRowTransactions = usedRangeTransactions.getLastRow().getRowIndex();
// Get data range of Transactions
const dateRange = sheetTransactions.getRange(`A2:A${lastRowTransactions + 1}`).getValues();
const descriptionRange = sheetTransactions.getRange(`B2:B${lastRowTransactions + 1}`).getValues();
const accountDebitRange = sheetTransactions.getRange(`C2:C${lastRowTransactions + 1}`).getValues();
const accountCreditRange = sheetTransactions.getRange(`D2:D${lastRowTransactions + 1}`).getValues();
const amountRange = sheetTransactions.getRange(`E2:E${lastRowTransactions + 1}`).getValues();
//Iteration on each row of the columns.
for (let i = 0; i < dateRange.length; i++) {
let dateValue = dateRange[i][0]; // Value of column A (Date)
let descriptionValue = descriptionRange[i][0]; // Value of column B (Description)
let accountDebitValue = accountDebitRange[i][0]; // Value of column C (AccountDebit)
let accountCreditValue = accountCreditRange[i][0]; // Value of column D (AccountCredit)
let amountValue = amountRange[i][0]; // Value of column E (Amount)
// adds "fields" object to "rows"
jsonData.data[0].document.dataUnits[1].data.rowLists[0].rows.push({
"fields": {
"Date": formatDateToYyyyMmDd(dateValue),
"Description": descriptionValue.toString(),
"AccountDebit": accountDebitValue.toString(),
"AccountCredit": accountCreditValue.toString(),
"Amount": amountValue.toString()
},
"operation": {
"name": "add"
}
});
}
//BUDGET
// Get the specific sheet
const sheetBudget = workbook.getWorksheet("Budget");
// Get the range of cells used in the sheet
const usedRangeBudget = sheetBudget.getUsedRange();
// Get the last used row of the sheet
const lastRowBudget = usedRangeBudget.getLastRow().getRowIndex();
// Get data range of BUDGET
const dateRangeBudget = sheetBudget.getRange(`A2:A${lastRowBudget + 1}`).getValues();
const descriptionRangeBudget = sheetBudget.getRange(`B2:B${lastRowBudget + 1}`).getValues();
const accountDebitRangeBudget = sheetBudget.getRange(`C2:C${lastRowBudget + 1}`).getValues();
const accountCreditRangeBudget = sheetBudget.getRange(`D2:D${lastRowBudget + 1}`).getValues();
const amountRangeBudget = sheetBudget.getRange(`E2:E${lastRowBudget + 1}`).getValues();
// iteration on each row of the columns.
for (let i = 0; i < dateRangeBudget.length; i++) {
let dateValueBudget = dateRangeBudget[i][0]; // Value of column A (Date)
let descriptionValueBudget = descriptionRangeBudget[i][0]; // Value of column B (Description)
let accountDebitValueBudget = accountDebitRangeBudget[i][0]; // Value of column C (AccountDebit)
let accountCreditValueBudget = accountCreditRangeBudget[i][0]; // Value of column D (AccountCredit)
let amountValueBuget = amountRangeBudget[i][0]; // Value of column E (Amount)
jsonData.data[0].document.dataUnits[2].data.rowLists[0].rows.push({
"fields": {
"Date": formatDateToYyyyMmDd(dateValueBudget),
"Description": descriptionValueBudget.toString(),
"AccountDebit": accountDebitValueBudget.toString(),
"AccountCredit": accountCreditValueBudget.toString(),
"Amount": amountValueBuget.toString()
},
"operation": {
"name": "add"
}
});
}
// ACCOUNTS
// Get the specific sheet
const sheetAccount = workbook.getWorksheet("Accounts");
// Get the range of cells used in the sheet
const usedRangeAccounts = sheetAccount.getUsedRange();
// Get the last used row of the sheet
const lastRowAccounts = usedRangeAccounts.getLastRow().getRowIndex();
// Get data range of ACCOUNTS
const sectionRange = sheetAccount.getRange(`A2:A${lastRowAccounts + 1}`).getValues();
const groupRange = sheetAccount.getRange(`B2:B${lastRowAccounts + 1}`).getValues();
const accountRange = sheetAccount.getRange(`C2:C${lastRowAccounts + 1}`).getValues();
const descRange = sheetAccount.getRange(`D2:D${lastRowAccounts + 1}`).getValues();
const bclassRange = sheetAccount.getRange(`E2:E${lastRowAccounts + 1}`).getValues();
const sumInRange = sheetAccount.getRange(`F2:F${lastRowAccounts + 1}`).getValues();
const grRange = sheetAccount.getRange(`G2:G${lastRowAccounts + 1}`).getValues();
// Iteration on each row of the columns.
for (let i = 0; i < accountRange.length; i++) {
let sectionValue = sectionRange[i][0]; // Value of column A (section)
let groupValue = groupRange[i][0]; // Value of column B (Group)
let accountValue = accountRange[i][0]; // Value of column C (Account)
let descValue = descRange[i][0]; // Value of column D (Description)
let bclassValue = bclassRange[i][0]; // Value of column E (BClass)
let sumInValue = sumInRange[i][0]; // Value of column F (Sum In)
let gr1Value = grRange[i][0]; // Value of column G (Gr1)
// adds "fields" object to "rows"
jsonData.data[0].document.dataUnits[3].data.rowLists[0].rows.push({
"fields": {
"Section": sectionValue.toString(),
"Group": groupValue.toString(),
"Account": accountValue.toString(),
"Description": descValue.toString(),
"BClass": bclassValue.toString(),
"Gr": sumInValue.toString(),
"Gr1": gr1Value.toString()
},
"operation": {
"name": "add"
}
});
}
let jsonString = JSON.stringify(jsonData);
createAc2(JSON.parse(jsonString));
}
async function createAc2(jsonData: JSON) {
// Web server url
let _LOCALHOST = "http://localhost:8081"; //for macOS use "https://127.0.0.1:8089";
// Replace with your Webserver password
let _PASSWORD = "My_Password";
// Request to Banana web server
let url = _LOCALHOST + "/v2/doc?show&acstkn=" + _PASSWORD;
// Accounting type parameters
let fileTypeGroup = 100; // Double entry accounting
let fileTypeNumber = 100; // without VAT
let fileDecimals = 2;
try {
// Body of the request
const httpBody = {
fileType: {
accountingType: {
docGroup: fileTypeGroup,
docApp: fileTypeNumber,
decimals: fileDecimals
}
},
data: jsonData,
}
const response = await fetch(url, {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(httpBody)
});
} catch (error) {
console.log(error);
}
}
// Convert Date type of Excel in yyyy-mm-dd string format
function formatDateToYyyyMmDd(date: number | string | number | boolean): string {
if(typeof date == 'number'){
let dateString = new Date(Math.round((date - 25569) * 86400 * 1000)).toISOString().split("T")[0];
return dateString;
}
}
Results in Banana Accounting Plus
If you don't see the Budget data, use the command with the Shift + F9 keys (Windows and Mac) or Cmd + 9 (Mac) for recalcute the data in Banana Accounting.
Output of accounts base
Output of accounts of budgeting
Output of transactions of budgeting
Output of transactions
Create a report Financial statement and Income statement
- When you pass all the data in Banana Accounting Plus, you can print the report in base of your configuration in the table Accounts.
- You can use a Enhanced Balance Sheet with groups report for printing, which integrates transaction and budget data as described in this image.
Procedure to create a report with Enhanced Balance Sheet with Groups
- Open the file that you have created.
- menu Reports → Enhanced balance sheet with groups → select Columns → in the section Balance sheet and Profit and loss statement → select Current and Budget → click Ok.
- You can see a tutorial of how to create and customize the Balance Sheet and Profit & Loss Statement.
Report of Financial statement
Report of Income statement
