How to import daily exchange rate table to PBI ?

[Paulyeo11]

Hi All

Can some one share with me some link on how to get the exchange rate table from web ? what is need to track exchange rate for SGD vs EURO and SGD vs USA

Paul

[ddecoene]

For the Europeans amongts us, you can pull FX rates from the ECB

http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml

Source: https://www.mrexcel.com/board/threads/power-query-from-web-foreign-exchange-rates.917945/

[v-stephen-msft]

Hi @Paulyeo11 ,

I edited my previous reply, please check if my reply is helpful to you, thank you.

Best Regards,

Stephen Tao

[Paulyeo11]

Hi Stephen

Thank you so much for your sharing.

Paul

[v-stephen-msft · Accepted solution]

Hi @Paulyeo11 ,

This is a web page about exchange rates. You can choose to get the data source from the web page in Power BI Desktop.

1.In Power BI Desktop, select Get Data > Web from the Home ribbon.

2.Enter the URL of the web page.

3.Once you’ve typed in (or pasted) the URL, select OK. 

4.You can select Load data or Transform data.

Tips: 

Getting data from a web page lets users easily extract data from web pages, and import that data into Power BI Desktop. Often however, data on Web pages aren't in tidy tables that are easy to extract. Getting data from such pages can be challenging, even if the data is structured and consistent.

There's a solution. With the Get Data from Web by example feature, you can essentially show Power BI Desktop which data you want to extract by providing one or more examples within the connector dialog. Power BI Desktop gathers other data on the page that match your examples. With this solution you can extract all sorts of data from Web pages, including data found in tables and other non-table data. Please refer to: Get webpage data by providing examples.

Reference:

Connect to webpages from Power BI Desktop

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Paulyeo11]

Hi Stephen

I just try to download the exchange rate , it work fine. Thank you very much.

May i know how to download last 3 year exchange rate ? for example SGD to EURO.

Paul

[amitchandak]

@Paulyeo11 , Refer if this video can help

https://www.youtube.com/watch?v=9oPFgHvGyKc

https://www.youtube.com/watch?v=_ubNovnNx5k

[mahoneypat]

This is probably done using one of the free REST APIs out there for exchange rates (you would need to sign up and get a token), but you can also scrape it off a webpage with parameter inputs.  Below is an example.  Create two new blank queries and paste each of the code snippets below into them separately (replacing the text that is there).  The first is a function that makes the weball and the second is an example of how to use it to pull SGD/USD/EUR rates.  You would just need to use the function in your table that has Currency1 and Currency2 values (USD, SGD, etc.).

(Currency1, Currency2)=>
let
    Source = Web.BrowserContents("https://www.xe.com/currencyconverter/convert/?Amount=1&From=" & Currency1 & "&To=" & Currency2),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.XeTable-iKdsnZ.cDpLwI.ConversionTable-iZxPqc.dbfNjJ.currencyconversiontable > * > TR > :nth-child(1)"}, {"Column2", "TABLE.XeTable-iKdsnZ.cDpLwI.ConversionTable-iZxPqc.dbfNjJ.currencyconversiontable > * > TR > :nth-child(2)"}}, [RowSelector="TABLE.XeTable-iKdsnZ.cDpLwI.ConversionTable-iZxPqc.dbfNjJ.currencyconversiontable > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
    Custom1 = Text.BeforeDelimiter(#"Changed Type"{1}[Column2], " ")
in
    Custom1


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnZ3UdJRCg12UYrVgfFcQ4OUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Currency1 = _t, Currency2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Currency1", type text}, {"Currency2", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnER", each fnER([Currency1], [Currency2]))
in
    #"Invoked Custom Function"

Regards,

Pat