JSON Currency Exchange Rates revisited

Update 30 September 2023: Free end point no longer available for this service. See JSON Currency Exchange Rates, v3 for a revised, functioning demo.

Demo file: json-rates-via-api-exchangerate-host.zip

Today we’re going to take a fresh look at pulling currency exchange rates into FileMaker, and this article is directly based on its predecessor. Why the re-visitation? Two reasons actually:

1. The “free” endpoints I relied on back in 2020 (and in 2021 when I revised the original demo) have been monetized, and apart from any cost considerations, I want this demo to work out of the box without requiring an API token.
2.  The check box set I used for symbol selection last time was designed to accommodate a fixed and relatively small number of entries. That was a short-sighted decision that could not possibly scale gracefully. This time around I’m using an approach that will automatically accommodate any number of symbols.

The demo file retrieves and displays current and historical currency exchange rates from exchangerate.host, a free service which provides rates “sourced from financial data providers and banks, including 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.exchangerate.host/[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 heading text to navigate to the exchangerate.host web site.

Click the “x” icons to clear fields.

When dates are empty you will get rates from the most recent business day.

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.

The Currency Portal

In the previous incarnation of this demo there were 33 currencies available from the rate provider, and the layout was designed to accommodate that number. This time around the rate provider currently offers 171 symbols, and that number will likely fluctuate over time, so I wanted the symbol selector to accommodate any number of entries, and to be self-maintaining.

Like the rates themselves, the list of symbols is available as a JSON object…

…so I decided to go with a simplified version of the JSON-based portal technique that we looked at a little over a year ago in Connecting Portals to JSON Arrays.

Final Thoughts

Did you notice in the first screen shot that the Bitcoin (BTC) rate is rendered in scientific notation? On these particular dates the BTC rate comes in as 0.000043 in the raw JSON…

…but as soon as any FileMaker JSON function is invoked, the value is converted to scientific notation — in this case 4.3e-05.

As we saw a couple years ago in Bypassing Scientific Notation, there’s a simple hack to force a conversion from scientific to standard notation.

The tricky part is getting the standard notation to “stick” when you push the value into a JSON object or array, because as soon as you invoke JSONSetElement or JSONFormatElements (or any other JSON function), that value will be “helpfully” transformed like so.

Nonetheless, given that formatted (a.k.a. “prettified”) JSON is intended to be read by human beings, and for the sake of consistency, wouldn’t it be nice to find a way to display that number in standard notation? And of course not just that number but any value that appears in scientific notation? I think so. Perhaps that is code for another day.