Excel Live Currency Exchange Rate with Web Query

If you want to create a table of exchange rates that can be updated live in Excel, you have come to the right place. In this article you will learn how to retrieve exchange rates using a Get & Transform web query, also known as a PowerQuery query. You will also learn how to turn that query into a function that can be used in another query to populate a table of exchange rates.

The API I will use to do this is the currency.com API. You can find the documentation at https://currency.com/api. For a free API I find this is pretty good. There is no need to authenticate yourself to get a rate. More expensive APIs such as XE.com might be of interest if you need to retrieve more niche currency pairs or more recent data.

Step 1: Create the Web Query

To start, we need to create the web query to fetch the exchange rate. Our query will start off fetching a single rate, and then we can convert it to a function that can be used many times to get multiple rates.

Go to the Data tab on the ribbon and click From Web. Enter the following URL: https://api-adapter.backend.currency.com/api/v1/ticker/24hr?symbol=USD/CAD You can replace the symbol if you wish but we will be converting this to a function so it won’t matter shortly. In the screenshot above, I separated the symbol into another URL part to make it easier to change the query into a function later. Click OK.

At this point, give the query a recognizable name. This will become the name of the function. If you only need one currency pair, you can simply Close & Load this query and lastPrice is the exchange rate. However, if you would like to create a table of many rates, right click lastPrice and click Drill Down. Once that’s done, click back to the Home tab and click the bottom part of Close & Load and choose Close & Load To…

When the dialog pops up, choose Only Create Connection and click OK.

Step 2: Create Query for Exchange Rate List

If you want to create a table of rates, you need to create a list of rates that table should contain and turn that into a query. Create the list and then go to the Data tab and click From Table/Range. If your table has a header like mine, check My Table Has Headers and click OK.

Step 3: Change your Web Query to a Function

When the query editor loads, find your other query in the sidebar on the right. We need to change that into a function. Click the query and go into the advanced editor. Add the first line and change the third line and click OK.

(Pair as text) =>
let
    Source = Json.Document(Web.Contents("https://api-adapter.backend.currency.com/api/v1/ticker/24hr?symbol=" & Pair)),
    lastPrice = Source[lastPrice]
in
    lastPrice

Step 4: Use the Function

Now you need to modify your table query to use the function to fetch the exchange rate for pairs in your list. Find your table query and go to the advanced editor. Change it as follows and click OK.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pair", type text}}),
    #"Inserted Exchange Rate" = Table.AddColumn(#"Changed Type", "Exchange Rate", each FXRate([Pair]), type text)
in
    #"Inserted Exchange Rate"

You may receive a warning about data privacy. Click Continue and check ignore and Save.

Close & Load and you are done! You will have a table like the one below. If you want to update the pairings, modify the original table you created in Step 2 and refresh the query. If pairs are showing up blank, the API doesn’t support them.

Why Use an API Anyway?

When I first created the video at the top of this tutorial, I looked to see what others on YouTube were doing. I felt pretty silly working so hard to get this API working when I saw others importing an HTML table like I show you in one of my other tutorials. However, using an API offers a couple of key advantages:

• APIs are expecting other programs to retrieve data from them and should provide a warning when the data format will change. Websites can update their design sporadically and break your sheet.
• The API sends a minimal amount of relevant data rather than excess data to show how the website displays. This should result in faster loading times and less bandwidth use. This is important if you plan to update this data often, such as once every minute.

So there you have it. Enjoy!