Build Cashflow Reports From JavaScript Spreadsheets and Dynamic Arrays
Tags: javascript, webdev, tutorial
Published: 2022-05-06 20:33:36

---
canonical_url: https://www.grapecity.com/blogs/posts/how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays
description: Learn how to build cash flow reports from JavaScript Spreadsheets with dynamic arrays. See more from SpreadJS today.
---

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:

1.  **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.
2.  **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](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/Cashflow_Calendar.zip) 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.

![transaction](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/1-transaction.png)

### 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](https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~setBindingPath.html)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](https://www.grapecity.com/spreadjs/javascript-spreadsheet-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:

1.  Click the _Template_ menu on _Data tab_—a _Field List_ panel will appear on the right
2.  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
3.  Drag the fields in the desired cell of the template range

![template](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/2-templates.png)

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:

1.  Select the date cell "A2:D2" as it is merged
2.  _Conditional Formatting_ → _New Rule_
3.  As a rule, type and select _Use formula_ to determine which cells to format
4.  Enter your formula, in our case _\='Cell Template'!$C$6>0_
5.  Click _Format_ → _Fill_ → Select _Green_ as the font color
6.  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

![calendar](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/3-cal.png)

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

1.  On the _Formulas_ tab, select _Name Manager_
2.  In the pop-up, click the _New_ button
3.  Set the name of the cell. In our example: name: **currentMonth**
    1.  Refer To: **$D$2**. You can also add a comment and change the referents

![name manager](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/4-name-manager.png)

#### Step 2: Create the CashFlow Calendar

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

![empty](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/5-empty.png)

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](https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Sheets.Events~CellClick_EV.html). 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.

![sequence](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/6-sequence.png)

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:

1.  Select B4:H9 and then the dates of the calendar → _Conditional Formatting_
2.  Select _New rule_ from the drop-down and select "Use the formula to determine which cells to format as a rule type"
3.  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
4.  Click Format
5.  _Number_ → _Custom_
6.  Set ";;;" as formatter to make blank all the right cells

![condition](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/7-cond.png)

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.

![ran](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/8-ran.png)

1.  Select the cell range B4:H9
2.  Format → More Number Format → Custom
3.  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:

![calendar](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/9-calendar.png)

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](https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Sheets.Events~SelectionChanged_EV.html) 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:

1.  On the _Formulas_ tab, select _Name Manager_
2.  In the pop-up, click the _New_ button
3.  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}),"")**

![transaction](https://global-cdn.grapecity.com/blogs/spread/20220112-how-to-build-monthly-cashflow-report-from-javascript-spreadsheets-with-dynamic-arrays/10-transaction.png)

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.