Currency Data in Excel
I have previously written an article on how to compare Cryptocurrencies in Google Sheets and explained why Google Sheets is the better choice compared to Excel.
My biggest issue with Excel was that some features needed for currency conversion are not available on macOS. However, I decided to do a tutorial on how to get currency data in Excel anyway because there are two ways to do it, and only one requires a Windows operating system.
So, without any further ado, here are two methods to get currency data in Excel.
Method 1: Excel's Stock feature (for Windows and macOS)
This method is suitable for you if you don't rely on real-time data. It is quickly done and doesn't need any special requirements. We will use Excel's integrated Stock feature so we won't need an API key. However, this means that we won't get as accurate data as we would with an API.
Step 1:
First, we create a table with the (crypto)currencies we want to convert. The only thing that is important here is using the right format. We need to use the international currency codes (i.e., USD, EUR, BTC, ETH, etc. otherwise, Excel won't recognize them as currencies) in the format "From Currency/To Currency. "
ℹ️ For example:
We want to convert US Dollar to Bitcoin. So we enter "USD/BTC "to see the value of Bitcoin for 1 US Dollar.
Step 2:
Now that we have a list of currencies we want to convert, we need the Excel Stocks feature. You can find it under Data > Stocks. We now select the currencies we need to convert and select Stocks. Excel will now (if you have used the right format) convert them into currencies. The stock symbol in your cells shows you that it worked.
Step 3:
To get information from this feature, we either click on the stock item in the cells or use this shortcut: CTRL(CMD)+Shift+F5. This will open a card with information about the currency pair. We are interested in the price. However, to compare the price values, we need them in our table, not just on the card.
We can do one of two things for the price to also appear in the table.
- Use the formula: Click in the cell next to one of the currency pairs and enter: =[reference-cell].Price. Excel will auto-fill the remaining cells, so you don't have to do this for every pair.
ℹ️ For example :=A2.Price
- Use the icon: You can also click on the icon in the right lower corner of your table. It will open a list of available categories. Select "price," and Excel creates a new column with the conversion rates.
Method 2: Real-Time Currency Rates (for Windows only)
As mentioned before, Excel Stocks is an excellent feature as it is easy to use and doesn't need any additional tools. However, if you are looking for a more professional approach and need real-time data instead of as-is information, method two might suit you. But don't worry, you don't need any programming skills. All you need is a free API key.
Step 1: API Key Procurement
You won't get around finding an API to work with real-time currency data. There are some free currency APIs out there. For this example, I use freecurrencyapi.com. They offer 5000 free monthly requests. To get started, I log into the app and copy my API Key.
Step 2: Create a Web Query
To get started, we open a spreadsheet and create a web query to fetch the exchange rates. We go to Data > From Web.
A new window will appear asking for an URL. Here, we enter our API key and click OK after. The base currency automatically is USD.
Step 3: Drilling Down
After starting our web query, we get directed to another window. We now need to execute a Drill Down. To do so, we right-click on Record next to Data and then select Drill Down.
Step 4: Transform Into Table & Load
This will, again, redirect us. We select "Into table" in the new Window by right-clicking on the first conversion. Click OK on the Window that appears after. This will get us our currency rates. To finalize the web query, click on Close & Load in the left upper corner.
Now we have the live currency rates in our Excel Sheet. If we want to work with the latest prices, we simply click on Table Design > Refresh.
It all comes down to your needs
Here we go! These two methods allow you to compare real-time (crypto) currency rates in Excel. While both have pros and cons, in the end, it comes down to what you want to gain from fetching currency data in Excel. Do you need it for a hobby project or professionally? It won't hurt to try out both. They are easy to do and don't require any special skills.
Let me know in the comments which one works best for you and why! I hope this tutorial helps!