Jon Jon - 1 month ago 6
HTML Question

How can I create an expense table using HTML tables and jQuery?

I'm creating a budgeting calculator and I'm at the point where I want to allow the user to build a table of their expenses.

There are three initially set in the table for them: Rent, Water, and Electric/Gas. After the last row is a button they can click to add a new row after the current last row.

Here's my issue. I want the user to be able to input their expenses in ANY of the three columns that I provide: Bi-Weekly, Monthly, or Annually, because they may only know that particular expense for one of those time periods, so I'd like to do the calculation for them, automagically, for the other cells.

I'm not sure how to approach this. Is there a plugin or library available that will mimic an excel spreadsheet where I can define a particular equation for a COLUMN so that any values inputted into that column will update the other columns accordingly and it doesn't matter which one (bi-weekly, monthly, annually) they used?

Here's some relevant code from this Fiddle of a current snapshot of my code:

<table class="moneyTable width100" id="expenses">
<tr>
<td class="deleteCell fill-white"></td>

<td class="right-border fill-white width50">
<h3>Expense Type</h3>
</td>

<td class="fill-white">
<h3>Bi-Weekly</h3>
</td>

<td class="fill-white">
<h3>Monthly</h3>
</td>

<td class="fill-white">
<h3>Annually</h3>
</td>
</tr>

<tr>
<td class="deleteCell">
<h4 class="delete"><a class="deleteRow">[x]</a></h4>
</td>

<td class="right-border"><input class="expense"
type="text" value="Rent" width="50"></td>

<td><input class="expense currency" disabled="disabled"
placeholder="0" type="text" width="50"></td>

<td><input class="expense currency" disabled="disabled" placeholder="0" type="text" width="50"></td>

<td><input class="expense currency" disabled="disabled" placeholder="0" type="text" width="50"></td>
</tr>

<tr>
<td class="deleteCell">
<h4 class="delete"><a class="deleteRow">[x]</a></h4>
</td>

<td class="right-border"><input class="expense" id="titleWater"
type="text" value="Water" width="50"></td>

<td><input class="expense currency" placeholder="0"
type="text" width="50"></td>

<td><input class="expense currency" placeholder="0"
type="text" width="50"></td>

<td><input class="expense currency" placeholder="0"
type="text" width="50"></td>
</tr>

<tr>
<td class="deleteCell">
<h4 class="delete"><a class="deleteRow">[x]</a></h4>
</td>

<td class="right-border"><input class="expense" id="titleElectric"
type="text" value="Electric/Gas" width="50"></td>

<td><input class="expense currency" placeholder="0"
type="text" width="50"></td>

<td><input class="expense currency" placeholder=
"0" type="text" width="50"></td>

<td><input class="expense currency" placeholder=
"0" type="text" width="50"></td>
</tr>

<tr>
<td class="deleteCell"></td>

<td class="right-border">
<h3><a class="addRow">(Add Row)</a></h3>
</td>

<td></td>

<td></td>

<td></td>
</tr>

<tr>
<td class="deleteCell fill-white"></td>

<td class="right-border fill-white">
<h3>Total Expenses</h3>
</td>

<td class="fill-white">
<h4 id="bwNet"></h4>
</td>

<td class="fill-white">
<h4 id="monNet"></h4>
</td>

<td class="fill-white">
<h4 id="annNet"></h4>
</td>
</tr>





$(document).ready(function() {
$('#expenses input:gt(0)').keyup(function() {
$(this).closest('td').siblings('td').not(':first').find('input').not(this).val($(this).val());
});
});

$(document).ready(function () {
$("a.deleteRow").live('click', function (){
$(this).parent().parent().insertBefore();
});
$("a.addRow").live('click', function (){
$("table.moneyTable tr:last").prev('tr').before("<tr class='bottom-border'><td class='deleteCell'><a class='deleteRow'><h4 class='delete'>[x]</h4></a></td><td class='right-border label'><input class='expense' type='text' width='50' placeholder='(...)' /></td><td><input class='expense currency' type='text' width='50' placeholder='0' /></td><td><input class='expense currency' type='text' width='50' placeholder='0' /></td><td><input class='expense currency' type='text' width='50' placeholder='0' /></td></tr>");
});
});

Answer

I never bumped on an automated spreadsheet script doing this automatically for you. But this calculation can be done by a manual script. The right approach should not be an equasion for the columns, though. On keyup a calculation should be done fist calculating a basic annually expense for the entire row. Next you .val() for each td->input resp. 1/52nd, 1/12th and 1/1. Of course the calculation of the first basic (annual) expense depends on what field the input is done (x52, x12 or x1)