I am creating an investment sheet for my cryptocurrency and, since all value is tied to Bitcoin price, need a cell that will change to the current price.
You can use WEBSERVICE command.
=WEBSERVICE(“spam-link removed”)&T(TODAY())
The T(TODAY() command is there just to be able to update data by pressing F9
I have attached a sample file with complete parsing of the json string. Prices can be updated by pressing F9 or a pushbutton.
EDIT: I have updated the file to work with any locale (decimal separator).
1 Like
Tried sample but nothing happens! Nothing is retrieved using either the button or F9.
Edit: Should note, did this on Ubuntu 18.04 Mate using LO v 6.2.0.3
Using a language with comma as decimal separator, need to be substitute the dot with a comma before numeric conversion.
Ratslinger what is the date you see on the first sheet upper left corner. It should show last update UTC time, if it is more than 5 mins old the data is not updated. Maybe you have firewall blocking?
EDIT: I uploaded the ods file again, the button was in design mode and was not working.
m.a.riosv Yes that is a problem. I have decimal sign set as period.
@tarmog First had to move pushbutton as it was in upper left corner of first sheet. There is no date at all there (cell A1) and using button or F9 does nothing. All cells which should show data on both sheets show #VALUE!. And no, firewall is not an issue.
After saving then re-opening the file, all cells now show Err:540. Don’t have a definition on that code.
Edit: Have now also tried different OS (Mint 18.3 Cinnamon), different LO version (v6.2.4.2 from TDF), different ISP, and Tools->Options->LibreOffice Calc->General Updating from On request to Always. All results are the same.
@Ratslinger you are right, this is probably the issue. The issue also seems to apply to linux mostly. I have Libre on windows 10 and older libre (5.2.4.2). You may want to start debugging from the beginning if you already haven’t and test the webservice command. In my file it is located in second sheet (“Calc”) B22 cell.
Additionally, the refresh date is located in B1 cell not A1.
Sorry, not my project. Just thought I’d point out it doesn’t work; at least not in Linux.
The cell content in Calc.C5:L16 is not numeric, hence the #VALUE! errors on the first sheet. Whether that works or not depends on the setting how strings are converted to numbers. Better practice is to explicitly convert each value using NUMBERVALUE(), for example in cell Calc.C5
=NUMBERVALUE(MID(C$4;FIND(":";C$4;FIND($A5;C$4))+3;(FIND(CHAR(10);C$4;FIND($A5;C$4))-3)-(FIND(":";C$4;FIND($A5;C$4))+3));".")
and then copy that cell to clipboard and paste onto C5:L16
Btw, the IF(TODAY(),"") can be rewritten as T(TODAY())
After saving then re-opening the file, all cells now show Err:540. Don’t have a definition on that code.
See status bar on such cell. External content disabled. Click Enable Content in the Automatic update of external links has been disabled info bar on top of the document.
@erAck You may notice in my comments that even with that resolved, webservice is not working for me - Linux.
Shrug, it works for me, also Linux. LO 6.2.4 and 6.1.6
@erAck My error is same as bug report mentioned above. Tried with different webservice URL and had no problem - so far only with the one used in answer.
@tarmog this is amazing can you please explain how to add more coins and get the great logo etc ? When i try to copy the cells it is unclear to me how to change or add any new coins to get same effect? it just says calc.b3 etc
The source site has table available,
if yes, use Sheet / Link to External Data… refresh every 60 seconds.
With the data in the worksheet, work on another worksheet by looking at the linked data.