Convert Currencies in Zoho Creator with a Free Exchange Rate API

Foreign Exchange Rates and Currency Conversion in Zoho Creator

Quote in pesos, report in dollars. Invoice in euros, reconcile in sterling. Any business that crosses a border needs a live rate, and a free API plus four lines of Deluge covers it.

Fixer is not just moved — the replacement cannot do what the original did. api.fixer.io now redirects to a 404 HTML page; keyless calls are gone. Fixer was acquired by APILayer and lives at data.fixer.io with a mandatory access key — but read the plan before you migrate to it. On the free tier the base currency is locked to EUR and HTTPS is a paid feature. The original script called base=MXN over plain HTTP, so the modern Fixer free tier cannot reproduce it on either count. This article uses Frankfurter, which is keyless, HTTPS, allows any base currency, and serves European Central Bank data — the same source Fixer was built on.

Before you start

Nothing. No account, no key, no configuration.

Deluge function

decimal Currency.convert(decimal amount, string fromCode, string toCode)
{
    if(fromCode == toCode)
    {
        return amount;
    }

    response = invokeurl
    [
        url  : "https://api.frankfurter.dev/v1/latest?base=" + fromCode + "&symbols=" + toCode
        type : GET
    ];

    rates = response.getJSON("rates");
    if(rates == null)
    {
        info "FX lookup failed: " + response.toString();
        return 0;
    }

    rate = rates.getJSON(toCode);
    if(rate == null)
    {
        info "No rate returned for " + fromCode + " -> " + toCode;
        return 0;
    }

    return (amount * rate.toDecimal()).round(2);
}

Notes

  • Use api.frankfurter.dev/v1/, not .app. The old api.frankfurter.app host now issues a 301 redirect, and Deluge’s invokeurl does not reliably follow redirects — you get an empty response rather than an error.
  • Store the rate you used, not just the converted amount. Six months later somebody will ask why an invoice converted the way it did. A Rate_Used and Rate_Date field on the record answers that in one glance and is worth more than the two fields cost.
  • Do not convert on every read. Calling this from a report formula means one API call per row per page load. Convert once when the record is saved.
  • ECB rates are daily, not live. They publish once each working day around 16:00 CET, and there are no weekend updates. For accounting and quoting that is exactly right. For anything trading-related it is not.
  • Alternative: https://open.er-api.com/v6/latest/MXN is also keyless and HTTPS, and returns all currencies in one call — useful if you convert into several at once. Avoid exchangerate.host, which is now APILayer and requires a key.

This script is part of the free Creator Scripts Deluge Library.

All 39 Deluge scripts, the full Zoho Creator course, and every downloadable asset are now free. Get free access →