Update 27 Feb 2023: this article has been superseded by JSON Currency Exchange Rates revisited.
Update 11 Apr 2021: as per comments below, demo has been updated to use a new service: openrates.io.
Welcome back for another exciting round of JSON exploration. Last time we discussed a JSON-related bug fix in FM 19, as well as a free package tracking service that returns results in JSON format. Today we’re going to look at a free currency exchange rate service.
Demo file: json-exchange-rates-via-openrates.zip
I threw together this FileMaker front end to pull current and historical currency exchange rates from OpenRates.io, a free service which provides rates published by the European Central Bank. Rates are updated once per business day at 16:00 (4 PM) Central European Time.
When you click Get Rates, the demo queries…
https://api.openratesapi.io/[criteria]
…via Insert From URL, and populates $$json with the result (which is then displayed via the “json” field).
Some Notes re: the Demo
Click the “x” icons to clear fields.
When fields are clear, default values will be used — e.g., if dates are empty you will get rates from the most recent business day, and if none of the currency boxes are checked, you will get rates for all currencies.
If a base currency is not specified, it will default to EUR (euro).
Checking the Format box will apply JSONFormatElements to the result.
When the Format box is checked the JSON keys will appear in alphabetical order, which is mostly okay in terms of human readability except for “start_at”, which logically belongs between “base” and “end_at”. Checking the Sort box remedies this annoyance via simple substitution. (Note: “start_at” and “end_at” only appear when a date range has been entered, otherwise there is a single “date” key.)
Changes are not applied automatically — click Get Rates to refresh.
Preview of Coming Attractions
Next time we’ll explore a number of different ways to render a found set as a JSON object.
Have recently tried this solution which worked very well, however in recent month it seems that ExchangeRatesAPI.io require an access key to be used to obtain the rates. I have an access key but cannot get this added to the “get rates” script to allow successful access to the rates. Would it be possible to add how to add this to the script to get the solution working again?
Hi Malcolm,
Arggggh.
It looks like they’ve not only added the access key requirement; they’ve also changed the API parameters. If I find time and motivation to re-do the demo I will… in the mean time, here’s what I’ve learned so far — if in the demo you change the $url parameter to this —
“http://api.currencylayer.com/live?access_key=xxxxxxxxxxxxxxxxxxxxxxx”
— (where “xxxxxxxxxxxxxxxxxxxxxxx” represents your access key), and for the time being don’t include $criteria in the url string, the demo will return results, though not the ones you’re asking for.
To get the demo working again properly will require reconfiguring $criteria… e.g., the argument formerly called “base” has been renamed to “source”.
Good luck,
Kevin
Double arggh — according to https://currencylayer.com/documentation, “source” currency for free accounts is restricted to USD. You need to be on a paid plan to change the source currency to anything other than USD. Oh well, it was a great free service while it lasted. Sigh.
Status update — good news: it appears that by changing literally one line of code in the demo all is now well again — specifically, in the “get rates” script, the $url variable has been repointed to https://api.openrates.io.