Ask Your Question

Import current BTC and ETH values to Libreoffice Calc

asked 2017-12-03 11:02:53 +0200

dx486 gravatar image

updated 2017-12-03 11:48:36 +0200

I want to import current Bitcoin (BTC) and Ethereum (ETH) values into LibreOffice Calc. How can I do it, please?

My specific purpose is getting vaules from or any other exchange operating in TRY; however please feel free to answer this question if you have an answer for USD or EUR.

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted

answered 2020-04-09 15:56:28 +0200

HEET gravatar image

Thanks to the answers above, i was able to get the rates in a single cell by combining the code.


=NUMBERVALUE(MID(WEBSERVICE(""),SEARCH("""last"": """,WEBSERVICE(""))+9,SEARCH(""", ""timestamp""",WEBSERVICE(""),SEARCH("""last"": """,WEBSERVICE("")))-SEARCH("""last"": """,WEBSERVICE(""))-9),".")


=NUMBERVALUE(MID(WEBSERVICE(""),SEARCH("""last"": """,WEBSERVICE(""))+9,SEARCH(""", ""timestamp""",WEBSERVICE(""),SEARCH("""last"": """,WEBSERVICE("")))-SEARCH("""last"": """,WEBSERVICE(""))-9),".")


=NUMBERVALUE(MID(WEBSERVICE(""),SEARCH("""last"": """,WEBSERVICE(""))+9,SEARCH(""", ""timestamp""",WEBSERVICE(""),SEARCH("""last"": """,WEBSERVICE("")))-SEARCH("""last"": """,WEBSERVICE(""))-9),".")

For info about the supported currency pairs on Bitstamp, you can refer to the link here: API Documentation

edit flag offensive delete link more


That would repeat the web query 5 times per cell! Today, I'd re-write this using REGEX like this:

=NUMBERVALUE(REGEX(WEBSERVICE("");".*""last"": ""(\d+\.\d+)"".*";"$1");".")
Mike Kaganski gravatar imageMike Kaganski ( 2020-04-09 21:37:58 +0200 )edit

You are a star! Thank you.

I reused your formula and called Binance instead as I needed access to more coins. Sample below:

HEET gravatar imageHEET ( 2020-04-13 04:51:45 +0200 )edit

answered 2020-04-09 22:51:33 +0200

m.a.riosv gravatar image

updated 2020-04-09 22:52:13 +0200

With a bit of transformation data with WEBSERVICE() can be transformed on a xml string that can be readed by FILTERXML():
="<data>"&REGEX(REGEX(REGEX(REGEX(WEBSERVICE("");"\{(.*)\}";"$1");"""| ";"";"g");",";CHAR(10);"g");"(.*):(.*)";"<$1>$2</$1>";"g")&"</data>"
and with
extract data.

Maybe it can be used in other cases, with a little modification and more accuretly to avoid no desire substitutions. ReadJsonWithWebserviceFilterxml.ods

edit flag offensive delete link more

answered 2018-06-01 16:42:26 +0200

hjek gravatar image

updated 2018-06-08 15:00:24 +0200

You'll need to find some web API that provides the value of Bitcoin or Ethereum. Let's pick a random one: Bitstamp.

This is going to get really ugly, because their API sends a JSON response and Calc can't parse JSON. First in a cell (say A1) we retrieve the JSON from the API: =WEBSERVICE("")

Then w can try to extract the value of the last attribute from the JSON response by string processing: =VALUE(MID(A1;SEARCH("last";A1)+8;SEARCH("timestamp";A1)-40))

As I'm writing this, that gives the value 7426.02, which means the price of one Bitcoin is 7426.02$.

You should be able to easily convert this to Turkish lira.

Hope it helps.

EDIT: Fixed references to unexplained cells, and used semicolon to separate function args. Thanks @erAck

EDIT: Also, give the LOC extension a look.

edit flag offensive delete link more

answered 2018-06-01 17:44:11 +0200

erAck gravatar image

The answer given by @hjek doesn't work because it uses a reference to E9, which apparently should had been the actual WEBSERVICE() result. However, even if that was present then the formula with its hard coded -40 works only if the field value contains exactly 7 characters (i.e. in a 4.2 (4 digits integer and 2 digits decimals) format). Second, using the VALUE() function it works only in a locale that uses a . dot decimal separator. For a slightly more independent variant use

A2: =SEARCH("""last"": """;A1)
A3: =SEARCH(""", ""timestamp""";A1;A2)
A4: =MID(A1;A2+9;A3-A2-9)

Note that it includes double quotes, colon and comma in the search of "last": " and ", "timestamp" to ensure an actual field name is found, ensures timestamp follows last and calculates the length of the MID string from those offsets instead of hardcoding it. Using NUMBERVALUE() with a dot decimal separator makes the conversion independent from the current locale.

One could further process the extracted string value in A4 to check with SEARCH() and a regular expression (if regular expressions are enabled for formulas under Tools -> Options -> Calc -> Calculate) whether it is exactly one numeric value as expected, i.e. using =SEARCH("^\d+(\.\d+)?$";A4;1) that returns 1 if the expression matches (text is number and only one number) and #VALUE! error otherwise. Which might be important in case the web site API decides to shuffle their fields in a different order but timestamp still follows last just not immediately..

@hjek, please when giving examples use the ; semicolon parameter separator that works in every locale regardless of the current settings, and make sure the example actually works or explain the prerequisites, as here with E9, which then shouldn't had the WEBSERVICE() call included in the formula given because calling it twice for the same URL is a waste of resources. Thanks.

edit flag offensive delete link more


Thanks for the corrections!

I still had some references to the E9 cell where I'd been storing the web result. Will correct my asnwer. Didn't know about ;. Also, way better use of the SEARCH() function here. Definitely better not to hardcode the string offsets, too.

hjek gravatar imagehjek ( 2018-06-01 18:08:47 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2017-12-03 11:02:53 +0200

Seen: 1,436 times

Last updated: Apr 09