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!
Top comments (0)