[Solved] Any Alternatives to GETQUOTE?

yee_har · Post by **yee_har** » Mon Mar 16, 2015 8:10 pm

Hi.

Please excuse my ignorance if the following is a ridiculous suggestion. I'm just a "dabbler" and in no way "techie".

I've managed to get bid prices from Yahoo Finance into LibreOffice Calc by reverting to my original method of:

"Insert"
"Link to External Data..."
"URL of external data source" = https://uk.finance.yahoo.com/q?s=AV.L
Press Enter twice to populate "Available tables/ranges"
Select "HTML_2" and Enter/OK to return a small selection of relevant prices to the spreadsheet.

Is this process (or any part of it) helpful in providing a feed into SMF.oxt ?

madsailor · Post by **madsailor** » Wed Mar 18, 2015 4:03 am

yee_har,
Thanks for the info. I didn't even realize that this was possible in calc, and I'm sure it will be useful info for others looking for a way to import data that SMF doesn't support (yet).
It looks like calc is using html screen scraping to import the data, which is a method that I am working on for adding ADVFN data to SMF. So, when I have an opportunity I can roll this into SMF for the Yahoo elements that aren't already supported.
As a note, I prefer to avoid screen scraping when possible, since it is likely to break any time the website changes its layout/format, but in the case of the data we are looking for, there is not much choice.

edit this topic's initial post · Post by **Villeroy** » Wed Mar 18, 2015 8:50 am

@ yee_har
GETQUOTES lets you pick the values you are interested in from many pages.

Code: Select all

Symbol	Current $
ABC	16,7
XYZ	93,03
DEF	32,55
UVW	90,2
BLA	94,84
FOO	7,88
BAR	45,15

Now you can build your own statistics, prognosis and charts on these lists which IMHO ist the one any only reason why one wants to have these data in a calculator program.

@madsailor
This linked table scraped from html tables or from the named ranges of another spreadsheet is a so called area link;
[Tutorial] External Links In Calc
http://www.openoffice.org/api/docs/comm ... Links.html

If I had to handle this kind of stuff, I would create links to all my stocks and then use the spreadsheet's lookup functions to pull data from the import ranges.
Alternatively, you can script regular csv downloads to a target directory (shell, MS PowerShell, Python, Perl, whatever), connect a Base document to the target directory (which adds a database abstraction layer) and then link spreadsheet areas to database queries.

1219 · Post by **1219** » Fri Mar 27, 2015 5:07 pm

When using smf.oxf to get stock information on my iMac, I get the following errors. =GETMORNINGKEY(A1;2) yields an Err:504 in the cell; =GETYAHOO('VGHCX',29) yields Err:508. Is there something that needs to be done to make this extension work in OpenOffice?

madsailor · Post by **madsailor** » Fri Mar 27, 2015 8:17 pm

1219 wrote:=GETMORNINGKEY(A1;2) yields an Err:504 in the cell

The hotfix that you can find here: https://drive.google.com/file/d/0B8TbA- ... sp=sharing works with XOM in A1. VGHCX is not listed on Morningstar's backend as traded on a standard exchange, so SMF returns 'Exchange lookup failed. Only NYSE, NASDAQ, and AMEX are supported.'

OO uses an older version of the programming language that SMF is written in. This causes things to break in OO that work fine in LO.

1219 wrote: =GETYAHOO('VGHCX',29) yields Err:508.

You need to use double rather than single quotes, and a semicolon instead of a comma: =GETYAHOO("VGHCX";29). FWIW Yahoo doesn't return this element for this ticker, so SMF returns 'N/A' (you can try using 5 instead of 29 to verify yourself).

1219 wrote: Is there something that needs to be done to make this extension work in OpenOffice?

As mentioned above OO requires a semicolon delimiter, while LO requires a comma delimiter. The next version of the extension will be backwards compatible in order to fully support OO, and hopefully the next version of OO will have an updated Python language.

1219 · Post by **1219** » Fri Mar 27, 2015 8:59 pm

Neither of the above fixes work. I used =GETYAHOO("XOM";29) and =GETYAHOO("XOM";5) and get Err:504 with each. Am I correct in assuming the results are because OO uses the wrong version of Python?

madsailor · Post by **madsailor** » Fri Mar 27, 2015 9:24 pm

Yes, that would be the most likely cause. The wiki lists an Err:504 as a parameter error, meaning that SMF is not returning the expected float or string. The SMF.oxt version that I referenced in my previous comment is a partial fix for backwards compatibility. With this =GETYAHOO(), =GETMORNINGKEY(), =GETMORNINGFIN(), and =GETMORNINGQFIN all work for me in OO version 4.1.1.

edit this topic's initial post · Post by **Villeroy** » Sat Mar 28, 2015 12:46 am

With the wrong Python version (2.x vs. 3.x) the extension refuses to install.
According to the help, error 504 is a "Parameter list error". "Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference."

=GETYAHOO("XOM";5) => 84.32
tested with LibreOffice 4.4.1 and OpenOffice 4.1.1 (both Linux)

Dunkgrease · Post by **Dunkgrease** » Mon Mar 30, 2015 6:00 pm

I have tried to install this extension and get the following error message:

(com.sun.star.uno.RuntimeException{{Message= "couldn't load file:///C:/Users/Steve?AppData/Roaming/Openoffice/4/user/uno_packages/cache/uno_pacakges/sv50drd.tmp_/SMF-2.oxt?smf.py for reason type urllib.request.Request is unknown", Context=(com.sun.star.uno.XInterface)@0}}

Any idea how to solve this.

Thanks

Steve

edit this topic's initial post · Post by **Villeroy** » Mon Mar 30, 2015 9:41 pm

It is made for LibreOffice. You find a "compatibility" version here: https://drive.google.com/file/d/0B8TbA- ... view?pli=1

caronte44 · Post by **caronte44** » Sun Apr 12, 2015 9:05 pm

It used to work on osx last time that I tried now I reinstalled and got an error message, tried the version in googledrive but..

any help?

caronte44 · Post by **caronte44** » Sun Apr 12, 2015 9:12 pm

sorry I solved, pls ignore the previous message

edit this topic's initial post · Post by **Villeroy** » Mon Apr 13, 2015 12:08 pm

caronte44 wrote:sorry I solved, pls ignore the previous message

No. Would you please share with us how you solved that problem?

Leo_57792 · Post by **Leo_57792** » Tue Apr 14, 2015 2:58 am

When 0.7.0 oxt is installed deleting the earlier 0.6.4 oxt, I get results that are very different than 0.6.4 working version in the libreoffice 4.4.2.2 [LIBRE FRESH] stockquote. Any comments appreciated. Also another comment is how come GETQUOTE seems to work ok on openoffice on my win 7, with no problems. Most likely two different approaches and programming languages might be used with these two .
Someone please briefly explain why, I really like to know, I suspect it is the limitations in the language used in question,
Also, using one of my computers, oxt downloads as oxt, no problems, other computer downloads as a zipped folder, why is that,

Thanks,

Post by **RoryOF** » Tue Apr 14, 2015 5:08 am

The difference in download (.oxt or .zip) is due to the internet browser used. Internet Explorer often silently renames .oxt files to .zip. Not using it, I cannot say if this is IE version specific or for all IE versions.

Leo_57792 · Post by **Leo_57792** » Wed Apr 15, 2015 4:05 am

Thank you RoryOF I appreciate it,

madsailor · Post by **madsailor** » Wed Apr 15, 2015 5:36 am

Leo_57792 wrote:When 0.7.0 oxt is installed deleting the earlier 0.6.4 oxt, I get results that are very different than 0.6.4 working version in the libreoffice 4.4.2.2 [LIBRE FRESH] stockquote. Any comments appreciated. Also another comment is how come GETQUOTE seems to work ok on openoffice on my win 7, with no problems.

1) Please post a specific example of ticker and data code used that returns different results between the two versions.
2) It's not possible to find a copy of GETQUOTE nowadays, as development has been discontinued, and it has been removed from the extension center. If you (or anyone else) has a copy that you can post online somewhere, I would be interested in seeing how it works and where it gets information from.

Thanks.

Leo_57792 · Post by **Leo_57792** » Thu Apr 16, 2015 10:47 pm

Thanks Madsailor,
The libreoffice 4.4.2.2 [FRESH VERSION} WITH SMF.OXT version 0.6.4 tested as follows,
GE symbol placed in A1, cell A2 coded with = getyahoo(A1, 29) yields 27.28 which is correct for April 17, 2015 approximately 4 pm
2. Everything else being same, smf.oxt version 0.6.4 replaced with 0.7.0 yields GE stock price of -1.4 .
It is possible that, version 4.4.2.2. behaves somewhat different than the STILL version. As I said my version is 4.4.2.2.
Thanks

jaquack · Post by **jaquack** » Sat Apr 18, 2015 6:56 pm

I'm trying to follow this thread, and admit I don't understand much.... "colon delimiter", "Python", etc.... I'm just not that adept at this stuff. I follow that it is not as easy as Google Sheets =GoogleFinance(A3,"price") command,but Sheets has its own problems. I tried to copy/paste
= getyahoo(A1, 29)
command into a cell and all I got was Err:508. Earlier I tried linking to an external data source, but all I could get were various tables with various data. All I want is the stock quote.
I'm using Open Office on a Windows 8.1 computer. Can someone walk me through it?

Thanks in advance.

edit this topic's initial post · Post by **Villeroy** » Sat Apr 18, 2015 7:48 pm

The programming issues are not your business.

You install the extension to LIBREOFFICE which is a different program than OPENOffice.
LIBREOffice is also the program where you can use comma as list separator unless comma is the decimal separator. OPENOffice always uses semicolon as list separator. This is why you get Err.508 which is described in the F1-help as follows:

F1-Help wrote:508
Error: Pair missing
Missing bracket, for example, closing brackets, but no opening brackets

madsailor · Post by **madsailor** » Sat Apr 25, 2015 7:25 pm

Leo_57792 wrote:Thanks Madsailor,
The libreoffice 4.4.2.2 [FRESH VERSION} WITH SMF.OXT version 0.6.4 tested as follows,
GE symbol placed in A1, cell A2 coded with = getyahoo(A1, 29) yields 27.28 which is correct for April 17, 2015 approximately 4 pm
2. Everything else being same, smf.oxt version 0.6.4 replaced with 0.7.0 yields GE stock price of -1.4 ....

Sorry I took so long getting back to you.

Please note that many of the Yahoo datacodes changed between version 0.6.4 and 0.7.0 since Yahoo deprecated many of their data elements. For example, datacode 29 referred to 'Last Trade(Price Only)' in 0.6.4, while the same code refers to 'Change from 52 Week High' in 0.7.0. 'Last Trade(Price Only)' is datacode 21 in 0.7.0. As always, the current datacodes are listed in the example worksheets included with the extension.

FYI here are side-by-side screencaps of YahooExample.ods for 0.6.4 vs 0.7.0 : http://imgur.com/a/Q0vch

Leo_57792 · Post by **Leo_57792** » Sun Apr 26, 2015 12:19 am

Dear Madsailor,

Thank you for your time to get back, based on what you sent, it all makes sense, I appreciate it, it is a little oversight on my part....
Have a great weekend....

mplaut · Post by **mplaut** » Wed Apr 29, 2015 9:55 pm

Thank you to everyone and especially madsailor and Villeroy.

I use OO 4.1.1 on W7 and Villeroy's compatibility version works like a charm. I also have to use his VALUE() conversion to get numbers.

I have GETQUOTE installed (and it still seems to work ok) and would be happy to post it if someone could tell me how to extract it.

Leo_57792 · Post by **Leo_57792** » Wed Apr 29, 2015 11:22 pm

Using Libre 4.4.2.2 and smf extension for stock quotes, I have difficulty applying conditional formatting to the cell indicating Stock Price Change in the cell. I wanted to make it green for positive change, and red for negative. I do the conditional formatting, but colors does not change. Cell has =yahooquote(pointer to stock symbol, data code for "change"). Basically if negative value set to red, and positive green, does not happen. Anyone reaches out appreciated.

madsailor · Post by **madsailor** » Thu Apr 30, 2015 6:08 am

Leo_57792 wrote:Using Libre 4.4.2.2 and smf extension for stock quotes, I have difficulty applying conditional formatting to the cell indicating Stock Price Change in the cell. I wanted to make it green for positive change, and red for negative. I do the conditional formatting, but colors does not change. Cell has =yahooquote(pointer to stock symbol, data code for "change"). Basically if negative value set to red, and positive green, does not happen. Anyone reaches out appreciated.

1) Select the cells you want to conditionally format and right click to bring up the context menu.
2) Select "Format cells".
3) At the bottom of the numbers tab (should be the one it opens to) is a field labeled "Format code". In this field type "[GREEN]0;[RED]-0" without the quotes, click OK, and you should be in business.
This works fine for me in LO 4.2.8.2 with SMF 0.7.0

mplaut wrote:I have GETQUOTE installed (and it still seems to work ok) and would be happy to post it if someone could tell me how to extract it.

Thanks for the offer. I managed to find a version on sourceforge after digging through archived webpages. Looking at the source of GETQUOTE, it seems to pull data from yahoo, so the actual data that the GETYAHOO portion of SMF supplies should match up exactly with (and expand on) GETQUOTE.

Leo_57792 · Post by **Leo_57792** » Fri May 01, 2015 4:52 pm

Hi Madsailor,
Thank you for your advise, I am using 4.4.2.2 Fresh version, with smf 0.6.4, I did not have luck as of yet with [GREEN]0;[RED]-0 in the Format Code box,
I will try again. By the way, is there a quick way to refresh the portfolio at anytime after current time values displayed since it is not realtime. I appreciate it madsailor, I really had great hopes for your advise to work, it is simple, hopefully I will get it working...

jaquack · Post by **jaquack** » Wed May 06, 2015 9:55 pm

Villeroy wrote: You install the extension to LIBREOFFICE which is a different program than OPENOffice.
LIBREOffice is also the program where you can use comma as list separator unless comma is the decimal separator. OPENOffice always uses semicolon as list separator. This is why you get Err.508 which is described in the F1-help as follows:
F1-Help wrote:508
Error: Pair missing
Missing bracket, for example, closing brackets, but no opening brackets

Sooooo... I don't understand what you mean by "install the extension to LIBREOFFICE". I have to install LIBREOFFICE instead of Open Office? And then install an extension (or mod?) that allows me to use the "getquote" command? And at that point, I can use a comma to separate "A1" from "29", instead of a semicolon?

Thanks for your help.

madsailor · Post by **madsailor** » Thu May 07, 2015 9:38 am

jaquack wrote: Sooooo... I don't understand what you mean by "install the extension to LIBREOFFICE". I have to install LIBREOFFICE instead of Open Office? And then install an extension (or mod?) that allows me to use the "getquote" command? And at that point, I can use a comma to separate "A1" from "29", instead of a semicolon?

Thanks for your help.

The GETQUOTE extension is no longer developed or supported by it's creator; it has been succeeded by the SMF extension. SMF will function in OpenOffice with this version: https://drive.google.com/file/d/0B8TbA- ... view?pli=1, or in LibreOffice with this version: https://github.com/madsailor/SMF-Extension/releases.

Please read the description here to get an idea of the functionality and how to use the extension: https://github.com/madsailor/SMF-Extension.

NOTE: This is where the difference comes in - when using the OO version your input should look like "=GETYAHOO('XOM';21)", while when using the LO version it should look like "=GETYAHOO('XOM',21)". Note the semicolon vs. comma.

Also, the upcoming release will support both LO and OO out of the box, consolidating the two versions I linked above; however the semicolon vs comma difference will remain..

howdydooit · Post by **howdydooit** » Fri Jun 19, 2015 2:50 am

Thanks so much to all who have contributed! This is a recent project for me, so I had no experience with the previous getquote function, and I'm a bit of a neophyte in terms of anything more complex in a spreadsheet than simple math or a graph.

I'm having a weird problem. When I use the function, I get a great return of the data. Example, =GETYAHOO(A25; 21)
Ticker Symbols are in Column A, Prices are in column B

A25 has the current quote perfectly. I have calculations across columns that depend on the value in that cell. Everything works fine. When I copy that cell (happens to be "B25") and paste it into the additional cells in the column, I get a column of proper values for B26, B27, B28, and so on.

The problem is, there are 2 cells in adjoining columns that have formulas in them that depend on BXX, and the result that I now get is #VALUE!
When I look at the contents of the cell, it's the correct statement, with the appropriate cell value incremented.

So, =GETYAHOO(A25; 21) returns the proper value in B25, and all the calculations work in D25 and S25, but
=GETYAHOO(A26; 21) returns a proper value in B26, but screws up the formulas in D26 and S26

If I type the formulas out manually over the pasted entry, it doesn't work until I've manually written to that cell twice. The first time I get the proper B column value, but the D/S error is the same, and the second time I manually write over the formula, D and S are working again! (Just for fun, I even copied the working value and pasted it back into the same cell, which breaks the dependent formulas).

What is going on??? Is there a hidden attribute that identifies when a cell has a pasted value, even though it reads the proper formula?

husky55 · Post by **husky55** » Fri Jun 19, 2015 2:46 pm

I just like to thank you all for helping and developing SMF. As a very long time user of MS Excel Query and GETQUOTE, both of which no longer working, SMF has been a wonderful replacement. Version .70 works fine in Libre, the OO version works but requires some modifications with the VALUE function and (;) instead of (,). I would like to point out that Google Spreadsheet will also allow quote retrieval from their server. I use both Google and Yahoo to check out on the data. Again my great appreciation for you all.

[Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

smf.oxt Err:508 and Err:504

Re: smf.oxt Err:508 and Err:504

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?