Skip to content

Exchange rates in Google Sheets

Add live and historical exchange rates to any Google Sheet with three custom functions backed by the FxFeed API:

Function Returns
=FXRATE(from, to, [date]) The rate from one currency to another: how many to one from buys
=FXCONVERT(amount, from, to, [date]) An amount, or a range of amounts, converted
=FXRATES(base, [currencies], [date]) A two-column table of currency codes and rates

Leave out date for the latest rate, or pass a date cell or "YYYY-MM-DD" text for a past day; history goes back to 1999. Rates are published on working days, so a weekend or a bank holiday takes the rates of the last working day before it. The functions autocomplete in the formula bar like built-in ones.

Install

  1. Get a free API key if you do not have one yet.
  2. In your spreadsheet, choose Extensions → Apps Script.
  3. Replace everything in Code.gs with the script at the end of this page (also served as plain text at /integrations/google-sheets/FxFeed.gs) and click Save.
  4. Reload the spreadsheet. An FxFeed menu appears in the menu bar.
  5. Choose FxFeed → Set API key, paste your key and click OK. Google asks you once to authorize the script, because it connects to api.fxfeed.io and shows a prompt in the sheet.
  6. Optionally choose FxFeed → Test API key: it makes one request and shows a rate.

The key is stored in the script's Script Properties under FXFEED_API_KEY, never in a cell, so it does not travel with a copied range, an export or a published sheet. People with edit access to the spreadsheet can open its Apps Script project, so share edit access only with people you would give the key to. FxFeed → Remove API key deletes it.

Examples

=FXRATE("USD", "EUR")                   latest USD to EUR rate
=FXRATE("EUR", "GBP", "2024-01-02")     EUR to GBP on 2 January 2024
=FXRATE(A2, B2, C2)                     currency codes and a date from cells
=FXCONVERT(100, "USD", "JPY")           100 US dollars in yen
=FXCONVERT(D2:D50, "USD", "EUR", C2)    a column of amounts at one day's rate
=FXRATES("USD", "EUR,GBP,JPY")          three rows of code and rate
=FXRATES("EUR")                         every currency against the euro

FXCONVERT multiplies by exactly the rate FXRATE returns for the same arguments, so a converted column always agrees with a rate column beside it. Round for display with =ROUND(FXCONVERT(…), 2) or a number format.

Requests and caching

Each request the script makes counts towards your plan's monthly allowance: 1,000 requests a month with daily data on the free plan, and hourly data on the paid plans. The script is built to spend as few as possible:

  • One request fetches every rate for a base currency and day, so FXRATE("USD", "EUR"), FXRATE("USD", "GBP") and FXRATES("USD") share it.
  • Answers are cached with Apps Script's CacheService: the latest rates for one hour, past days for six hours, the longest the cache keeps anything.
  • A range passed to FXCONVERT costs one request, however many rows it has.
  • Errors are never cached, so a corrected key or a new month works at once.

Google Sheets decides when to recalculate a custom function, mainly when its arguments change, so a latest rate can stay on screen longer than the hour the script caches it. Change an argument or re-enter the formula to fetch it again.

Errors

Hover over a cell showing #ERROR! to read the message.

Message What to do
No FxFeed API key Choose FxFeed → Set API key. No menu? Reload the spreadsheet.
FxFeed rejected the API key (401) The key is wrong or was revoked: copy it again from your dashboard and set it again.
FxFeed monthly request limit reached (402) This month's requests are used up. The message links to the upgrade page; otherwise the count starts again on the 1st (UTC).
Your FxFeed plan does not include this (403) The request is outside what your plan covers.
FxFeed rate limit reached (429) Many cells asked at once. The script already retries; wait a few seconds and recalculate.
FxFeed error (400): … The API's own message, such as an unknown currency code.
Unknown function: FXRATE The script is not saved in this spreadsheet's Apps Script project: open Extensions → Apps Script and save it.

The endpoints the script calls, /latest and /historical, are described in the API reference. For a walkthrough with more spreadsheet recipes, read Live Currency Conversion in Google Sheets.

The script

/**
 * FxFeed for Google Sheets: exchange-rate custom functions backed by the
 * FxFeed API (https://fxfeed.io).
 *
 *   =FXRATE("USD", "EUR")                     latest rate
 *   =FXRATE("USD", "EUR", "2024-01-02")       rate on a past day
 *   =FXCONVERT(100, "USD", "EUR")             100 USD in EUR
 *   =FXCONVERT(A2:A20, "USD", "EUR", B1)      a column of amounts on a day
 *   =FXRATES("USD", "EUR,GBP,JPY")            a two-column table of rates
 *
 * Install: Extensions > Apps Script, paste this file, save, reload the
 * spreadsheet, then FxFeed > Set API key. The key is kept in the script's
 * properties, never in a cell. Get a free key at https://fxfeed.io/signup.
 *
 * Every answer is cached (latest rates for an hour, past days for six
 * hours, the longest CacheService allows), and one request per base
 * currency and day serves every target currency, so a sheet full of
 * formulas spends very little of your monthly quota.
 *
 * Version 1.0.0. Documentation: https://fxfeed.io/docs/google-sheets
 */

var FXFEED_API_BASE = 'https://api.fxfeed.io/v2';
var FXFEED_KEY_PROPERTY = 'FXFEED_API_KEY';
var FXFEED_CACHE_LATEST_SECONDS = 60 * 60;
var FXFEED_CACHE_HISTORICAL_SECONDS = 6 * 60 * 60;
var FXFEED_RETRIES = 3;
var FXFEED_SIGNUP_URL = 'https://fxfeed.io/signup';
var FXFEED_PRICING_URL = 'https://fxfeed.io/pricing';

/**
 * Returns the exchange rate from one currency to another: how many units
 * of `to` one unit of `from` buys. Leave out the date for the latest rate.
 *
 * @param {string} from The currency to convert from, e.g. "USD".
 * @param {string} to The currency to convert to, e.g. "EUR".
 * @param {Date|string} [date] Optional day for a historical rate: a date cell or "YYYY-MM-DD".
 * @return {number} The exchange rate.
 * @customfunction
 */
function FXRATE(from, to, date) {
  var base = fxfeedCurrency_(from, 'from');
  var target = fxfeedCurrency_(to, 'to');
  var day = fxfeedDay_(date);
  if (base === target) {
    return 1;
  }
  var rates = fxfeedRates_(base, day);
  if (typeof rates[target] !== 'number') {
    throw new Error('FxFeed has no ' + base + '/' + target + ' rate' + (day ? ' for ' + day : '') + '.');
  }
  return rates[target];
}

/**
 * Converts an amount, or a range of amounts, from one currency to another
 * at the latest rate or at the rate of a past day.
 *
 * @param {number|Array<Array<number>>} amount The amount, or a range of amounts, to convert.
 * @param {string} from The currency to convert from, e.g. "USD".
 * @param {string} to The currency to convert to, e.g. "EUR".
 * @param {Date|string} [date] Optional day for a historical rate: a date cell or "YYYY-MM-DD".
 * @return {number|Array<Array<number>>} The converted amount(s).
 * @customfunction
 */
function FXCONVERT(amount, from, to, date) {
  // Read the amounts before the rate, so a typo costs no request.
  if (Array.isArray(amount)) {
    var amounts = amount.map(function (row) {
      return (Array.isArray(row) ? row : [row]).map(function (cell) {
        return cell === '' || cell === null ? '' : fxfeedAmount_(cell);
      });
    });
    var rateForRange = FXRATE(from, to, date);
    return amounts.map(function (row) {
      return row.map(function (cell) {
        return cell === '' ? '' : cell * rateForRange;
      });
    });
  }
  var value = fxfeedAmount_(amount);
  return value * FXRATE(from, to, date);
}

/**
 * Returns a two-column table of rates for one base currency: the currency
 * code in the first column and its rate in the second.
 *
 * @param {string} base The base currency, e.g. "USD".
 * @param {string} [currencies] Optional comma-separated currencies, e.g. "EUR,GBP". Leave out for every currency.
 * @param {Date|string} [date] Optional day for historical rates: a date cell or "YYYY-MM-DD".
 * @return {Array<Array<string|number>>} Rows of [currency, rate].
 * @customfunction
 */
function FXRATES(base, currencies, date) {
  var from = fxfeedCurrency_(base, 'base');
  var day = fxfeedDay_(date);
  var rates = fxfeedRates_(from, day);
  var wanted = [];
  if (currencies === undefined || currencies === null || String(currencies).trim() === '') {
    wanted = Object.keys(rates).sort();
  } else {
    wanted = fxfeedFlatten_(currencies).join(',').split(',').filter(function (code) {
      return code.trim() !== '';
    }).map(function (code) {
      return fxfeedCurrency_(code, 'currencies');
    });
  }
  return wanted.map(function (code) {
    if (code === from) {
      return [code, 1];
    }
    if (typeof rates[code] !== 'number') {
      throw new Error('FxFeed has no ' + from + '/' + code + ' rate' + (day ? ' for ' + day : '') + '.');
    }
    return [code, rates[code]];
  });
}

/** Adds the FxFeed menu when the spreadsheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('FxFeed')
    .addItem('Set API key', 'fxfeedSetApiKey')
    .addItem('Test API key', 'fxfeedTestApiKey')
    .addItem('Remove API key', 'fxfeedRemoveApiKey')
    .addToUi();
}

/** Menu: asks for the API key and stores it in the script properties. */
function fxfeedSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var answer = ui.prompt(
    'FxFeed API key',
    'Paste your FxFeed API key (it starts with "fxf_"). Get a free key at ' + FXFEED_SIGNUP_URL + '.\n' +
      'It is stored in this script\'s properties, not in the spreadsheet.',
    ui.ButtonSet.OK_CANCEL
  );
  if (answer.getSelectedButton() !== ui.Button.OK) {
    return;
  }
  var key = answer.getResponseText().trim();
  if (!/^fxf_?[A-Za-z0-9_-]+$/.test(key)) {
    ui.alert('That does not look like an FxFeed API key: keys start with "fxf_". Nothing was saved.');
    return;
  }
  PropertiesService.getScriptProperties().setProperty(FXFEED_KEY_PROPERTY, key);
  ui.alert('Saved. FXRATE, FXCONVERT and FXRATES will use this key.');
}

/** Menu: makes one request with the stored key and reports the result. */
function fxfeedTestApiKey() {
  var ui = SpreadsheetApp.getUi();
  try {
    var body = fxfeedFetch_('/latest', { base: 'USD', currencies: 'EUR' });
    ui.alert('The key works. 1 USD = ' + body.rates.EUR + ' EUR (' + String(body.date).slice(0, 10) + ').');
  } catch (err) {
    ui.alert(err.message);
  }
}

/** Menu: forgets the stored API key. */
function fxfeedRemoveApiKey() {
  PropertiesService.getScriptProperties().deleteProperty(FXFEED_KEY_PROPERTY);
  SpreadsheetApp.getUi().alert('The FxFeed API key was removed from this spreadsheet\'s script.');
}

/**
 * Every rate for one base currency, latest (day empty) or on a day, from
 * the cache or one API request. A lock keeps a sheet recalculating many
 * cells at once from asking for the same rates several times.
 */
function fxfeedRates_(base, day) {
  var cache = CacheService.getScriptCache();
  var cacheKey = 'fxfeed:v1:' + base + ':' + (day || 'latest');
  var cached = cache.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
  var lock = LockService.getScriptLock();
  var locked = lock.tryLock(20000);
  try {
    if (locked) {
      cached = cache.get(cacheKey);
      if (cached) {
        return JSON.parse(cached);
      }
    }
    var body = day
      ? fxfeedFetch_('/historical', { base: base, date: day })
      : fxfeedFetch_('/latest', { base: base });
    var rates = body.rates || {};
    if (day && Object.keys(rates).length === 0) {
      rates = fxfeedLastRatesBefore_(base, day);
    }
    cache.put(cacheKey, JSON.stringify(rates), day ? FXFEED_CACHE_HISTORICAL_SECONDS : FXFEED_CACHE_LATEST_SECONDS);
    return rates;
  } finally {
    if (locked) {
      lock.releaseLock();
    }
  }
}

/**
 * The rates of the last day up to `day` that has any. Rates are published
 * on working days, so a weekend or a bank holiday answers no rates of its
 * own; like most accounting practice, it takes the previous working day's.
 * One /timeseries request covers the week before.
 */
function fxfeedLastRatesBefore_(base, day) {
  var end = new Date(day + 'T00:00:00Z');
  var start = new Date(end.getTime() - 7 * 86400000);
  var body = fxfeedFetch_('/timeseries', {
    base: base,
    start_date: start.toISOString().slice(0, 10),
    end_date: day,
  });
  var days = Object.keys(body.rates || {}).filter(function (d) {
    return d <= day && Object.keys(body.rates[d]).length > 0;
  }).sort();
  if (days.length === 0) {
    throw new Error('FxFeed has no ' + base + ' rates on or in the week before ' + day + '.');
  }
  return body.rates[days[days.length - 1]];
}

/**
 * Calls the FxFeed API and returns the parsed JSON body, or throws an
 * error worded for the person reading the cell. A rate-limited request is
 * retried with a short pause; failures are never cached.
 */
function fxfeedFetch_(path, params) {
  var key = PropertiesService.getScriptProperties().getProperty(FXFEED_KEY_PROPERTY);
  if (!key) {
    throw new Error('No FxFeed API key: choose FxFeed > Set API key (free key at ' + FXFEED_SIGNUP_URL + ').');
  }
  var query = Object.keys(params).map(function (name) {
    return encodeURIComponent(name) + '=' + encodeURIComponent(params[name]);
  }).join('&');
  var url = FXFEED_API_BASE + path + '?' + query;
  var response;
  for (var attempt = 1; attempt <= FXFEED_RETRIES; attempt++) {
    response = UrlFetchApp.fetch(url, {
      method: 'get',
      headers: { 'X-API-Key': key, Accept: 'application/json' },
      muteHttpExceptions: true,
    });
    if (response.getResponseCode() !== 429 || attempt === FXFEED_RETRIES) {
      break;
    }
    Utilities.sleep(1000 * attempt);
  }
  var status = response.getResponseCode();
  var body = {};
  try {
    body = JSON.parse(response.getContentText());
  } catch (e) {
    body = {};
  }
  if (status === 200 && body.success !== false) {
    return body;
  }
  throw new Error(fxfeedErrorMessage_(status, body));
}

/** Words a failed response for a cell's error tooltip. */
function fxfeedErrorMessage_(status, body) {
  var message = body && (body.message || (body.error && body.error.info));
  switch (status) {
    case 401:
      return 'FxFeed rejected the API key (401). Check it with FxFeed > Set API key.';
    case 402:
      return 'FxFeed monthly request limit reached (402). Upgrade your plan at ' +
        ((body && body.upgrade_url) || FXFEED_PRICING_URL) + ' or wait for the new month (UTC).';
    case 403:
      return 'Your FxFeed plan does not include this (403)' + (message ? ': ' + message : '.');
    case 429:
      return 'FxFeed rate limit reached (429): too many requests at once. Wait a few seconds and recalculate.';
    default:
      if (status >= 500) {
        return 'FxFeed is unavailable right now (' + status + '). Try again in a minute.';
      }
      return 'FxFeed error (' + status + ')' + (message ? ': ' + message : '.');
  }
}

/** A three-letter currency code from a cell, upper-cased. */
function fxfeedCurrency_(value, name) {
  var code = String(value === undefined || value === null ? '' : value).trim().toUpperCase();
  if (!/^[A-Z]{3}$/.test(code)) {
    throw new Error('"' + name + '" must be a three-letter currency code such as USD, got "' + code + '".');
  }
  return code;
}

/**
 * A day as "YYYY-MM-DD" from a date cell, a date serial number or text;
 * empty for the latest rate. A date cell is read in the spreadsheet's own
 * time zone, which is how Sheets hands it to the function.
 */
function fxfeedDay_(value) {
  if (value === undefined || value === null || value === '') {
    return '';
  }
  if (Object.prototype.toString.call(value) === '[object Date]') {
    if (isNaN(value.getTime())) {
      throw new Error('"date" is not a valid date.');
    }
    return Utilities.formatDate(value, fxfeedTimeZone_(), 'yyyy-MM-dd');
  }
  if (typeof value === 'number') {
    // A date serial number: days since 30 December 1899.
    var ms = Math.round((value - 25569) * 86400000);
    return Utilities.formatDate(new Date(ms), 'UTC', 'yyyy-MM-dd');
  }
  var text = String(value).trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) {
    throw new Error('"date" must be a date cell or text like 2024-01-31, got "' + text + '".');
  }
  return text;
}

/** The spreadsheet's time zone, or the script's outside a spreadsheet. */
function fxfeedTimeZone_() {
  try {
    return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  } catch (e) {
    return Session.getScriptTimeZone();
  }
}

/** A number from a cell, or an error naming the bad value. */
function fxfeedAmount_(value) {
  var amount = typeof value === 'number' ? value : Number(String(value).replace(/,/g, ''));
  if (typeof value === 'boolean' || isNaN(amount)) {
    throw new Error('"amount" must be a number, got "' + value + '".');
  }
  return amount;
}

/** The values of a cell or a range, as one flat list of strings. */
function fxfeedFlatten_(value) {
  if (!Array.isArray(value)) {
    return [String(value)];
  }
  var out = [];
  value.forEach(function (item) {
    out = out.concat(fxfeedFlatten_(item));
  });
  return out;
}