LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 03-16-2019, 11:49 AM   #1
GPGAgent
Member
 
Registered: Oct 2018
Posts: 213

Rep: Reputation: Disabled
Question LibreOffice Calc -Stock market (equities) price updates


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.
 
Old 03-16-2019, 07:50 PM   #2
Beryllos
Member
 
Registered: Apr 2013
Location: Massachusetts
Distribution: Debian
Posts: 524

Rep: Reputation: 316Reputation: 316Reputation: 316Reputation: 316
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.
 
Old 03-16-2019, 08:11 PM   #3
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.7.1908
Posts: 4,248

Rep: Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483
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.
 
Old 03-16-2019, 08:14 PM   #4
xl
Member
 
Registered: Feb 2013
Location: Germany, EU
Distribution: Debian (stable release)
Posts: 67

Rep: Reputation: 10
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.

Last edited by xl; 03-16-2019 at 08:28 PM.
 
1 members found this post helpful.
Old 03-17-2019, 11:37 AM   #5
GPGAgent
Member
 
Registered: Oct 2018
Posts: 213

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by xl View Post
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
 
Old 03-17-2019, 12:21 PM   #6
GPGAgent
Member
 
Registered: Oct 2018
Posts: 213

Original Poster
Rep: Reputation: Disabled
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
 
Old 03-17-2019, 01:00 PM   #7
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.7.1908
Posts: 4,248

Rep: Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483Reputation: 1483
This, perhaps? https://help.libreoffice.org/Calc/WEBSERVICE
 
Old 03-17-2019, 01:26 PM   #8
xl
Member
 
Registered: Feb 2013
Location: Germany, EU
Distribution: Debian (stable release)
Posts: 67

Rep: Reputation: 10
Quote:
Originally Posted by GPGAgent View Post
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
 
Old 03-17-2019, 01:35 PM   #9
GPGAgent
Member
 
Registered: Oct 2018
Posts: 213

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by xl View Post
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!
 
Old 06-09-2019, 08:25 AM   #10
olin000
LQ Newbie
 
Registered: Jun 2019
Posts: 2

Rep: Reputation: Disabled
You could use Alpha Vantage API and PARSEJSON function from LibreOffice GetRest Plugin to get a stock quote.
 
Old 06-09-2019, 08:25 AM   #11
olin000
LQ Newbie
 
Registered: Jun 2019
Posts: 2

Rep: Reputation: Disabled
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.
 
  


Reply

Tags
calc, libreoffice


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Command Line Stock price Program/Script wanted dmchess Linux - Software 9 12-10-2017 01:38 PM
CLI command to pull Stock Price and volume from Google dmchess Linux - Desktop 15 12-10-2017 11:14 AM
Stock market "find the best price model" program cigarstub Programming 1 01-07-2007 09:24 AM
Processor Upgrade (Pentium M) VS. Price: Which has the best price/tech ratio? hanzj Linux - Laptop and Netbook 8 03-22-2005 07:18 PM
Upgrading stock 2.4.22 kernel to 2.5.x then to 2.6.6 with existing stock .config file Kyl3 Slackware 8 06-09-2004 06:34 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 11:03 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration