Currency exchange rate example

In this example, the goal is to get monthly currency exchange rates for a given currency pair (i.e. USD > EUR, USD > GBP, CAD > JPY, etc.). Currency abbreviations are entered in cells F5 and F6, and the start and end dates are entered in cells F7 and F8. If any of these four inputs are changed, the results seen in columns B and C will update dynamically. This can be done with the STOCKHISTORY function, whose purpose is to retrieve historical information for a financial instrument over time.

Overview

Although the name suggests otherwise, the STOCKHISTORY function can work with a variety of financial instruments, including bonds, index funds, mutual funds, bonds, and currency pairs. To work with currency exchange rates, STOCKHISTORY requires a currency pair entered as text.

STOCKHISTORY takes up to 11 arguments, most of which are optional. The syntax for the first 4 arguments looks like this:

=STOCKHISTORY(stock,start_date,[end_date],[interval])

Stock is a unique key for the instrument being retrieved, entered as a text value. For currency exchange rates, stock will be a currency pair like "USD:CAD". Start_date and end_date are Excel dates that define the time period being requested. Interval is the time interval – daily, weekly, or monthly. For a more complete description of all arguments and properties, see our STOCKHISTORY function page.

Basic currency exchange example

To get exchange rates for a given currency pair with STOCKHISTORY, enter the two 3-letter codes separated by a colon (:) as the stock argument. For example, to get the currency exchange rate between the US Dollar ("USD") and the Euro ("EUR") for the months of January 2021 through March 2021, you can use STOCKHISTORY like this:

=STOCKHISTORY("USD:EUR","1-Jan-2021","1-Mar-2021",2)

In this example, the stock argument is the text value "USD:EUR", start_date is "1-Jan-2021", end_date is "1-Mar-2021". The interval argument specifies the time period to use between prices. The options are daily (0), weekly (1), or monthly (2). In this case, we want a monthly interval, so we provide 2. The result is an array with three months of rates:

To reverse the direction of the exchange, just swap the order of the currency pairs:

=STOCKHISTORY("EUR:USD","1-Jan-2021","1-Mar-2021",2)

Returns exchange rates in the opposite direction:

Note: in general, it's safer to use the DATE function to enter dates in other formulas because it eliminates the risk of a text value being misinterpreted on other systems with different locale settings, but in this case dates are entered as text to keep things simple.

Connecting the input cells

To connect the basic formula above with the input cells in the example, shown, we need to substitute cell references for the hardcoded values. The final formula in cell B4 is:

=STOCKHISTORY(F5&":"&F6,F7,F8,2)

Notice we assemble the stock argument by concatenating the currency symbols in cells F5 and F6 together, separated by a colon:

F5&":"&F6 // returns ""USD:EUR"

The result is fully dynamic. If any of these four inputs are changed, STOCKHISTORY will immediately return a new set of exchange rates. In the screen below, currency 1 has been set to "USD" (US Dollar) and currency 2 has been set to "JPY" (Japanese Yen):

The STOCKHISTORY function automatically sets the currency symbol as needed.

Note: when interval is set to monthly (2) as in this example, the STOCKHISTORY function will return the last available exchange rate in a given month.