Blog: Create a Cashflow Forecaster #3 (More Posts)
In this series, I am creating 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.
In the last blog, I talked about the steps I took to sort out the cashflow calculations, row by row, and balance by balance in the data table as indicated below. I worked on a solution that got all the cogs of the application working together.
To summarise, I did it by simply calling the relevant functions and adding in the relevant indexes so calculations were performed row by row; one value affecting another value and updating the formulas one after the other.
// 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);
});It’s possible to manipulate the values of each cell in the balance column so that they display the balance of the cell above it, starting from the second row ( #balance-cell-1).
The function to call is updateBalanceCell().
// Function to update the balance for a specific cell
function updateBalanceCell(cellIndex) {
const startingBalance = parseFloat(startingBalanceInput.value) || 0;
let previousBalance = parseFloat(startingBalanceInput.value) || 0;
for (let i = 0; i <= cellIndex; i++) {
const incoming = parseFloat(incomingInputs[i].value) || 0;
const outgoing = parseFloat(outgoingInputs[i].value) || 0;
const balance = previousBalance + incoming - outgoing;
balanceCells[i].textContent = balance.toFixed(2);
// Save the balance to localStorage using the same key format for all cells
localStorage.setItem(`balanceCell${i}`, balance.toFixed(2));
// Set the current balance as the previous balance for the next iteration
previousBalance = balance;
}
}
// Update balances for all cells
for (let i = 0; i < incomingInputs.length; i++) {
updateBalanceCell(i);
}To update the balance, I’m using the following formula: balance = previousBalance + incoming - outgoing, where previousBalance is the balance of the cell above, incoming is the value from the incoming input for the current cell, and outgoing is the value from the outgoing input for the current cell.
And then below the function definition we have to implement it by calling the function and putting that function call in a loop. We are doing the calculations for as many times as there are available table cells.
Now let’s press on with data persistence.
We can do this with 2 functions. Consider the data table.
<table class="cashflow_table">
<tbody>
<tr>
<!--<th>October</th>-->
<th class="budget_in">In (£)</th>
<th class="budget_out">Out (£)</th>
<th class="forecast_date">Date</th>
<th class="forecast_balance">Balance (£)</th>
<th class="comments">Comments</th>
<th class="estimated">Estimated(?) </th>
</tr>
<tr>
. . .
</table>When updating the “In” and “Out” input fields, we need to save their values to localStorage – that’s what the first function does. And in the second function what we’re saying is when the page loads, load the “In” and “Out” values from localStorage and populate the inputs with these values.
function updateInOutInputs(cellIndex) {
}
function loadInOutInputs(cellIndex) {
}Each of these functions takes a cellIndex. and we can provide this index which refers to the table row with a loop.
But before that, we need to use the setItem() and getItem() methods again to save the last values to localStorage.
function updateInOutInputs(cellIndex) {
const inInput = incomingInputs[cellIndex];
const outInput = outgoingInputs[cellIndex];
// Get the "in" and "out" values from the inputs
const incomingValue = parseFloat(inInput.value) || 0;
const outgoingValue = parseFloat(outInput.value) || 0;
// Save the "in" and "out" values to localStorage
localStorage.setItem(`incomingCell${cellIndex}`, incomingValue.toFixed(2));
localStorage.setItem(`outgoingCell${cellIndex}`, outgoingValue.toFixed(2));
}In these examples, incomingCells is a key name that also takes the cellIndex in the value, which ensures that the key name is always unique. The second parameter specifies how the date is formatted when it is loaded.
localStorage.setItem(`incomingCell${cellIndex}`, incomingValue.toFixed(2));
localStorage.setItem(`outgoingCell${cellIndex}`, outgoingValue.toFixed(2));and then in loadInOutInputs(), getItem() has the same expressions passed in.
const incomingValue = localStorage.getItem(`incomingCell${cellIndex}`);
// Function to load the "in" and "out" inputs from localStorage....
function loadInOutInputs(cellIndex) {
const inInput = incomingInputs[cellIndex];
const outInput = outgoingInputs[cellIndex];
// Load the "in" and "out" values from localStorage
const incomingValue = localStorage.getItem(`incomingCell${cellIndex}`);
const outgoingValue = localStorage.getItem(`outgoingCell${cellIndex}`);
// Populate the "in" and "out" inputs with the loaded values
inInput.value = incomingValue || 0;
outInput.value = outgoingValue || 0;
}And here is where we provide the indexes by the event listeners.
// Event listeners for input changes
for (let i = 0; i < incomingInputs.length; i++) {
incomingInputs[i].addEventListener('input', function () {
updateBalanceCell(i);
updateInOutInputs(i); // Save "in" and "out" values to localStorage
});
outgoingInputs[i].addEventListener('input', function () {
updateBalanceCell(i);
updateInOutInputs(i); // Save "in" and "out" values to localStorage
});
// Load the "in" and "out" values when the page loads
loadInOutInputs(i);
}
// Load initial balances and populate the data from localStorage
loadInitialBalances();Estimated Classes
In the Estimated column, I have left a class on the first-row cell on it called .yes_estimated . This class mimics what happens when the class is part of that table cell after the user has clicked on it – marking out a forecast row as being an estimated figure. The next task is to make the rest of the cells toggleable, so making the class exist or not as a class attribute of the given table cell.
So first I’ll make sure to select all the table cells in the “estimated” column
// Get all cells in the "Estimated" column
const estimatedCells = document.querySelectorAll('.yes_estimated');Next, I’ll define the function that toggles the class on and off. If the class exists on the selected element, remove it. If it does, we’ll add it back on; both actions on a click event.
// Function to toggle the .estimated class on a cell
function toggleEstimatedClass(cell) {
if (cell.classList.contains('yes_estimated')) {
cell.classList.remove('yes_estimated'); }
else {
cell.classList.add('yes_estimated');
}
}Now, we’ll wrap an event listener inside a forEach() method that seeks to toggle each cell in the “Estimated” column. Remember the idea is to toggle the “.yes_estimated” class.
estimatedCells.forEach((cell, index) => {
cell.addEventListener('click', () => {
toggleEstimatedClass(cell);
});
});All of those functions in place should solve the task. But there’s a problem. The toggling only works in the first table cell in the column. And it works because the class is already part of the DOM.
The test condition checks if the clicked element contains the class, but allows a condition block to do something about it if the class does not exist. So to my mind, this should be working.
Then the issue just hit me. I was trying to target the same element I was trying to toggle in my code. So, no wonder the condition fails because it’s self-referential.
The solution.
Each of the table cells needs a class added to it as part of the DOM. So I added a new class to each cell in the Balance column (toggle_estimated) in my PHP/HTML file.
. . .
<td class="toggle_estimated"></td>
</tr>By doing this, I can modify the selector on estimatedCells by changing the string argument to these new classes.
const estimatedCells = document.querySelectorAll('.toggle_estimated');And I can freely toggle each of these table cells because we know .toggle_estimated will always be part of the DOM and JavaScript won’t miss any classes that it needs.
estimatedCells.forEach((cell, index) => {
cell.addEventListener('click', () => {
cell.classList.toggle('yes_estimated');
});
});Finally, we come to the issue of making the changes to the toggle persist. I didn’t see how it was possible to store whether or not exists in localStorage? Well, it turns out we can do that quite easily by doing a check for whether a classList in an element contains a string, storing that to a variable, and using that variable in setItem().
function toggleEstimatedClass(cell, cellId) {
// Update the isEstimated variable based on the class state
const isEstimated = !cell.classList.contains('yes_estimated');
if (isEstimated) {
cell.classList.add('yes_estimated');
} else {
cell.classList.remove('yes_estimated');
}
// Save the state to localStorage with a unique key for each cell
localStorage.setItem(`estimatedState_${cellId}`, isEstimated ? 'true' : 'false');
}You will have noticed the function has introduced 2 parameters. cell and cellId. The first parameter works out which cell has been clicked and therefore which to toggle. The cellId is how JavaScript creates unique IDs to save changes to and therefore save unique changes avoiding the bug where the entire column is toggled.
localStorage.setItem(`estimatedState_${cellId}`, isEstimated ? 'true' : 'false');On each of the cells in the “Estimated” Column, we are applying the class immediately upon clicking the cell and also update the isEstimated variable based on the class state.
// Add a click event listener to each cell in the "Estimated" column
estimatedCells.forEach(cell => {
// Generate a unique identifier for the cell based on its position in the table
const cellId = `cell_${cell.parentElement.rowIndex}_${cell.cellIndex}`;
// Load the state from localStorage and apply it to the cell
const isEstimated = localStorage.getItem(`estimatedState_${cellId}`);
if (isEstimated === 'true') {
cell.classList.add('yes_estimated');
}
cell.addEventListener('click', () => {
toggleEstimatedClass(cell, cellId);
});
})Now the application is really taking shape.
- We have the Cashflow correctly formatting data adapting the calculations according to the starting balance and correctly adapting the forecasting when the In and Out number fields are created.
- We have the data fully persisting using localStorage so users do not have to start forecasting data again when they leave or refresh the page.
- And we have given the user the ability to indicate whether a particular forecast on a given table row is an estimated forecast.