LibreOffice Calc -Stock market (equities) price updates

GPGAgent · 03-16-2019, 11:49 AM

Hi,

I'm looking for a method to update a portfolio of stocks (equities) I track in a LibreOffice Calc spreadsheet automatically, somewhat like the Googlefinance functions you can use on a Google Docs spreadsheet in Chrome, e.g. =GoogleFinance("LON.BP.","price") would return the value of a share in BP on the London stock exchange.

Beryllos · 03-16-2019, 07:50 PM

I suppose you could use wget or curl to grab the data and your own script to put it into a csv file which can be opened in Calc. A nice little programming project.

scasey · 03-16-2019, 08:11 PM

Excel has/had a "remote data" option which would pull data from a URL. Does Calc have the same ability?
Help menu can be your friend.

äxl · 03-16-2019, 08:14 PM

LibreOffice has =webservice (and =importxml) to call from JSON files. You will need links but I only know links to grab BTC exchange courses.

Another alternative is to "insert sheet" from online or from a file and then you can choose an online link. From that sheet you can export data to your original sheet.

GPGAgent · 03-17-2019, 11:37 AM

Quote:

Originally Posted by äxl

LibreOffice has =webservice (and =importxml) to call from JSON files. You will need links but I only know links to grab BTC exchange courses.

Another alternative is to "insert sheet" from online or from a file and then you can choose an online link. From that sheet you can export data to your original sheet.

I've seen this in my searches:

Code:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes?s="&U4&"&f=l1"),".")

where U4 cell contains LON:RDSB = i.e. Royal Dutch Sheel (B) shares on the London Stock exchange
But no luck getting it to work.

curl and wget look possible, need to see how they work first.
Cheers folks

GPGAgent · 03-17-2019, 12:21 PM

Okay folks I've found this:
This page:
https://www.bloomberg.com/markets/chart/data/1D/ulvr:ln
returns this:

Code:

precision	2
prev_close	4193
data_values

The data I want in a cell is on line 2 second item: 4193

I've tried webservice:

Code:

=webservice"https://www.bloomberg.com/markets/chart/data/1D/ulvr:ln"

and get an error

Code:

err:509

This

Code:

=WEBSERVICE("https://www.bloomberg.com/markets/chart/data/1d/ulvr:ln")

doesn't error, but doesn't return anything.

It's getting close I think but I need help on the syntax, google hasn't found anything useful so far

Cheers

scasey · 03-17-2019, 01:00 PM

This, perhaps? https://help.libreoffice.org/Calc/WEBSERVICE

äxl · 03-17-2019, 01:26 PM

Quote:

Originally Posted by GPGAgent

I've seen this in my searches:

Code:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes?s="&U4&"&f=l1"),".")

where U4 cell contains LON:RDSB = i.e. Royal Dutch Sheel (B) shares on the London Stock exchange
But no luck getting it to work.

curl and wget look possible, need to see how they work first.
Cheers folks

My search engine says that download.finance.yahoo.com is down since Jan. 2018.
Maybe use https://finance.yahoo.com/quote/RDS-B?p=RDS-B ?

My VPN is blocked from Bloomeberg so can't test right now.
If curl/wget don't work maybe also try this:
https://help.libreoffice.org/Calc/In...Table_WebQuery

GPGAgent · 03-17-2019, 01:35 PM

Quote:

Originally Posted by äxl

My search engine says that download.finance.yahoo.com is down since Jan. 2018.
Maybe use https://finance.yahoo.com/quote/RDS-B?p=RDS-B ?

My VPN is blocked from Bloomeberg so can't test right now.
If curl/wget don't work maybe also try this:
https://help.libreoffice.org/Calc/In...Table_WebQuery

Yep that's right about Yahoo, I knew that.

Bloomberg works fine for me, I can see the share price, I just need to get it into a cell!

olin000 · 06-09-2019, 08:25 AM

You could use Alpha Vantage API and PARSEJSON function from LibreOffice GetRest Plugin to get a stock quote.

olin000 · 06-09-2019, 08:25 AM

The below method could work for you. Just verified.
(Seemingly free) registration at https://www.alphavantage.co/ is required, but you can test a demo quote of MSFT without it.

To get the last stock price you can use the link:
https://www.alphavantage.co/query?fu...FT&apikey=demo

Some other data series (like historical price quotes) are available as well. Refer to the documentation https://www.alphavantage.co/documentation/.

For parsing the JSON output you can use LibreOffice GetRest Plugin from https://extensions.libreoffice.org/e...getrest-plugin (https://github.com/DmytroBazunov/Lib...eGetRestPlugin). The direct download link is http://dl.bintray.com/binnarywolf/Li...RestPlugin.oxt.

For me the dot (".") in the node name e.g. "05. price" was causing an error, so I replaced it with a simple substitute.

At the end the final formula looks like this:
=+PARSEJSON(SUBSTITUTE(PARSEJSON(WEBSERVICE("https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=MSFT&apikey=demo"),"Global Quote"),"05. price","05 price"),"05 price")

Feel free to suggest a more elegant way, but the above should work.

BTW when testing Bloomberg (e.g. https://www.bloomberg.com/markets/ap...rice/AAPL%3AUS) it seems to work when accessed from a browser, but when the link is queried with LibreOffice WEBSERVICE then the "Are you a robot?"/CAPTCHA protection is being activated and displayed in the cell content as a result.

Cheers.