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. |
Quote:
I've seen this in my searches: Code:
=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quotes?s="&U4&"&f=l1"),".") 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: Code:
precision 2 I've tried webservice: Code:
=webservice"https://www.bloomberg.com/markets/chart/data/1D/ulvr:ln" Code:
err:509 Code:
=WEBSERVICE("https://www.bloomberg.com/markets/chart/data/1d/ulvr:ln") 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
|
Quote:
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 |
Quote:
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. |
Minor improvement
Just find a way to do the thing without pluging, besides the poor performance, it may be enough for an user use case.
Code:
=NUMBERVALUE( |
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. |
Quote:
|
All times are GMT -5. The time now is 06:59 PM. |