Importing BTC price to spreadsheet

SkyHead · October 8, 2018, 6:18pm

Hi, I’m trying to have a live(60sec) updated BTC price on my spreadsheet, I have googled abit but still cant find out what to do, I came over this thread
Import current BTC and ETH values to Libreoffice Calc

But I either get #VALUE or all the values from (my remote source) in text format like they are displayed in the API.
I want them to show as numbers i.e. A1: BTC\USD Current B2: The current price of BTC

Please take time to explain in great detail as I only installed it yesterday

Thank you,
SkyHead

(Edit: anonymised possible spam -AK)

Ratslinger · October 8, 2018, 6:37pm

Hello,

This type of question has been asked a number of times. Explanation in detail is not typical as this deals with creating macros specific to your needs and best done in Python. For further information, please see my answer in this post → link libre base to API on a website,. In the answer are links to other answers some containing code.

Edit 2018-10-09:

Have carried this one step further. Attached is a sample Calc file with the python macro embedded in the document. It utilizes the API in your question. It is executed by a push button on the sheet. This recalls the data and the results are placed in column B. I did not format any results. In some cases I don’t even know or care what they are.

Sample — BTCpython.ods

The python code is overly simplified so as to be somewhat readable for those unknowing. It accesses the data, converts it to a string which is then read in JSON format into data fields. The active sheet in Calc is used and one-by-one the data is moved into cells.

The actual Python code is just a text file named JsonBTC3.py. The macro the push button is connected to is json_btc. It was inserted into the document using APSO; see this post → Did you know that you could also use APSO to Organize your Python scripts within LibreOffice?. I have created a small manual for embedding the script in a document using APSO; it is in my answer here → Can I embed python script and integrate with basic? (solved)

Going beyond this creates too many ifs ands and where by’s.

SkyHead · October 9, 2018, 4:58pm

I have made the python.py script
Tried “python pyhton.py CoinCalc.ods” and get returned
Bitcoin
6613.76231894

How do I load that macro into LO? Should it be *.py extension when I load it?
I use manjaro linux\mate DE

This is the code I stole from you, as I have no python skills.

import json

from urllib.request import urlopen

url = urlopen(’<remote-site>’)

obj = json.load(url)

if int(obj[0][‘rank’]) == 1:

coin_name = obj[0][‘name’]

SkyHead · October 10, 2018, 4:11pm

Thank you for this

Ratslinger · October 10, 2018, 4:15pm

As you have been helped, please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.

SkyHead · October 9, 2018, 4:55pm

I have made the python.py script
Tried “python pyhton.py CoinCalc.ods” and get returned
Bitcoin
6613.76231894

How do I load that macro into LO? Should it be *.py extension when I load it?
I use manjaro linux\mate DE

This is the code I stole from you, as I have no python skills.

import json

from urllib.request import urlopen

url = urlopen(’<remote-site>’)

obj = json.load(url)

if int(obj[0][‘rank’]) == 1:

coin_name = obj[0][‘name’]

coin_price = obj[0][‘price_usd’]

print(coin_name)

print(coin_price)

Ratslinger · October 9, 2018, 5:34pm

Please do not respond using an answer. Either add a comment or if more room is needed edit original question and add there noting the edited information.

Since you have no Python skills, how are you going to maintain this when the API changes? There is much more to this than what you may picture. Once the info is obtained, code is needed to put in the spreadsheet. Also more code is needed for you to automatically retrieve the info on a repeated basis automatically. Much more to consider.

SkyHead · October 9, 2018, 5:59pm

There is no easier way?

Ratslinger · October 9, 2018, 6:02pm

It is the easiest way I know currently.

Bruno495 · May 28, 2022, 11:27am

I was looking for this too. I found a plugin on github.
https://github.com/prikhi/libreoffice-binance-api : it inports the data from the public Binance API.

Install:

Download the latest plugin from the Releases page. Make sure you grab the BinanceApi.oxt and not the source code downloads.

In LibreOffice, go to Tools -> Extension Manager -> Add and select the BinanceApi.oxt file you downloaded. Restart LibreOffice when prompted.

Usage

Simply pass your trading pair to the BINANCEPRICE function:

=BINANCEPRICE("ETHBUSD")
or
=BINANCEPRICE("BTCBUSD")
or ... (you can of course only see the result of pairs they offer)