none
Power Query - Free Currency Exchange Rate Feed? RRS feed

  • Question

  • Hi Folks

    Does anyone know of a free or low cost exchange rate / FX feed that PowerQuery can plug into (say via Odata)?  I  need monthly FX data with history going back 3-4 years. 

    Cheers

    Steve

    Friday, June 6, 2014 1:19 AM

Answers

All replies

  • How about parsing a page like this?

    http://www.x-rates.com/historical/?from=EUR&amount=1&date=2014-06-03&date=2014-06-03

    It looks like there is quite a lot you can set in the URL and From Web in Power Query does a pretty good job parsing the page.

    • Marked as answer by Steve_Fox Tuesday, June 10, 2014 2:33 AM
    Monday, June 9, 2014 6:54 PM
    Moderator
  • Hi Ben

    Thanks for the reply.  That page does Parse well. However the monthly FX history page (link below) which is the dataset I am after, doesn't seem to parse at all :(. 

    http://www.x-rates.com/average/?from=NZD&to=USD&amount=1.00&year=2014

    Any other ideas?

    Cheers

    Steve 



    Tuesday, June 10, 2014 2:26 AM
  • Ok- Found a suitable feed that worked 

    http://www.exchangerates.org.uk/USD-NZD-exchange-rate-history.html

    Cheers

    Steve

    Tuesday, June 10, 2014 2:34 AM
  • Great! Glad you were able to make it work. From Web looks for standard HTML tables and extracts those. Otherwise everything is dumped in to the Document data structure. If the page doesn't have a table on it, digging into the Document object can get pretty gnarly. I usually give up and try to find another source for my data at that point.
    Tuesday, June 10, 2014 4:28 AM
    Moderator
  • For this, I had been using this and it's great.

    It has instructions which can help you.

    I have been using MarketXLS for this, it's been working Great so far


    Just visit the site for more info.

    They also have a live support and a forum to help you with your questions/concerns.

    Tuesday, July 5, 2016 1:08 AM
  • This may be a bit late to the party but the below works well for me. It gets exchange rate data from the ECB and fills in weekend/holiday rates from the last banking day to get you rates for all dates -- just in case your business operates on weekends. You may want to tweak the "Source" line to give you all the currencies you need and the appropriate starting date.

    let
        Source = Csv.Document(Web.Contents("https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.GBP+CAD+USD+JPY+CHF.EUR.SP00.A?startPeriod=2017-01-01", [Headers=[Accept="text/csv"]]),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"CURRENCY", "TIME_PERIOD", "OBS_VALUE"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"CURRENCY", type text}, {"TIME_PERIOD", type date}, {"OBS_VALUE", type number}}),
        #"Renamed Columns" = Table.RenameColumns( #"Changed Type", {{"CURRENCY","Currency"}, {"TIME_PERIOD", "DateTo"}, {"OBS_VALUE", "Rate"}}),
        #"Added DateFrom" = Table.AddColumn(#"Renamed Columns", "DateFrom", each fnMissingDates(#"Renamed Columns", [Currency], [DateTo])),
        #"Expanded DateFrom" = Table.ExpandListColumn(#"Added DateFrom", "DateFrom"),
        #"Removed DateTo" = Table.RemoveColumns(#"Expanded DateFrom",{"DateTo"}),
        #"Renamed DateFrom" = Table.RenameColumns( #"Removed DateTo", {{"DateFrom","Date"}}),
        #"Reordered Columns" = Table.SelectColumns( #"Renamed DateFrom", {"Date", "Currency", "Rate"}),
        #"Sorted Table" = Table.Sort(#"Reordered Columns", { {"Date", Order.Descending}, "Currency" })
    in
        #"Sorted Table"

    plus you need this function

    // fnMissingDates
    (Table as table, ThisCurr as text, ThisDate as date) as list=>
       let 
        Source =  Table.SelectRows(Table, each [Currency] = ThisCurr and [DateTo] > Date.AddDays(ThisDate, -6) and [DateTo] < ThisDate ),
        DateFrom = Date.AddDays(List.Max(Table.Column(Source, "DateTo")), 1), 
        DateFrom2 = if DateFrom = null then ThisDate else DateFrom,
        DateList = List.Dates(DateFrom2, Int16.From(Date.AddDays(ThisDate, 1) - DateFrom2) , #duration(1, 0, 0, 0 ))
    in
        DateList



    Monday, November 12, 2018 1:29 PM
  • here download daily exchange rates from web source https://www.timehubzone.com/currencies/usa.  However, I don't know how to make the date dynamic so when I refresh the query the next day, the query will know to update the date accordingly.

    https://www.timehubzone.com/currencies/result?from=usd&to=gbp&amount=2000

    Saturday, November 17, 2018 4:54 PM
  • How about this instant currency converter? It detects the users location and display local currency default. I do not see others have this instantly. 

    Just go to Currency.World

    Tuesday, May 5, 2020 12:15 PM