Ask Your Question
0

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 btcturk.com 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

2 Answers

Sort by » oldest newest most voted
0

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("https://www.bitstamp.net/api/ticker/")

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
0

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

A1: =WEBSERVICE("https://www.bitstamp.net/api/ticker/")
A2: =SEARCH("""last"": """;A1)
A3: =SEARCH(""", ""timestamp""";A1;A2)
A4: =MID(A1;A2+9;A3-A2-9)
A5: =NUMBERVALUE(A4;".")

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

Comments

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

2 followers

Stats

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

Seen: 1,014 times

Last updated: Jun 08 '18