in Code

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!

Write a Comment

Comment

  1. Hi Joss, the demo seems not to load… looks like you are referencing your scripts from localhost.

    Happy New Year!

    • Shit, thought I changed that. Must’ve saved over it. Thanks so much for the heads up! Fixed now.

      F- must try harder

  2. Joss I trying to understand how function this (too complicated for me).
    I tries to find manner that several currency convert to a single currency with money.js, and so I came to this website.
    Probably a solution is simple but I can not find it.
    What can I tell you … a solution would be welcome.