Link dynamic data from website to Calc

[Rainmanne]

I’m trying to link exchange rates from this page XML Daily Foreign Exchange Rates for U.K. Pound Sterling (GBP) to a spreadsheet. I use it at work in Excel and it works fine. But it does not work in Libreoffice Calc for some reason. I tried to Sheet/Link to External Data but it does not insert a table. I also noticed Data/XMLSource but it’s greyed out. I woonder if it’s possible to do in Libreoffice at all.

[mariosv]

I think it’s possible with WEBSERVICE() and FILTERXML() functions working together.

Help WEBSERVICE() FILTERXML()

Help about XPath expressions

Sample file to recover the data from mentioned web.

LinkDynamicDataFromWebsiteToCalc.ods

[raiko]

Thanks for the example. I tried to follow your example but only able to get 1 cell works. Can you show me how to “extend” the range?

[akd]

I think the answer is here:

How to use FILTERXML to have all the mapped items of an XML source?

This must be set as an array. Select the number of cells for returned information, open Function Wizard, enter your formula as stated in your question and turn on Array (lower left corner): [image description] Select OK will produce: [image description]

In short: use array function.