[Solved] Stock Market Functions LO Calc Extension

[crusader]

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)?

[Zizi64]

These financial webpages (and the similar ones) will be always change. The software developers must follow the changes... It means a lot of work - for free.

This extension is (as many other extensions are) third party softwares. Please ask the developer:
https://extensions.libreoffice.org/exte ... -extension

As I seen, the latest version is more than 2 years old.

There is a bugreport link on the site:
https://github.com/madsailor/SMF-Extension/issues
Try to write to the developer...


And - because this extension is an opensource project -, you can modify the source code, you can fix the bugs (if it is possible anyway...), you can fork the project...
The extension was written in Python language. Just rename a copy of the .oxt file to .zip extension. Unpack the content, and modify the Python code (or the other parameters in the .xml files) as you needed.

[fiddlehead]

crusader,

I am in the same boat as you, looking for a resolution or alternative to the Stock Market Function extension that stopped working just a few days ago.

As articulated here: Re: Fixing Yahoo Finance Macro - HELP, the URL for at least one YAHOO data page has recently been changed — note the inserted term "download." I tried the revised URL using the WEBSERVICE function in Excel (which had also stopped working when the SMF stopped working in OO Calc) and it works just fine.

In Excel, a working formula looks like this: =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")),
where U4 is the cell that contains the ticker symbol and l1 returns the last share price from YAHOO.

So at the risk of hijacking this thread, I'd like to just ask, is there an equivalent WEBSERVICE function in OO Calc that can be used with the new YAHOO URL, or any other web-based data provider?

Thanks,
—Peter

[Zizi64]

You can try the feature "External data":

Insert - Link to external data - ...

[Zizi64]

In Excel, a working formula looks like this: =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")),
where U4 is the cell that contains the ticker symbol and l1 returns the last share price from YAHOO.

Can you upload your Excel .xls type example file here?

[fiddlehead]

I can't get the "Link to External Data..." feature to work, but I have no idea how to navigate the dialog boxes.

In Excel, a working formula looks like this: =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")),
where U4 is the cell that contains the ticker symbol and l1 returns the last share price from YAHOO.

Can you upload your Excel .xls type example file here?

I don't know how to do that. Is there a recommended method?

[jrkrideau]

There is an upload tab at the bottom of the REPLY and QUOTE windows.

[crusader]

I am in the same boat as you, looking for a resolution or alternative to the Stock Market Function extension that stopped working just a few days ago.

Thank you for the response, Peter. It helps to know I am not alone.

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")), where U4 is the cell that contains the ticker symbol

I tried your formula (adjusting for U4 reference) in LibreOffice (my preferred Office Suite); unfortunately, it did not work for me. In the interim, I ran into the Quandl option. I did not quite grasp the formula, but in fairness, I didn't spent more than a few minutes.

I am still biased in favor of SMF; unfortunately, I can't even tell the difference between python and cobra , so amending SMF is out of question. Will keep on checking to see if there is something easy and robust out there.

Hallelujah!

[Zizi64]

I can't get the "Link to External Data..." feature to work, but I have no idea how to navigate the dialog boxes.

In Excel, a working formula looks like this: =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")),
where U4 is the cell that contains the ticker symbol and l1 returns the last share price from YAHOO.

Can you upload your Excel .xls type example file here?

I don't know how to do that. Is there a recommended method?

Do you mean: a method to upload an example file here?

Just use the "Upload attachment" tab below the answer edit box of this forum (do not use the Quick Reply button).

[fiddlehead]

=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")), where U4 is the cell that contains the ticker symbol

I tried your formula (adjusting for U4 reference) in LibreOffice (my preferred Office Suite); unfortunately, it did not work for me. In the interim, I ran into the Quandl option. I did not quite grasp the formula, but in fairness, I didn't spent more than a few minutes.

I was interested to know if you could use the webservice function, as I read somewhere the function is included in LibreOffice Calc. It is *not*, as far as I know, included in OpenOffice Calc, which is what I have been using.

If you couldn't get the formula to work, I wonder if it was something as simple as the syntax of the VALUE function in Calc. The NUMBERVALUE function in the example formula I provided for Excel should be replaced with the VALUE function in Calc. Does that substitution make a difference?

Also, I am sorry for thoroughly hijacking this thread by now.

[fiddlehead]

Do you mean: a method to upload an example file here?

Just use the "Upload attachment" tab below the answer edit box of this forum (do not use the Quick Reply button).

Thanks! That's new to me!

Attached is an example of a working WEBSERVICE function in Excel. As noted above, I was under the impression a similar WEBSERVICE function is available in LibreOffice Calc. Is that true?

If I were to switch to LibreOffice suite (assuming there is a WEBSERVICE function available), would all OpenOffice documents be compatible?

[Villeroy]

Yes, LO has a WEBSERVICE function.
btw: =NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1")) makes not much sense without specifying the decimal separator.
=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1"),".")
on my system:
=NUMBERVALUE(WEBSERVICE("http://download.finance.yahoo.com/d/quo ... &U4&"&f=l1");".")

[saanichtonian]

Thank you Villeroy. You gave me the bit I needed! (at least untill yahoo &%#@s it up again)

So, to put it all together...

To get stock quotes in Libre Office without using an extension, you can use the following...

Enter into a cell:

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

where U4 is the cell that contains a stock symbol
Alternatively, the stock symbol can be put directly in place of the cell reference surrounded by double quotes (eg &"BGM.V"&) instead of (&U4&)

The l1 (Last Trade (Price Only)) in the statement can be replaced with the following, depending on what information you want displayed. (eg: instead of f=l1 make it f=c1 (change))

***
Financial Data you can Download (brazenly swiped from http://www.jarloo.com/yahoo_finance/)

Pricing Dividends

a: Ask y: Dividend Yield
b: Bid d: Dividend per Share
b2: Ask (Realtime) r1: Dividend Pay Date
b3: Bid (Realtime) q: Ex-Dividend Date
p: Previous Close
o: Open

Date

c1: Change d1: Last Trade Date
c: Change & Percent Change d2: Trade Date
c6: Change (Realtime) t1: Last Trade Time
k2: Change Percent (Realtime)
p2: Change in Percent

Averages

c8: After Hours Change (Realtime) m5: Change From 200 Day Moving Average
c3: Commission m6: Percent Change From 200 Day Moving Average
g: Day’s Low m7: Change From 50 Day Moving Average
h: Day’s High m8: Percent Change From 50 Day Moving Average
k1: Last Trade (Realtime) With Time m3: 50 Day Moving Average
l: Last Trade (With Time) m4: 200 Day Moving Average
l1: Last Trade (Price Only)
t8: 1 yr Target Price

Misc

w1: Day’s Value Change g1: Holdings Gain Percent
w4: Day’s Value Change (Realtime) g3: Annualized Gain
p1: Price Paid g4: Holdings Gain
m: Day’s Range g5: Holdings Gain Percent (Realtime)
m2: Day’s Range (Realtime) g6: Holdings Gain (Realtime)

52 Week Pricing Symbol Info
k: 52 Week High i: More Info
j: 52 week Low j1: Market Capitalization
j5: Change From 52 Week Low j3: Market Cap (Realtime)
k4: Change From 52 week High f6: Float Shares
j6: Percent Change From 52 week Low n: Name
k5: Percent Change From 52 week High n4: Notes
w: 52 week Range s: Symbol
s1: Shares Owned
x: Stock Exchange
j2: Shares Outstanding

Volume Misc
v: Volume
a5: Ask Size t7: Ticker Trend
b6: Bid Size Misc t6: Trade Links
k3: Last Trade Size i5: Order Book (Realtime)
a2: Average Daily Volume l2: High Limit
l3: Low Limit
v1: Holdings Value
v7: Holdings Value (Realtime)
Ratios s6: Revenue
e: Earnings per Share
e7: EPS Estimate Current Year
e8: EPS Estimate Next Year
e9: EPS Estimate Next Quarter
b4: Book Value
j4: EBITDA
p5: Price / Sales
p6: Price / Book
r: P/E Ratio
r2: P/E Ratio (Realtime)
r5: PEG Ratio
r6: Price / EPS Estimate Current Year
r7: Price / EPS Estimate Next Year
s7: Short Ratio

All I can say is, it's working for me.

[crusader]

I was interested to know if you could use the webservice function, as I read somewhere the function is included in LibreOffice Calc. It is *not*, as far as I know, included in OpenOffice Calc, which is what I have been using.

Villeroy beat me to the answer on this one. I have found LO to be more robust.

If you couldn't get the formula to work, I wonder if it was something as simple as the syntax of the VALUE function in Calc. The NUMBERVALUE function in the example formula I provided for Excel should be replaced with the VALUE function in Calc. Does that substitution make a difference?

The NUMBERVALUE function works just fine in (LO) Calc. It appears the issue was the decimal separator as pointed out by Villeroy. His formula works fine in LO.

Also, I am sorry for thoroughly hijacking this thread by now

Rather than hijacking the thread, you actually helped contribute to the solution: for that, I thank you! I will go ahead and mark this thread as solved since it has resolved my issue; however, I will keep an eye out for an updated version of SMF extension.

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

Thank you, Villeroy: that did it!

[mplaut]

Is there still no solution for Openoffice?

O happy day! The SMF extension works in LibreOffice, using =GETYAHOO(L30,21) for the price!

[crusader]

Is there still no solution for Openoffice?
O happy day! The SMF extension works in LibreOffice, using =GETYAHOO(L30,21) for the price!

Not a single Datacode works for me - and yes, I use LibreOffice

[SunnyGirl]

Yes, it works fine in LO 4.3.3.2
I also copied the table from the great website jarloo.com
Go there, copy the table, print it out. It doesn't reproduce well here and I don't know how to insert a table.
Calc will learn the sequence and you can paste the changes into your spreadsheet as a block.
That's a great benefit if you have scores of equities and data points that you need to convert from the old GETYAHOO.
I sure do appreciate the effort you folks have expended @saanichtonian!

Sunny

[crusader]

I am back because I had to "upgrade" from Linux Mint 17.3 to 18.2 and in the process had to reinstall everything (data backup is a good concept). I also "upgraded" to LO 5.4.0 RC2. The formula I used successfully (shown below) when using LO 5.3.x (pre-upgrade) now doesn't update quotes when I start my laptop - as it did with my previous OS and LO versions.

Code: Select all

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

When I hit F9, it does upgrade the value (AutoCalculate is turned on). Under Tools -> Options -> LibreOffice Calc -> General -> Updating, I have selected "Always" as the option for "Update links when opening." Clearly, neither of these work: any suggestions (other than reinstalling LO 5.3.x and checking to see if there are issues with LO 5.4.0 RC2)?

For the record: the GETYAHOO function still doesn't work for me.

[Zizi64]

The RC2 is not a stable version. Do not use for your jobs. It is for the developers and volunteers. Please report the bug to the developers on the bugreport web pages.

[crusader]

As much as I hated to, I uninstalled LO 5.4.0 RC2 and installed LO 5.3, the version I was using prior to upgrading to LO 5.4.0 RC2. Stock values using the formula shown in my last post still don't update when I restart my computer. I wonder if it is the OS?

I guess I am relegated to my original question: is there a robust [free] application that allows downloading stock quotes to LO Calc?

[Villeroy]

The parameters of your URL look strange.

Code: Select all

...?s="&B3&"&f=l1"

is l1 supposed to be a cell address like B3?

menu:Data>Calculate>AutoCalculation must be checked. (AOO: Tools>CellContents>AutoCalculation). This setting is stored with the document.

[crusader]

is l1 supposed to be a cell address like B3?

No, l1 in my formula refers to the last share price. I gleaned this from one of the above posts.

AutoCalculation must be checked.

AutoCalculate is checked.

[fiddlehead]

Just stopped by to see if there was another work-around yet. Sorry to see continued issues for you, crusader. It still surprises me that there aren't tons of people looking for something like this, and that numerous working extensions aren't developed and maintained by DIY investors who also happen to have the needed programming skills.

At this point, I think I'll convert the whole OO Calc file back to an Excel workbook because, at least for now, there is a working webservice function in Excel. (I've resisted installing a third office suite on my PC just to get the webservice function in LO.) Typing in 10 price values every time I want to update my spreadsheet is getting old.

Ironically, the whole reason I started using OO Calc 6 years ago was for access to a working extension that I couldn't get in the ancient version of Excel I had then.

[Villeroy]

The WEBSERVICE function works fine for me.
It updates on open.
It updates when you edit a reference.
It updates on hard recalculation [Ctrl+Shift+F9].

[saanichtonian]

@Villeroy

If one only wants to update one or more cells without waiting for the whole sheet to recalc (mine takes a minute or two), the quickest way I have found is to delete the contents, then ctrl-Z (undo delete).

[Villeroy]

You may also re-enter the symbol or do a dummy edit (F2, Space, Enter) to the formula.

[crusader]

I still can't get any of the above to work. In the process of installing JStock.

[mplaut]

Libre Office works fine for me.

Once it worked in Openoffice, but not after that one time.

JStock looks interesting.

[mplaut]

I just installed Libre Office 5.4.1.2 and it does not seem to work any more.
Also LO 5.3.6.1 does not work.

For the record, I have been using -Getquote(L30,21) where the L30 reference is to a stock symbol.
I tried putting in the stock symbol as a string and a quoted string and it still does not work.

However the SMF extentions using GETYAHOO work fine, in LO 5.3.6.1
But they do not work in 5.4.1.2

[Villeroy]

@mplaut
Your above postings do not contain any helpful information except office version. "Does not work" is a null info.

LibreOffice 5.4.0.3 comma locale:

Code: Select all

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

LibreOffice 5.4.0.3 point locale:

Code: Select all

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