Build Cashflow Reports from JavaScript Spreadsheets & Dynamic Arrays

Published in

MESCIUS inc.

8 min readMay 6, 2022

Managing business cash flow is a critical function of the operation of a business. Tracking how money enters and leaves accounts ensures financial stability and helps keep a close business health audit.

A cash flow calendar presents your cash flow in an intuitive and easy-to-use daily summary. It provides a view of each day of the month with highlighted positive and negative inflows and outflows.

This blog will learn how to create a Cashflow Calendar in JavaScript using our Excel-like JavaScript spreadsheet solution, SpreadJS. This calendar will make extensive use of the following powerful features:

Dynamic array formulas — returns multiple results to a range of cells based on one formula. This example makes use of the SEQUENCE and FILTER functions.
RANGEBLOCKSPARKLINE(template_range, data_expr) — this sparkline allows the developer to define a template of cell ranges (template_range) as a single cell type and apply that template to a cell to load a set of data (data_expr) into the template. This template could include multiple rows and/or columns.

Download the sample and follow along.

DataSource Sheet

The data source of our example is a list of transactions.

We have created a more dynamic table, and we can reference Table1 when we need the data instead of the range of cells.

This table contains information regarding the TransactionID, type of transaction, date of the transaction, name of the company, name of the account, amount of the deposit, and withdrawal.

Template Sheet

This page contains the template range that we will use to present the transactions happening in our cash flow calendar.

This range of cells here will be used as a template for the cells that will contain the wanted information in the cash flow calendar.

The first thing we do is arrange the cells and then set the binding path for the cells.

It could be done via Javascript by using the SpreadJS setBindingPath method.

templateSheet.setBindingPath(0, 1, "month"); templateSheet.setBindingPath(1, 2, "date"); templateSheet.setBindingPath(2, 2, "start"); templateSheet.setBindingPath(3, 2, "withdrawals"); templateSheet.setBindingPath(4, 2, "deposits"); templateSheet.setBindingPath(5, 2, "end");

If we are using SpreadJS Designer, which is included in the download and can be installed from the “\SpreadJS.Release.x.x.x\Designer\Designer Runtime” folder, we have to follow these steps:

Click the Template menu on Data tab-a Field List panel will appear on the right
Hover over the Start branch and add fields by clicking the green + button *Note that you can remove fields with the “x” button and modify those with the settings located to the right of the branch
Drag the fields in the desired cell of the template range

To make it possible for the days with cash shortfalls (negative ending balance) to be colored with RED, the days with a positive ending balance to be colored with GREEN, and the neutral ones with BLACK, we can use Conditional Formatting. On the designer:

Select the date cell “A2:D2” as it is merged
Conditional Formatting → New Rule
As a rule, type and select Use formula to determine which cells to format
Enter your formula, in our case =’Cell Template’!$C$6>0
Click Format → Fill → Select Green as the font color
Repeat the same steps, but with the formula: =’Cell Template’!$C$6<0 *Note that the color should be set to red for cases with a negative balance

Cashflow Calendar: Rendering Sheet

Step 1: Add a MonthPicker Element

The first element of our calendar is a changeable month element. To add it, use the MonthPicker, a type of drop-down cell style in SpreadJS.

JavaScript:

var monthPickerStyle = new GC.Spread.Sheets.Style();
       monthPickerStyle.dropDowns = [
          {
              type: GC.Spread.Sheets.DropDownType.monthPicker,
              option: {
                  startYear: 2019,
                  stopYear: 2021,
                  height: 300,
              }
          }
      ];
      sheet.setStyle(2, 5, monthPickerStyle);

Designer:

Select the cell (in our case B2)

Home Tab → Cell Dropdowns → Month Picker
On the right of Command, click the …
Set the start, end year, and the height of the picker

We then assign a name to the cell containing the month when making calculations.

On the Formulas tab, select Name Manager
In the pop-up, click the New button
Set the name of the cell. In our example: name: currentMonth

-Refer To: $D$2. You can also add a comment and change the referents

Step 2: Create the CashFlow Calendar

First, we create the design of the calendar, which should look something like this:

Use the SEQUENCE(rows,columns,start,step) function to assign the dates in our calendar. This allows us to retrieve later the cell value on CellClick. The formula for the B4 cell is:

=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1) JavaScript:

cashflowSheet.setFormula(3, 1, '=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)');

We have not yet used a formatter for these cells.

The next step is to use the conditional formatting to make it possible for the dates belonging to other months but the selected one be blank:

Select B4:H9 and then the dates of the calendar → Conditional Formatting
Select New rule from the drop-down and select “Use the formula to determine which cells to format as a rule type”
Enter your formula, in our case “=MONTH(B4)<>MONTH(currentMonth)” — this format would be only for the cells whose month is different from the one selected in the drop-down
Click Format
Number → Custom
Set “;;;” as formatter to make blank all the right cells

The following step includes using the RANGEBLOCKSPARKLINE that will use the range of cells in the TemplateSheet as a single cell type and the OBJECT function to apply the template in all the cells representing the dates in our cashflow calendar.

As we have used SEQUENCE to set values for these cells, we will use RANGEBLOCKSPARKLINE as a format.

Select the cell range B4:H9
Format → More Number Format → Custom
Set the formater as : =RANGEBLOCKSPARKLINE(‘Cell Template’!$A$2:$D$7,OBJECT(“date”,@,”start”,IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]<@))-SUM(FILTER(Table1[Withdrawal],Table1[Date]<@)),0),”withdrawals”,IFERROR(SUM(FILTER(Table1[Withdrawal],Table1[Date]=@)),0),”deposits”,IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]=@)),0),”month”,MONTH($A$2)))

As a first argument, it takes a cell range as the template from TemplateSheet.

As a second argument, it takes an OBJECT that takes data from the Table1, located on the Datasource Sheet.

[date]: the current value of the cell
[start]: Sum of all previous Deposits — Sum of all the previous Withdrawals
[withdrawals]: Sum of current Withdrawals
[deposits]: Sum of current Deposits
[end]: [start] + Sum of all current Deposits — Sum of all the current Withdrawals

Using the formula is to bind and return a range template to make working with range templates easier.

This is the final output:

As shown in the picture above, the cells containing days of the calendar give information regarding the Start/End Balance, Sum of Deposits, and Sum of Withdrawals.

Step 3: Get Daily Transactions

If we want to extract from the DataSource page the list of all the transactions, we can do with the help of the SelectionChanged event. Worksheets in SpreadJS have their events bound to specific actions when those events occur.

In our example, we have used this handy SpreadJS functionality to extract the list of all the transactions when the user selects a date from the calendar.

We assign a name to the cell that will contain the selected date, deposits, and withdrawals since it’s easier to make calculations and a table that will contain the information regarding the transactions. The steps when creating a name range for currentMonth are:

On the Formulas tab, select Name Manager
In the pop-up, click the New button
Set the name of the cell

In our example:

name: currentSelection; refer to: =’Cash-Flow’!$B$11

name: currentDeposits; refer to: =FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Deposit]>0))

name: currentWithdrawals; refer to: =FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Withdrawal]>0))

Set different formulas that will get the list of all deposits, the list of all withdrawals, end, and start balance.

Start Balance (SUM of all previous Deposits — Sum of all previous Withdrawals ): =IFERROR((SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]<$B$11))-SUM(FILTER(tblTransactions[Withdrawal],tblTransactions[Date]<$B$11))),0)
End Balance (Start Balance + SUM of current Deposits — Sum of current Withdrawals ): =IFERROR(D13+(SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]=$B$11))-SUM(FILTER(tblTransactions[Withdrawal],tblTransactions[Date]=$B$11))),0)

Where D13 is the Start Balance:

Deposits: =IFERROR(FILTER(currentDeposits,{1,0,1,1,0}),””)
Withdrawals: =IFERROR(FILTER(currentWithdrawals,{1,0,1,0,1}),””)

For the moment the currentSelection is manually inserted. To have it changed as per user date selection, follow the next step.

Create the event handler function in JavaScript (see below):

// on day selection, update a cell used in filtering the data to show detailed transaction list
 cashflowSheet.bind(GC.Spread.Sheets.Events.SelectionChanged, function (sender, args) {
    const sheet = args.sheet;
    const row = args.newSelections[0].row;
    const col = args.newSelections[0].col;

    if ((row < 3 || row >= 3 + 6)
        || (col < 1 || col >= 1 + 7))
        return;
    // set the current date cell so that FILTER would update.
    sheet.setValue(10, 1, sheet.getValue(row, col));
});

Once a user click cell, the above code checks if the cell is inside the calendar boundaries ( B4:H9). Otherwise, it updates the currentSelection, and as a consequence, all the formulas used to get the balances and information regarding the transactions give the right results as they point in the changed selected date.

The above example is one of the many ways to use the SpreadJS features to enhance your applications and transform your content from a simple set of data to an engaging, ultra-useful Excel-like dashboard.

This JavaScript component offers, above else, hundreds of statistical and financial functions and formulas that will help you create effortlessly various elements in your financial applications.

Originally published at https://www.developer.mescius.com on May 6, 2022.