Hello: new to Linux, and even newer to Libre Office. Looking to add live data into a spreadsheet. I’ve seen a few posts from years ago, but they aren’t sufficiently helpful (either not explained clearly enough, or with dead links). Could someone give a hand?
Thanks!
Welcome NewUser/NewSUDOer:)
do you mean to add links to external data in calc? (via: Menu>Sheet>External Links…)
there is also Menu>View>Datasources to link tables from (registered)LibreBase .odb file, if you are using that aswell.
So more specifically, I want to set up a spreadsheet with daily prices of both national currencies and cryptocoins. From what I have found and read, it seems an extension is necessary, but I had no luck figuring our how to install the extensions.
MainMenu>Tools>Extension Manager …
OK, that takes me to the general Extensions page. I’ve gotten there already.
But when I search for and find getrest, and then log in, I’m not taken to the getrest extension, but here:
And this site seems a place for devs to add their new extensions. I feel foolish, going in circles with these pages.
Thanks.
This is not at all in my field of interest, but…
And have no clue for what reason somebody should need such a tool.
How should an extension for such a purpose work reliably without getting granted itself long-term reliable sources?
To display (and also to evaluate in one or another way) data openly supplied as html-tables or (much better) in CSV style via a known URL isn’t a real problem. Everybody can start with it using established features of LibO.
How to find such sources, and to guess which ones will be thoroughly updated, maintained, offered also next year and the year after, …that is the question.
I don’t think that the author of an extension could somehow take responsibility here.
OTOH such an author will not be able to create and maintain the needed data source himself.
Again: Where from should he(f/m) get “quality data” for free?
The ECB and other internationally meaningful banks may provide data. The ECB offers time sequences (youngest on top) in a single csv since 1999-04-01 (3 months after the official creation of the EUR; long before there were notes or coins in EUR) for 41 currencies, e.g.
Who should do this for “crypto”?
So I figured out how to connect to an API. But now the problem is I can’t use the output (BTC Price in USD) in a further function, e.g., multiplying it by amount of BTC. Now it seems I need a way of pulling the numerical value out into #NAME?. A hoped “=query” might work (that’s what’s currently there, producing the error), but that’s not working.
As to purpose: A friend asked me to create a spreadsheet for him to track his investment. I didn’t think it would be that difficult…
What do you mean by this? What do you “connect” actually to what - and by what means? Discard such unclear statements.
Spreadsheets have a well-defined set of functions. They are not chatter-pals eager to guess what you might have meant.
Trash screenshots in such cases. Attach your example. That should be as short, reduced, and simple as possible regarding the purpose. It may contain some additional remarks concerning the relevant cells/ranges/rows/columns/formulas/…
Anyway you won’t get anywhere with spreadsheets if you don’t study any tutorial or examples by experienced users in advance. A Q&A site can not provide a specialized tutorial for every newcomer.
It seems you pulled a json-value in xour cell. Now you have to extract it with internal functions of Calc.
Text-functions or regular expressions are possible.
.
Please upload your sheet, or a part of your sheet, wich contains the used formula. On your picture nobody can see wich formula you tried to correct it. (You didn’t really wrote only =query ?
Yes, exactly, I need to extract that JSON-value. I have read through all the functions, and hunted diligently for things to watch and/or read about this. Not looking to lazily skip the research, as Lupp (utterly condescendingly) suggests. I am new to LibreOffice, to be sure. But I am trying to learn. Leaving Windows for Linux is not an easy leap–there is an overwhelming large amount to learn, and much of it must be learned almost immediately to do one’s work.
Here is what I am trying to create:
I need to extract the JSON price to the Current Price cell, so that I can multiply it by “Current Holdings” to output “Total Value”.
To successfully get the bitcoin price, I used:
=GETREST(“https://api.coingecko.com/api/v3/simple/price?ids=BITCOIN&vs_currencies=USD&include_market_cap=false&include_24hr_vol=false&include_24hr_change=false&include_last_updated_at=false&precision=2”)
The (incorrect) formula I used to try to pull the numerical value was: =query(C3, “select USD.price”)
Appreciatively
I guess @mariosv has fully answered your question.
.
I have found out =query() is available in google-docs. As LibreOffice has a full database this additional command is not necessary. In this case it would not help, as it is a sql-like query for tabular data in a sheet, not designed to extract values from json.
.
As you didn’t upload your file. Please try to reproduce the solution of @mariosv without using his file. Then you might get an idea, why we often ask for odc-files instead of screenshots…
So I spoke too soon, in thinking (aloud) that I might be able to understand @mariosv’s solutions. It seems the formulae for both getting the coin prices and extracting those prices into other cells are currently beyond my ken. For instance, I cannot fathom the logical meaning of the extraction formula: =REGEX(E2,"^.:(.)}}","$1"). No idea either what is going on there, or how would would even learn what is going on there.
I’m sorry I didn’t understand the significance of uploading the actual file. I had already inputted data from my friend, and didn’t want to upload that, so quickly did the screenshot. With future issues, I’ll just copy the file, remove and sensitive data, and upload.
I look forward to learning more about Libre Office over the next many years.
Thank you all, again!
REGEX help
https://help.libreoffice.org/latest/en-US/text/scalc/01/func_regex.html?DbPAR=CALC#bm_id831542233029549
and regular expressions
https://help.libreoffice.org/latest/en-US/text/shared/01/02100001.html?&DbPAR=CALC&System=WIN
^.*: searches the text from the beginning up to the semicolon
(.*) create a group with text between the semicolon and the first close of brackets.
\}\} searches for the two close of brackets. (\ is an escape to considered the brackets as text)
$1 returns the text of the first group in search expression.
In this case, as even returning a JSON it has only one value, so seems easy to extract it, without extensions or macros.
Sample file
Webservice_Bitcoin_Currency_values.ods (22.8 KB)
2 Likes
Thank you SOO much for this. I will study it and try to understand, not just copy/paste.
That this is “easy” for you is amazing. And you improved by allowing me to just type in the name of each coin, rather than hunt down each API individually! Again, thank you!!