[Solved] Stock Market Functions LO Calc Extension

Discuss the spreadsheet application
mplaut
Posts: 27
Joined: Thu May 06, 2010 12:41 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by mplaut »

I apologize for the lack of info.

Instead of a value, it says: unknown url type: https

Forgive my ignorance, but I do not know the connection, if any, between the code you entered using Webservice, and the SMF extensions.
M Plaut
Openoffice 4.1.4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Villeroy »

The URL I have tested uses the unencrypted http: protocol. It works with https as well.
WEBSERVICE is the function which has been built into LibreOffice. It returns a string, a numeric string in this partiular case.
NUMBERVALUE is another LO function which converts a numeric string into a number. The optional second argument is either "," or "." depending on the decimal separtor which is used in the string. An optional third argument specifies the thousands separator.
I would not use the SMF extension but you can try with http: instead of https:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Lupp »

(Just to avoid misunderstandings:)

LibO still accepts the semicolon (";") as the parameter delimiter in any locale. Only in "point locales", if not told otherwise, it replaces the semicolon with the comma (",") after a formula was recognized and only for the display.

Since the "localistaion" concerning such a fundamental syntactical element that not was in conflict with any decimal separator, was a specifically bad idea, I would encourage every user of LibO to go:
> 'Tools' > 'Options' > 'LibreOffice Calc' > 'Formula' > 'Separators' > 'Function:' (actually 'Parameters' meant) > Enter the semicolon there.

The forums on free office software in English language are international. Many contributors there are not native speakers of English, and a majority of them, I suppose, live in "comma regions" concerning the decimal separator. OK. The comma/point schism is an annoying historical fact we cannot change now.

The additional comma/semicolon alternative, however, is without any need already an annoying difficulty for contributors in the "ask.libreoffice.org/en" site as far as formulas are discussed. It's also an additional problem for the many users with little insight.

Down with silly "localisations". The semicolon is distinct in any locale, the comma conflicts with the decimal separator in a majority of locales.
By the way: In the persistent representation of Calc documents (files) always the semicolon is used to delimit parameters - and always the point as the decimal separator. ('OpenFormula' specification, subchapters 5.6 and 5.3 rspectively.)
Even MS know that.
Last edited by Lupp on Thu Nov 02, 2017 4:31 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mplaut
Posts: 27
Joined: Thu May 06, 2010 12:41 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by mplaut »

This is what I have entered:
=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... L31&"&f=l1") , ".")
where L31 has a stock symbol.
The result is: #VALUE!
(Maybe this is a Yahoo problem because when I entered the URL in my browser it also did not work.)

This is in LibreOffice 5.3.3.2
M Plaut
Openoffice 4.1.4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Villeroy »

mplaut wrote:(Maybe this is a Yahoo problem because when I entered the URL in my browser it also did not work.)
"Did not work" is meaningless.

The error message tells us everything:
download.finance.yahoo.com wrote:Sorry, Unable to process request at this time -- error 999.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mplaut
Posts: 27
Joined: Thu May 06, 2010 12:41 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by mplaut »

What does "error 999" tell you?
M Plaut
Openoffice 4.1.4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Villeroy »

Since 999 is not a http error, you may ask the yahoo admins and they will tell you that it means "Sorry, Unable to process request at this time".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Lupp »

On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mplaut
Posts: 27
Joined: Thu May 06, 2010 12:41 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by mplaut »

Now I got the following, more detailed reply:

"It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com."

I do not know if that means that the functionality of download.finance has been moved to finance, or if it no longer exists.
M Plaut
Openoffice 4.1.4 on Windows 10
Shojin
Posts: 6
Joined: Fri Nov 03, 2017 1:02 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Shojin »

Yahoo have killed the API it seems. I'm trying to figure out how to replace the links with an alternative from alphavantage, but can't get it to work just yet.
Last edited by FJCC on Fri Nov 03, 2017 1:10 am, edited 1 time in total.
Reason: removed the live link
LibreOffice 5.3.6.1
saanichtonian
Posts: 4
Joined: Tue Jun 06, 2017 3:01 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by saanichtonian »

For what it's worth, and I'll believe it when I see it...

---
https://forums.yahoo.net/t5/Yahoo-Finan ... 03/page/25

"Nixon Administratin' 'Hoo Nixon
Administratin' 'Hoo
‎11-02-2017 08:20 AM
Re: Is Yahoo! Finance API broken?

Hi All - I'm blocking new replies to this thread since it refers to an older outage.

The new download issue which began 11/01/17 and returning an error 999 for most users is currently being investigated and we hope to have it resolved soon. "


---
I hope he's right and the API will be back. I'm not holding my breath.

In the meantime, I have made a copy of my portfolio spreadsheet and am using the 'quotes.csv' downloaded direct from the portfolio on yahoo finance and the instructions for Libreoffice Calc at http://blog.louic.nl/?p=622.

It took me about half an hour to get it all in place using an added sheet to my spreadsheet for the linked csv file and using an update button (or Edit>Links>quotes.csv>update) to update when I download the newest csv file.

There will have to be some futzing around when your yahoo portfolio changes, and it's not as nice and easy as the API, but it's working.


For a discussion on alternate APIs - https://news.ycombinator.com/item?id=15616880
Running xubuntu 14.04
with LibreOffice Version: 4.2.8.2
Shojin
Posts: 6
Joined: Fri Nov 03, 2017 1:02 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Shojin »

I got it to work with Alpha Vantage. It's not quite as simple as with Yahoo as it downloads a csv and then you have to isolate the number you need in Calc, but it works. For all examples replace "demo" with your API key (free, but you need to signup at alphavantage.co)

For latest stock price (SPX in this example)

Code: Select all

=WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=SPX&interval=1min&apikey=demo&datatype=csv")
That looks up the csv. Then isolate the most recent close price with this, presuming the above is in cell A1

Code: Select all

=VALUE(MID(A1,FIND(CHAR(160),SUBSTITUTE(A1,",",CHAR(160),9))+1,FIND(CHAR(160),SUBSTITUTE(A1,",",CHAR(160),10))-FIND(CHAR(160),SUBSTITUTE(A1,",",CHAR(160),9))-1))
This is calling the csv and then isolating the most recent close price, between the 9th and 10th commas. (Thanks to u/BREXIT-NINE-ELEVEN on Reddit for that).

For forex it's different

Code: Select all

=WEBSERVICE("https://www.alphavantage.co/query?function=CURRENCY_EXCHANGE_RATE&from_currency=EUR&to_currency=USD&apikey=demo")
Then isolate the price as before

Code: Select all

=NUMBERVALUE(MID(A1,FIND("5. E",A1),+20,10),".")
This one isolates the price by searching for the entry after "5. E" which is the "5. Exchange Rate" line.

Edited to reduce lookups.
Last edited by Shojin on Sat Dec 30, 2017 3:22 am, edited 6 times in total.
LibreOffice 5.3.6.1
saanichtonian
Posts: 4
Joined: Tue Jun 06, 2017 3:01 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by saanichtonian »

Hi Shojin

Just out of curiosity, and assuming that the CSV file you get is just the stocks you follow, did you try opening it directly in a libreoffice spreadsheet?

As I am a holder of Canadian stocks, could you tell me if Alpha Vantage gives quotes for them?

Do they also give quotes for commodities?

thanks
Running xubuntu 14.04
with LibreOffice Version: 4.2.8.2
Shojin
Posts: 6
Joined: Fri Nov 03, 2017 1:02 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Shojin »

The stock one will download a csv if you go to it directly, which can then be opened in a spreadsheet. Not sure if it works for Canadian stocks - give it a go. Details for all the data they provide is on the site here.
LibreOffice 5.3.6.1
Shojin
Posts: 6
Joined: Fri Nov 03, 2017 1:02 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Shojin »

Looks like Alpha Vantage is getting slammed as everyone switches over - pretty slow response times at the moment.
LibreOffice 5.3.6.1
mplaut
Posts: 27
Joined: Thu May 06, 2010 12:41 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by mplaut »

I found another way but I have not yet managed to get it to work.

You can make a spreadsheet in Google docs using the googlefinance function to get information. Then you can publish that spreadsheet on the web to make it accessible with the Webservice function. On the other hand, maybe you can just use the Google spreadsheet to do what you want to do.
Here is a sample of the spreadsheet. The second page of the spreadsheet has Help.
https://docs.google.com/spreadsheets/d/ ... sp=sharing
M Plaut
Openoffice 4.1.4 on Windows 10
Minnie5435
Posts: 1
Joined: Wed Nov 08, 2017 9:33 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Minnie5435 »

crusader wrote:For some time, I have been using LibreOffice Stock Market Function Extension 7.0 satisfactorily to access stock market values, specifically stock prices; however, several days ago, this function stopped working - and I am not the only one who experienced this.

My research shows:
  • Financial websites, particularly Yahoo Finance, frequently make technical changes (purposefully?) that render extensions like Stock Market Function useless.
    Stock Market Function Extension 7.0 is designed to pull data from Morningstar also; unfortunately, the most important parameter, the stock price, is not part of the elements that can be pulled from Morningstar.
    Google has developed a function; however, it works with Google docs only.
    There are other half baked options that are not worth anyone's time; the options that are robust are mostly tied to Yahoo Finance and have also been rendered useless by Yahoo Finance's most recent upheaval.
This site articulates the issue well.

Questions:
  • Are there any robust extensions/macros out there that can be used with LibreOffice Calc - especially those not tied to Yahoo Finance?
    Is there a way to import stock quotes from Google docs (to LO Calc) without having to go to Google docs and do a copy and paste/download the file each time?
    Bottom line: are there any suggestions on how to deal with the issue of looking up stock prices in LO Calc without experiencing frequent disruptions (the ups and downs of the stock market are exciting enough)?
I recommend you use MarketXLS as an alternative.
Yahoo Finance API has been discontinued.
MarketXLS have regular updates and customer support unlike yahoo.
It cost a little but it will definitely save you time.
Hope it helps.
OpenOffice 3.1 on Windows Vista
Shojin
Posts: 6
Joined: Fri Nov 03, 2017 1:02 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Shojin »

mplaut wrote:I found another way but I have not yet managed to get it to work.

You can make a spreadsheet in Google docs using the googlefinance function to get information. Then you can publish that spreadsheet on the web to make it accessible with the Webservice function. On the other hand, maybe you can just use the Google spreadsheet to do what you want to do.
Here is a sample of the spreadsheet. The second page of the spreadsheet has Help.
https://docs.google.com/spreadsheets/d/ ... sp=sharing
I actually got this to work using a similar formula to the one I posted earlier to isolate the numbers in the alphavantage feeds. I'm finding alphavantage to be pretty unreliable - I can deal with delayed data, but #VALUE returns half the time are useless. This is what works for me, and looks to be more functional that alphavantage so far, although I only just set it up so haven't tested reliability yet:
  • - Make a Google Sheets document, list the tickers you need in a column, do a =GOOGLEFINANCE(A1) in the next column and drag that down the table.
    - File > Publish to the web, select .csv format and copy the link
    - In Libreoffice/Excel list the tickers again then use this formula to extract the numbers (this looks for the ticker symbol in A1; adjust as appropriate)

    Code: Select all

    =NUMBERVALUE(MID(WEBSERVICE("[input Google Sheets .csv link here]"),FIND(A1,WEBSERVICE("[input Google Sheets .csv link again here"))+7,7),".")
    - Adjust the "+7,7" as necessary: the +7 is the number of characters where the number you needs starts and the second 7 is the number of characters to return in the cell. So if you look up EURUSD in A1 the .csv will return "EURUSD,1.23546". +7 accounts for "EURUSD,", then the second 7 returns "1.23546".
You could do the call to the .csv once by putting it in another cell and then adjusting the formula to just do a webservice lookup on that cell, but this keeps everything in one cell, obviously. Either way, it seems to work for now.
LibreOffice 5.3.6.1
rcfowler
Posts: 5
Joined: Fri Feb 06, 2015 5:17 pm
Location: Skokie, IL

Re: [Solved] Stock Market Functions LO Calc Extension

Post by rcfowler »

New here. Found this post, downloaded LibreOffice_6.1.1_Win_x64, and tried:

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &A2&"&f=l1"),".")

where A2 is the cell containing the stock symbol (ITW in my case) and l1 calls for the last price.

Auto calculation is on, but no data appear.

Then saw posts that Yahoo no longer provides data. Also investigated ADVANTAGE but not for me.

Using Windows 10 on a 64 bit HP laptop. Also have Open Office 4.1.5.

Got here because the prices in GOOGLE FINANCE are unreliable.

I'm trying to import at least:

Last price
52 week high
52 week low

and if possible:

ex-dividend date
dividend per share
50 day moving average

Suggestions?
OpenOffice 4.1.5 and Libre Office 6.1.0 on Windows 10 64 bit
Shojin
Posts: 6
Joined: Fri Nov 03, 2017 1:02 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by Shojin »

Yahoo is dead, as you have discovered. Alphavantage works, but found that to be unreliable with long lookup times and frequent errors/no data returned. It might be worth checking to see if this has improved, as I haven't tried for months.
Google Finance has been the best option that I have found; the simplest method is just to use Google Sheets and use the function directly in there. Pulling it into Excel of Libreoffice can be done with the WEBSERVICE function, as illustrated above, but it's fairly fiddly and annoying.

I had it working pretty well, but checking again yesterday I see Libreoffice doing some pretty strange things when trying to recalculate, like drawing trace arrows all over the formulas, and updating only once and then failing. I don't really need to pull this data at the moment, but if and when I do again in the future I'll just do everything directly in Google Sheets, despite my reservations about Google in general.

In summary, I've found no easy and reliable way to pull live data into Excel, short of buying a package that specialises is this, which I have seen elsewhere, but forget the name of at the moment.
LibreOffice 5.3.6.1
rcfowler
Posts: 5
Joined: Fri Feb 06, 2015 5:17 pm
Location: Skokie, IL

Re: [Solved] Stock Market Functions LO Calc Extension

Post by rcfowler »

Google Finance has been the best option that I have found
Thanks for your reply. I have been using Google Finance functions for some time in a spreadsheet where I analyze over 400 stocks, but over the past few months the prices for a small subset of stocks have been way off. Luckily, the problem doesn't seems to effect the results for a large number of stocks I use to screen for candidates for selling weekly options. I agree that for now Google Finance is the best option provided the data I really use is okay. I'll check from time to time, and if there appears to be more problems, I'll post for the benefit of the group.
OpenOffice 4.1.5 and Libre Office 6.1.0 on Windows 10 64 bit
mplaut
Posts: 27
Joined: Thu May 06, 2010 12:41 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by mplaut »

FWIW I moved everything to Google sheets and am using GoogleFinance there. It seems to be reliable for the several months that I have been using it.
M Plaut
Openoffice 4.1.4 on Windows 10
crusader
Volunteer
Posts: 506
Joined: Sun Jan 20, 2008 5:06 am

Re: [Solved] Stock Market Functions LO Calc Extension

Post by crusader »

More than a year ago, I initiated this thread because non-paying options for pulling stock market data were dwindling and the only real choice (even then) was Google Sheets - although I do not believe I mentioned it at that time. More than a year later, I hate to admit: I am using Google Sheets, capitalizing on GoogleFinance functions.

I have found it to be fairly reliable. Yes, it is both slow and imperfect; however, it beats the competition (there is none)! The numbers initially come through as negative (when they are actually positive) and vice versa, but this settles down after a while. My biggest concern is privacy. Yes, there is "privacy" - how much and what kind? No one is going to read pages and pages of vague, incomprehensible garbage.

It is said beggars can't be choosers: so I have the option of subscribing to a commercial package or put up with Google Sheets knowing every entry, calculation, transaction is being watched and recorded - and is very likely used - to Google's advantage - at my expense! Who says Google Sheets are free? For now, I am putting up with Google Sheets; down the road, I plan to subscribe to commercial software that is compatible with LIbre Office - for me, the best office suite!
LO 7.x on Linux Mint
In a world without walls, who needs Window$.
A candle loses nothing by lighting another candle.
hacketm
Posts: 6
Joined: Sun Feb 18, 2018 9:52 pm

Re: [Solved] Stock Market Functions LO Calc Extension

Post by hacketm »

I was able to do it, go to insert, Link to external data, Paste from yahoo finance on a seperate sheet, change the update to .01 minutes,(warning the quicker you choose for update the slower the sheet becomes to load)

Once you paste the weblink it will not allow you the press ok, Simply click on link and press enter, Then select HTML All
enter in the cell where you want the data from, =sheet3.a2

Hope this helps
Open Office 4.1.5
Post Reply