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.
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).
- Confirm the prompt to transform the dataset into a table.
- Click OK.
- 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.
- 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.
- 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!
Hi, great content.
Is there a way to use EPF.Yahoo to search a specific date spot rate? I am struggling with the formula.
Hello Jacques,
To search for a specific date’s spot exchange rate using Excel, you can utilize an API like Alpha Vantage or ExchangeRate-API for historical data. Here’s how you can set it up using Alpha Vantage:
Steps:
1. Sign up for an API key at Alpha Vantage.
2. Use Excel’s WEBSERVICE function to fetch data for historical exchange rates.
For example, to get daily exchange rates from USD to EUR, use this URL:
https://www.alphavantage.co/query?function=FX_DAILY&from_symbol=USD&to_symbol=EUR&apikey=YOUR_API_KEY
Replace YOUR_API_KEY with your actual API key.
3. In Excel, enter the following formula:
=WEBSERVICE(“https://www.alphavantage.co/query?function=FX_DAILY&from_symbol=USD&to_symbol=EUR&apikey=YOUR_API_KEY”)
This will pull the data as a JSON string.
4. Parse the JSON data: To extract the exchange rate for a specific date, you can use a combination of Excel functions like FILTERXML, or for JSON data, you’ll need either a VBA script or a third-party add-in to parse it.
For example, you can use a formula like this (assuming the data is in cell A1):
=FILTERXML(A1, “//Time Series FX (Daily)/2023-04-12/4. close”)
This will return the closing exchange rate for April 12, 2023.
Regards
ExcelDemy