Why Does the GOOGLEFINANCE Function Return No Values for Currencies on Specific Dates?
The GOOGLEFINANCE function is a powerful tool in the Google Sheets web application for tracking market data. However, many users encounter frustrating "N/A" errors or empty cells when attempting to pull historical currency exchange rates. This issue is rarely a bug in your formula; rather, it is usually a result of how financial data is structured and reported globally.
Here are the primary technical reasons why your currency query is returning no values and how to solve it.
1. Market Closures: Weekends and Bank Holidays
The most common reason for missing values is the absence of trading activity. Foreign exchange (Forex) markets generally operate 24 hours a day during the week, but they close on weekends and major international holidays.
- The Problem: If you request an exchange rate for a Saturday or Sunday,
GOOGLEFINANCEmay return an error because no official "closing price" exists for that day. - The Fix: Use a formula that searches for the most recent available weekday. For example, instead of a single date, use a range that covers a few days and select the latest entry.
2. Exchange Rate Latency and Data Gaps
Google Finance aggregates data from various third-party providers. Occasionally, there is a delay or a "data gap" in the feed for specific currency pairs, especially for exotic currencies (e.g., USD to Mongolian Tugrik).
- Historical Limitations: Google Finance does not store every tick of data indefinitely. If you are looking for a currency pair from several decades ago, the data might simply not exist in the Google database.
- The Fix: Verify the currency pair string. Ensure you are using the format
"CURRENCY:USDEUR". If one provider is down, the function may fail until the next successful sync.
3. Incorrect Ticker Syntax
The GOOGLEFINANCE function is highly sensitive to syntax. A common mistake is treating a currency pair like a stock ticker.
- Wrong:
=GOOGLEFINANCE("USD-EUR", "price", "01/01/2023") - Right:
=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2023,1,1)) - Note: You must include the
"CURRENCY:"prefix to tell the web application specifically to look at the Forex servers.
4. Regional Setting Conflicts
Google Sheets uses your Spreadsheet Settings to interpret dates and currency formats. If your spreadsheet is set to a region that uses DD/MM/YYYY but you enter a date as MM/DD/YYYY, the function will look for an invalid date and return no values.
- Go to File > Settings.
- Check your Locale and Time Zone.
- Use the
DATE()function inside your formula to ensure the date is "browser-neutral."
5. Function Limitations on "Attribute" Requests
Not all attributes are available for currencies. While you can pull "price," "high," "low," and "open" for stocks, currencies in GOOGLEFINANCE are often limited strictly to the closing price for historical queries.
- If you attempt to request
"volume"or"marketcap"for a currency pair, the function will return an error because those metrics do not apply to the Forex market in the same way they do for equities.
How to Handle Missing Data: The "IFERROR" Method
To prevent your entire spreadsheet from breaking when a specific date is missing, wrap your function in an IFERROR statement combined with a fallback query:
=IFERROR(GOOGLEFINANCE("CURRENCY:USDEUR", "price", A1), "Data Unavailable")
Conclusion
When GOOGLEFINANCE returns no values for currencies, it is typically due to market holidays, weekend gaps, or syntax errors. By understanding that Forex markets aren't active 365 days a year and ensuring you use the "CURRENCY:" prefix, you can significantly reduce the number of N/A errors in your financial models. For robust tracking, always incorporate error-handling formulas to account for the days when the global markets take a break.
