In the end I settled on a python script - see below.
To use it, just save the code and then start the server in a command line / terminal (this is for Linux, but the command prompt syntax would be relatively similar)
sudo python3 libreoffice-proxy.py (sudo not required if you use port > 1024)
Then, to use it from within Libreoffice, prepend http://localhost to the desired URL, e.g. to retrieve HMRC tax information you could do. This should work for both the Link External Data as well as the WEBSERVICE formula, e.g.
=WEBSERVICE("http://localhost/www.trade-tariff.service.gov.uk/api/v2/exchange_rates/files/monthly_xml_2022-8.xml")
Supposing you have an existing cell, A2 with a date in that you’d like to use to query the exchange rates - and you want to find the HMRC monthly USD exchange rate for that month, you could use the following formula:
=FILTERXML(WEBSERVICE(SUBSTITUTE(WEBSERVICE("http://localhost/www.trade-tariff.service.gov.uk/api/v2/exchange_rates/files/monthly_xml_{MONTH}.xml","{MONTH}",YEAR(A2)&"-"&MONTH(A2))),"//exchangeRate[countryCode = ""US""]/rateNew")
Here’s the code for the proxy server.
libreoffice-proxy.py
1 import requests
2 import socketserver
3 import http.server
4 import urllib
5
6 port = 80
7
8 class MyProxy(http.server.SimpleHTTPRequestHandler):
9 def do_GET(self):
10 url="https://" + self.path[1:]
11 self.send_response(200)
12 self.end_headers()
13 print("attempting to retrieve ",url)
14 r = requests.get(url)
15 self.wfile.write(r.text.encode('utf-8'))
16
17 def do_HEAD(self):
18 self.do_OPTIONS()
19
20 def do_OPTIONS(self):
21 self.send_response(204)
22 self.end_headers()
23
24 httpd = http.server.HTTPServer(('', port), MyProxy)
25 print ("Now serving at", str(port))
26 try:
27 #httpd.handle_request()
28 httpd.serve_forever()
29 finally:
30 httpd.server_close()
