There are quite a few exchange rate APIs out there to get the latest and historic exchange rates. Google used to have a nice, simple and free one – the finance API, but that was disabled some time ago. You can still get exchange rates and lots of other things using the GoogFinance workbook function, but you’re going to need to be adding that as formula in your spreadsheet, as there’s not a public end point for that (but watch this space for a future post on a workaround for that).
The problem with the remaining fx APIS is that they are not free, or if they have a free tier, it has very limited functionality and miserly rate limits. I think it’s a mistake to not have a free tier, or to have one that doesn’t have the capability to get you past the evaluation of providers and then the development phase of your app – you end up making wrong choices for the wrong reasons, and the API providers misses out on an opportunity.
It would be better if APIs thad a startup package that gave you adequate access up until the time that you go live, and perhaps charged a little more once you did to compensate. It would also help those (like me) that try out APIS just so they can write about them. A limited functionality API means I can’t try out many of the features I’d like to evaluate.
Swop.cx
The best of the bunch for me is swop.cx.
Here’s the pricing from their site
The 1000 requests a month is not really a lot when you’re developing something a little complex, and basic things like specifying a base currency and doing conversions are not included, but this is pretty much standard. Fixer.io is very similar in capability and has less of a monthly allowance. Swop.cx also has the advantage of both a REST and a GraphQL option. If you’re familiar with my site, you’ll know I’m a big GraphQL fan, so that clinched it for me.
Motivation
Once I’d decided on the API to use I needed to find a way to optimize my monthly allowance, and do some things that were not directly available in the free tier (like conversion, and changed base currency). This article is about an Apps Script library (bmSwopCx) that allows you to do all that.
Getting started
First get your apiKey from swop.cx and store it in a property store for use later. Once you’ve done that, then delete this code from your app so it’s not inadvertently committed to github or whatever.
Assuming you’re using the free tier, you’ll need to start by getting an instance of the Fx class. As with all my libraries nowadays, they are dependency free – this means you pass over UrlFetchApp.fetch so it can use that to communicate with SwopCx’s GraphQL API
If you’re using a paid tier, you can enhance that by passing a default base currency, and tell the library it’s okay to attempt to change the base currency on subsequent calls.
The default base currency if not specified is “EUR”. You cannot change the base currency if your apiKey is for the free subscription tier as swopcx will generate an error.
Free tier methods
Only a couple of the methods are available for the swopcx.io free tier
latest
This gets the latest exchange rate for a list of currencies you provide, relative to your default base currency
If you have a paid tier, you can also specify an alternative base currency
onThisDay
This gets the exchange rate on a specific day, expressed as yyyy-mm-dd.
If you have a paid tier, you can also specify an alternative base currency
currencies
This returns the metadata about selected currencies. The numeric code is the ISO4217 number of the currency
Other methods
None of the other swopcx methods are available in the free tier so they are not in the library as I can’t try them out. However we still need a way to convert currencies and use other base currencies, and also a way to minimize our monthly quota.
Conversion
The convert method isn’t available in the free tier, but we can still convert from any currency to any other simply by knowing each of their rates against the same base currency regardless of what that base currency actually is.
So let’s say we want to convert swiss francs to US dollars.
The relative rate of CHF to USD is USDRateToEuro/CHFRateToEuro
hackConvert
bmSwopCx provides a method to convert currencies to each other as long as their rates were calculated from the same base rate (no matter what that was). As input it takes the result of a latest() or onThisDay() query containing each of the currencies and it returns the conversion rate between the two.
A bonus of this method is that it doesn’t need to make a separate API call to do the calculation if you already have the input rates available from a previous call.
Amounts
By default, the amount to convert is 1 so the result will be the same as the rate, but you can also specify an amount
Conversion accuracy
This won’t necessarily give precisely the same result as a direct conversion from an API that allows it would, as there are many factors involved in conversion rates between one currency and another and using their relativity to a third currency can only be a close estimate.
For example, it may be that the respective exchange rates are sourced at slightly different times, or from different banks, or using a different methodology than a direct conversion would be, but the estimate should be good enough for mose uses.
Metadata
SwopCx provides source transparency – where the rates came from and how they was calculated. There’s a lot of stuff. bmSwopCx will provide full metadata by providing the meta: true property – like this
Caching
One technique to avoid using up the rate limit is to use caching. You’ll notice that in each of the examples that touch the API (latest, onThisDay and currencies), we get the result from the .data property as below. (hackConvert result doesn’t have a data property as it never accesses the API)
That’s because bmSwopCx supports caching – here’s the complete result from a latest query. You’ll notice there’s a timestamp and a fromCache property. This will tell you if the result came from cache or was a fresh query, and at what time the API was accessed to get this result
Enabling caching
You enable caching on instantiation by passing an expiry time and some cache property store for it to use
Now you get this result, whenever a result is taken from cache rather than freshly from the API
Disabling cache
To completely disable cache, then don’t provide a cache service on instantiation.
To disable cache for a single query you can use the noCache property
Optimizing requests by batching
The quota is by query, so if you can batch up many requests into a single query you can avoid many calls when a few will do.
Let’s say you have this sheet. The when column is the date the rate is required from (blank means use the latest)
amount | from | to | when | rate | converted | nameFrom | nameTo |
100 | USD | EUR | 2016-05-01 | ||||
1000 | EUR | USD | 2016-05-01 | ||||
250 | GBP | CHF | 2016-05-01 | ||||
1000 | GBP | EUR | 2016-05-01 | ||||
5.99 | GBP | AUD | 2016-05-01 | ||||
100 | USD | EUR | |||||
1000 | EUR | USD | |||||
250 | GBP | CHF | |||||
1000 | GBP | EUR | |||||
5.99 | GBP | AUD | |||||
11.48 | AUD | GBP | |||||
11.48 | AUD | GBP | 2016-05-01 | ||||
You want to calculate the missing fields, but making a separate call for each row could use a lot of your quota. You can summarize to batch up queries by date. We can see here that we need only 2 dates – the latest, and 2016-05-01.
If we collect all the currencies of interest and make just 2 calls (or none if the are already in cache), we can get everything required to use the hackConvert method (which doesnt use the API at all) to cacluate the individual conversions. Here’s an example
Getting the sheet data
As usual, I’m using the bmPreFiddler library to handle the boring stuff around sheet management – for more info see Handly helper for fiddler
Normalizing the date
The API needs dates in yyyy-MM-dd format, and I’ll use the timezone the script is running in, rather than the sheet timezone
Dedupping
Next we need to find out exactly which dates and currencies we can summarize the queries into. Fiddler has a built in uniqueValues method which will get rid of most of the duplication, but I want to reduce the dates to a single day and combine both from and to currencies so we can boil them down a little further by pushing them in and out of a Set
Making the API requests
Now weve reduce the problem to just couple of queries, we can hit the API
Updating the sheet
Fiddler has a mapping function so we can update each row with values from the API results
The result
and that’s it
amount | from | to | when | rate | converted | nameFrom | nameTo |
100 | USD | EUR | 2016-05-01 | 0.8779053179 | 87.79053179 | United States dollar | Euro |
1000 | EUR | USD | 2016-05-01 | 1.139075 | 1139.075 | Euro |
United States dollar
|
250 | GBP | CHF | 2016-05-01 | 1.406301788 | 351.575447 | Pound sterling | Swiss franc |
1000 | GBP | EUR | 2016-05-01 | 1.281684852 | 1281.684852 | Pound sterling | Euro |
5.99 | GBP | AUD | 2016-05-01 | 1.914193763 | 11.46602064 | Pound sterling | Australian dollar |
100 | USD | EUR | 0.8414767243 | 84.14767243 | United States dollar | Euro | |
1000 | EUR | USD | 1.188387 | 1188.387 | Euro |
United States dollar
| |
250 | GBP | CHF | 1.26103789 | 315.2594724 | Pound sterling | Swiss franc | |
1000 | GBP | EUR | 1.171750502 | 1171.750502 | Pound sterling | Euro | |
5.99 | GBP | AUD | 1.89099791 | 11.32707748 | Pound sterling | Australian dollar | |
11.48 | AUD | GBP | 0.5288213144 | 6.070868689 | Australian dollar | Pound sterling | |
11.48 | AUD | GBP | 2016-05-01 | 0.5224131535 | 5.997303003 | Australian dollar | Pound sterling |
Links
bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)
bmSwopCx (19Y6OgrMB_bqEtTvSBHnwKkmbWhyi4jdkQ5FxQXVN-i1Jzx2PKziPqQhd) github