[Solved] Any Alternatives to GETQUOTE?

fiddlehead · Post by **fiddlehead** » Sun Jan 18, 2015 1:06 am

Are there any functioning alternatives to GETQUOTE?

I've used the GETQUOTE extension for several years — with excellent results — to track a retirement portfolio. I understand that function is no longer available. The funny thing is, the availability of first GETPRICE and then GETQUOTE was what drew me to OpenOffice in the first place. Without an easy way to download the latest closing prices of a handful of index mutual funds, I might have to migrate away from OpenOffice. I'd rather find a suitable alternative.

Someone pointed me to the SMF extension for LibreOffice: http://extensions.libreoffice.org/exten ... -extension. I downloaded and installed this extension, and with a minor syntax change (a comma to a semicolon) and some experimenting was able to get it to return the latest closing price of a fund.

=GETYAHOO(Ticker,Datacode) ———> =GETYAHOO("Ticker";29)

Unfortunately, Calc will not recognize the value as a number when used by other functions. Are LibreOffice extensions not completely compatible with OpenOffice?

It's hard to believe that such this type of extension does not exist for such a widely used spreadsheet application.

Thanks for your help,
—Peter

edit this topic's initial post · Post by **Villeroy** » Sun Jan 18, 2015 2:56 am

=VALUE(GETYAHOO("Ticker";29))

fiddlehead · Post by **fiddlehead** » Sun Jan 18, 2015 8:59 am

Villeroy wrote:=VALUE(GETYAHOO("Ticker";29))

Thanks Villeroy!

So (for now anyway) it looks like a great alternative to the now unavailable GETQUOTE extension is the LibreOffice extension SMF Extension. An incidental benefit is that the data retrieval is much faster with SMF than it was with GETQUOTE.

The only catch is that you have to use the VALUE function to convert the result to a number for use elsewhere in the spreadsheet.

SMF Extension adds functions to Calc that allow stock market data retrieval. The value of 29 for "Datacode" appears to correctly retrieve the latest closing price of a fund. You'll have to experiment with other "Datacode" values to retrieve other data.

Hopefully this will help others looking for an alternative to GETQUOTE.

Thanks again,
—Peter

edit this topic's initial post · Post by **Villeroy** » Sun Jan 18, 2015 2:51 pm

It looks as if the author wrapped some existing Python code into an office extension (which is an excellent idea), but then he forgot to convert extracted text values into floating point decimals.
=VALUE(GETYAHOO(A1;B1)) only works with locales using the point as decimal separator.

If any user of a document may use a comma locale, the following formula works in any case replacing the point with the actual decimal separator derived from number Pi before converting the resulting string to a number:

Code: Select all

=VALUE(SUBSTITUTE(GETYAHOO(A1;B1);".";MID(PI();2;1))

Leo_57792 · Post by **Leo_57792** » Fri Feb 20, 2015 10:25 pm

Thank you "madsailor" for the new version of SMF 0.6.2 on Feb20, 2015 solving the Stock Quote issue, tested it, it works great. Is there a workaround to make it to work "realtime" or refresh itself on an interval. However, it is good as it is......Thanks again,

edit this topic's initial post · Post by **Villeroy** » Fri Feb 20, 2015 11:05 pm

Leo_57792 wrote:Thank you "madsailor" for the new version of SMF 0.6.2 on Feb20, 2015 solving the Stock Quote issue, tested it, it works great. Is there a workaround to make it to work "realtime" or refresh itself on an interval. However, it is good as it is......Thanks again,

Would you please be so kind and tell us which project you refer to so we don't need to guess who the madsailor is?
It is certainly not http://extensions.libreoffice.org/exten ... -extension which is more than one year old.
This is the internet. The use of hyperlinks is a matter of course.

Leo_57792 · Post by **Leo_57792** » Sat Feb 21, 2015 12:04 am

Hi Villeroy,
Please refer to http://extensions.libreoffice.org/exten ... -extension new version 0.6.2 smf.oxt releasing manager madsailor
works to get stock quotes successfully, on LibreOffice 4.4. Sorry for the confusion, I have seen your suggestions to fix the older versions of SMF .
Version 0.6.2 works without modifications!!!/

edit this topic's initial post · Post by **Villeroy** » Sat Feb 21, 2015 12:16 am

There is no office extension to download.

Leo_57792 · Post by **Leo_57792** » Sat Feb 21, 2015 1:24 am

Hi Villeroy,
http://extensions.libreoffice.org/exten ... -extension Click on blue link [0.6.2] released TODAY...., by releasing manager "madsailor"
you will see smf.oxt [new one] , remove old one by tools extension manager, and download and replace with the new smf.oxt.

All Releases
Version Released Description Compatibility Licenses Status
CLICK HERE ==>0.6.2 Feb 20, 2015 Fix for LibreOffice ver 4.2.x with Python 3.x More about this release…
LibreOffice 3.6
LibreOffice 4.0
LibreOffice 4.1
LibreOffice 4.2
LGPL-v3+ release-candidate

edit this topic's initial post · Post by **Villeroy** » Sat Feb 21, 2015 1:39 am

I don't see any blue link [0.6.2] (JavaScript is enabled).

Current Release

Keine stabile Version aktuell verfügbar.
[English: No stable release availlable]

Leo_57792 · Post by **Leo_57792** » Sat Feb 21, 2015 2:09 am

Hi Villeroy,

Please use this link instead, when I click on blue 0.6.2 that is what I see, here it is, click on this you will find, the new smf.oxt and ods files there.
download the smf.oxt , and install.
http://extensions.libreoffice.org/exten ... lpha/0.6.2

edit this topic's initial post · Post by **Villeroy** » Sat Feb 21, 2015 2:36 am

Thank you. Now I have the extension installed and it still imports text values which are rather pointless in arithmetic calculators.

Code: Select all

=VALUE(SUBSTITUTE(GETYAHOO(A1;B1);".";MID(PI();2;1))

is still the formula to be used with that add-in.

Edit: I did not realie that the functions return real text sometimes

Code: Select all

=IF(ISERR(VALUE(SUBSTITUTE(GETYAHOO($A$1;$B4);".";MID(PI();2;1))));GETYAHOO($A$1;$B4);VALUE(SUBSTITUTE(GETYAHOO($A$1;$B4);".";MID(PI();2;1))))

LibreOffice users can use a slightly shorter formula with IFERR:

Code: Select all

=IFERR(VALUE(SUBSTITUTE(GETYAHOO($A$1;$B4);".";MID(PI();2;1))));GETYAHOO($A$1;$B4))

Leo_57792 · Post by **Leo_57792** » Sat Feb 21, 2015 3:25 am

Hi Villeroy,
I have LibreOffice 4.4.0.3 with SMF 0.6.2 smf.oxt
When you click on YahooExample.ods 4 th row under smf.oxt it runs as is without any modifications, in other words,
Brk-b
Usage : =GETYAHOO(Ticker,Datacode)
Datacode: Example:
Dividend Yield 1 N/A
Dividend per Share 2 0.00
Ask (Realtime) 3 148.58
Dividend Pay Date 4 N/A
Bid (Realtime) 5 148.45
Ex-Dividend Date 6 N/A
Previous Close 7 147.48
Open 8 147.27

I believe smf.oxt did not get installed correctly, you do not have to modify anything, do not have to use =VALUE(SUBSTITUTE(GETYAHOO(A1;B1);".";MID(PI();2;1)).
I have two cells programmed as =getyahoo(stock cell ID, 29) which gives me "Last Trade Price")
I have another cell programmed as =getyahoo(stock cell ID, 9) change in the stock price
That is ALL i have, it is getting late and my sleep time, but we discuss again next couple of days.
Try to run YahooExample.ods by clicking on it 4TH UNDER smf.oxt, with LIBREOFFICE 4.4.0 it should run as is without any modifications AT ALL.

edit this topic's initial post · Post by **Villeroy** » Sat Feb 21, 2015 3:28 am

All you numbers are text. Importing numeric text into a calculator is pointless.
I have attached modified example files to my above posting.

edit this topic's initial post · Post by **Villeroy** » Sat Feb 21, 2015 1:17 pm

The 3 functions return correct values after I modified smf.py in the extensions root folder. There is still some room for improvements in respect to date values.
This version of smf.py returns floating point decimals if possible, otherwise text:

Code: Select all

#  smf.py - Pyuno/LO bridge to implement new functions for LibreOffice Calc
#
#  Copyright (c) 2013 David Capron (drbluesman@yahoo.com)
#
#  license: GNU LGPL
#
#  This library is free software; you can redistribute it and/or
#  modify it under the terms of the GNU Lesser General Public
#  License as published by the Free Software Foundation; either
#  version 3 of the License, or (at your option) any later version.
#
import os
import sys
import inspect
import unohelper
from com.smf.ticker.getinfo import XSmf
# Add current directory to path to import yahoo and morningstar modules
cmd_folder = os.path.realpath(os.path.abspath
                              (os.path.split(inspect.getfile
                                             ( inspect.currentframe() ))[0]))
if cmd_folder not in sys.path:
    sys.path.insert(0, cmd_folder)
import yahoo
import morningstar

class SmfImpl(unohelper.Base, XSmf ):
    """Define the main class for the SMF extension """    
    def __init__( self, ctx ):
        self.ctx = ctx
        self.yahoo_flag = ['0', '']
        self.keyratio_flag = ['0', '']
        self.financial_flag = ['0', '']
    #Following functions are called and mapped by LO through the Xsmf.rdb file.
    def getYahoo( self, ticker, datacode ):
        try:
            x = float(yahoo.fetch_data(self, ticker, datacode))
        except:
            x = yahoo.fetch_data(self, ticker, datacode)
        return x    
            
    def getMorningKey( self, ticker, datacode):
        try:
            x = float(morningstar.fetch_keyratios(self, ticker, datacode))
        except:
            x = morningstar.fetch_keyratios(self, ticker, datacode)
        return x

    def getMorningFin( self, ticker, datacode):
        try:
            x = float(morningstar.fetch_financials(self, ticker, datacode))
        except:
            x = morningstar.fetch_financials(self, ticker, datacode)
        return x

def createInstance( ctx ):
    return SmfImpl( ctx )

g_ImplementationHelper = unohelper.ImplementationHelper()
g_ImplementationHelper.addImplementation( \
    createInstance,"com.smf.ticker.getinfo.python.SmfImpl",
        ("com.sun.star.sheet.AddIn",),)

I notified the extension author about this topic.

Leo_57792 · Post by **Leo_57792** » Sat Feb 21, 2015 6:26 pm

Thank you Villeroy, have a great weekend,

madsailor · Post by **madsailor** » Sat Feb 21, 2015 8:49 pm

For anyone with issues in the future:

Please log an issue at github here: https://github.com/madsailor/SMF-Extension/issues

Villeroy wrote:The 3 functions return correct values after I modified smf.py in the extensions root folder. There is still some room for improvements in respect to date values.
This version of smf.py returns floating point decimals if possible, otherwise text:

I notified the extension author about this topic.

Thank you for your fix, and more importantly for notifying me. Unfortunately the extension site doesn't have a clear 'report issues to author' method, so I didn't realize this was a problem, nor did I realize this thread existed. I have incorporated your fix along with stripping ',' from raw strings to make float conversion work for numbers greater than 999.

I am not sure if LO handles dates differently than just 'a string'. If you have any ideas on this I can look at returning those elements in a more useful form to LO as well. Unfortunately I think some of the randomly formatted elements returned by yahoo will have to remain as is, since they are highly variable and don't lend themselves well to a solution.

The updated 0.6.3 version of the SMF extension can be found here: http://extensions.libreoffice.org/exten ... lpha/0.6.3

Thanks everyone for the feedback, and best wishes with the extension and your investments!

edit this topic's initial post · Post by **Villeroy** » Sat Feb 21, 2015 9:19 pm

Thank you for integrating the fix. All Excelish spreadsheet programs treat dates and times as a subtype of doubles. Calc knows only 2 data types string and double. Formulas can return error values as a third type.

1899-12-30 is Calc's zero point of the time scale as you can see when you format a cell with a zero as date.
Todays date formatted as an integer is day number 42056 since 1899-12-30. Mid day is 42056.5
In rare cases, the zero day may differ for compatibility with legacy spreadsheets. This mapping of date-times to floats is difficult to handle BUT for mere dates it would be OK to store them as ISO strings like 1999-12-31. Calc can treat ISO strings as if they were formatted integers and the conversion from ISO dates to integers works flawlessly with any locale setting. I'd think that ISO strings are OK. Most users won't notice any difference and =VALUE(ISO-Date) will do the right thing in any case. Same with time stamps such as "1999-12-31 23:45:59.99" with either comma or point as decimal separator.
Compared to dates with leap years and variable zero day settings, time values are easy: hours/24+minutes/1440+seconds/86400 ==> float (fraction of a day) which is displayed as hours:minutes:seconds when formatted as ~~date~~ time.

madsailor · Post by **madsailor** » Sat Feb 21, 2015 11:12 pm

Thanks for the info. I will look at formatting dates to ISO strings for the Morningstar data elements. As it stands now they are returned as partial ISO format ie. 2014-12, so the best method is probably to just append an inconsequential day ie. 2014-12-01. The Yahoo data elements will take a bit more effort.
At the moment only the Yahoo 'last trade time' element returns data that is specific enough to warrant a timestamp, with a current format of '4:00pm' - about as unhelpful as possible. I'll take a look at re-formatting this before it is passed back to LO.
If you have any other issues/thoughts/suggestions feel free to let me know. I am just a hobbyist that wanted to import financials from the web into LO, and I saw no solution, so I made one, then shared it in the hopes that others would find it useful as well. Feedback can help me make the extension better, which helps make LO and the FOSS community stronger.

Leo_57792 · Post by **Leo_57792** » Sun Feb 22, 2015 2:56 am

Thank you "madsailor", I will test 0.6.3, although 0.6.2 worked for my purposes, it was a very needed fix,

Post by **karolus** » Sun Feb 22, 2015 10:31 am

Hallo

@madsailor: your overhelmed:

Code: Select all

def financial_datacode_map():
    """Create a dictionary mapping datacodes to (row, col) in data."""
    def find_row_col(datacode):
        #Match datacode to row, column.
        for row in range(0, 27):
            for col in range(0, 7):
                if datacode == col + (6*row):
                    return row, col
    #Create and return the dictionary.
    return {datacode: find_row_col(datacode) for datacode in range(1, 163)}

~~simplyfied as ``dict-comprehension``~~

Code: Select all

def financial_datacode_map():
    return {n:(divmod(n, 7)) for n in range(1,163) if n%7 }

[/strike]
update:

Code: Select all

def financial_datacode_map():
    def mapping( n ):
        i, r = divmod(n-1,6)
        return i, r+1
    return {n: mapping(n) for n in range(1, 163)}

madsailor · Post by **madsailor** » Sun Feb 22, 2015 11:50 am

karolus wrote: simplyfied as ``dict-comprehension``
Code: Select all
def financial_datacode_map():
    return {n:(divmod(n, 7)) for n in range(1,163) if n%7 }

Great idea. Unfortunately this breaks the code as it stands in morningstar.py line 141 when datacode 7 is called. If you fork the repo, make your changes into a working version, and send a pull request (https://github.com/madsailor/SMF-Extension/pulls) I'll incorporate your changes. Otherwise it may take some time for me to add this.

Post by **karolus** » Sun Feb 22, 2015 12:15 pm

Hallo

Sorry, change to

Code: Select all

def financial_datacode_map():
    def mapping( n ):
        i, r = divmod(n-1,6)
        return i, r+1
    return {n: mapping(n) for n in range(1, 163)}

madsailor · Post by **madsailor** » Sun Feb 22, 2015 1:53 pm

karolus wrote: Sorry, change to

Code: Select all

def financial_datacode_map():
    def mapping( n ):
        i, r = divmod(n-1,6)
        return i, r+1
    return {n: mapping(n) for n in range(1, 163)}

This is working, thank you for the optimization. I have included it along with a load of ISO formatting for time/date strings in the next release which can be found here: http://extensions.libreoffice.org/exten ... lpha/0.6.4

Post by **karolus** » Sun Feb 22, 2015 3:17 pm

Hallo

Update for :

Code: Select all

def keyratio_datacode_map():
    
    skip_list = {16, 17, 18, 28, 29, 38,
                 39, 40, 41, 46, 51, 56,
                 61, 62, 63, 69, 70, 71,
                 92, 93, 98, 99, 100}
    
    allowed = sorted(set(range(109)) - skip_list)
    
    def mapping (num):
        row, col = divmod(num-1,11)
        return allowed[row], col+1
    
    return {datacode: mapping(datacode) 
            for datacode in range(1, 947) }

madsailor · Post by **madsailor** » Sun Feb 22, 2015 9:08 pm

Included. Thanks again.

yee_har · Post by **yee_har** » Fri Mar 13, 2015 3:27 pm

Hi and thanks for this extension (SMF.oxt) which could be my saviour now that GETQUOTE - after being flaky recently - now seems to be totally broken.

When using SMF.oxt and trying to get "bid" prices (datacode "5") I'm getting "N/A". This happens with all stock tickers tried - including BARC.L, VOD.L, XOM - when at the same time the appropriate values are shown on both the .com and UK versions of Yahoo Finance. It also happens with "ask" prices (datacode "3") although that is not important to me.
SMF.oxt will return other values such as "Last trade" (datacode "29") but I really need "bid" price.

Would appreciate any thoughts on this.

Thanks again,

madsailor · Post by **madsailor** » Fri Mar 13, 2015 10:23 pm

It looks like Yahoo was nice enough to switch around all their calls and data elements sometime in the last day or so, so I will need to do a pass to determine which calls are needed for which elements with the new changes. This also breaks all the Morningstar functionality since I was using a call to Yahoo to determine which tickers were traded on which exchanges. I am looking at getting exchange data directly from the NASDAQ site (which will remove Yahoo as such an achilles heel for the extension), and once I have that sorted I will fiddle with Yahoo to see what elements are what. I have some family business until mid April that will slow my ability to address this issue, but I will try to make time to sort this out soon. Unfortunately until then you'll have to sit tight (or contribute a fix at the github repo).

edit this topic's initial post · Post by **Villeroy** » Sat Mar 14, 2015 1:01 am

same with "morningstar": viewtopic.php?f=9&t=75936

madsailor · Post by **madsailor** » Sat Mar 14, 2015 7:02 am

New version 0.7.0 is up. It fixes all the Morningstar functionality (by migrating exchange calls from Yahoo to the hopefully more stable NASDAQ site), and also adds Morningstar quarterly data with a new function GETMORNINGQFIN().
Unfortunately it looks like Yahoo permanently deprecated Bid and Ask, along with many other data elements. This forced me to restructure the datacodes, so the new version will require some changes to existing spreadsheets to match up with the new datacodes. YahooExample.ods has been updated to reflect the changes.
Wish I had a better solution, and a happier answer, but until we find a more reliable data source, or possibly work out some JSON calls to Yahoo, it looks like 'Bid' is down for the count.

[Solved] Any Alternatives to GETQUOTE?

[Solved] Any Alternatives to GETQUOTE?

Re: Any Alternatives to GETQUOTE?

Re: Any Alternatives to GETQUOTE?

Re: [solved: Use SMF] 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?

Re: [Solved] Any Alternatives to GETQUOTE?

Re: [Solved] Any Alternatives to GETQUOTE?