Blog: Create a Cashflow Forecaster #2 (More Posts)
In this series, I’m going to create another app that mimics something I made on an Excel Spreadsheet. It’s an application that takes a starting figure and calculates changes in the forecast based on inputs and outputs on any given row of data.
I’ve been using DOM Scripting and I’ve already made a start which you can read about in my last post.
There are a lot of tasks to get your head around in an app like this that is supposed to feel like a Spreadsheet; which often has many moving parts – things that react to the actions of changes that a human makes.
So it’s good to make a plan of how you want to go about things; which tasks to do first; what tasks will be most affected by the priorities you make.
This blog is mainly going to focus on the Datatable’s “Balance” Column. We need to modify the application so that all the table cells are equal to the value of .js-starting_balance. But not only that but change dynamically when it does.
We’ll start by implementing the dynamic balance calculation for the first cell in the “Balance” column before applying it to all rows.
prompt: "lets modify the values in the "Balance" column so that they dynamically reflect the balance of .js-starting_balance.
Or would it be better if we did this in stages and just did this for the first cell only to begin with i.e. the the first chilld of the "Balance" and the first row of data"I thought this might be a better idea to break it down into smaller stages.
First, we have to add an ID attribute to the first “Balance” cell:
. . .
<tr>
<td><input type="number" class="balance_incoming" id="" placeholder="0.00" value="0" min="0" /></td>
<td><input type="number" class="balance_outgoing" id="" placeholder="0.00" value="0" min="0" /></td>
<td><!--06/10/2023--><input type="date" class="balance_date" id="" value="" /></td>
<td id="balance-cell-1"> </td>
. . . I’ve given the first cell in the “Balance” column an ID for easy selection in JavaScript.
This will give us a reference point to the first cell in the table when we’re making calculations. The calculations have to start somewhere and the DOM interpreter has to know what it is working with and where.
I updated the HTML for the first cell like this:
<td id="balance-cell-1">0.00</td>Going back to the script, I also got references to the initial Starting Balance (.js-starting_balance) and all the .balance_incoming and .balance_outgoing cells, so the browser has a reference point to them. We will be attaching event listeners to them later on to get the cogs turning.
// Get references to the relevant elements
const firstBalanceCell = document.getElementById('first-balance-cell');
const startingBalanceInput = document.querySelector('.js-starting_balance');
const incomingInput = document.querySelector('.balance_incoming');
const outgoingInput = document.querySelector('.balance_outgoing');With the selectors, we can now define the functions to update the balance for the first cell dynamically according .js-starting_balance.
// Function to update the balance for the first cell
function updateFirstBalanceCell() {
// Get the starting balance, incoming, and outgoing values
const startingBalance = parseFloat(startingBalanceInput.value);
const incoming = parseFloat(incomingInput.value);
const outgoing = parseFloat(outgoingInput.value);
// Calculate the balance for the first cell
const balance = startingBalance + incoming - outgoing;
// Update the first balance cell with the calculated balance
firstBalanceCell.textContent = balance.toFixed(2);
}Add an event listener to all of the inputs to detect changes to the values of all the inputs.
startingBalanceInput.addEventListener('input', updateFirstBalanceCell);
incomingInput.addEventListener('input', updateFirstBalanceCell);
outgoingInput.addEventListener('input', updateFirstBalanceCell);Finally, I call the function to put our new changes into effect.
updateFirstBalanceCell();This leaves us with a problem. We can see the function has worked because the content of the cell has duly changed. But it is NaN which means Not a Number. It appears that there’s either an issue recognising the value of the calculations at all, or it doesn’t know whether it should be displaying an integer or a float. Let’s see if we can take care of that.
This likely arises from the initial calculation when parsing the values as floats. It can happen when the input fields are empty or contain non-numeric characters.
We can solve this by modifying the function to carry out checks on all the inputs to make sure that only a valid number is accepted.
function updateFirstBalanceCell() {
// Get the starting balance, incoming, and outgoing values
const startingBalance = parseFloat(startingBalanceInput.value) || 0;
const incoming = parseFloat(incomingInput.value) || 0;
const outgoing = parseFloat(outgoingInput.value) || 0;
. . .That solves the issue of NAN. Now let’s make sure that .balance-cell-one is taking into account its value before any interaction with the “In” or “Out” column.
The table cell still doesn’t update properly when the page loads, the cells still show up as 0, initially. I wonder if there’s something we need to do, to display it as it is stored in localStorage?
Add the saved first balance to localStorage by calling setItem on it. We can format the saved item to 2 decimal places as before.
// Save the balance to localStorage
localStorage.setItem('firstBalance', balance.toFixed(2));Function to load the initial balance from localStorage.
function loadInitialBalance() {
const savedBalance = localStorage.getItem('firstBalance');
if (savedBalance) {
firstBalanceCell.textContent = savedBalance;
}
}Load the initial balance when the page loads using the load event on the window object.
window.addEventListener('load', function () {
loadInitialBalance();
// Call updateFirstBalanceCell to calculate and display the balance initially
updateFirstBalanceCell();
});Let’s now recap what we’ve done.
So far we have
- Created the ability to set a starting balance for your Cashflow Forecast.
- This starting balance updates in the first cell dynamically showing the same value as .js-starting_balance in tandem.
- The incoming and outgoing boxes update the first row’s balance dynamically and correctly.
Next Step
The next thing to try is to fill in the rest of the balance cells in the same way as #balance-cell-1 and ensure they all update dynamically taking into account the current value of .js-starting_balance.
This requires some modifications. We’re now tracking multiple cells in the “Balance” column. So we need to get reference(s) to the dynamic balances.
const balanceCells = document.querySelectorAll('[id^="balance-cell-"]');This means ensuring the table structure is correct including ID attributes for all balance cells… like this
<tr>
. . .
<td id="balance-cell-1">0.00</td>
</tr>
<tr>
. . .
<td id="balance-cell-2">0.00</td>
</tr>
<tr>
. . .
<td id="balance-cell-3">0.00</td>
</tr>
<!-- ... Repeat for other rows -->Doing it this way means it’s easy to add more rows to the application if we need or want to. We have to somehow modify the updateFirstBalance() function so that it makes changes to all the balance amounts in the column.
function updateBalanceCell(cellIndex) {
balanceCells[cellIndex].textContent = balance.toFixed(2);
localStorage.setItem(`balanceCell${cellIndex}`, balance.toFixed(2));
}We’re now going to be working with multiple table cells in the balance column. So let’s change all references to incoming and outgoing inputs to be plural.
e.g. incomingInput is now incomingInputs.
...
const incomingInputs = document.querySelectorAll('.balance_incoming');
const outgoingInputs = document.querySelectorAll('.balance_outgoing');
...
// Function to update the balance for a specific cell
function updateBalanceCell(cellIndex) {
const startingBalance = parseFloat(startingBalanceInput.value) || 0;
const incoming = parseFloat(incomingInputs[cellIndex].value) || 0;
const outgoing = parseFloat(outgoingInputs[cellIndex].value) || 0;
const balance = startingBalance + incoming - outgoing;
balanceCells[cellIndex].textContent = balance.toFixed(2);
localStorage.setItem(`balanceCell${cellIndex}`, balance.toFixed(2));
}Starting Balances need to be placed across the balance column. What this should ensure is that the numbers go in the cells and either increment the cells dynamically or change them to what is typed in.
startingBalanceInput.addEventListener('input', function () {
for (let i = 0; i < incomingInputs.length; i++) {
updateBalanceCell(i);
}
});These functions are supposed to fill in the balances in all the cells in the Balance column
for (let i = 0; i < incomingInputs.length; i++) {
incomingInputs[i].addEventListener('input', function () {
updateBalanceCell(i);
});
outgoingInputs[i].addEventListener('input', function () {
updateBalanceCell(i);
});
// Load the initial balance from localStorage when the page loads
const savedBalance = localStorage.getItem(`balanceCell${i}`);
if (savedBalance) {
balanceCells[i].textContent = savedBalance;
}
// Initial calculation for each balance cell
updateBalanceCell(i);
}And we’ll make sure to call it again outside the function so it performs the balance updates when the script finishes.
// Initial calculation for the first cell balance
updateBalanceCell(1);But all this does is change the balances when .js-starting_balance interacted with. Let’s try and fix that.
We should load the initial balances from localStorage for each cell when the page loads
// Function to load the initial balances from localStorage
function loadInitialBalances() {
// Set initial values for balanceCell0, balanceCell1, balanceCell2, and balanceCell3
for (let i = 0; i < balanceCells.length; i++) {
balanceCells[i].textContent = startingBalanceInput.value;
}
for (let i = 0; i < balanceCells.length; i++) {
const savedBalance = localStorage.getItem(`balanceCell${i}`);
if (savedBalance) {
balanceCells[i].textContent = savedBalance;
}
}
}So where are we now?
1. When we edit the .js-starting_balance in the first data input, the balance cells change to that value and it persists to localStorage.
2. Changing the in and out number boxes correctly changes the balance for that particular row, but not the following cells in the rows below.
3. However when we interact with in and out number boxes in new rows the balances change to the correct calculated value accordingly.
Values should update in real-time when incoming and outgoing input boxes are interacted with.
So it’s progress but not quite where the app needs to be,
Despite all this, there is still only one cell that persists #balance-cell-one. There’s still one more thing we need to do. We need to call updateBalanceCell() multiple times with the cell index passed in, so that is one function call for as many rows as there are in the table.
// Load the initial balance when the page loads
window.addEventListener('load', function () {
// Call updateFirstBalanceCell to calculate and display the balance initially
updateBalanceCell(0);
updateBalanceCell(1);
updateBalanceCell(2);
updateBalanceCell(3);
});Now we have real-time calculations being made. We need to make more modifications so changes are persisted so that incoming and balances stay in place. Also, I want to make it possible for the user to mark whether or not a forecast row is an estimated balance. We’ll work on that next.