Exchange Rates Table

Exchange Rates Table

While creating a report spanning a wide range of dates with values form multiple currencies I was looking for a way to get the rate per day and currency.
Here’s the solution I created using Power Query.

I located a website that accepts a date as a URL parameter “https://www.x-rates.com/historical/?from=USD&amount=1&date=2018-06-07” and created a query using “Web.Page” over “Web.Contents” and I split the URL into 2 pieces “https://www.x-rates.com/historical/?from=USD&amount=1&date=” and a parameter “DateParam” containing a date formatted ‘YYYY-MM-DD’:

let
WebPage =
    Web.Page ( Web.Contents ( "http://www.x-rates.com/historical/?from=USD&amount=1&date=" & DateParam ) )

Resulting in this table

Then I navigated to the table containing all exchange rates:

#"Exchange Table" =
    WebPage { [ ClassName = "tablesorter ratesTable" ] } [Data],

Result:

And transformed the column names and set the rate type to number:

#"Exchange Columns" =
    Table.TransformColumnTypes (
        Table.RenameColumns (
            Table.SelectColumns (
                #"Exchange Table",
                {
                    "US Dollar▲",
                    "inv. 1.00 USD▲▼"
                }
            ),
            {
                { "US Dollar▲", "Currency" },
                { "inv. 1.00 USD▲▼", "Rate" }
            }
        ),
        {
            { "Rate", type number }
        }
    ),

Result:

Then I defined a list of desired currencies (it can be created as a separate query but for demonstration I’m showing it in the query):

#"Currency List" =
        {
            "British Pound",
            "Canadian Dollar",
            "Euro"
        },

And filtered the table to reflect only the desired currencies:

#"Filtered Currencies" =
    Table.SelectRows (
        #"Exchange Columns",
        each List.Contains ( #"Currency List", [Currency] )
    ),

Result:

Then I added a record for “US Dollar”:

 #"Added USD Record" =
    Table.InsertRows (
        #"Filtered Currencies",
        0,
        {
            [
                Currency = "US Dollar",
                Rate = 1
            ]
        }
    ),

Result:

And finally I wrapped in at up into a function “fnGetExchange”:

let
Source = (ParamGetExchange as text) => let
WebPage =
    Web.Page ( Web.Contents ( "http://www.x-rates.com/historical/?from=USD&amount=1&date=" & DateParam ) ,)

#"Exchange Table" =
    WebPage { [ ClassName = "tablesorter ratesTable" ] } [Data],

#"Exchange Columns" =
    Table.TransformColumnTypes (
        Table.RenameColumns (
            Table.SelectColumns (
                #"Exchange Table",
                {
                    "US Dollar▲",
                    "inv. 1.00 USD▲▼"
                }
            ),
            {
                { "US Dollar▲", "Currency" },
                { "inv. 1.00 USD▲▼", "Rate" }
            }
        ),
        {
            { "Rate", type number }
        }
    ),

#"Currency List" =
    {
        "British Pound",
        "Canadian Dollar",
        "Euro"
    },

#"Filtered Currencies" =
    Table.SelectRows (
        #"Exchange Columns",
        each List.Contains ( #"Currency List", [Currency] )
    ),

#"Added USD Record" =
    Table.InsertRows (
        #"Filtered Currencies",
        0,
        {
            [
                Currency = "US Dollar",
                Rate = 1
            ]
        }
    )

in
    #"Added USD Record"

in
    Source

The next step was using the “fnGetExchange” function to generate a table spanning the required range of dates.
First I defined the start and end date (and duration):

let
StartDate =
    #date ( 2017, 1, 1 ),

EndDate =
    #date ( 2017, 12, 31 ),

Duration =
    1,

Then I generated a table with one date per record:

#"Date List" =
    List.Dates (
        StartDate,
        Number.From ( EndDate - StartDate ) + 1,
        #duration ( Duration, 0, 0, 0 )
    ),

#"Date List Table" =
    Table.TransformColumnTypes (
        Table.FromList (
            #"Date List",
            Splitter.SplitByNothing (),
            {
                "ExchangeDate"
            },
            null,
            ExtraValues.Error
        ),
        {
            { "ExchangeDate", type date }
        }
    ),

And then for each row used the function to create a table with the exchange rates for that date (passing in the date formatted as text using the required “yyyy-MM-dd” format):

#"Extracted Exchange Table" =
    Table.AddColumn(
        #"Date List Table",
        "ExchangeData",
        each fnGetExchange ( Date.ToText ( [ExchangeDate], "yyyy-MM-dd" ) ),
        type table
    ),

And then expanded the table column to get the final result. (Due to some sort of weird bug, when expanding the table the data types get set as “type any” so I reset it explicitly):

#"Exchange Table" =
    Table.TransformColumnTypes (
        Table.ExpandTableColumn (
            #"Extracted Exchange Table",
            "ExchangeData",
            {
                "Currency",
                "Rate"
            }
        ),
        {
            { "Currency", type text },
            { "Rate", type number }
        }
    )

in
#"Exchange Table"

Full code:

let
StartDate =
    #date ( 2017, 1, 1 ),

EndDate =
    #date ( 2017, 12, 31 ),

Duration =
    1,

#"Date List" =
    List.Dates (
        StartDate,
        Number.From ( EndDate - StartDate ) + 1,
        #duration ( Duration, 0, 0, 0 )
    ),

#"Date List Table" =
    Table.TransformColumnTypes (
        Table.FromList (
            #"Date List",
            Splitter.SplitByNothing (),
            {
                "ExchangeDate"
            },
            null,
            ExtraValues.Error
        ),
        {
            { "ExchangeDate", type date }
        }
    ),

#"Extracted Exchange Table" =
    Table.AddColumn(
        #"Date List Table",
        "ExchangeData",
        each fnGetExchange ( Date.ToText ( [ExchangeDate], "yyyy-MM-dd" ) ),
        type table
    ),
    
#"Exchange Table" =
    Table.TransformColumnTypes (
        Table.ExpandTableColumn (
            #"Extracted Exchange Table",
            "ExchangeData",
            {
                "Currency",
                "Rate"
            }
        ),
        {
            { "Currency", type text },
            { "Rate", type number }
        }
    )

in
    #"Exchange Table"

I hope you find my code useful!