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. 



    Friday, June 6, 2014 1:19 AM


All replies

  • How about parsing a page like this?

    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
  • 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 :(.

    Any other ideas?



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



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

        Source = Csv.Document(Web.Contents("", [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" })
        #"Sorted Table"

    plus you need this function

    // fnMissingDates
    (Table as table, ThisCurr as text, ThisDate as date) as list=>
        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 ))

    Monday, November 12, 2018 1:29 PM
  • here download daily exchange rates from web source  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.

    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