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

div class="date-setcion"> Last updated: December 28, 2022
Last updated: December 28, 2022
Get FREE Advanced Excel Exercises with Solutions!

Exchange rate is very crucial to businessmen, especially those who are dealing with foreign currencies. The rate of exchange varies with time. So, it is very important to know beforehand the exchange rate of a certain currency. In this article, we will discuss how to get the exchange rate by date in Excel.


Download Practice Workbook

You can download the practice workbook here.


2 Ways to Get Exchange Rate by Date in Excel

In this article, we will show 2 methods to get the exchange rate by date in Excel. Firstly, we will use the STOCKHISTORY function to get the job done. Then, we will use the Currencies data type to get the exchange rate.


1. Using STOCKHISTORY Function to Get Exchange Rate by Date in Excel

The STOCKHISTORY function is a novel function in Excel. It gets the historical data of a stock or any financial element. Users can define the time period from when the function will retrieve data from. In this method, we will use this function to get the exchange rate of Euro from USD for a certain period.

Steps:

  • Firstly, select the B4 cell and type the following formula,
=STOCKHISTORY(F4&":"&F5,F6,F7,2,1)
  • Then, hit Enter.
  • As a result, we will get 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

P:S- Users should have a stable internet connection.

Read More: Convert Euro to USD in Excel (2 Handy Methods)


2. Applying Currencies Data Type

The Currencies data type is an easy way to get the exchange rate of a certain currency. Here, we will turn a certain conversion into Currency data type and Excel will automatically show the conversion rate.

Steps:

  • Firstly, select the dataset B4:B10.
  • Secondly, go to the Insert tab.
  • Finally, choose Table. You may also create a table by using the keyboard shortcut CTRL+T.
  • Consequently, a prompt will be on the screen.

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

  • In the command, select the dataset as the range to be turned into a table.
  • Then, 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.
  • After that, select the cell in the range B5:B10.
  • Next, go to the Data tab.
  • Thereafter, select the Data Type group.
  • Finally, from the drop-down list, select Currencies.
  • Consequently, the data type of the data will be changed.

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

  • Afterward, select the plus sign to the top-right corner of the table.
  • From the available options, select Price.
  • As a result, we will have a currency exchange rate column included in the Table.

  • Then again, click on the plus sign.
  • From the prevailing options, choose the Last trade time option.

  • As a result, we will have the last date and time of the trading of the currencies.

Thus, you can have the information about the exchange rate prices by exact time and date using Excel’s Stock/Currency Data Type.

Read More: How to Convert USD to Euro in Excel (3 Useful Methods)


How to Get Exchange Rate from a Date in Excel

In this method, we will extract the exchange rate of a particular date from a list. Here, we will use the VLOOKUP function to match the date and extract the currency from the list. Suppose we want to know the exchange rate price on the 21st of November. So, we arrange our dataset like the following image.

How to Get Exchange Rate from a Date in Excel

Steps:

  • Firstly, select the C13 cell and enter the following formula in the cell,
=VLOOKUP(C12,C5:D10,2,FALSE)
  • Then, hit Enter.
  • As a result, we will get the price for the particular date.

Read More: How to Create Real Time Currency Converter In Excel


Conclusion

In this article, we have talked about 2 methods to get exchange rate by date in Excel. These two methods will allow users to be abreast to the new rate of exchange for a particular currency. This article will be particularly helpful for the users who trade currencies. They will get an articulated document of the current exchange rate for any currency. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo