Excel Hack: How to Get Currency Data in Excel

Introduction

If you have seen my last article, you will know that spreadsheets and I have a somewhat ambivalent relationship. But I really hope that is not the main thing you got from the article. In it, I described how to get real-time currency data into Google Sheets (if you are interested, you can find it here).

What led me to Google Sheets in the first place was that I am using a Macbook, and I could not find an easy solution to getting real-time data in Excel with it. However, there is one method to get currency data in Excel, no matter what device you are working with, and that is what I will show you today.

Let’s get started!

The step-by-step guide for getting currency data in Excel

This method relies on Excel’s Stock feature. The good news is that you won’t need an API key for this. However, using an API will give you more accurate data. If you want to know how to work with an API, check out this article. It explains the method I will show you and one where you will need a Windows device and a currency API key.

Step 1: Create a table with the currencies you want to convert

First of all, Excel needs to know what currencies you want to convert. So, we start by creating a table and naming the first column “Currency Pairs.”

As for the currency pairs, it is essential to use the correct format; otherwise, Excel won’t recognize them as currencies. We need the international currency code for each currency. Here is a quick list of what some of them look like:

Next, we have to define a base currency. I will use USD. This means that I want to know the conversion rate from USD to the other currencies listed. For Excel to convert them, they must have this format: From Currency/To Currency

So, if I want to convert USD to EUR, I enter: USD/EUR

Step 2: The Excel Stocks feature

After entering all the currencies we want to convert, we select them and go to Data > Stocks.

Excel will convert them into a data type if you formated them correctly. You will know that they are correct if the stock icon appears in your cells. However, if there is a question mark icon, Excel could not find a match, so you will have to go back and rework them.

Step 3: Get currency information.

When you click on the stock icon, the appearing card shows you more information about the currency pair. For getting the currency conversion, the category “price” is the one that interests us for now. Another way to show the cart is by using this shortcut: CTRL (for Mac CMD)+Shift+F5.

Step 4: Calculations with the conversion

Seeing the conversion rate is a good start, but that won’t be enough if you want to calculate prices or compare the rates. So, we want the conversion rate to show in our table. For this, we can go two different ways:

• Using the formula: Click in the cell next to one of the currency pairs and enter: =[reference-cell].Price.

  For Example, we type “=,” select the cell “USD/EUR,” and then choose “price” in the drop-down. Excel will auto-fill the remaining cells, so you don’t have to do this for every pair.

• Using the icon: If you click on the icon next to the table, it opens a list of all the available categories. By clicking on “price,” a new column with the conversion rate is created.

And that’s it. You can try out more categories that might be important for you, like the last trade time. To keep your data up to date, you need to refresh it manually. To update, go to Data > Refresh all. So I wouldn’t recommend this method for professional trading purposes.

One of many methods

As I said before, I chose to show you this method because it works on every system. There are many more methods to work with currency data in Excel, ranging from super easy to necessary programming skills and varying in the accuracy of the data.

I still hope this relatively straightforward method helps you.

Happy converting!