Convert Currency in Airtable

In this guide, we will convert currency in Airtable using a free currency conversion API and the Data Fetcher Airtable app. We'll first get the latest exchange rates for different currencies from the API into Airtable. Then, we'll fetch historical rates to convert currencies on particular dates in the past.

Install Data Fetcher

Create Output Table

  • Create a table in your base called 'Transactions'.
  • Add a field called 'Currency' with type 'Single line text'.
  • Add a field called 'Amount' with type 'Number'.
  • Add the currencies that you want to convert and the amounts for each transaction.
Screenshot 2021-07-29 at 14.19.07.png

Convert Currencies in Airtable

  • On the home screen of the Data Fetcher app, click 'Create request'.
  • On the create request screen, enter the request name 'Convert currencies'.
  • Based on the exchange rate API documentation, the base URL for all API requests is https://api.exchangerate.host/convert. Enter this URL in the URL field on the create request screen:

https://api.exchangerate.host/convert

  • Add a parameter called from. For the value we want to use a reference to the 'Currency' column in the 'Transactions' table, so enter ***Transactions*Currency***.
  • Add a parameter called to with the value USD. If you want to convert into a different currency (e.g. GBP, EUR), use the symbol for that currency instead.

The full URL should now be:

https://api.exchangerate.host/convert?from=***Transactions*Currency***&to=USD

  • Set the output table to the 'Transactions' table by clicking 'Use current'.
  • Set the output view to 'Grid view' by clicking 'Use current'.

Screenshot 2021-07-29 at 14.24.59.png
  • Click 'Advanced' to open the advanced settings.
  • Under 'Run on Multiple Records', click 'Use current' to set the table to 'Transactions', then click the second 'Use current' button to set the view to 'Grid view'. This means an API request will run for every record in our 'Transactions' table and update the exchange rate.

Screenshot 2021-07-29 at 14.27.07.png
  • Click 'Run'. You'll see a loading message while the request runs, then the response field mapping modal will open. This is where we select which fields from the API response to keep and how they should map to the fields in the output table. Click 'Show response' on this modal to see the actual response.
  • Click 'Filter all' to deselect all fields, then select the following fields and data types:

Response Field

Data type

Use table field

Or, create field:

Result

Number

Exchange Rate

Screenshot 2021-07-29 at 14.29.12.png
  • Click 'Confirm'. Data Fetcher will create the new fields in the output table, then the request will run again and the exchange rates will be imported into Airtable
  • Click 'Save'.
  • Add a new field called 'Amount (USD)' with type 'Formula' and set the formula to Amount*{Exchange Rate}. Set the field formatting to 'Currency'. This has the value of all the amounts converted from the different currencies into USD.

Convert Currencies for Historical Dates in Airtable

Rather than fetching the latest exchange rates, we can get the exchange rate on a particular date for each record.

  • Add a field to the 'Transactions' table called 'Date' with type 'Date'.
  • Enter the date you want to use for each transaction.
  • We need to turn the date field into the correct format for the exchange rate API (e.g. 2021-04-01). Add a field called 'Formatted Date' with type 'Formula' and set the formula to DATESTR(Date).
Screenshot 2021-07-29 at 14.47.58.png
  • In Data Fetcher, add a parameter called date and value ***Transactions*Formatted Date ***. This is a reference to the field we just created.
  • Run the request again. The currencies will be converted on the dates specified in the 'Date' field.
  • Click 'Save'.