Exceljet

Quick, clean, and to the point

Currency exchange rate example

Excel formula: Currency exchange rate example
Generic formula 
=STOCKHISTORY(currency1&":"&currency2,start,end)
Summary 

To get historical currency exchange rates over time, you can use the STOCKHISTORY function. In the example shown, the formula in cell B4 is:

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

The result is the monthly exchange rate for the USD > EUR currency pair shown in cells F5 and F6. Note that STOCKHISTORY returns an array of values that spill onto the worksheet into multiple cells. The STOCKHISTORY is only available in Excel 365.

Explanation 

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:

Currency exchange rate USD to EUR example

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:

Currency exchange rate EUR to USD example

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):

Currency exchange rate USD to JPY example

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.

Dynamic Array Formulas are only available in Excel 365 and Excel 2021.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.