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 <spam-link removed> 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: removed spam-link)
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("<possible-spam-anonymised>")
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: anonymised spam-link (second time) -AK)
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("spam-link removed")
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.
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.
Thanks to the answers above, i was able to get the rates in a single cell by combining the code.
For BTCUSD:
=NUMBERVALUE(MID(WEBSERVICE("spam-link removed"),SEARCH("""last"": """,WEBSERVICE("spam-link removed"))+9,SEARCH(""", ""timestamp""",WEBSERVICE("spam-link removed"),SEARCH("""last"": """,WEBSERVICE("spam-link removed")))-SEARCH("""last"": """,WEBSERVICE("spam-link removed"))-9),".")
For ETHUSD:
=NUMBERVALUE(MID(WEBSERVICE("spam-link removed"),SEARCH("""last"": """,WEBSERVICE("spam-link removed"))+9,SEARCH(""", ""timestamp""",WEBSERVICE("spam-link removed"),SEARCH("""last"": """,WEBSERVICE("spam-link removed")))-SEARCH("""last"": """,WEBSERVICE("spam-link removed"))-9),".")
For ETHBTC:
=NUMBERVALUE(MID(WEBSERVICE("spam-link removed"),SEARCH("""last"": """,WEBSERVICE("spam-link removed"))+9,SEARCH(""", ""timestamp""",WEBSERVICE("spam-link removed"),SEARCH("""last"": """,WEBSERVICE("spam-link removed")))-SEARCH("""last"": """,WEBSERVICE("spam-link removed"))-9),".")
For info about the supported currency pairs on Bitstamp, you can refer to the link here: <spam-link removed>
(edit: removed numerous spam-links)
That would repeat the web query 5 times per cell! Today, I’d re-write this using REGEX like this:
=NUMBERVALUE(REGEX(WEBSERVICE("spam-link removed");".*""last"": ""(\d+\.\d+)"".*";"$1");".")You are a star! Thank you.
I reused your formula and called Binance instead as I needed access to more coins. Sample below:
=NUMBERVALUE(REGEX(WEBSERVICE("spam-link removed"),".*""price"":""(\d+\.\d+)"".*","$1"),".")Can the answer be updated for the shorter regex version? How do you get the ERR:540 to go away?
With a bit of transformation data with WEBSERVICE() can be transformed on a xml string that can be readed by FILTERXML():
="<data>"®EX(REGEX(REGEX(REGEX(WEBSERVICE("spam-link removed");"\{(.*)\}";"$1");"""| ";"";"g");",";CHAR(10);"g");"(.*):(.*)";"<$1>$2</$1>";"g")&"</data>"
and with
=FILTERXML(A$2;"//data/high")
extract data.
Maybe it can be used in other cases, with a little modification and more accuretly to avoid no desire substitutions. ReadJsonWithWebserviceFilterxml.ods