Formatting currency columns in tables with and accounting.js

This article runs through and demonstrates using accounting.js and underscore.js to format columns of numbers inside tables. It’s not complicated, and you might find it interesting, but it’s really a specific solution aimed at a common problem.

So. Probably the most common usage example for formatting accounting columns is inside tables, for example:

Hair Net                $   (1.50) « symbols and units lined up
Treatment Creme         $  (12.99)
Cut and Finish          $ (125.00)

This table is easily templated from an array of arrays (a ‘matrix’), each array representing a row (<tr>) in the table, each value representing a cell (<td>) – like so:

// Each nested array represents a <tr>, each value a <td>
var expenseReport = [
	["Hair Net", -1.5],
	["Treatment Creme", -12.99],
	["Cut and Finish", -125]
// Example templated markup:
<table><tbody><tr><td>Hair Net</td><td>-1.5</td>

The problem here is that each of these arrays represents a row, each containing a single one of the values you need – but if you want to column-format those numbers with accounting.js’s formatColumn() method, you’ll need to have those values in an array by themselves, so that you can do this:

var values = [-1.5, -12.99, -125];
// ["$   (1.50)", "$  (12.99)", "$ (125.00)"]

Hence you need a matrix where each nested array represents a vertical column in the table, like this:

// Each nested array is now a hypothetical 'column' in the table:
var expenseReport = [
	["Hair Net", "Treatment Creme", "Cut and Finish"],
	[1.5, 12.99, 125] // we gots the values!

So what you effectively need to do is flip around that matrix of values, so that you have access to all those numbers without having to pluck out the second value from each row manually.

This is a process known as transposing a matrix, and it is exactly this that underscore.js performs with From the underscore.js docs (emphasis added):


Merges together the values of each of the arrays with the values at the corresponding position. Useful when you have separate data sources that are coordinated through matching array indexes. If you’re working with a matrix of nested arrays, zip.apply can transpose the matrix in a similar fashion.['moe', 'larry', 'curly'], [30, 40, 50], [true, false, false]);
=> [["moe", 30, true], ["larry", 40, false], ["curly", 50, false]]

Obviously it’s unfeasible to pass in each row of the table manually (e.g., row2, row3, ...) so you need to use[], matrix), which will call with the matrix’s nested arrays (table rows) as arguments, returning a transposed matrix where all the values are swapped into equivalent positions.

Example of matrix transposition

I threw together a little jsFiddle demo to show what this does. Click the button a few times.

Not loading? View it on jsFiddle here.

The process, step-by-step:

If you have a table represented by a matrix (array of arrays) in a javascript variable, where each array represents a table row, you could use this example code to apply accounting.formatColumn() to a column of values at the 2nd index (row[1]):

// The original matrix:
var expenseReport = [
	["Hair Net", -1.5],
	["Treatment Creme", -12.99],
	["Cut and Finish", -125]

// Options for accounting.js (see docs)
var options = {
	symbol: "HK$",
	precision: 0,  
	format: {
		pos : "%s %v",   // for positive values, eg. "$ 1.00"
		neg : "%s (%v)", // for negative values, eg. "$ (1.00)"
		zero : "%s -- "  // for zero values, eg. "$ --"

// Transpose the matrix to access the columns as arrays:
expenseReport =[], expenseReport);

// Format the column of values (at position [1]):
expenseReport[1] = accounting.formatColumn(expenseReport[1], options);

// Transpose the matrix back to original positions:
expenseReport =[], expenseReport);

Done! The expenseReport matrix now looks like this – an array of rows, ready to be templated into an HTML table:

    "Hair Net",
    "HK$   (2)"
    "Treatment Creme",
    "HK$  (13)"
    "Cut and Finish",
    "HK$ (125)"

This same logic applies to big tables of data, too – once you’ve used to access the table’s columns as arrays, you can use formatColumn on as many of them as you need to, with as many different configurations as you like.

The next step

I’m also hoping to implement this into accounting.js somehow, if there’s demand for it. Something like formatTable, although it could get complicated, so it’d probably be implemented as an additional plugin, maybe for jayQwery or the awesome DataTables plugin. Heck, look at me, I’m ramblin’ again.

Comment up if you have questions, comments or personal problems you’d like to discuss.

My weekend project: accounting.js

accounting.js is a tiny JavaScript library for number, money and currency formatting, with optional excel-style column rendering (to line up symbols and decimals). It’s lightweight, fully localisable and has zero dependencies.

I decided to take the couple of number formatting functions I posted last week and turn them into a library, with the key addition being the excel-style column formatting, which takes an array of values and adds padding between the currency symbol and the number, so that the symbols and decimal places are aligned:

Original: With accounting.js:
123.5 $ 123.50
3456.49 $ 3,456.49
777888.99 $ 777,888.99
-5432 $ -5,432.00
// Format list of numbers for display:
accounting.formatColumn([123.5, 3456.49, 777888.99, 12345678, -5432], "$ ");

It also includes functions for formatting any value to currency with custom precision and symbols, and a function to remove all currency formatting:

// Default usage and custom precision/symbol :
accounting.formatMoney(12345678); // $12,345,678.00
accounting.formatMoney(4999.99, "€", 2, ".", ",")); // €4.999,99
accounting.formatMoney(-500000, "£ ", 0) ); // £ -500,000

// Basic number formatting:
accounting.formatNumber(9876543); // 9,876,543 

// Remove formatting:
accounting.unformat("GBP £ 12,345,678.90"); // 12345678.9

It’s early days, but check out the accounting.js homepage for more info and live demos, and watch the github repository for the next version.

MotionCAPTCHA 0.2 – iPhone, iPad and Android support

MotionCAPTCHA 0.2 is now tagged, which makes the basic functionality work on mobiles and tablets (android/iOS). It’s still not ready for production use, but I’m pretty stoked to be able to play with it on mobiles.

I’ve had some amazing feedback and an awesome response from over 100,000 testers (whoa!) – so if you’re reading this and you tested out / tweeted / commented on the first version, thanks so much!

MotionCAPTCHA 0.2 Demo  •  Plugin Homepage  •  Code on Github

Next weekend I’m getting cracking on v0.3, which will (possibly) feature IE support via excanvas and adding a shapey-switcharoo method for changing the shape (in case you’re finding the arrow a bit of a challenge)

After that, I’ll be building it up from scratch as a production-ready CAPTCHA plugin.

Version 1.0 will:

  • as a default, use a traditional PHP image-based CAPTCHA (to beat 99.9% of spam bots)
  • add the option to allow assisted-mobility users to switch back to a default CAPTCHA (so that they can submit the form via audio captcha or letters)
  • progressively enhance to MotionCAPTCHA only if the user’s browser supports it
  • maybe add more shapes and make a bunch of other improvements

These will address many of the accessibility and security concerns raised by many of the early testers.

I’d love people to get involved – so if you read the roadmap and think “Hey, I can code this better than this asshole!” – please fork it, make changes and submit apull request and I’ll try to get it merged in!

(No offense taken about the asshole comment)

Introducing MotionCAPTCHA: Stop Spam, Draw Shapes

MotionCAPTCHA is a jQuery plugin, combining MrDoob’s Harmony Canvas experiment with the best parts of two gesture recognition algorithms, that requires users to draw the shape they see in order to submit a form.

MotionCAPTCHA jQuery plugin

Although it’s a proof-of-concept for now, I’m pretty confident that I can turn this into a production-ready CAPTCHA solution, featuring a PHP Captcha fallback, proper accessibility methods and server-side authorisation.

Killing spam has never been so artistic!

Try out MotionCAPTCHA (Demo) •  Plugin Homepage

Make sure to watch the repository on Github to see when this thing becomes production-ready. I’m pumped about it!

Check out the code on Github

PS. Thanks for all the awesome and kind support everyone’ given so far on Forrst and around the interwebs. Y’all are great!