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!

Open Exchange Rates Project (and money.js) update

Just a few notable updates to money.js and the open exchange rates API:

Open Exchange Rates API

In a nutshell:

  • We’ve added exchange rate data going back to 1999, thanks to the hard work of Sebastian Majstorovic and his epic 390,000-line pull request
  • The data is now collected from the public Yahoo! Finance API, which seems to be more accurate and reliable.
  • The proxy at openexchangerates.org now supports JSONP requests (like this) by adding a `callback` parameter to the URL. This works for historical files, too.
  • The proxy also now downloads, caches and serves the API files statically instead of requesting them from github each time (should be 10x faster, no benchmarks yet though.)

This project has really taken on a life of its own, thanks to a bunch of great people and feedback and the ever-diligent Currency Bot. Check out the homepage if you’ve not seen it yet. The documentation still needs work in the coming weeks.

Oh, and the code is now under MIT license, but the data license we’re still trying to figure out. Anybody a lawyer?

money.js (JavaScript Currency Conversion library)

What’s new:

  • Added a license (MIT) which it turns out is a really big deal for people wanting to use the library in commercial applications. Now everyone can do whatever they like, wherever they please.
  • Added basic parsing, so you can write e.g. `fx("$1.99 HKD").to("EUR")` and have it work exactly as you’d expect.

Beyond a few fixes and extra comments, not much has changed. It’s still tiny (~1.2kb minified, probably around 0.6kb gzipped) and still does everything you need out of the box, with no dependencies, so give it a shot!

Mail ’em in!

If you’re using money.js and/or the Open Exchange Rates API in any neat projects, please let me know so I can build a showcase!

By the way – I’m shooting for a post every few days until New Year to clear a several-month backlog of 90%-finished drafts and ideas.

money.js and the Open Source Exchange Rates API = better currency conversion

Today I’m chuffed to release two closely related projects that have been eating up all my spare time recently. I started work on both specifically for our enterprise analytics application (just like accounting.js) but they soon took on a life of their own – quite literally, in one case.

money.js – JavaScript currency conversion library

money.js - javascript currency conversion library

money.js – javascript currency conversion library

money.js is a tiny (1kb) JavaScript library for realtime currency conversion, in the browser and on the server, with zero dependencies. It features simple method chaining and can convert to / from any currency, provided you have the exchange rates for them.

You only need exchange rates relative to one single currency (eg. USD) to be able to convert between any others – money.js does it all for you.

No longer will you need to go wading through shit-heaps of messy, 10-year-old “FREE currency converter javascripts!!1” code just to add basic conversion to your web-app or online store!

More info, demo, download and docs at the money.js homepage

“But where do we get these exchange rates?” (“Maybe there’s some in that truck!!..”)

The biggest issue with currency conversion has to be sourcing reliable exchange rates without having to sell your mum to pay for the API access. I’ve been wanting to take a stab at that for a long time, and here it is:

The Open Source Exchange Rates API project

Open Source Exchange Rates API

The Open Source Exchange Rates API, powered by the lovable (and deadly) Currency Bot

While working on money.js, I realised very quickly that finding a free, reliable source of JSON-formatted, regularly updated data (with no access limits or hidden catches) is, well, impossible. It just doesn’t exist.

Until now!

The Open Source Exchange Rates API provides up-to-date, flexible and portable currency conversion data that can be used in any application, framework or language (not just JavaScript).

It was originally designed for integration with money.js, and hacked together in an afternoon. It’s powered by a simple nodeJS scraper and a list of currencies: every hour, Currency Bot collects all the exchange rates from the Google Calculator API (one by one), then pushes them in JSON format to a public GitHub code repository for everybody to use.

The rates are available historically for any day since the project started, and could soon be extended to provide data going back several years.

Also, it’s mirrored on openexchangerates.org, which sends files with friendly Access-Control HTTP headers for cross-origin resource sharing (CORS) – so that they can be loaded into an app or page via AJAX.

I would write more, but I’m shattered after waking up at 6am to get Currency Bot out of his can’t-scrape-won’t-scrape funk (he had a few teething problems.) He’s back on form and collecting data like a boss – you can see it at the currencybot/open-exchange-rates GitHub repository.

Learn more about using the free service at the Open Source Exchange Rates API homepage!

The next step

Wow. I’ve wanted to get these two done for a long time, and now I’m left scrambling to find something else to do – lucky for me, there’s plenty. Here’s the future plans for these two projects (if you feel you can contribute, please get in touch!)

  • Find a way to do a once-off retrospective crawl of historical exchange rates going back as far as possible. This will almost certainly involve abusing another free but non-user-friendly or rate-limited service. Open to suggestions.
  • Package up money.js into a npm module so that you can go npm install moneyjs.
  • Write documentation for how to use the Open Source Exchange Rates API in the most popular languages and frameworks
  • Write a full test suite for money.js
  • And a bunch more – all I can say is, hit ‘watch’ on the GitHub repositories (here and here) and follow me on twitter here if you’re interested and want to keep up to date.

Thanks for reading!

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!