Convert a table of mixed values to a single currency with money.js

You can use money.js and data from the open exchange rates API to convert/normalise an entire table of values from multiple currencies to any other single currency.

I’m using it in our analytics application to let users compare the money values from assorted items (in different currencies) in a single currency of their choice. Every data table has an option to normalise currencies for all values.

Convert a table of values with money.js

Below you’ll find a table of Christmas-themed adult novelty products. All of the items are in different currencies for some reason (a realistic scenario, if you’ve been scraping data from various websites.)

What if you want to compare the unit price and gross revenue values for each product in a single currency? Try it!

Iframe not loading? You can also view the demo here. I wrote it before Christmas, in case you’re wondering why it’s Christmas-themed!

In this demo, I’m also using underscore.js for data manipulation and client-side templating, jQuery for DOM manipulation/events and accounting.js for number formatting.

The table is built using a very simple underscore.js template, from a matrix of values (array of arrays) representing table rows and individual cells. (If you’re unsure about building tables from data matrices, I wrote a post that deals with it some more, explaining how to format tables of numbers with accounting.js and underscore.js).

The code

The code that does the conversion is a little rough around the edges, but here’s how it breaks down:

  • Define the data table as an array of columns (each an object, with title and metadata) and an array of table rows (each an array of table cells). In your application, this data would probably come from an API.
  • Use the underscore.js template to draw the table and jQuery to render it.
  • When the select box is changed, loop over the table rows (items), and for each one, get the item’s original currency, and use money.js’ fx.convert() to convert the value. Then redraw the table.

Here’s the underscore.js template, some funky syntax highlighting going on there:

<script type="text/template" id="tableTemplate">
    <table>
        <thead>
            <tr>
                <% _(tplColumns).each(function(col) { %>
                    <th data-column="<%= col.key %>"><%= col.title %></th>
                <% }); %>
            </tr>
        </thead>

        <tbody>
            <% _(tplRows).each(function(row) { %>
                <tr>
                    <% _(row).each(function(value, i) { %>
                        <td class="<%= tplColumns[i].type || '' %>">
                            <%= formatCell(value, tplColumns[i].type) %>
                        </td>
                    <% }); %>
                </tr>
            <% }); %>
        </tbody>
    </table>
</script>

So we gots a table header row, with the <th> cells templated from the tplColumns array, and then the table body, with each <th> representing a single item (an array in the tplRows array and each <td> representing a single value. The template relies on a handy formatCell() function, which uses accounting.js to format each table cell’s value, according to its type.

The JavaScript code itself is a little janky, but it works for our purposes:

// Handy callback function to run accounting.js formatting on cells:
function formatCell(value, type) {
    return type === 'number' ? accounting.formatNumber(value)
         : type === 'money' ? accounting.formatNumber(value, 2)
         : value;
}

// On ready:
jQuery(document).ready(function($) {

    // The columns in the table:
    var columns = [
        { title: 'Product',       key: 'name'      },
        { title: 'Units Sold',    key: 'sold',    type: 'number' },
        { title: 'Currency',      key: 'fx',      type: 'fx'     },
        { title: 'Unit Price',    key: 'price',   type: 'money'  },
        { title: 'Gross Revenue', key: 'revenue', type: 'money'  }
    ];

    // The rows of data (corresponding to columns):
    var rows = [
        [ 'His &amp; Hers Prophylactics',         24965, 'USD', 12.99, 324295.35 ],
        [ 'Christmas Stockings, Fishnet',         12600, 'GBP', 16.8,  211680    ],
        [ 'Candy Underwear, Gummy',               8965,  'SEK', 80.5,  721682.5  ],
        [ 'Santa Hat and Santa Suit, Crotchless', 13543, 'EUR', 10.5,  142201.5  ],
        [ 'Vibrating Christmas Crackers x 6',     9954,  'HKD', 108,   1075032   ]
    ];

    // Handy underscore.js template that builds a table from data like the above:
    // (See script tag with template, above)
    var template = _.template( $('#tableTemplate').html() );

    // Template the table's HTML using our data:
    $('#table').html( template({
        tplColumns: columns,
        tplRows: rows
    }));

    // Convert the data:
    $('#currency').change(function() {
        var targetFx = $(this).find(':selected').val();
        var currentFx;

        if ( !targetFx ) return false;

        // Now we're gonna use underscore.js to confapulate the data:
        var newRows = _.map(rows, function(row) {
            // Keeps the original row intact:
            row = _.clone(row);

            // Get the 'from' currency:
            currentFx = row[2];

            // Update the row's currency with the 'target':
            row[2] = targetFx;

            // Convert the numbers yo:
            row[3] = fx.convert(row[3], { from: currentFx, to: targetFx });
            row[4] = fx.convert(row[4], { from: currentFx, to: targetFx });

            return row;
        });

        // Now template the data:
        $('#table').html(template({
            tplColumns: columns,
            tplRows: newRows
        }));

        return false;
    });

    // Reset to original data:
    $('#reset').click(function() {
        // Template the original data:
        $('#table').html( template({
            tplColumns: columns,
            tplRows: rows
        }));
    });
});

Note that the formatting part takes a shortcut and uses predefined indices to find values in the data array (in our app, it’s a little more complicated, as the index of the currency value is unpredictable – so there’s a method to find out which it is, which I’ve left out for brevity.)

This method relies on your using data arrays (e.g. vie AJAX) to build tables dynamically with JavaScript. If your tables are templated on the server-side and delivered as fully-rendered HTML, there are other ways to achieve this result, but I’ll save those for another day.

Any questions, hit me up in the comments!

Bonus points

PS. Here’s the code that loads the data from the exchange rate API into money.js, and also builds the drop-down currency select box:

// Get the latest exchange rates from openexchangerates.org:
$.ajax({
    url: 'http://openexchangerates.org/latest.json?app_id=[YOUR_APP_ID]',
    dataType: 'jsonp',
    success: function(data) {
        fx.rates = data.rates;
        fx.base = data.base;
    }
});

// Get the list of currencies too, and create an extra group in the drop-down:
$.ajax({
    url: 'http://openexchangerates.org/currencies.json',
    dataType: 'jsonp',
    success: function(data) {
        var $optgroup = $('<optgroup label="All Currencies"/>');
        _.each(data, function(name, code) {
            $optgroup.append('<option value="' + code + '">' + name + ' (' + code +')</option>');
        });
        $optgroup.appendTo('#currency');
    }
});

Hope you’ve enjoyed reading this festive exploration of money exchanging extravagance!

Formatting currency columns in tables with underscore.zip 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:

EXPENSES
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];
accounting.formatColumn(values);
// ["$   (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 _.zip(). From the underscore.js docs (emphasis added):

zip   _.zip(*arrays)

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.

_.zip(['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. _.zip(row1, row2, row3, ...) so you need to use _.zip.apply([], matrix), which will call _.zip 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 = _.zip.apply([], expenseReport);

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

// Transpose the matrix back to original positions:
expenseReport = _.zip.apply([], 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 _.zip 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.

Format and un-format money/currency in JavaScript

Here’s a couple of simple JavaScript money-formattin’ snippets I use all the time where currencies are handled.

The code in this post has been expanded into accounting.js, a tiny JS library with some handy methods for number, money and currency formatting. Check it out!

Also: need to do real-time currency conversion in your apps and sites? Take a look at the Open Exchange Rates API!

The first is a function to format money/currency in JavaScript, turning an integer or float into a formatted string (with customisable decimal precision, currency symbol, decimal- and thousands-separators).

The second is a regular expression that removes currency formatting, so that parseInt / parseFloat can be used to extract the value from a currency-formatted string.

JavaScript Money Format

This extends the native Number object, so that all new numbers (integers/floats) can have the moneyformat() method called on them. If you prefer not to extend JavaScript’s native Number object for any reason, this also works fine as a standalone function or a library method (examples below.)

// Extend the default Number object with a formatMoney() method:
// usage: someVar.formatMoney(decimalPlaces, symbol, thousandsSeparator, decimalSeparator)
// defaults: (2, "$", ",", ".")
Number.prototype.formatMoney = function(places, symbol, thousand, decimal) {
	places = !isNaN(places = Math.abs(places)) ? places : 2;
	symbol = symbol !== undefined ? symbol : "$";
	thousand = thousand || ",";
	decimal = decimal || ".";
	var number = this, 
	    negative = number < 0 ? "-" : "",
	    i = parseInt(number = Math.abs(+number || 0).toFixed(places), 10) + "",
	    j = (j = i.length) > 3 ? j % 3 : 0;
	return symbol + negative + (j ? i.substr(0, j) + thousand : "") + i.substr(j).replace(/(\d{3})(?=\d)/g, "$1" + thousand) + (places ? decimal + Math.abs(number - i).toFixed(places).slice(2) : "");
};

NB: minified version below.

Some JS currency-formatting examples:

// Default usage and custom precision/symbol :
var revenue = 12345678;
console.log(revenue.formatMoney()); // $12,345,678.00
console.log(revenue.formatMoney(0, "HK$ ")); // HK$ 12,345,678

// European formatting:
var price = 4999.99;
console.log(price.formatMoney(2, "€", ".", ",")); // €4.999,99

// It works for negative values, too:
console.log( (-500000).formatMoney(0, "£ ") ); // £ -500,000

Currency to number – removing money formatting:

Say you have a currency-formatted string, eg. "HK$ -100,000.50" and you want to get the value from that, removing all currency formatting and thousand-separators. Default methods won’t get you very far: parseInt and parseFloat both return NaN (“not a number”, as the string had non-numeric characters in it.)

No problemo. A simple regex will take care of this:

var price = (12345.99).formatMoney(); // "$12,345.99"

// Remove non-numeric chars (except decimal point/minus sign):
priceVal = parseFloat(price.replace(/[^0-9-.]/g, '')); // 12345.99

NB: This only works where the decimal separator is a point (.) – if the separator is a comma (,), put a comma in the regex instead of a point, eg: /[^0-9-,]/g

Hope somebody finds this useful!

Non-Number.prototype version:

// To set it up as a global function:
function formatMoney(number, places, symbol, thousand, decimal) {
	number = number || 0;
	places = !isNaN(places = Math.abs(places)) ? places : 2;
	symbol = symbol !== undefined ? symbol : "$";
	thousand = thousand || ",";
	decimal = decimal || ".";
	var negative = number < 0 ? "-" : "",
	    i = parseInt(number = Math.abs(+number || 0).toFixed(places), 10) + "",
	    j = (j = i.length) > 3 ? j % 3 : 0;
	return symbol + negative + (j ? i.substr(0, j) + thousand : "") + i.substr(j).replace(/(\d{3})(?=\d)/g, "$1" + thousand) + (places ? decimal + Math.abs(number - i).toFixed(places).slice(2) : "");
}

// To create it as a library method:
myLibrary.formatMoney = function(number, places, symbol, thousand, decimal) {
	/* as above */
}

// Example usage:
formatMoney(54321); // $54,321
myLibrary.formatMoney(12345, 0, "£ "); // £ 12,345

Minified version of prototype method

Number.prototype.formatMoney=function(d,g,h,a){d=!isNaN(d=Math.abs(d))?d:2;g=g!==undefined?g:"$";h=h||",";a=a||".";var f=this,c=f3?b%3:0;return g+c+(b?e.substr(0,b)+h:"")+e.substr(b).replace(/(\d{3})(?=\d)/g,"$1"+h)+(d?a+Math.abs(f-e).toFixed(d).slice(2):"");};

Have any improvements, or spot any bugs? Comments appreciated!