Import Currency Rates Into Excel With Power Query - Part 1

We believe that Forex data should be accessible to all. Hence to aid Excel users' access to the currency rates, we decided to write a simple-to-follow tutorial. You will learn how to import and update live forex rates via a REST API in Excel using Power Query. The tutorial requires no programming knowledge and should be simple enough to follow for anyone with a basic excel understanding.

If you are not interested in power query and would like to instead use our Add-in to get Forex and CFD data into Microsoft Excel, please read our tutorial on Excel Add-In. The article also accompanies a video.

Before we start, you will need to open an Excel spreadsheet and signup into our API. Don't worry. It takes seconds.

You can also follow this tutorial as a video:

Let's Begin!

Part I

Step 1

In the spreadsheet, please click on Data. Then click on From Web (To import data from the web), as shown below.

Now login in first. Then visit our docs page and copy the URL from the Live endpoint as shown below.

Step 2

Once you copy the URL, paste it in the box in excel and press OK.

Step 3

Once you click OK, you will be directed to another box. Right-click on List (next to quotes) and click drill down, as shown below.

Step 4

Once you click, you will be redirected. This time, right-click on List again and click on "To Table" (shown in the image below). A box will appear. Please click OK on that box.

Step 5

On the following page, click the box with two arrows right of column 1. Untick the box that says to use the original column name as a prefix box and press ok.

Step 6

You will now see live rates in the boxes, click close and load in the top left corner.

Voila! Now you have live currency rates in an Excel spreadsheet. All you need to do is refresh these rates to get the latest prices.

Even though it's good to have FX rates in Excel, we are still not quite there, as we will need to change things when we need different sets of rates. Don't worry. We will show it's easy to do. However, before we begin, it will be good to break down the request we make to the API to understand how to change it in the future.

Part II

If you remember, we copy-paste the URL from the docs page in Step 2. However, unless we understand what the URL consists of. We can't change our request and hence our data in the table. The below example shows us requesting EURUSD, GBPUSD and UK100:

"marketdata.tradermade.com/api/v1/live?

currency=EURUSD,GBPUSD,UK100&api_key=your-api-key"

We can change this by doing the following:

"marketdata.tradermade.com/api/v1/live?

currency=USDJPY,EURGBP,EURJPY,USDCHF&api_key=your-api-key"

We have added USDJPY, EURGBP, EURJPY, and USDCHF, and the data return will match it.

Now, we understand what changes to make. Let's see it in practice. Double-click on the live connection under queries and the 'connection' on the right of the spreadsheet, as shown below.

Once the Query window opens, navigate to View and click on Advanced Editor.

In the Advanced Editor, replace the old currency string with the new one and press 'Done' as shown below.

You can also rearrange your columns by drag-drop if you don't like the order of things. Once done, click the home button, click 'close' and 'save,' and you will have new rates in the spreadsheet.

As you can see, rates are updated, and columns are rearranged. It is that simple.

You can do several other things to make things dynamic, like changing requests straight from the spreadsheet. But that is for another tutorial in this series. As we know, it can be overwhelming to take on new concepts. We will also introduce historical forex data in the subsequent tutorial. We hope this tutorial will go a long way in getting non-programmers to use our data. If you have any questions, please get in contact. We are also open to tutorial suggestions.