Currency Exchange Rates - WebConnect & Fixer.io

Getting currency and exchange rate data is possible through the standard web connector and the Fixer.io restful API. 

Documentation for the Fixer.io (https://fixer.io/)

STEP 1: CREATE A FREE ACCOUNT on Fixer.io

Sign up for an account to receive an API key. (https://fixer.io/product)

Free accounts may make up to 1000 calls per month. Free accounts only provide data for the base currency Euro. To use other base currency and free account, then you will need to use transformations to change the base currency (see Step 4).

STEP 2: ADD A WEB CONNECT DATASTREAM IN ADVERITY

• Navigate through Connect > +Add new Datastream and select Web Connect from the list.
• Set the configuration options as follows:
  • Name: Add a Datastream name
  • Connection: leave this blank.
  • URL: http://data.fixer.io/api/{start}?base=EUR&access_key={API Key}
    With this call you will get data for one day only,i.e. start of the fetch. If you wish to fetch ranges, you will need to adjust your URL and upgrade to paid packages
  • If you want to get only a certain currency, you can specify it with e.g. "&symbols=huf", in this case, you will get only exchange rate of Hungarian Forint.
  • Request method: GET
  • Date format: %Y-%m-%d 
  • Parser: JSON
  • Encoding: UTF-8

STEP 3: TRANSFORMATION TO TRANSPOSE RAW DATA

An API Call delivers the currency rates in columns:

In order to Tranpose it you need to use the following Script:

- metaaddfield

 {
  "fieldname": "datastream_extract_range_start",
  "metaname": "datastream_extract_range_start"
  }

- convertx

 {
  "field": "date",
  "expression": "unicode({datastream_extract_range_start})[:10]"
  }

-cutout

 {
  "fields": [
  "datastream_extract_range_start"
  ]
  }

-melt

 {
  "key": [
  "base",
  "date"
  ],
  "variablefield": "currency",
  "valuefield": "Rate"
  }

-convertx

 {
  "field": "currency",
  "expression": "{currency}.split('.')[1]"
  }

-select

 {
  "where": "{currency}",
  "missing": null
  }

-cut

 {
  "fields": [
  "base",
  "date",
  "currency",
  "Rate"
  ]
  }

-distinct

 {
  "key": [
  "date",
  "currency"
  ]
  }

STEP 4: TRANSFORMATION TO CHANGE BASE CURRENCY

If you do not wish to have for your base currency Euro, then you can use the following transformation to change it:

This is an Example to change base currency to GBP

-addfieldx

 {
  "field": "new_base",
  "missing": null,
  "expression": "'GBP'",
  "index": null
  }

-cut

 {
  "fields": [
  "currency",
  "Rate",
  "base"
  ],
  "subtable": "eur->gbp"
  }

-select

 {
  "where": "{currency} == 'GBP'",
  "subtable": "eur->gbp"
  }

-addfieldx

 {
  "field": "new_base_rate",
  "missing": null,
  "expression": "1/float({Rate})",
  "subtable": "eur->gbp"
  }

-join

 {
  "how": "left",
  "keys": [
  "base"
  ],
  "subtables": [
  "eur->gbp"
  ],
  "columns": [
  "new_base_rate"
  ]
  }

-addfieldx

 {
  "field": "new_rate",
  "missing": null,
  "expression": "float({Rate})*float({new_base_rate})",
  "index": null
  }

-cutout

 {
  "fields": [
  "base",
  "date",
  "Rate",
  "new_base_rate"
  ],
  "missing": null
  }