Excel Tutorial: Working With Currency Data

Introduction

In my last article, I wrote about the struggle of getting currency data into Google Sheets. (If you haven't seen it, check it out here).
This article described two different ways to work with almost real-time currency data. However, out of my experience, I know that some people don't work with GSheets. This might be your company's policy; some organizations don't want their data to be online.

Whichever reason someone might have to not work with Google Sheets, don't worry. I am here to show you how to get Currency Data into Excel.

This article will show you two methods to get your data into Excel. I have done quite a lot of research to find out the two most straightforward ways.

However, the second method described here will only work on Windows. The neverending feud between Windows and macOS claimed its next victim, but more on that later.

Let's dive in!

Method 1:

If you are working with Excel 365, this method will work on both Windows and macOS. Get started by opening your spreadsheet and following the step-by-step guide:

1. Enter your currency pairs.

Start by entering the currencies you want to compare. The format here is essential.
"From Currency/to currency"

You will need to use the ISO currency codes, so US Dollar is USD, Euro equal EUR, British Sterling Pound is GBP, etc. therefore, in our first cell, we will enter:
"USD/EUR"

2. Using Excel Stocks

Besides the conversion rate from USD to EUR, we also want to know it for GBP, CAD, and AUD. So we create a table and enter the pairs in the correct format.
After this, we select the correctly formatted cells and click on Data > Stocks.

3. Matching Data

If Excel finds a match for your currency pairs, it converts them into a data type. If everything is correct, the stock icon will appear in your cells. If Excel could not find data to match, a question mark will appear instead of the stock icon. Just try again after correcting your format.

4. Get data information

By clicking the stock icon, you can now open the currency card. It shows you information about your currency pair, like its price, the last trade time, the rate high and low, and more.
Or you can use the shortcut to show the card
CTRL (for Mac CMD)+Shift+F5.

5. Show information in columns

If the card alone isn't enough for you, you can also show them in columns. To do so, we simply click into the cell next to the currency pair we need the conversion of and enter
=[reference-cell].Price

For example: My currency pair is USD/EUR.
I, therefore, type "=" then select the cell "USD/EUR" and then choose the category "price". Excel then auto-fills the rest of the currency pair prices.
So, the formula in my example is:
=A2.price

And that's it!

This method is excellent if you need fast access to currency data. However, the information will not update automatically and can be delayed. If you need data for professional trading purposes, method two will be more helpful.

If you need more information, check out this article and the screenshots included. It really helped me.

Method 2:

As mentioned before, this method only works for Windows and not for macOS. Sadly, the feature I will show you just doesn't exist in Excel for macOS. However, if you are a Windows user, you are in luck because this method is as easy as it gets and offers real-time data!
Let's take a look!

Step 1: You need a currency API key

I know this seems like an unnecessary extra step, but believe me when I tell you it's super easy and worth the real-time data. I created a free account within a minute, copied the API key, and was ready to go.

Step 2: Creating a web query

Fire up your Excel sheet, and let's start creating a web query to fetch our rates. We go to Data > From Web.
A window will appear that asks us for an URL. We enter our API key and click OK.

Step 3: Drill Down

We now get redirected to another window. We will now drill down on the information the first fetch got us. In my example are two sets that I can drill down on; meta and data. We right-click Record next to data (since we want more information about currency data, not the meta-information) and then select Drill Down.

Step 4: Into table

Again, we get redirected to the currency data we were looking for. All we now need to do is get the information into our Excel. We right-click on the first conversion and select Into table. After this, we click Close and load in the left upper corner.

Et voil! All our currencies are in our spreadsheet. We go to Table Design > Refresh to keep them up to date.

Less complicated than it might seem

If you are still here, thank you first of all for reading this. I know it can seem a little overwhelming at times but trust me, as soon as you have tried it once, you will realize it's more straightforward than it might seem. Let me know in the comments if you have any questions or if you would like more tutorials like this. Good luck with your conversations; I hope I could help!