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 . It explains the method I will show you and one where you will need a Windows device and a currency API key. this article 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: Currency Currency Code US Dollar USD Euro EUR British Pound sterling GBP Swiss Franc CHF Japanese Yen JPY Australian Dollar AUD Canadian Dollar CAD Next, we have to define a base currency. I will use USD. This means that I want to know the conversion rate USD the other currencies listed. For Excel to convert them, they must have this format: from to 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: Click in the cell next to one of the currency pairs and enter: . Using the formula: =[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. 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. Using the icon: 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!

Google

Microsoft

Super

Excel Hack: How to Get Currency Data in Excel

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

4 APIs Every Startup Should Know About

Easily getting a user’s country, currency, timezone or language from their IP address

Ionic Currency Converter

HSBC Throws Hat in the Ring - New App Zing Aims to Rival Revolut and Wise!

4 Data Transformations Made Spreadsheet-Easy

4 APIs Every Startup Should Know About

Easily getting a user’s country, currency, timezone or language from their IP address

Ionic Currency Converter

HSBC Throws Hat in the Ring - New App Zing Aims to Rival Revolut and Wise!

4 Data Transformations Made Spreadsheet-Easy

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps