Power Query from web. Foreign Exchange Rates

[Quiquegl]

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]

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]

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]

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]

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