0

The premise is to retrieve current currency rates into a Libre/OpenOffice Calc spreadsheet. Previously I did it using a JSON service, with manual tweaking of substrings, which isn't all that user-friendly. So I wanted to find out how it could be done using native functions in Libre/OpenOffice.

The service: https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml which returns an XML such as:

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <gesmes:subject>Reference rates</gesmes:subject>
    <gesmes:Sender>
        <gesmes:name>European Central Bank</gesmes:name>
    </gesmes:Sender>
    <Cube>
        <Cube time='2021-03-23'>
            <Cube currency='USD' rate='1.1883'/>
            <Cube currency='JPY' rate='128.99'/>
            <Cube currency='BGN' rate='1.9558'/>
            <Cube currency='CZK' rate='26.198'/>
            <Cube currency='DKK' rate='7.4360'/>
            <Cube currency='GBP' rate='0.86198'/>
            <Cube currency='HUF' rate='366.51'/>
            <Cube currency='PLN' rate='4.6191'/>
            <Cube currency='RON' rate='4.8893'/>
            <Cube currency='SEK' rate='10.1813'/>
            <Cube currency='CHF' rate='1.1066'/>
            <Cube currency='ISK' rate='148.30'/>
            <Cube currency='NOK' rate='10.1783'/>
            <Cube currency='HRK' rate='7.5755'/>
            <Cube currency='RUB' rate='90.2781'/>
            <Cube currency='TRY' rate='9.3638'/>
            <Cube currency='AUD' rate='1.5491'/>
            <Cube currency='BRL' rate='6.5562'/>
            <Cube currency='CAD' rate='1.4938'/>
            <Cube currency='CNY' rate='7.7367'/>
            <Cube currency='HKD' rate='9.2290'/>
            <Cube currency='IDR' rate='17105.58'/>
            <Cube currency='ILS' rate='3.9099'/>
            <Cube currency='INR' rate='86.1025'/>
            <Cube currency='KRW' rate='1342.48'/>
            <Cube currency='MXN' rate='24.5926'/>
            <Cube currency='MYR' rate='4.8988'/>
            <Cube currency='NZD' rate='1.6918'/>
            <Cube currency='PHP' rate='57.779'/>
            <Cube currency='SGD' rate='1.5955'/>
            <Cube currency='THB' rate='36.819'/>
            <Cube currency='ZAR' rate='17.5818'/>
        </Cube>
    </Cube>
</gesmes:Envelope>
Juha Untinen
  • 809

1 Answers1

0

The reason for this in particular is that I presume this use case is very common, and the European Central Bank data is an official source for the rates (so, popular to use), so this should be helpful to many.

There are two built-in functions that you can combine to retrieve the data without resorting to manually editing substring lengths (my previous solution: https://superuser.com/a/1599638/174037).

  • WEBSERVICE obviously, to retrieve the data
  • FILTERXML to parse data from XML input (European Central Bank XML in this case)

So to parse the data from the original XML, you would use:

Now B1 will contain the actual value regardless of the current length, so you don't need to manually edit the MID "substring" when the currency rate changes from for example 1.2325 to 1.23

The explanation for the xpath (the "//*[local-name()='Cube' and @currency='" & A1 & "']/@rate" part above):

  • It finds "any" XML element called <Cube> where the element also has an attribute called currency which matches the value defined in Cell A1 (USD in this example)
  • Then it "fetches" the value of the attribute rate in that result, which in this case is the currency ratio compared to Euro (<Cube currency='USD' rate='1.1883'/> at the moment)

So now Cell B1 will have the string value 1.1883. Depending on your locale, you might also want to convert it to a comma instead of period for the decimals, for example in Cell C1: =VALUE(SUBSTITUTE(B1;".";",")). And perhaps to have the currency rate vice versa in Cell D1: =1/C1

Juha Untinen
  • 809