Search code examples
powerbipowerquery

How to get historical exchange rate data from the European Central Bank API?

I have trouble getting historical exchange rate data from the ECB API: https://data.ecb.europa.eu/help/api/overview

I am an absolute novice and so far I was only able to get the current exchange rates through https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

The end result I would like to achieve is a something like this (exchange rates for EURO):

Date USD GBP
2024-08-29 1.09 0.92
2024-08-28 1.12 0.89

... back to 2000-January-1 (or if the info does not exist this far back: as far back as it exists)

I am working in Power Query M (PowerBI).

The query I use right now is the following, however this does not output what I actually need. It Outputs this:

Currency_name Rate Date
USD 1.11 2024-08-27
GBP 0.84 2024-08-27 
let

    url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml",
    

    Source = Xml.Tables(Web.Contents(url)),
    

    ExpandedSender = Table.ExpandTableColumn(Source, "Sender", {"name"}, {"Sender.name"}),
    ExpandedEurofxref = Table.ExpandTableColumn(ExpandedSender, "http://www.ecb.int/vocabulary/2002-08-01/eurofxref", {"Cube"}, {"Eurofxref.Cube"}),
    ExpandedCube = Table.ExpandTableColumn(ExpandedEurofxref, "Eurofxref.Cube", {"Cube"}, {"Cube.Cube"}),
    ExpandedCubeCube = Table.ExpandTableColumn(ExpandedCube, "Cube.Cube", {"Cube", "Attribute:time"}, {"Cube.Cube.Cube", "Date"}),
    ExpandedRate = Table.ExpandTableColumn(ExpandedCubeCube, "Cube.Cube.Cube", {"Attribute:currency", "Attribute:rate"}, {"Currency", "Rate"}),
    

    FilteredCurrencies = Table.SelectRows(ExpandedRate, each [Currency] = "USD" or [Currency] = "GBP"),
    

    ReplacedComma = Table.TransformColumns(FilteredCurrencies, {{"Rate", each Text.Replace(Text.From(_), ".", ","), type text}}),
    

    USDDate = Table.SelectRows(ReplacedComma, each [Currency] = "USD"){0}[Date],
    

    EURRow = Table.FromRecords({
        [Currency = "EUR", Rate = "1", Date = USDDate]
    }),
    

    CombinedTable = Table.Combine({ReplacedComma, EURRow}),
    #"Removed Columns" = Table.RemoveColumns(CombinedTable,{"subject", "Sender.name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Currency", "Currency_name"}})
    
in
    #"Renamed Columns"

Would highly appreciate some help. Thank you very much <3 -L

Solution

  • I had to look for a source that contains everything, see if this is what you need. If the answer helped, please mark it at this answer is useful. Euro is always 1, if you need a EUR column, put a new column with value 1. your source in the other code is daily report.

       let

    url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-sdmx.xml",
    Source = Xml.Tables(Web.Contents(url)),
    Table = Source{1}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{1}[Table],
    #"Obs Expandido" = Table.ExpandTableColumn(Table2, "Obs", {"Attribute:TIME_PERIOD", "Attribute:OBS_VALUE"}, {"Obs.Attribute:TIME_PERIOD", "Obs.Attribute:OBS_VALUE"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Obs Expandido", each ([#"Attribute:CURRENCY"] = "GBP" or [#"Attribute:CURRENCY"] = "USD")),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Linhas Filtradas",{"Obs.Attribute:TIME_PERIOD", "Obs.Attribute:OBS_VALUE", "Attribute:CURRENCY", "Attribute:FREQ", "Attribute:CURRENCY_DENOM", "Attribute:EXR_TYPE", "Attribute:EXR_SUFFIX", "Attribute:TIME_FORMAT", "Attribute:COLLECTION"}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Colunas Reordenadas",{"Attribute:FREQ", "Attribute:CURRENCY_DENOM", "Attribute:EXR_TYPE", "Attribute:EXR_SUFFIX", "Attribute:TIME_FORMAT", "Attribute:COLLECTION"}),
    #"Coluna em pivô" = Table.Pivot(#"Colunas Removidas", List.Distinct(#"Colunas Removidas"[#"Attribute:CURRENCY"]), "Attribute:CURRENCY", "Obs.Attribute:OBS_VALUE"),
    #"Linhas Classificadas" = Table.Sort(#"Coluna em pivô",{{"Obs.Attribute:TIME_PERIOD", Order.Descending}})
in
    #"Linhas Classificadas"

    or

    let

    url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml",
    Source = Xml.Tables(Web.Contents(url)),
    #"Sender Expandido" = Table.ExpandTableColumn(Source, "Sender", {"name"}, {"Sender.name"}),
    #"http://www.ecb.int/vocabulary/2002-08-01/eurofxref Expandido" = Table.ExpandTableColumn(#"Sender Expandido", "http://www.ecb.int/vocabulary/2002-08-01/eurofxref", {"Cube"}, {"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube"}),
    #"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube Expandido" = Table.ExpandTableColumn(#"http://www.ecb.int/vocabulary/2002-08-01/eurofxref Expandido", "http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube", {"Cube"}, {"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube"}),
    #"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube Expandido" = Table.ExpandTableColumn(#"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube Expandido", "http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube", {"Cube", "Attribute:time"}, {"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube", "http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Attribute:time"}),
    #"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube Expandido" = Table.ExpandTableColumn(#"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube Expandido", "http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube", {"Attribute:currency", "Attribute:rate"}, {"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube.Attribute:curr", "http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube.Attribute:rate"}),
    #"Colunas Removidas" = Table.RemoveColumns(#"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube Expandido",{"subject", "Sender.name"}),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Colunas Removidas",{{"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube.Attribute:curr", "Currency"}, {"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Cube.Attribute:rate", "Rate"}, {"http://www.ecb.int/vocabulary/2002-08-01/eurofxref.Cube.Cube.Attribute:time", "Time"}}),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Colunas Renomeadas",{"Time", "Currency", "Rate"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Colunas Reordenadas", each ([Currency] = "GBP" or [Currency] = "USD")),
    #"Coluna em pivô" = Table.Pivot(#"Linhas Filtradas", List.Distinct(#"Linhas Filtradas"[Currency]), "Currency", "Rate"),
    #"Linhas Classificadas" = Table.Sort(#"Coluna em pivô",{{"Time", Order.Descending}}),
    #"Valor Substituído" = Table.ReplaceValue(#"Linhas Classificadas",".",",",Replacer.ReplaceText,{"USD"}),
    #"Valor Substituído1" = Table.ReplaceValue(#"Valor Substituído",".",",",Replacer.ReplaceText,{"GBP"})
in
    #"Valor Substituído1"