// 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
