Use Power Query to download daily exchange rates from Web

ctran

New Member
Joined
Mar 8, 2017
Messages
3
Dear All,

I want to use power query to download daily exchange rates from web source www.xe.com/currencytables. However, I don't know how to make the date be 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=20<wbr>17-03-07.

How do I change the following language code to make the date not be static at 2017-03-07 and update 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=20<wbr>17-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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
Hi Caroline,

you can use this this command in you Source-step:

Code:
= Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date="&Text.Combine(List.Reverse(Text.Split(Text.From(Date.From(DateTime.LocalNow())), ".")), "-")&""))

It retrieves the current date and transforms it to the desired format :)
 

ctran

New Member
Joined
Mar 8, 2017
Messages
3
Hello Imke -

I tried inserting the above line code but run into a bit of a snag. Received a Expression.Error: The column "Currency code' of the table wasn't found. Tried to figure this out on my own but not getting far :eek:.

Thank you,
Caroline
 

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
Pls try this code:
Code:
let
 Source = Web.Page(Web.Contents("http://www.xe.com/currencytables/?from=USD&date="&DateTime.ToText(DateTime.LocalNow(), "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"

It contains some easier code that some of my friends on Twitter provided :)
If you still receive the errors, you should delete the last step ("#Changed Type") and do the format-conversion manually again. It could be a problem with the special signs.
 

ctran

New Member
Joined
Mar 8, 2017
Messages
3

ADVERTISEMENT

Thanks, Imke. You're absolutely amazing! This did the trick :)
 

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
I have been using 2 different functions for a while, depending on the number of data points I want to return. If I only need the rate for up to 10 different dates I get it from XE.com because it has a wide range of currencies using a relatively simple function that resembles Imke's. However, when I want to plot exchange rates over time, the function turns slow and I have to use the second function that queries the FED's website and fill missing values using the first function (rates are usually one week late in the FED's website).

The function that queries XE returns a scalar value and has the following code:
Code:
// fnForexValue( 
    optional LookupDate as nullable date, //Defaults to Today.
    optional FromCurr as nullable text, //Defaults to USD.
    optional ToCurr as nullable text //Defaults to EUR.
) as number =>
let
    Today = 
        DateTime.Date( DateTime.FixedLocalNow() ) as date,
    ForexDate = 
        if LookupDate is null 
        then Today 
        else LookupDate as date,
    CurrFrom = 
        if FromCurr is null 
        then "USD" 
        else FromCurr as text,
    CurrTo = 
        if ToCurr is null
        then "EUR" 
        else ToCurr as text,
    URL = 
        "http://www.xe.com/en/currencytables/"
        & "?from="
        & CurrTo
        & "&date="
        & Date.ToText( ForexDate, "yyyy-MM-dd" ) as text,        
    Forex = 
        Number.Round( 
                Number.FromText( 
                    List.Last( 
                        Record.FieldValues( 
                            Table.SelectRows(
                                Web.Page(
                                    Web.Contents( URL )
                                ){0}[Data]
                                , each Record.FieldValues( _ ){0} = CurrFrom
                            ){0} 
                        ) 
                    )
                ), 
                4 
            )
in
    Forex

The longer version that uses both the FED's data and XE's returns a table and has performed at least twice as fast in my experience. It's drawbacks are that it always converts from USD and that whenever you need to convert to a currency that you haven't used before, you must browse the FED's site to look for the correct "series". It is one of the longest functions I use regularly, hopefully because it takes you step by step and not because it is way too far from the optimal version (any recommendation to improve it is welcome). Here's the code:
Code:
// fnForexTable(
    StartDate as date,
    optional EndDate as nullable date,//Defaults to Today.
    optional ToCurr as nullable text//Defaults to EUR.
) as table =>


let
    Today = 
        DateTime.Date( DateTime.FixedLocalNow() ) as date,
    //Prevents errors caused by requesting future dates.
    ToDate = 
        List.Min( { Today, EndDate } ) as date,
    //Prevents a negative DayCount that cannot be handled by List.Dates().
    FromDate = 
        List.Min( { StartDate, ToDate } ) as date,
    DayCount = 
        Duration.Days( ToDate - FromDate ) + 1 as number,
    /*Spans all dates in the period and is later used to fill those that are 
    missing in the FED database. */
    DatesList = 
        List.Dates( FromDate, DayCount, #duration( 1, 0, 0, 0 ) ) as list, 
    //Manually generated list relating currencies to the series for the URL. 
    FedSeriesTable = 
        Table.FromRows( 
            {
                { "GBP", "3777001afbcc5b173e81a2055241b679" },
                { "CNY", "356f2a973bbb516442c693dc19615b69" },
                { "INR", "3604f6e0c01fd1a3275b2de1f6547cb6" },
                { "JPY", "1e182bb4a226cda2b7a8593472bac48f" },
                { "CHF", "f838388dca2fd4e8bdfb846f3d2c35df" },
                { "BRL", "e9ee4ef1e0a912f189d364c8ae586f31" },
                { "AUD", "91c3fa18b51a37d6c8bb96f5263c9409" },
                { "MXN", "b23e56a43a4fe0a996e9e2418bdbc2a8" }, 
                { "EUR", "15ba55e8f5302d7efe51819c57682787" }

            }, 
            type table[Currency = text, FedSeries = text] 
        ) as table,
    //If the currency is not supported, defaults to EUR.
    Curr = 
        if List.Contains( FedSeriesTable[Currency], ToCurr )
        then ToCurr 
        else "EUR" as text,
    //Retrieves the series for the selected currency.
    CurrSeries = 
        FedSeriesTable{ [Currency = Curr] }[FedSeries] as text,
    //Concats the various parameters to acces the database.
    FedURL = 
        "https://www.federalreserve.gov/datadownload/Output.aspx?"
        & "rel=H10&"
        & "series=" & CurrSeries & "&"
        & "from=" & Date.ToText( FromDate, "MM/dd/yyyy" ) & "&"
        & "to=" & Date.ToText( ToDate, "MM/dd/yyyy" ) & "&"
        & "filetype=sdmx&"
        & "layout=seriescolumn" as text,
    LoadXML = 
        Xml.Tables( Web.Contents( FedURL ) )[[Table]] as table,
    //Navigates through the XML tables to retrieve the exchange rates.
    ExtractData = 
        LoadXML
            {1}[Table]
            {0}[Table]
            {0}[#"http://www.federalreserve.gov/structure/compact/H10_H10"]
            {0}[Series]
            {0}[#"http://www.federalreserve.gov/structure/compact/common"]
            {0}[Obs] as table,
    ColumnNames = 
        Table.ColumnNames( ExtractData ) as list,
    IgnoreCase = 
        Comparer.OrdinalIgnoreCase as function,
    //Searches for keywords to prepare a list of lists for column renaming.
    ReNames = 
        List.Transform( 
            ColumnNames,
            ( OldName as text ) =>
                if Text.Contains( OldName, "VALUE", IgnoreCase )
                then { OldName, "Forex" }
                else 
                    if Text.Contains( OldName, "TIME", IgnoreCase )
                    then { OldName, "Date" }
                    else 
                        if Text.Contains( OldName, "STATUS", IgnoreCase )
                        then { OldName, "Status" }
                        else { OldName, OldName }
        ) as list,
    RenameColumns = 
        Table.RenameColumns( ExtractData, ReNames )[[Date],[Forex]] as table,
    DataTypes = 
        Table.TransformColumnTypes( 
            RenameColumns, 
            {
                { "Forex", type number }, 
                { "Date", type date }
            }
        ) as table,
    //Removes missing values that carry the value -9999.
    ForexFED = 
        Table.SelectRows( 
            DataTypes, 
            each [Forex] > 0 and [Date] >= FromDate
        ) as table,
    //Generates a table with the dates absent in the FED database.
    XeDateColumn = 
        Table.FromList( 
            List.Difference( DatesList, ForexFED[Date] ), 
            Splitter.SplitByNothing(), 
            type table [Date = date] 
        ) as table,
    //Checks if the selected exchange rate is stored by the FED as USD per Unit.
    USDperUnit = 
        List.Contains( { "EUR", "AUD", "GBP", "NZD" }, Curr ) as logical,
    //Switches between from/to currencies according to the previous step.
    CurrFrom = 
        if USDperUnit
        then Curr
        else "USD" as text,
    CurrTo = 
        if USDperUnit
        then "USD"
        else Curr as text,
    //Queries XE.com once for each date, filters the table and returns the rate.
    XeForex = 
        Table.AddColumn( 
            XeDateColumn, 
            "Forex", 
            each Number.Round( 
                Number.FromText( 
                    List.Last( 
                        Record.FieldValues( 
                            Table.SelectRows(
                                Web.Page(
                                    Web.Contents(
                                        "http://www.xe.com/en/currencytables/"
                                        & "?from="
                                        & CurrTo
                                        & "&date="
                                        & Date.ToText( [Date], "yyyy-MM-dd" )
                                        & ""
                                    )
                                ){0}[Data]
                                , each Record.FieldValues( _ ){0} = CurrFrom
                            ){0} 
                        ) 
                    )
                ), 
                4 
            ), 
            type number 
        ) as table,
    AppendForex = 
        Table.Combine( { ForexFED, XeForex } ) as table,
    SortByDate = 
        Table.Sort( AppendForex, {{ "Date", Order.Ascending }} ) as table,
    //Inverts the rates if necessary.
    ForexTable = 
        if USDperUnit
        then Table.TransformColumns( 
            SortByDate, 
            { "Forex", each Number.Round( 1 / _, 4), type number } 
        )
        else SortByDate as table
in
    ForexTable

I hope it is useful for someone, it has been for me.
 

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
Hi FranzV,
that's really impressive! One can learn a lot from these transformations!
Did you format this by hand or did you find a program that does this?
One little comment: The first opening parenthesis' are included in the commented row, but they should sit in the second row instead:

wrong:
Code:
// fnForexValue( 
    optional LookupDate as nullable date, //Defaults to Today.
correct:
Code:
// fnForexValue
    (optional LookupDate as nullable date, //Defaults to Today.
 

FranzV

Board Regular
Joined
Dec 27, 2016
Messages
178
Hi Imke,

I use a slightly tweaked version of Matt Mason's Power Query language file for Notepad++ when writing my queries. The color highlighting, auto-complete and parenthesis features make it much friendlier than the Advanced Editor.

Thanks for pointing out the mistake with the parenthesis, I must have made a mistake when copy-pasting.

PS: Ich bin ein grosser Fan.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,447
Messages
5,547,985
Members
410,820
Latest member
Prepost
Top