Live Exchange Rates in Excel and Google Sheets

Spreadsheet-driven companies still price invoices, revalue inventory, and roll forward intercompany balances in Excel and Google Sheets. When those files hold a rate copied from a browser tab last Thursday, a quiet move in EUR/USD live rate or GBP/USD becomes a silent P&L error. Finance operations teams do not need a dealing desk inside the workbook. They need a documented, timestamped FX feed that refreshes on a schedule audit can explain.

Public HTML rate pages break, often violate a provider's terms, and cannot be pinned to bid, ask, or mid. A CSV- or JSON-friendly currency API is the durable path: IMPORTDATA or Apps Script on the Google side, WEBSERVICE or Power Query on the Excel side—with refresh quotas and caching treated as design constraints, not afterthoughts.

Key takeaways

• Use a documented JSON or CSV rates endpoint; do not scrape public HTML pair pages.
• IMPORTDATA is the fastest Google Sheets path for CSV; Apps Script is the production path for JSON.
• In Excel, WEBSERVICE is a one-cell fetch; Power Query is the robust, refreshable option.
• Cache TTL, recalculation, and daily UrlFetchApp / query quotas dictate the real refresh interval.
• Store API keys in Script Properties or Power Query credentials, never in a shared cell.

Why finance workbooks still need a live FX API

FP&A, treasury, and shared-services teams live in workbooks because that is where the rest of the close lives: revenue waterfalls, inventory layers, intercompany netting, and multi-entity P&L. A static rate table copied at month-end is enough for a slow currency. It is not enough when the book has material USD/JPY or emerging-market exposure, or when invoices are issued throughout the day.

The integration bar is operational, not speculative. The feed should identify the pair, the side (mid versus bid/ask), a timestamp in UTC, and a source that can be named in an accounting memo. A subscription API exists for that contract. Browser scraping does not.

Google Sheets: IMPORTDATA for CSV-friendly endpoints

When the rates API offers a CSV or TSV URL, native IMPORTDATA is the least code. Place the endpoint on a Config sheet and pull it with a single formula:

=IMPORTDATA(Config!B2)

Keep the API key out of the formula if the vendor supports header-based auth. Query-string keys leak through version history, published links, and screenshots. If a key in the URL is the only option, restrict the spreadsheet to a tightly controlled Drive folder and rotate the key on a calendar.

IMPORTDATA is not a streaming ticker. Google caches imported content and refreshes on its own schedule—often on the order of an hour, not seconds. Manual recalculation does not guarantee a new HTTP request. For a dashboard that must move closer to the market, skip IMPORTDATA and use Apps Script.

When JSON is the only option

Sheets has no built-in JSON importer. Community IMPORTJSON formulas exist; they inherit custom-function caching (often hours) and are hard for a finance owner to debug. A short Apps Script that writes a values block is easier to support and easier to audit.

Apps Script: JSON fetch plus a time-driven trigger

The production pattern is: fetch JSON with UrlFetchApp, map fields into rows, write once with setValues, and schedule a time-driven trigger. Store the key in Script Properties. Install the trigger from a one-time setup function so a non-engineer does not click through the Triggers UI blindly. Paste the JSON endpoint issued with the Live-Rates subscription into RATES_API_URL.

function refreshLiveRates() {
  const props = PropertiesService.getScriptProperties();
  const url = props.getProperty('RATES_API_URL');
  const apiKey = props.getProperty('RATES_API_KEY');
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) return;

  try {
    const response = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true,
      headers: {
        'Accept': 'application/json',
        'Authorization': 'Bearer ' + apiKey
      }
    });
    if (response.getResponseCode() !== 200) {
      throw new Error('Rates request failed: HTTP ' + response.getResponseCode());
    }
    const payload = JSON.parse(response.getContentText());
    const rates = payload.rates || payload;
    const rows = rates.map(function (item) {
      return [item.pair, item.bid, item.ask, item.mid, item.timestamp];
    });
    const sheet = SpreadsheetApp.getActive().getSheetByName('FX Rates');
    sheet.getRange('A1:E1').setValues([['Pair', 'Bid', 'Ask', 'Mid', 'Timestamp']]);
    sheet.getRange('A2:E').clearContent();
    if (rows.length) {
      sheet.getRange(2, 1, rows.length, 5).setValues(rows);
    }
    sheet.getRange('G1:H1').setValues([['Last success (UTC)', new Date().toISOString()]]);
  } finally {
    lock.releaseLock();
  }
}

function installHourlyRatesTrigger() {
  const handler = 'refreshLiveRates';
  ScriptApp.getProjectTriggers().forEach(function (trigger) {
    if (trigger.getHandlerFunction() === handler) {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  ScriptApp.newTrigger(handler).timeBased().everyHours(1).create();
}

Run installHourlyRatesTrigger once from the Apps Script editor. Google will prompt for authorization to access the spreadsheet and external URLs. Map the JSON fields the vendor actually returns rather than assuming names. Hourly is a sensible default for accounting and pricing workbooks; sub-hour polling burns daily UrlFetchApp quota and rarely changes a controller's decision.

Excel: WEBSERVICE and Power Query

Excel offers two native paths. WEBSERVICE is a worksheet function: it GETs a URL and returns the body as text. Combined with FILTERXML it can parse simple XML; for JSON it is the wrong tool. Recalculation, not a wall clock, drives refresh, and Excel may cache the response.

=WEBSERVICE(Config!B2)

Power Query as the Excel standard

For JSON, use Power Query (Get Data → From Web). Point it at the same rates endpoint, authenticate with an API key header where supported, expand the record or list into columns, and load to a table named FxRates. On Excel desktop, set query properties to refresh on file open and, if needed, on a timer. Excel for the web supports refresh with more limits; confirm the tenant's behavior before promising intra-day updates to the business.

Power Query is also where type hygiene belongs: pair as text, rates as decimal, timestamp as datetime zone. Downstream XLOOKUP or Data Model relationships then stay stable when a new pair appears.

Refresh quotas, caching, and what actually updates

The interval typed into a trigger is not the interval the spreadsheet will honor.

• Google Sheets IMPORT* functions are cached by Google's servers. Sub-hour freshness is not a reliable SLA.
• Apps Script custom functions cache aggressively. Time-driven scripts that write cells do not, but they count against UrlFetchApp and trigger quotas. Consumer Gmail accounts sit on a lower daily fetch cap than Google Workspace.
• Overlapping triggers should take a LockService lock or they will double-write the rate table.
• Excel WEBSERVICE follows calculation. A workbook left in manual calc will show yesterday until someone presses Calculate.
• Power Query background refresh can fail silently on a laptop that slept through the interval. Surface the query's last-refresh timestamp on the cover sheet.

Weekend and holiday sessions need a policy: keep the last good print with a visible timestamp, or flag the book as stale when that timestamp is older than a threshold. FX markets close; an HTTP 200 with a frozen quote is not an error, but it is not live either.

A practical layout for treasury and FP&A

Give the workbook three sheets: Config (endpoint, key location, refresh policy), FxRates (raw API dump, untouched by humans), and Rates (the lookup table formulas actually use—EUR/USD, local billing currencies, and the group's functional currency). Formulas should never point at the raw dump's column index; they should XLOOKUP a pair on the Rates sheet so a vendor field rename does not cascade through every invoice template.

Document in Config whether the book uses mid or bid/ask, the time zone of the timestamp, and who owns the API subscription. That one paragraph saves the next close.

FAQ

How often does Google Sheets refresh IMPORTDATA?

Google caches IMPORTDATA, IMPORTXML, and similar functions on its own schedule, typically on the order of an hour. Recalculating the sheet or reopening it does not guarantee a fresh HTTP request, so IMPORTDATA is a convenience path, not a real-time ticker.

Why use a time-driven Apps Script trigger instead of a custom function?

Custom functions are cached for long periods and re-run in unpredictable ways as the sheet recalculates. A time-driven trigger calls UrlFetchApp on a clock, writes a values block, and can stamp a last-success time—behavior finance owners can explain and monitor.

Is Excel WEBSERVICE enough, or is Power Query required?

WEBSERVICE is enough for a single CSV or plain-text URL that a formula can split. JSON, authentication headers, column types, and scheduled refresh belong in Power Query, which is the production choice for most Excel rate books.

What happens to quotas if the workbook stays open all day?

An open file does not by itself poll the API. IMPORTDATA follows Google's cache, WEBSERVICE follows Excel calculation, and Apps Script follows the trigger calendar. Aggressive intervals on a consumer Apps Script account will hit the daily UrlFetchApp cap and then fail until the quota resets.

Ready to replace pasted rates with a subscription feed? Compare options on the Live-Rates plans page and wire the JSON or CSV endpoint into the patterns above.