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 knowledge of programming and should be simple enough to follow for anyone with 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 to our API. Don't worry it takes seconds.
In the spreadsheet click on Data and then click on From Web (To import data from the web) as shown below.
Once you copy the URL simply paste it in the box in excel and press ok.
Once you click ok you will be directed to another box, simply right click on List (next to quotes) and click drill down as shown below.
Once you click you will be redirected again this time right-click on List again and click on "To Table" (shown in the image below). A box will appear simply click ok on that box.
On the following page, click the box with two arrows right of column1. Untick the box that says use original column name as prefix box and press ok.
You will now see live rates in the boxes, simply 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 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 as we will show it's fairly easy to do however before we begin it will be good to break down the request we make to the API to have an understanding of how to change it in the future.
If you remember we simply 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:
We can change this though simply by doing the following:
We have added USDJPY, EURGBP, EURJPY and USDCHF and the data return will match it.
Now that we understand what needs changing let's see it in practice. Double click on the live connection under queries and connection on the right of the spreadsheet as shown below.
Once the Query window opens simply navigate to View and click on Advanced Editor.
In the Advanced Editor simply replace the old currency string with the new 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 simply click the home button and click close and save and you will have new rates in the spreadsheet.
As you can see rates are updated and columns rearranged. It is that simple.
There is a lot of other things you can do to make things dynamic like changing request straight from the spreadsheet but that is for another tutorial in this series. As we know it can be overwhelming to take new concepts. We will also introduce historical forex data in the next 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.