5

I have a *ods.-spreadsheet in LibreOffice. Some fields contain numbers in currency format (JPY), but they could also be in general number format. I want to create corresponding fields which display the amount of money in Euro and US$, doing the conversion automatically (i.e. without me supplying conversion rates, and the conversion rates updating themselves).

Is this possible and if yes, how?

6

Without using a plugin, try using api.fixer.io:

=LEFT(RIGHT(WEBSERVICE("http://api.fixer.io/latest?symbols=EUR,GBP"),9),7)

This will retrieve something like this:

{"base":"EUR","date":"2017-08-17","rates":{"GBP":0.90895}}

and display this:

0.90895

The same technique would work with any other currency URL.

Regards

Edit 2020-09-15: The original URL provided is no longer valid. You can still get free rates at fixer.io, but you have to sign up for an api key. Here is an alternative place to get the data: https://exchangeratesapi.io/.

| improve this answer | |
  • This should be the accepted answer. – Dan Dascalescu Oct 26 '17 at 3:44
  • Beware, fixer.io gives currency prices that are several days out of date (2 days in my testing). – JKAbrams Mar 11 '18 at 11:48
  • I just get ERR:540 :/ – endolith Jul 2 at 5:23
  • @endolith the sample url is not valid anymore. You'll have to find another one – doc Jul 3 at 6:32
  • that URL gives this warning IMPORTANT - PLEASE UPDATE YOUR API ENDPOINT This API endpoint is deprecated and has now been shut down. To keep using the Fixer API, please update your integration to use the new Fixer API endpoint, designed as a simple drop-in replacement. – phuclv Sep 11 at 7:23
1

There might be better ways but this is what I'm using:

First import the data from the website:
Insert -> "Link to External Data..." -> URL (eg https://forum.openoffice.org/en/forum/search.php?keywords=exchange+rates)

Then Define the cell containing the exchange rate:
Select cell (eg the JPY rate) Insert -> Names -> Define... (eg euro_to_jpy)

Now you can use in formula:

=A2*euro_to_jpy 

(see here for more: https://forum.openoffice.org/en/forum/search.php?keywords=exchange+rates)

| improve this answer | |
1

A slight generalization of doc's answer:

First, get currency data into a cell with the base currency of your choice (in this example, ILS is the base currency):

=WEBSERVICE("https://api.exchangeratesapi.io/latest?base=ILS")

Let's say that the output resides in cell I17. Now, the foreign-to-base ratio can be obtained by (example for GBP):

=MID(I17,SEARCH("GBP", $I$17)+5,9)

The number 9 here represents the number of characters of the exchange rate that are used (number of digits + decimal point). You may use more or less as required (number is concatenated, not rounded), but not more than 12. If you don't need high accuracy, it may be safer to use just 4 characters in case the api changes in the future.

[I used exchangeratesapi.io since it is a public service provided by the European Central Bank, which I think makes it unlikely to be discontinued]

| improve this answer | |
  • The webservice works well, but the lookup doesn't work. It gives Err:501 (invalid character). I'll fiddle around and see what's wrong. It is unfortunate the proposed FILTERJSON() is not yet implemented in LibreOffice bugs.documentfoundation.org/show_bug.cgi?id=113974 – Juha Untinen Nov 4 at 9:23
  • Ah, two things: 1) Depending on locale, Libre uses a different parameter separator (semicolon instead of comma, in my case) and 2) The length of the value in the API response is shorter than 9 characters. So, in my case, this works: =MID($A$1;SEARCH(A3;$A$1)+5;7) where the API data is in cell A1 and my lookup text is in A3 (and A4, A5, A6, etc for different currencies). – Juha Untinen Nov 4 at 9:35
0

You can use the GETYAHOO function of the SMF Extension for Calc to automatically get currency exchange rates. For instance, to convert from JPY to EUR you'd use it like this:

=GETYAHOO("JPYEUR=X", 1)

P.S.: you may need to tweak this code in order to make it work. The function I actually use is called GETQUOTE, from the Getquote extension which is not available anymore but still works.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy