use power query to download daily exchange rates

[chtran]

Hello,

I'm using power query to download daily exchange rates from web source www.xe.com/currencytables.  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.

For example, the URL web source is http://www.xe.com/currencytables/?from=USD&date=2017-03-07.

How do I change the following syntax to make the date not static at 2017-03-07 so that when I refresh it the next day the formula will change to 2017-03-08?

let
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date=2017-03-07")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})
in
    #"Changed Type"

Thank you very much.

Caroline

[trysodin]

Hi all,

You can do this by simply changing the link to  http://www.xe.com/currencytables/?from=USD the website then defaults to the current date.

Its always teh simple answer!

Cheers,

Trys

[estherbunny]

Dear all,

May I know if I can substitute the Datetoday to change it to point to a cell value in excel to make the date variable instead of today date?

Thanks!

Best regards,

ESther

[estherbunny]

Dear all,

May I know if I can substitute the "Datetoday" to change it to point to a cell value in excel to make the date variable instead of today's date?

Thanks!

Best regards,

ESther

[Anonymous]

Hi Caroline,

Just in case you don't have the solution yet, here you go.  I assigned the current date to DateToday variable and replaced the hard-coded date in your "Source" line, with the neccesary formating.

let
    DateToday = DateTime.Date(DateTime.LocalNow()),
    Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&"&Date.ToText(DateToday, "yyyy-MM-dd"))),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Currency code ▲▼", type text}, {"Currency name ▲▼", type text}, {"Units per USD", type number}, {"USD per Unit", type number}})
in
    #"Changed Type"

Best regards,

Ferdinand

[Punitsetia]

Does it work Dynamically??????