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.

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.

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.

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.

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:

=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

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

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:

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

and get an error

err:509

This

=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

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

Originally Posted by GPGAgent I've seen this in my searches: =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

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!

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

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.

Just find a way to do the thing without pluging, besides the poor performance, it may be enough for an user use case.

=NUMBERVALUE(
  REGEX(
    REGEX(
      SUBSTITUTE(
        WEBSERVICE(
          "https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol="& <<YOUR TICKET>>  &"&apikey="& << YOUR API KEY >>
        ),
        $B$2,
        "*"
      ),
      ".*price"": """,
      "",
      "g"
    ),
    """,.*",
    "",
    "g"
  ),
  "."
)

Ghe $B$2 is a cell with a new line "\n" I could not find how to represent that character inside a string.

I have a Python extension for LibreOffice Calc that may do what you need: https://github.com/cmallwitz/Financials-Extension

This may be a piece of useless information considering that Yahoo appears not to be available. I was unable to figure out how the code worked. The only real reason for posting the link below is that others may find elements of the code useful

Bash script for Yahoo Finance historical data.

I've been curious concerning how to download stock prices. Since I am able to download stock prices with KMyMoney, I have not put much effort into developing a bash script to download stock prices.

Originally Posted by Steve R. This may be a piece of useless information considering that Yahoo appears not to be available. I was unable to figure out how the code worked. The only real reason for posting the link below is that others may find elements of the code useful Bash script for Yahoo Finance historical data. I've been curious concerning how to download stock prices. Since I am able to download stock prices with KMyMoney, I have not put much effort into developing a bash script to download stock prices.

Not sure what your question is - do you have a specific question about the Python code or the accompanying README? Have you tried the extension from Calc?