Import Currency Rates Into Excel With Power Query - Part 1

08/13/2021 at 09:58AM

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 via 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.

excel_data1

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

excel_data2

Step 2

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

excel_data3

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.

excel_data4

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.

excel_data5

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.

excel_data6

Step 6

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

excel_data7

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.

excel_data8

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.

excel_data9

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

excel_data10

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

excel_data11excel_data12

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.

excel_data13

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

excel_data14

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.