# Power Query from web. Foreign Exchange Rates



## Quiquegl (Jan 30, 2016)

Dear All,

Is it possible to import for free the daily foreign exchange rates (all of them if possible)  from a reliable web such as Oanda or ECB to Power Query?

I have tried on both websites but can't get the data. So frustrating!!

Thanks a lot!

Enrique


----------



## ImkeF (Jan 31, 2016)

Could you provide a Webpage with an example or describe:
1) which currencies you're after
2) which period (only daily rates or for a longer period)?


----------



## Quiquegl (Jan 31, 2016)

US dollars versus the following foreign currencies:

*China yuan, **South Korea won, **U.K. pound, **Euro area euro

I used this website: 

*http://online.wsj.com/mdc/public/page/2_3021-forex.html.

I only need daily foreign exchanges although it would be nice to pull data for last month.


----------



## ImkeF (Feb 1, 2016)

These are 2 Options:

let
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date=2016-02-01")),
    Data0 = Source{0}[Data],
    #"Filtered Rows" = Table.SelectRows(Data0, each ([#"Currency code ▲▼"] = "CNY" or [#"Currency code ▲▼"] = "EUR" or [#"Currency code ▲▼"] = "GBP" or [#"Currency code ▲▼"] = "KRW"))
in
    #"Filtered Rows"

let
    Source = Web.Page(Web.Contents("http://online.wsj.com/mdc/public/page/2_3021-forex.html")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Column1] = "China yuan" or [Column1] = "Euro area euro" or [Column1] = "South Korea won" or [Column1] = "U.K. pound"))
in
    #"Filtered Rows"

Oanda is a paid service and ecb doesn't include won and yuan in their web service (you'd have to download the files, but this isn't what you're after, right?)
The first source let's you edit the date in the URL, so you can retrieve historical rates as well.


----------



## ImkeF (Feb 1, 2016)

...here comes the ECB web service against the EUR: 
let
    Source = Xml.Tables(Web.Contents("http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"subject", type text}}),
    #"http://www ecb int/vocabulary/2002-08-01/eurofxref" = #"Changed Type"{0}[#"http://www.ecb.int/vocabulary/2002-08-01/eurofxref"],
    Cube = #"http://www ecb int/vocabulary/2002-08-01/eurofxref"{0}[Cube],
    Cube1 = Cube{0}[Cube],
    #"Changed Type1" = Table.TransformColumnTypes(Cube1,{{"Attribute:time", type date}}),
    #"Expanded Cube" = Table.ExpandTableColumn(#"Changed Type1", "Cube", {"Attribute:currency", "Attribute:rate"}, {"Attribute:currency", "Attribute:rate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Cube", each ([#"Attribute:currency"] = "CNY" or [#"Attribute:currency"] = "GBP" or [#"Attribute:currency"] = "KRW" or [#"Attribute:currency"] = "USD"))
in
    #"Filtered Rows"


----------

