9

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?

5 Answers5

8

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.

Fixer.io   -   Important Changes to API (2018)

Source: Fixer - Important Announcement
The legacy Fixer API (api.fixer.io) is deprecated and discontinued as June 1st, 2018. A re-engineered API is available at https://data.fixer.io/api/. The core structure of the API has remained unchanged, but you will need to perform a few changes to your integration:

  1. Get a Free Fixer Access Token at fixer.io
  2. Replace API URL and add Access Key
    Old:   'https://api.fixer.io/latest'
    New: 'https://data.fixer.io/api/latest?access_key=YOUR_ACCESS_KEY'
Blindspots
  • 2,876
  • 2
  • 17
  • 21
doc
  • 181
  • 1
  • 4
  • 1
    This should be the accepted answer. – Dan Dascalescu Oct 26 '17 at 03: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 02 '20 at 05:23
  • @endolith the sample url is not valid anymore. You'll have to find another one – doc Jul 03 '20 at 06:32
  • 1
    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 '20 at 07:23
2

2023-03-29: The API's in the solutions from Doc and Shimi now require subscribing for API keys.

ExchangeRate-API

If you prefer a no-key option building on the spirit of Doc and Shimi's solutions, ExchangeRate-API still offers a free, no-key option.
* See 'Limitations of No-Key Option' section at end

Building on the approach by Shimi:

  1.   In the first cell, e.g. G13, put the following:
=WEBSERVICE("https://open.er-api.com/v6/latest/JPY")
  1.   In another cell, to prevent calling the API endpoint twice, put the following:
=MID(G13,SEARCH("EUR", G13)+5, 8)

This will convert JPY to EUR.


Limitations of No-Key Option

Open Access Endpoint: Differences Between Open/Free/Pro

  1.   Requests are Rate Limited
    • only 1 request is allowed per hour
    • rate limit based IP blocks expire after 20 minutes
  2.   Data is Stale
    • data is refreshed once every 24 hours
    • timestamp of next update included in responses
  3.   Attribution
    • incorporating rates in pages requires linked attribution
Blindspots
  • 2,876
  • 2
  • 17
  • 21
HasHPIT
  • 21
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)

Kevin Panko
  • 7,366
Gordon Lucas
  • 11
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.

Waldir Leoncio
  • 5,188
0

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.

Blindspots
  • 2,876
  • 2
  • 17
  • 21
Shimi
  • 9
  • 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 https://bugs.documentfoundation.org/show_bug.cgi?id=113974 – Juha Untinen Nov 04 '20 at 09: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 04 '20 at 09:35
  • this solution no longer works. "No-key" api access has been deprecated. Requests generate error: { "success":"false", "error":{ "code":101, "type":"missing_access_key", "info":"You have not supplied an API Access Key. [Required format: access_key=YOUR_ACCESS_KEY]" } } – Blindspots Mar 29 '23 at 19:13