Master Google Sheets' GOOGLEFINANCE Function for Currency Conversions

Google Sheets offers a powerful tool for currency conversion – the GOOGLEFINANCE function – unavailable in most other spreadsheet programs. This tutorial explores its capabilities, showing you how to retrieve current and historical exchange rates.

I. Getting Current Exchange Rates

The GOOGLEFINANCE function's basic syntax for currency conversion is straightforward:

GOOGLEFINANCE("CURRENCY:<from_currency><to_currency>")</to_currency></from_currency>

where <from_currency></from_currency> and <to_currency></to_currency> are the three-letter ISO 4217 currency codes (e.g., USD, EUR, GBP, JPY). These codes must be text strings.

Examples:

• USD to EUR: =GOOGLEFINANCE("CURRENCY:USDEUR")
• USD to GBP: =GOOGLEFINANCE("CURRENCY:USDGBP")
• USD to JPY: =GOOGLEFINANCE("CURRENCY:USDJPY")

For enhanced flexibility, use cell references:

Here, cell B3 uses the formula =GOOGLEFINANCE("CURRENCY:"&$A$1&A3) to dynamically combine currency codes from cells A1 and A3.

II. Retrieving Historical Exchange Rates

GOOGLEFINANCE also retrieves historical data. The extended syntax is:

GOOGLEFINANCE("CURRENCY:<from_currency><to_currency>", "price", start_date, [num_days|end_date], [interval])</to_currency></from_currency>

• "price" (optional): Specifies the attribute to retrieve (exchange rate in this case).
• start_date: The starting date (use the DATE function).
• num_days|end_date: Either the number of days or the end date.
• interval (optional): Data frequency ("DAILY", "WEEKLY", or numerical values like 1 for daily, 7 for weekly).

Examples:

• USDEUR exchange rates from September 1st to 10th, 2017: =GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2017,9,1), DATE(2017,9,10), "DAILY")

Simplifying with cell references:

=GOOGLEFINANCE("CURRENCY:USDEUR", "price", A1, A1 5, "DAILY") (A1 contains the start date)

• USDEUR rates for the last 10 days: =GOOGLEFINANCE("CURRENCY:USDEUR","price",TODAY()-10,TODAY())

• EUR to USD rates over 7 days using cell references:

=GOOGLEFINANCE(CONCATENATE("CURRENCY:", C2, B2), "price", DATE(YEAR(A2), MONTH(A2), DAY(A2)), DATE(YEAR(A2), MONTH(A2), DAY(A2) 7), "DAILY")

III. Currency Codes

Currency codes are typically three-letter ISO 4217 codes (e.g., USD, EUR, GBP). A comprehensive list is available in the original article's spreadsheet.

This guide empowers you to efficiently manage currency conversions within Google Sheets, streamlining financial analysis and eliminating manual calculations. Remember to consult the original article for a complete list of supported currency codes.

The above is the detailed content of Currency conversion in Google Sheets. For more information, please follow other related articles on the PHP Chinese website!