How to Get the Exchange Rate by Date in Excel (2 Methods)

Method 1 – Using the STOCKHISTORY Function

The STOCKHISTORY function is a powerful tool in Excel that retrieves historical data for stocks or financial elements.

  • To get the exchange rate of Euro from USD for a specific period, follow these steps:
    • Select cell B4.
    • Enter the following formula:
=STOCKHISTORY(F4&":"&F5,F6,F7,2,1)
  • Press Enter.
  • This will give you the exchange rate of Euro from USD on the first day of the last 11 months.

inserting formula to show how to exchange rate by date in excel

Note: Ensure you have a stable internet connection.

Method 2 – Applying Currencies Data Type

The Currencies data type simplifies getting exchange rates for specific currencies.

Here’s how to use it:

  • Select the dataset in cells B4:B10.
  • Go to the Insert tab and choose Table (you can also use the keyboard shortcut CTRL+T).

inserting table to show how to exchange rate by date in excel

  • Confirm the prompt to transform the dataset into a table.
  • Click OK.

confirming table range to show how to exchange rate by date in excel

  • As a result, the dataset will be transformed into a table.
  • Select the cell range B5:B10.
  • Go to the Data tab and choose the Data Type group.
  • From the drop-down list, select Currencies.
  • This changes the data type of the selected range.

using currencies data type to show how to exchange rate by date in excel

  • Add a currency exchange rate column by clicking the plus sign at the top-right corner of the table and selecting Price.

  • Click the plus sign again and choose Last trade time to get the trading date and time.

  • You have exchange rate information by exact time and date using Excel’s Stock/Currency Data Type.


How to Get Exchange Rate from a Specific Date

Suppose you want the exchange rate on the 21st of November.

  • Arrange your dataset as shown in the image.

How to Get Exchange Rate from a Date in Excel

  • Select cell C13 and enter the following formula:
=VLOOKUP(C12,C5:D10,2,FALSE)
  • Press Enter to get the price for that particular date.


Download Practice Workbook

You can download the practice workbook from here:


<< Go Back to Currency Conversion in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo