Currency exchange rates library for Apps Script – optimized for the parsimonious – swop.cx

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.

Page Content hide
1 Swop.cx
2 Motivation
3 Getting started
4 Free tier methods
4.1 latest
4.2 onThisDay
4.3 currencies
5 Other methods
6 Conversion
6.1 hackConvert
6.1.1 Amounts
6.1.2 Conversion accuracy
7 Metadata
8 Caching
8.1 Enabling caching
8.2 Disabling cache
9 Optimizing requests by batching
9.1 Getting the sheet data
9.2 Normalizing the date
9.3 Dedupping
9.4 Making the API requests
9.5 Updating the sheet
9.6 The result
10 Links

Swop.cx

The best of the bunch for me is swop.cx.

Here’s the pricing from their site

swop.cx pricing

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.

PropertiesService.getScriptProperties().setProperty("swop-cx-api-key", "your swopcx apiKey")
store apiKey

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

  const fx = bmSwopCx.Fx({
    fetcher: UrlFetchApp.fetch,
    apiKey: PropertiesService.getScriptProperties().getProperty("swop-cx-api-key")
  })
get an instance

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.

  const fx = bmSwopCx.Fx({
    fetcher: UrlFetchApp.fetch,
    apiKey: PropertiesService.getScriptProperties().getProperty("swop-cx-api-key"),
	defaultBase: "USD",
	freeTier: false
  })
if you have a paid tier

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

console.log(fx.latest({ symbols: 'USD,EUR,AUD,GBP,CHF' }).data)

/**
[ { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'AUD',
    quote: 1.616088 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'CHF',
    quote: 1.075103 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'EUR',
    quote: 1 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'GBP',
    quote: 0.853838 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'USD',
    quote: 1.186452 } ]
*/
get latest rates against base currency

If you have a paid tier, you can also specify an alternative base currency

fx.latest({ symbols: 'USD,EUR,AUD,GBP,CHF', base: "HKD" }).data
get latest rates against base currency

onThisDay

This gets the exchange rate on a specific day, expressed as yyyy-mm-dd.

console.log(fx.onThisDay({ symbols: 'USD,EUR,AUD,GBP,CHF' , startDate: '2016-06-01'}).data)

/**
[ { date: '2016-06-01',
    baseCurrency: 'EUR',
    quoteCurrency: 'AUD',
    quote: 1.537212,
    historical: true },
  { date: '2016-06-01',
    baseCurrency: 'EUR',
    quoteCurrency: 'CHF',
    quote: 1.106169,
    historical: true },
  { date: '2016-06-01',
    baseCurrency: 'EUR',
    quoteCurrency: 'EUR',
    quote: 1,
    historical: true },
  { date: '2016-06-01',
    baseCurrency: 'EUR',
    quoteCurrency: 'GBP',
    quote: 0.77249,
    historical: true },
  { date: '2016-06-01',
    baseCurrency: 'EUR',
    quoteCurrency: 'USD',
    quote: 1.116451,
    historical: true } ]
*/
get rates against base currency on a given day

If you have a paid tier, you can also specify an alternative base currency

fx.onThisDay({ symbols: 'USD,EUR,AUD,GBP,CHF' , startDate: '2016-06-01', base: "HKD" }).data
get rates against base currency on a given day

currencies

This returns the metadata about selected currencies. The numeric code is the ISO4217 number of the currency

console.log(fx.currencies({ symbols: 'USD,EUR,AUD,GBP,CHF'}).data)

/**
[ { code: 'AUD',
    name: 'Australian dollar',
    numericCode: '036',
    decimalDigits: 2,
    active: true },
  { code: 'CHF',
    name: 'Swiss franc',
    numericCode: '756',
    decimalDigits: 2,
    active: true },
  { code: 'EUR',
    name: 'Euro',
    numericCode: '978',
    decimalDigits: 2,
    active: true },
  { code: 'GBP',
    name: 'Pound sterling',
    numericCode: '826',
    decimalDigits: 2,
    active: true },
  { code: 'USD',
    name: 'United States dollar',
    numericCode: '840',
    decimalDigits: 2,
    active: true } ]
*/
currency metadata

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.

console.log(fx.latest({ symbols: 'USD,CHF' }).data)

/**
[ 
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'CHF',
    quote: 1.075103 },

  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'USD',
    quote: 1.186452 } ]
*/

/**
  therefore the rate for converting CHF -> USD 
  is 1.186452/1.075103
 **/
relative conversion

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.

  const fxResult = fx.latest({ symbols: 'USD,EUR,AUD,GBP,CHF' })
  console.log(fx.hackConvert(fxResult.data , {from: 'CHF' , to: 'USD'}))
  
  /**
 { rate: 1.1035705416132222,
  historical: false,
  date: '2021-08-02',
  result: 1.1035705416132222,
  to: 'USD',
  from: 'CHF',
  amount: 1 }
  */
  console.log(fx.hackConvert(fxResult.data , {to: 'CHF' , from: 'USD'}))
  /**
  { rate: 0.9061495956010018,
  historical: false,
  date: '2021-08-02',
  result: 0.9061495956010018,
  to: 'CHF',
  from: 'USD',
  amount: 1 }
  */
relative conversions

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

  const fxResult = fx.latest({ symbols: 'USD,EUR,AUD,GBP,CHF' })
  console.log(fx.hackConvert(fxResult.data , {from: 'AUD' , to: 'GBP', amount: 5000}))
  
  /**
{ rate: 0.5283363282197504,
  historical: false,
  date: '2021-08-02',
  result: 2641.681641098752,
  to: 'GBP',
  from: 'AUD',
  amount: 5000 }
  */
amount conversions

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

console.log(fx.latest({ symbols: 'HKD,USD', meta: true}).data)
/**
[{
	"date": "2021-08-02",
	"baseCurrency": "EUR",
	"quoteCurrency": "HKD",
	"quote": 9.221336,
	"meta": {
		"sourceShortNames": "CBB",
		"sourceNames": "Central Bank of Bahrain",
		"sourceIds": ["cbb"],
		"sources": [{
			"id": "cbb",
			"shortName": "CBB",
			"name": "Central Bank of Bahrain"
		}],
		"rateType": "calculated",
		"calculated": true,
		"calculationShortDescription": "Calculated: 9.221336 from CBB (EUR/BHD, BHD/HKD)",
		"calculationDescription": "Calculated rate: 9.221336 from CBB (EUR/BHD 0.446202 with BHD/HKD 20.666281)",
		"calculation": [{
			"pathRate": 9.2213358684,
			"weight": 1,
			"sourceRates": [{
				"sourceId": "cbb",
				"date": "2021-08-02",
				"baseCurrency": "EUR",
				"quoteCurrency": "BHD",
				"quote": 0.446202,
				"flipped": false,
				"fetched": "2021-08-02T09:30:13Z",
				"source": {
					"id": "cbb",
					"shortName": "CBB",
					"name": "Central Bank of Bahrain"
				}
			}, {
				"sourceId": "cbb",
				"date": "2021-08-02",
				"baseCurrency": "BHD",
				"quoteCurrency": "HKD",
				"quote": 20.6662808961,
				"flipped": true,
				"fetched": "2021-08-02T09:30:13Z",
				"source": {
					"id": "cbb",
					"shortName": "CBB",
					"name": "Central Bank of Bahrain"
				}
			}]
		}]
	}
}, {
	"date": "2021-08-02",
	"baseCurrency": "EUR",
	"quoteCurrency": "USD",
	"quote": 1.186452,
	"meta": {
		"sourceShortNames": "CBB",
		"sourceNames": "Central Bank of Bahrain",
		"sourceIds": ["cbb"],
		"sources": [{
			"id": "cbb",
			"shortName": "CBB",
			"name": "Central Bank of Bahrain"
		}],
		"rateType": "calculated",
		"calculated": true,
		"calculationShortDescription": "Calculated: 1.186452 from CBB (EUR/BHD, BHD/USD)",
		"calculationDescription": "Calculated rate: 1.186452 from CBB (EUR/BHD 0.446202 with BHD/USD 2.659002)",
		"calculation": [{
			"pathRate": 1.1864518548,
			"weight": 1,
			"sourceRates": [{
				"sourceId": "cbb",
				"date": "2021-08-02",
				"baseCurrency": "EUR",
				"quoteCurrency": "BHD",
				"quote": 0.446202,
				"flipped": false,
				"fetched": "2021-08-02T09:30:13Z",
				"source": {
					"id": "cbb",
					"shortName": "CBB",
					"name": "Central Bank of Bahrain"
				}
			}, {
				"sourceId": "cbb",
				"date": "2021-08-02",
				"baseCurrency": "BHD",
				"quoteCurrency": "USD",
				"quote": 2.6590016512,
				"flipped": true,
				"fetched": "2021-08-02T09:30:13Z",
				"source": {
					"id": "cbb",
					"shortName": "CBB",
					"name": "Central Bank of Bahrain"
				}
			}]
		}]
	}
}]
*/
full metadata

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)

console.log(fx.latest({ symbols: 'USD,EUR,AUD,GBP,CHF' }).data)

/**
[ { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'AUD',
    quote: 1.616088 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'CHF',
    quote: 1.075103 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'EUR',
    quote: 1 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'GBP',
    quote: 0.853838 },
  { date: '2021-08-02',
    baseCurrency: 'EUR',
    quoteCurrency: 'USD',
    quote: 1.186452 } ]
*/
data property

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

 console.log(fx.latest({ symbols: 'USD,EUR,AUD,GBP,CHF' }))


/**
{ data: 
   [ { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'AUD',
       quote: 1.613823 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'CHF',
       quote: 1.0762 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'EUR',
       quote: 1 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'GBP',
       quote: 0.853424 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'USD',
       quote: 1.188387 } ],
  digest: '45nE-uV7QdSgzZj3bxZQE840ors=',
  timestamp: 1627903829881,
  error: false,
  fromCache: false }
  */
full response

Enabling caching

You enable caching on instantiation by passing an expiry time and some cache property store for it to use

  const fx = bmSwopCx.Fx({
    fetcher: UrlFetchApp.fetch,
    apiKey: PropertiesService.getScriptProperties().getProperty("swop-cx-api-key"),
    cache: CacheService.getScriptCache(),
    cacheSeconds: 600
  })
instantiation with caching

Now you get this result, whenever a result is taken from cache rather than freshly from the API

{ data: 
   [ { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'AUD',
       quote: 1.613823 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'CHF',
       quote: 1.0762 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'EUR',
       quote: 1 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'GBP',
       quote: 0.853424 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'USD',
       quote: 1.188387 } ],
  digest: '45nE-uV7QdSgzZj3bxZQE840ors=',
  timestamp: 1627905309234,
  error: false,
  fromCache: true }
result from cache

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

 console.log(fx.latest({ symbols: 'USD,EUR' , noCache: true}))
/**	
{ data: 
   [ { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'EUR',
       quote: 1 },
     { date: '2021-08-02',
       baseCurrency: 'EUR',
       quoteCurrency: 'USD',
       quote: 1.188387 } ],
  digest: 'rcKeYYMd4Qx7ZvH_JzI5x8McmnM=',
  timestamp: 1627905633093,
  error: false,
  fromCache: false }
  */
disable cache for a single query

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)

amountfromtowhenrateconvertednameFromnameTo
100USDEUR2016-05-01    
1000EURUSD2016-05-01    
250GBPCHF2016-05-01    
1000GBPEUR2016-05-01    
5.99GBPAUD2016-05-01    
100USDEUR     
1000EURUSD     
250GBPCHF     
1000GBPEUR     
5.99GBPAUD     
11.48AUDGBP     
11.48AUDGBP2016-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

  const conversions = bmPreFiddler.PreFiddler().getFiddler({
    id: '1hVB-54VyLrulcoO50Sa7_JL0sxh-QplvV1Zkwfdn4SY',
    sheetName: 'fxr'
  })
getting the sheet data

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

  const formatDate = (date) => date ? Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), 'yyyy-MM-dd') : null
date normalization

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

  const dates = Array.from(
    new Set(conversions.getUniqueValues('when').map(f =>formatDate(f) )))
  const currencies = Array.from(
    new Set(conversions.getUniqueValues('from').concat(conversions.getUniqueValues('to'))))
dedupping

Making the API requests

Now weve reduce the problem to just couple of queries, we can hit the API

  const symbols = currencies.join(",")
  const results = dates.map(f => f ? fx.onThisDay({ startDate: f, symbols }).data : fx.latest({ symbols }).data)
  const currencyData = fx.currencies({symbols}).data
get results from API

Updating the sheet

Fiddler has a mapping function so we can update each row with values from the API results

  conversions.mapRows(row => {
    const d = formatDate(row.when)
    // find the result that is about this day for this row
    const fxResult = results.find(f=>d ? f[0].date === d : (!d && !f[0].historical))
    // do the conversion
    const conversion = fx.hackConvert(fxResult, {from: row.from, to: row.to, amount: row.amount })
    // update the rows from the api
    row.rate = conversion.rate
    row.converted = conversion.result
    row.nameFrom = currencyData.find(f=>f.code === row.from).name
    row.nameTo= currencyData.find(f=>f.code === row.to).name
    return row
  }).dumpValues()
updating and dumping the result

The result

and that’s it

amountfromtowhenrateconvertednameFromnameTo
100USDEUR2016-05-010.877905317987.79053179United States dollarEuro
1000EURUSD2016-05-011.1390751139.075Euro
United States dollar
250GBPCHF2016-05-011.406301788351.575447Pound sterlingSwiss franc
1000GBPEUR2016-05-011.2816848521281.684852Pound sterlingEuro
5.99GBPAUD2016-05-011.91419376311.46602064Pound sterlingAustralian dollar
100USDEUR 0.841476724384.14767243United States dollarEuro
1000EURUSD 1.1883871188.387Euro
United States dollar
250GBPCHF 1.26103789315.2594724Pound sterlingSwiss franc
1000GBPEUR 1.1717505021171.750502Pound sterlingEuro
5.99GBPAUD 1.8909979111.32707748Pound sterlingAustralian dollar
11.48AUDGBP 0.52882131446.070868689Australian dollarPound sterling
11.48AUDGBP2016-05-010.52241315355.997303003Australian dollarPound sterling

Links

bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)

bmSwopCx (19Y6OgrMB_bqEtTvSBHnwKkmbWhyi4jdkQ5FxQXVN-i1Jzx2PKziPqQhd) github