HYPTUS v11.31 Run-time error 5

[desmid]

That's correct, and you should be able to un-install it if required, also completely separate from your other applications.

I get a similar error message [in OpenOffice Windows] but slightly longer, apache won't let me copy paste it.

OK, thanks for that.

I think we'll need to re-visit the code on the LibreOffice/OpenOffice version and see why this might be happening for some people.

Thanks for the feedback, it's most appreciated.

Cheers,

Itsallaguess

Hi - I think the bug is straightforward concerning 'data' in the error message.
FWIW, here's a replacement for createPriceDict() that might be a bit more maintainable and it handles FX as well:

Code: Select all

def createPriceDict(text):
    m = re.search(r'.*?\[(.*?)\].*', text)
    if not m: return {}
    text = m.group(1)

    data = {}

    while text:
        m = re.search(r'.*?{(.*?)\}(.*)', text)
        if not m: break

        symbolData = m.group(1)
        symbol, price, currency = '', '', ''

        for element in symbolData.split(','):
            element = element.replace('"','')
            try:
                key,val = element.split(':', 1)
            except:
                continue

            if 'symbol' == key:
                symbol = val.replace('=X', '')
                continue

            if 'regularMarketPrice' == key:
                price = val
                continue

            if 'currency' == key:
                currency = val.replace('GBp', 'GBX')
                continue

        #logger.debug("cpd: %s,%s,%s", symbol,price,currency)

        data[symbol] = [price, currency]
        text = m.group(2)

    return data


Thanks to all who maintain HYPTUSS and the simpleyahooscrape as it's got me starting to build my own macros (finally!).

desmid

[Itsallaguess]

Hi - I think the bug is straightforward concerning 'data' in the error message.

FWIW, here's a replacement for createPriceDict() that might be a bit more maintainable and it handles FX as well:

Code: Select all

def createPriceDict(text):
    m = re.search(r'.*?\[(.*?)\].*', text)
    if not m: return {}
    text = m.group(1)

    data = {}

    while text:
        m = re.search(r'.*?{(.*?)\}(.*)', text)
        if not m: break

        symbolData = m.group(1)
        symbol, price, currency = '', '', ''

        for element in symbolData.split(','):
            element = element.replace('"','')
            try:
                key,val = element.split(':', 1)
            except:
                continue

            if 'symbol' == key:
                symbol = val.replace('=X', '')
                continue

            if 'regularMarketPrice' == key:
                price = val
                continue

            if 'currency' == key:
                currency = val.replace('GBp', 'GBX')
                continue

        #logger.debug("cpd: %s,%s,%s", symbol,price,currency)

        data[symbol] = [price, currency]
        text = m.group(2)

    return data


Well what a fantastic way to mark the (almost) one year anniversary of your last post desmid!!

Thanks very much indeed for taking an interest in this issue. Hopefully kiloran will be back in a few days, and I'm sure he'll be as grateful as I am with your solution to this issue in the above routine.

Please don't leave it another year before you post again!!

:O)

Cheers,

Itsallaguess

[desmid]

Hi - I think the bug is straightforward concerning 'data' in the error message.

...

Well what a fantastic way to mark the (almost) one year anniversary of your last post desmid!!

Thanks very much indeed for taking an interest in this issue. Hopefully kiloran will be back in a few days, and I'm sure he'll be as grateful as I am with your solution to this issue in the above routine.

Please don't leave it another year before you post again!!

:O)

Cheers,

Itsallaguess

Heh!

I'm afraid I'm a constitutional lurker - too much in awe of the regular HYP posters to do much other than gape slack-mouthed at all your accumulated financial legerdemain. But I do like coding... happy to poke around in the hope it's useful.

desmid

[Itsallaguess]

Can I please ask any LibreOffice users who are having issues running the original LibreOffice Beta file if they could download and try the following Beta version (b-g-dev2) to see if this fixes the specific issues they are seeing?

This problem seems to be affecting some Mac users and a couple of Windows users, so I would appreciate any feedback from those that are seeing the issue on the original download, if the following new Beta then fixes the issue for them -

http://www.filedropper.com/hyptop-upversion-b-g-dev2

Apologies for the temporary download link above, but if this fixes the issue then we'll look into giving it a more permanent home if the feedback is positive.

There's no need for anyone who is up and running with the original b-f-dev2 LibreOffice file to download this new version, and the above b-g-dev2 is currently only intended for those specific users who are having issues with the original b-f-dev2 LibreOffice Beta.

Huge thanks again to desmid for taking a look at this issue for us. I've tested his new procedure on my Windows machine and it works well, so I'm hoping the same will be true for those that have been having issues with the original LibreOffice Beta download file.

Any feedback from any affected users regarding the above would be most welcome.

Cheers,

Itsallaguess

[Arborbridge]

I've tried the latest version of HYPTUSS in its beta form, and it works for me.

Thanks to all the boffins who make this possible and keep it going in a crisis, and to Raptor who pointed me in the right direction to find the Beta version (which I had missed).

Arb.

[NeilW]

I get

com.sun.star.uno.RuntimeException: Error during invoking function HypUpdPriPy_1 in module vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py (<class 'KeyError'>: '^FTSE'
  File "/Applications/LibreOffice.app/Contents/Resources/pythonscript.py", line 875, in invoke
    ret = self.func( *args )
  File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 1141, in HypUpdPriPy_1
  File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 526, in writeTrackData
)

when I press 'Get Yahoo Prices' on the 'High Yield Portfolio Sheet'.

On the Watchlist sheet the process completes but no price appears.

Mac LibreOffice 5.3.7.2 just with the default 'BP' share entry

[desmid]

That accelerated fast! It's quite likely I've just solved one bug and created another - apologies if so. The code snippet was intended as an idea for SimpleScrapeYahoo.ods for review, but not (yet) HYPTUSS (though the same tiny bug is present).

Both HYPTUSS b-f-dev2 and b-g-dev2 do work unchanged in my flavour of Linux with Python 3.4.3 and LO 5.0.6.2.

The 'data' bug reported with SimpleScrapeYahoo.ods is only triggered when the createPriceDict routine is fed some bad input (something that doesn't look like a piece of Yahoo JSON, as might happen if the Yahoo request failed) and should not be affected by the enclosing spreadsheet environment or the Python version (same bug is triggered in Python 2.7.13).

However, NeilW's error message is more puzzling, and I will install LO 5.3.7 and look into that.

The actual 'data' variable bug in the original createPriceDict can be fixed minimally with one-line (with my comment at the side):

Code: Select all

def createPriceDict(html):   
    priceDict = {}
    matchObj = re.search( r'.*?\[(.*?)\].*', html)
    data = ''  #desmid: initialise in case re.search fails
    if matchObj:
        data = matchObj.group(1)
       
    while data:
        matchObj = re.search( r'.*?{(.*?)\}(.*)', data)
        if matchObj:
            symbolData = matchObj.group(1)
            symbol = ''
            price = ''
            currency = ''
            for symbolElement in symbolData.split(','):
                symbolElement = symbolElement.replace('"','')
                if 'symbol' in symbolElement:
                    symbol = (symbolElement[symbolElement.index(':')+1:])
                elif 'regularMarketPrice' in symbolElement:
                    price = (symbolElement[symbolElement.index(':')+1:])
                elif 'currency' in symbolElement:
                    currency = (symbolElement[symbolElement.index(':')+1:])
 
            priceDict[symbol] = [price, currency]
           
            data = matchObj.group(2)

    return(priceDict)


Obviously, batting code about in the forum is unsatisfactory for everyone, and I don't want to tread on Kiloran or itsallaguess' feet. Well, not too much anyway.

cheers
desmid

[Itsallaguess]

That accelerated fast! It's quite likely I've just solved one bug and created another - apologies if so. The code snippet was intended as an idea for SimpleScrapeYahoo.ods for review, but not (yet) HYPTUSS (though the same tiny bug is present).

Both HYPTUSS b-f-dev2 and b-g-dev2 do work unchanged in my flavour of Linux with Python 3.4.3 and LO 5.0.6.2.

The 'data' bug reported with SimpleScrapeYahoo.ods is only triggered when the createPriceDict routine is fed some bad input (something that doesn't look like a piece of Yahoo JSON, as might happen if the Yahoo request failed) and should not be affected by the enclosing spreadsheet environment or the Python version (same bug is triggered in Python 2.7.13).

However, NeilW's error message is more puzzling, and I will install LO 5.3.7 and look into that.

Thanks desmid, and please, don't worry about stepping on anyone's toes - I think it's great that we're giving something a go in a collaborative way, and even if we can allow Kiloran to see areas where things have been looked at in his absence, that will save some time at the very least, and you might even be able to crack this nut yet, who knows! :O)

Given your routine-alteration worked in my install of the b-f-dev2 HYPTUSS, I just assumed it was for that issue in the absence of any other info, so apologies if I misconstrued your original intentions with the first alteration.

Your feedback regarding the LibreOffice 5.3.7 install, and the errors NeilW has been getting, will be most welcome.

Cheers,

Itsallaguess

[gbalin]

Hi all
I am using the HYPTUSS with grateful thanks, and using the tweaks mentioned on this site for linking to my spreadsheet with my entire portfolio. This has funds in it, and digital look will show the fund but when i put the url into the digital look data sheet, it doesn't show the name of the fund [because there is no ticker?] and just comes up with a price of 0.
I realise this is not really a HYPTUSS question as the HYP bit is suspect for funds (although ...!), but I wonder if any of you clever people could point me the way to go?
GB

[Itsallaguess]

I am using the HYPTUSS with grateful thanks, and using the tweaks mentioned on this site for linking to my spreadsheet with my entire portfolio.

This has funds in it, and digital look will show the fund but when I put the url into the digital look data sheet, it doesn't show the name of the fund [because there is no ticker?] and just comes up with a price of 0.

I realise this is not really a HYPTUSS question as the HYP bit is suspect for funds (although ...!), but I wonder if any of you clever people could point me the way to go?

I think a more pertinent question would be to ask if you could point us towards the name of the fund??



Cheers,

Itsallaguess

[desmid]

Ok,

(1) The:

<class 'UnboundLocalError'>: local variable 'data' referenced before assignment ... in createPriceDict

is caused by a small problem in createPriceDict. It is not Mac-specific - it just depends on how successful the web fetch from Yahoo was and/or the probity of the symbol(s) requested. It's an intermittent problem with OO/LO that happened to show up with Mac users first. Since I added some 'features' in my replacement function, I'd not suggest using that in production as it stands, rather, use the minimally changed code in posting.php?mode=quote&f=27&p=94454#pr94442.

(2) However, that then gets us to NeilW's new error:

<class 'KeyError'>: '^FTSE' ... in writeTrackData

I can't replicate the error in a natural fashion. However, this is the one place in the code where priceDict is explicitly queried with known keys (^FTSE and then ^FTAS) and they happen not to be in the dictionary in his case. I can replicate the error by deleting the key in advance. It looks like another of these intermittent errors caused by a failure of the Yahoo request (my other idea was that the caret ^ might be being filtered out or interpreted somewhere as a regexp anchor). Anyway, so as not to crash your spreadsheet and keep working, I suggest a temporary workaround in the procedure 'writeTrackData'. Replace the lines:

Code: Select all

        trackSheet.getCellByPosition(0,trackRow).Value = float(priceDict['^FTSE'][0])  #FTSE
        trackSheet.getCellByPosition(1,trackRow).Value = float(priceDict['^FTAS'][0])  #FTSE all-share


with

Code: Select all

    try:
        trackSheet.getCellByPosition(0,trackRow).Value = float(priceDict['^FTSE'][0])  #FTSE
        trackSheet.getCellByPosition(1,trackRow).Value = float(priceDict['^FTAS'][0])  #FTSE all-share
    except KeyError:
        messageBox("FT indices not retrieved - please try again later", "Status")


In summary, there is one problem in createPriceDict, resolved with one line to initialize 'data' with an empty string, and a second problem with accessing that dictionary afterwards with a potentially non-existent key, and not trapping that situation.

cheers
desmid

[desmid]

This might interest the developers, or it might not.

While hacking around in the code, suitably protected by overalls and thick goggles, I found it useful to add logging behaviour, as there's no way to use a debugger that I know of while immersed in a OO/LO application, nor do humble 'print' statements go anywhere. The 'logging' module comes with Python (both 2 and 3) and worked well:

Code: Select all

# See: https://docs.python.org/3/howto/logging.html#logging-advanced-tutorial

import logging

LOGFILE = '/home/desmid/TRADE/SOFTWARE/HYPTUSS/output.log'

logger = logging.getLogger('HypTopUp')
logger.setLevel(logging.DEBUG)   #report debug messages but nothing less severe

lh = logging.FileHandler(LOGFILE)
lh.setLevel(logging.DEBUG)  #report debug messages but nothing less severe

fm = logging.Formatter('%(asctime)s %(name)s %(levelname)s - %(message)s')
lh.setFormatter(fm)

logger.addHandler(lh)

#now start producing message like this:
logger.debug("Start")   #there are other 'levels' of severity, like logger.info, logger.warning, and so on


and it makes nice messages like:

Code: Select all

2017-11-09 21:38:09,385 HypTopUp DEBUG - Start
2017-11-09 21:38:10,062 HypTopUp DEBUG - https://query1.finance.yahoo.com/v7/fin
2017-11-09 21:38:11,554 HypTopUp DEBUG - {"quoteResponse":{"result":[{"language" ...
2017-11-09 21:38:11,555 HypTopUp DEBUG - {'^FTAS': ['4108.39', 'GBP']}


which make life somewhat easier.

desmid

[kiloran]

Hi boys and girls!

Profuse apologies for my absence, I had a family bereavement last saturday, so it was a mad panic to get the beta version of the new HYPTUSS published with very little testing and no error trapping and the like, before a 350 mile drive. The worst timing possible after the Yahoo changes. Far from ideal but it's certainly caused some interesting discussion and we've found another python coder lurking in a quiet corner!

I'll be driving back home tomorrow so I hope I'll be able to do some catchup over the weekend, and then implement some fixes, though I'll still be somewhat limited due to ongoing funeral arrangements. At least the worst is over now.

--kiloran

[desmid]

And then there's unit testing. The following code contains the HYPTUSS b-f-dev2 createPriceDict routine and a load of URLs gleaned from these very pages to test the code and reveal deficiencies, or, in future, regressions. Normally one would import the library to be tested, but here I just plonked the target function into the file for illustration.

If this thing is saved as 'test_createPriceDict.py', then at a command line, run one of:

Code: Select all

python3 test_createPriceDict.py
python3 test_createPriceDict.py -v


and see it run through the tests. The second version with the trailing '-v' option adds some verbosity. Some systems might have, or it can be installed, 'nosetests', which is a useful wrapper for trawling through a directory hierarchy running many/all such tests. Here's the unit test. Most of it, in all its hideousness, is various lumps of JSON vomited out by Yahoo, which we use to test the routine.

Code: Select all

###########################################################################
# Unit tests for createPriceDict()
# Yahoo queries/responses as of 2017-11-09
###########################################################################

import unittest
import re

###########################################################################
# test original createPriceDict() with 'data' initialisation bug,
# gives:
#   UnboundLocalError: local variable 'data' referenced before assignment
# in:
#   test_empty, test_garbage
###########################################################################

def createPriceDict_b_f_dev2(html):
    priceDict = {}
    matchObj = re.search( r'.*?\[(.*?)\].*', html)
    if matchObj:
        data = matchObj.group(1)

    while data:
        matchObj = re.search( r'.*?{(.*?)\}(.*)', data)
        if matchObj:
            symbolData = matchObj.group(1)
            symbol = ''
            price = ''
            currency = ''
            for symbolElement in symbolData.split(','):
                symbolElement = symbolElement.replace('"','')
                if 'symbol' in symbolElement:
                    symbol = (symbolElement[symbolElement.index(':')+1:])
                elif 'regularMarketPrice' in symbolElement:
                    price = (symbolElement[symbolElement.index(':')+1:])
                elif 'currency' in symbolElement:
                    currency = (symbolElement[symbolElement.index(':')+1:])

            priceDict[symbol] = [price, currency]

            data = matchObj.group(2)

    return(priceDict)


###########################################################################
class test_cpd_simple(unittest.TestCase):

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=
    DATA_EMPTY_SYMBOL='{"quoteResponse":{"result":[],"error":null}}'

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=GARBAGE
    DATA_UNKNOWN_SYMBOL = '{"quoteResponse":{"result":[],"error":null}}'

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=AAT.L
    DATA_KNOWN_SYMBOL_WRONG_EXCHANGE = '{"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","tradeable":false,"symbol":"AAT.L"}],"error":null}}'

    #convenience wrapper function for the above, called in tests
    @staticmethod
    def createPriceDict(*args, **kwargs):
        return createPriceDict_b_f_dev2(*args, **kwargs)

    def test_empty(self):
        r = self.createPriceDict('')
        self.assertEqual(r, {})

    def test_garbage(self):
        r = self.createPriceDict('garbage')
        self.assertEqual(r, {})

    def test_empty_symbol(self):
        r = self.createPriceDict(self.DATA_EMPTY_SYMBOL)
        self.assertEqual(r, {})

    def test_unknown_symbol(self):
        r = self.createPriceDict(self.DATA_UNKNOWN_SYMBOL)
        self.assertEqual(r, {})

    def test_known_symbol_wrong_exchange(self):
        r = self.createPriceDict(self.DATA_KNOWN_SYMBOL_WRONG_EXCHANGE)
        self.assertEqual(r, {'AAT.L': ['', '']} )


###########################################################################
class test_cpd_share(unittest.TestCase):

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=GSK.L
    DATA_ONE_SHARE = '{"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","quoteSourceName":"Delayed Quote","currency":"GBp","regularMarketChangePercent":0.5582434,"regularMarketPreviousClose":1343.5,"bid":1350.5,"ask":1351.0,"bidSize":1754,"askSize":4179,"messageBoardId":"finmb_275442","fullExchangeName":"LSE","longName":"GlaxoSmithKline plc","financialCurrency":"GBP","averageDailyVolume3Month":8922796,"averageDailyVolume10Day":10966586,"fiftyTwoWeekLowChange":15.0,"fiftyTwoWeekLowChangePercent":0.011227545,"fiftyTwoWeekHighChange":-373.5,"fiftyTwoWeekHighChangePercent":-0.21658452,"fiftyTwoWeekLow":1336.0,"fiftyTwoWeekHigh":1724.5,"earningsTimestamp":1508914800,"earningsTimestampStart":1517828340,"earningsTimestampEnd":1518177600,"trailingAnnualDividendRate":0.8,"trailingPE":34.55243,"trailingAnnualDividendYield":5.9545966E-4,"marketState":"REGULAR","epsTrailingTwelveMonths":39.1,"epsForward":110.48,"sharesOutstanding":4918680064,"bookValue":0.166,"fiftyDayAverage":1462.6,"fiftyDayAverageChange":-111.599976,"fiftyDayAverageChangePercent":-0.07630246,"twoHundredDayAverage":1558.3348,"twoHundredDayAverageChange":-207.33484,"twoHundredDayAverageChangePercent":-0.13304897,"marketCap":66451369984,"market":"gb_market","shortName":"GLAXOSMITHKLINE PLC ORD 25P","exchangeDataDelayedBy":20,"priceHint":2,"exchange":"LSE","forwardPE":0.12228458,"priceToBook":8138.5547,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","gmtOffSetMilliseconds":0,"tradeable":false,"regularMarketPrice":1351.0,"regularMarketTime":1510241147,"regularMarketChange":7.5,"regularMarketOpen":1349.5,"regularMarketDayHigh":1365.0,"regularMarketDayLow":1345.15,"regularMarketVolume":7346447,"symbol":"GSK.L"}],"error":null}}'

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=GSK.L,VOD.L
    DATA_TWO_SHARES = '{"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","quoteSourceName":"Delayed Quote","currency":"GBp","gmtOffSetMilliseconds":0,"fiftyTwoWeekHighChangePercent":-0.3305776,"fiftyTwoWeekLow":178.4,"fiftyTwoWeekHigh":267.32,"fullExchangeName":"LSE","longName":"Barclays PLC","financialCurrency":"GBP","averageDailyVolume3Month":41093009,"forwardPE":0.08242746,"fiftyDayAverage":189.30714,"fiftyDayAverageChange":-10.357147,"fiftyDayAverageChangePercent":-0.05471081,"twoHundredDayAverage":200.38643,"averageDailyVolume10Day":66348256,"fiftyTwoWeekLowChange":0.55000305,"regularMarketPrice":178.95,"regularMarketTime":1510241642,"regularMarketChange":-0.5,"regularMarketOpen":179.45,"regularMarketDayHigh":180.55,"regularMarketDayLow":178.4,"regularMarketVolume":20943903,"regularMarketChangePercent":-0.27862915,"earningsTimestampStart":1469599200,"earningsTimestampEnd":1470045600,"trailingAnnualDividendRate":0.03,"trailingAnnualDividendYield":1.6717748E-4,"regularMarketPreviousClose":179.45,"bid":178.95,"ask":179.0,"bidSize":4233,"askSize":3500,"messageBoardId":"finmb_323899","earningsTimestamp":1508983200,"market":"gb_market","shortName":"BARCLAYS PLC ORD 25P","fiftyTwoWeekLowChangePercent":0.003082977,"fiftyTwoWeekHighChange":-88.37001,"twoHundredDayAverageChange":-21.436432,"twoHundredDayAverageChangePercent":-0.106975466,"marketCap":30500952064,"tradeable":false,"epsTrailingTwelveMonths":-3.2,"epsForward":21.71,"exchange":"LSE","sharesOutstanding":17044400128,"bookValue":3.745,"marketState":"REGULAR","exchangeDataDelayedBy":20,"priceHint":2,"priceToBook":47.78371,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","symbol":"BARC.L"},{"language":"en-US","quoteType":"EQUITY","quoteSourceName":"Delayed Quote","currency":"GBp","gmtOffSetMilliseconds":0,"fiftyTwoWeekHighChangePercent":-0.05536553,"fiftyTwoWeekLow":186.5,"fiftyTwoWeekHigh":233.9,"fullExchangeName":"LSE","longName":"Vodafone Group Plc","financialCurrency":"EUR","averageDailyVolume3Month":53942259,"forwardPE":24.549997,"fiftyDayAverage":213.77715,"fiftyDayAverageChange":7.1728516,"fiftyDayAverageChangePercent":0.03355294,"twoHundredDayAverage":217.56653,"averageDailyVolume10Day":59550585,"fiftyTwoWeekLowChange":34.449997,"regularMarketPrice":220.95,"regularMarketTime":1510241696,"regularMarketChange":5.050003,"regularMarketOpen":216.45,"regularMarketDayHigh":221.2,"regularMarketDayLow":215.95,"regularMarketVolume":52058796,"regularMarketChangePercent":2.3390474,"trailingAnnualDividendRate":0.172,"trailingAnnualDividendYield":7.9666515E-4,"regularMarketPreviousClose":215.9,"bid":220.95,"ask":221.0,"bidSize":0,"askSize":0,"messageBoardId":"finmb_324490","earningsTimestamp":1510648200,"market":"gb_market","shortName":"VODAFONE GROUP PLC ORD USD0.20 ","fiftyTwoWeekLowChangePercent":0.18471849,"fiftyTwoWeekHighChange":-12.949997,"twoHundredDayAverageChange":3.3834686,"twoHundredDayAverageChangePercent":0.015551421,"marketCap":59194269696,"tradeable":false,"epsTrailingTwelveMonths":-26.2,"epsForward":0.09,"exchange":"LSE","sharesOutstanding":26790799360,"bookValue":3.156,"marketState":"REGULAR","exchangeDataDelayedBy":20,"priceHint":2,"priceToBook":70.00951,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","symbol":"VOD.L"}],"error":null}}'

    #convenience wrapper function for the above, called in tests
    @staticmethod
    def createPriceDict(*args, **kwargs):
        return createPriceDict_b_f_dev2(*args, **kwargs)

    def test_one_share(self):
        r = self.createPriceDict(self.DATA_ONE_SHARE)
        self.assertEqual(r, { 'GSK.L': ['1351.0', 'GBp'] })

    def test_two_shares(self):
        r = self.createPriceDict(self.DATA_TWO_SHARES)
        self.assertEqual(r, {
            'BARC.L': ['178.95', 'GBp'],
            'VOD.L':  ['220.95', 'GBp'],
        })


###########################################################################
class test_cpd_index(unittest.TestCase):

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=^FTSE
    DATA_ONE_INDEX = '{"quoteResponse":{"result":[{"language":"en-US","quoteType":"INDEX","currency":"GBP","market":"gb_market","exchangeDataDelayedBy":15,"exchange":"FGI","askSize":0,"messageBoardId":"finmb_INDEXFTSE","fullExchangeName":"FTSE Index","averageDailyVolume3Month":737611713,"averageDailyVolume10Day":799295187,"fiftyTwoWeekLowChange":813.8696,"fiftyTwoWeekLowChangePercent":0.12186048,"fiftyTwoWeekHighChange":-106.430176,"fiftyTwoWeekHighChangePercent":-0.014005814,"fiftyTwoWeekLow":6678.7,"fiftyTwoWeekHigh":7599.0,"fiftyDayAverage":7467.536,"fiftyDayAverageChange":25.033691,"fiftyDayAverageChangePercent":0.0033523361,"twoHundredDayAverage":7422.032,"twoHundredDayAverageChange":70.5376,"twoHundredDayAverageChangePercent":0.009503812,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","gmtOffSetMilliseconds":0,"priceHint":2,"regularMarketChangePercent":-0.4933834,"regularMarketPreviousClose":7529.72,"bid":0.0,"ask":0.0,"bidSize":0,"shortName":"FTSE 100","regularMarketPrice":7492.57,"regularMarketTime":1510241538,"regularMarketChange":-37.15039,"regularMarketOpen":7529.72,"regularMarketDayHigh":7532.2,"regularMarketDayLow":7476.89,"regularMarketVolume":0,"tradeable":false,"marketState":"REGULAR","symbol":"^FTSE"}],"error":null}}'

    #convenience wrapper function for the above, called in tests
    @staticmethod
    def createPriceDict(*args, **kwargs):
        return createPriceDict_b_f_dev2(*args, **kwargs)

    def test_one_index(self):
        r = self.createPriceDict(self.DATA_ONE_INDEX)
        self.assertEqual(r, { '^FTSE': ['7492.57', 'GBP'] })  #NOTE: key is ^FTSE


###########################################################################
class test_cpd_currency_fx(unittest.TestCase):

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=GBPEUR=X
    DATA_ONE_FX = '{"quoteResponse":{"result":[{"quoteType":"CURRENCY","currency":"EUR","regularMarketPrice":1.1277249,"regularMarketTime":1510243440,"regularMarketChange":-0.0033086538,"regularMarketOpen":1.1310632,"regularMarketDayHigh":1.1292582,"regularMarketDayLow":1.1296135,"regularMarketPreviousClose":1.1310335,"regularMarketChangePercent":-0.29253367,"fiftyTwoWeekRange":"1.1258918 - 1.1592116","exchange":"CCY","messageBoardId":"finmb_GBPEUR_X","priceHint":5,"fiftyTwoWeekLow":1.1258918,"fiftyTwoWeekHigh":1.1592116,"shortName":"GBP/EUR","exchangeTimezoneShortName":"GMT","gmtOffSetMilliseconds":0,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","tradeable":false,"marketState":"REGULAR","fullExchangeName":"CCY","market":"ccy_market","exchangeDataDelayedBy":0,"symbol":"GBPEUR=X"}],"error":null}}'

    #convenience wrapper function for the above, called in tests
    @staticmethod
    def createPriceDict(*args, **kwargs):
        return createPriceDict_b_f_dev2(*args, **kwargs)

    def test_one_fx(self):
        r = self.createPriceDict(self.DATA_ONE_FX)
        self.assertEqual(r, { 'GBPEUR=X': ['1.1277249', 'EUR'] })  #NOTE: key is GBPEUR=X


###########################################################################
class test_cpd_miscellaneous(unittest.TestCase):
   
    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=AAL.L,AAL.L
    DATA_SAME_SHARE_REPEATED = '{"quoteResponse":{"result":[{"language":"en-US","quoteType":"EQUITY","currency":"GBp","sharesOutstanding":1399869952,"tradeable":false,"bookValue":16.229,"fiftyDayAverage":1415.2428,"fiftyTwoWeekHighChange":-50.5,"fiftyTwoWeekHighChangePercent":-0.032909743,"fiftyTwoWeekLow":950.1,"twoHundredDayAverageChangePercent":0.21708904,"marketCap":20774070272,"trailingAnnualDividendRate":0.48,"trailingPE":5.059666,"regularMarketChangePercent":-1.2312812,"bid":1440.0,"fiftyDayAverageChange":68.7572,"fiftyDayAverageChangePercent":0.048583325,"twoHundredDayAverage":1219.3027,"twoHundredDayAverageChange":264.69727,"priceHint":2,"regularMarketPreviousClose":1502.5,"ask":1500.0,"bidSize":450,"askSize":650,"messageBoardId":"finmb_409115","epsTrailingTwelveMonths":293.3,"epsForward":1.91,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","gmtOffSetMilliseconds":0,"marketState":"POSTPOST","trailingAnnualDividendYield":3.1946754E-4,"regularMarketPrice":1484.0,"regularMarketTime":1510247699,"regularMarketChange":-18.5,"regularMarketOpen":1498.5,"regularMarketDayHigh":1500.5,"regularMarketDayLow":1457.0,"regularMarketVolume":3333147,"exchange":"LSE","fullExchangeName":"LSE","longName":"Anglo American plc","financialCurrency":"USD","averageDailyVolume3Month":5541003,"averageDailyVolume10Day":5140918,"fiftyTwoWeekHigh":1534.5,"earningsTimestamp":1501120800,"exchangeDataDelayedBy":20,"shortName":"ANGLO AMERICAN PLC ORD USD0.549","forwardPE":7.769634,"priceToBook":91.441246,"market":"gb_market","fiftyTwoWeekLowChange":533.9,"fiftyTwoWeekLowChangePercent":0.5619409,"symbol":"AAL.L"},{"language":"en-US","quoteType":"EQUITY","currency":"GBp","sharesOutstanding":1399869952,"tradeable":false,"bookValue":16.229,"fiftyDayAverage":1415.2428,"fiftyTwoWeekHighChange":-50.5,"fiftyTwoWeekHighChangePercent":-0.032909743,"fiftyTwoWeekLow":950.1,"twoHundredDayAverageChangePercent":0.21708904,"marketCap":20774070272,"trailingAnnualDividendRate":0.48,"trailingPE":5.059666,"regularMarketChangePercent":-1.2312812,"bid":1440.0,"fiftyDayAverageChange":68.7572,"fiftyDayAverageChangePercent":0.048583325,"twoHundredDayAverage":1219.3027,"twoHundredDayAverageChange":264.69727,"priceHint":2,"regularMarketPreviousClose":1502.5,"ask":1500.0,"bidSize":450,"askSize":650,"messageBoardId":"finmb_409115","epsTrailingTwelveMonths":293.3,"epsForward":1.91,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","gmtOffSetMilliseconds":0,"marketState":"POSTPOST","trailingAnnualDividendYield":3.1946754E-4,"regularMarketPrice":1484.0,"regularMarketTime":1510247699,"regularMarketChange":-18.5,"regularMarketOpen":1498.5,"regularMarketDayHigh":1500.5,"regularMarketDayLow":1457.0,"regularMarketVolume":3333147,"exchange":"LSE","fullExchangeName":"LSE","longName":"Anglo American plc","financialCurrency":"USD","averageDailyVolume3Month":5541003,"averageDailyVolume10Day":5140918,"fiftyTwoWeekHigh":1534.5,"earningsTimestamp":1501120800,"exchangeDataDelayedBy":20,"shortName":"ANGLO AMERICAN PLC ORD USD0.549","forwardPE":7.769634,"priceToBook":91.441246,"market":"gb_market","fiftyTwoWeekLowChange":533.9,"fiftyTwoWeekLowChangePercent":0.5619409,"symbol":"AAL.L"}],"error":null}}'

    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=AAL.L,GBPUSD=X
    DATA_MIXED='{"quoteResponse":{"result":[{"language":"en-US","quoteType":"CURRENCY","currency":"USD","regularMarketPreviousClose":1.3116475,"bid":1.315824,"ask":1.3157376,"regularMarketChangePercent":0.31842265,"fiftyTwoWeekLow":1.1995153,"fiftyTwoWeekHigh":1.3615816,"regularMarketPrice":1.315824,"regularMarketChange":0.0041764975,"regularMarketOpen":1.3116819,"regularMarketDayHigh":1.316517,"regularMarketDayLow":1.3086778,"fiftyDayAverage":1.3245234,"twoHundredDayAverage":1.3030384,"shortName":"GBP/USD","bidSize":0,"askSize":0,"messageBoardId":"finmb_GBP_X","fiftyTwoWeekLowChange":0.025539994,"fiftyTwoWeekLowChangePercent":0.021291928,"priceHint":5,"fiftyTwoWeekHighChange":-0.07369,"fiftyTwoWeekHighChangePercent":-0.054120883,"gmtOffSetMilliseconds":0,"exchange":"CCY","twoHundredDayAverageChangePercent":-0.005722838,"tradeable":false,"regularMarketTime":1510257480,"regularMarketVolume":0,"market":"ccy_market","fiftyDayAverageChange":0.004991472,"fiftyDayAverageChangePercent":0.003768504,"twoHundredDayAverageChange":-0.0074570775,"fullExchangeName":"CCY","averageDailyVolume3Month":0,"averageDailyVolume10Day":0,"marketState":"REGULAR","sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","exchangeDataDelayedBy":0,"symbol":"GBPUSD=X"},{"language":"en-US","quoteType":"EQUITY","currency":"GBp","epsTrailingTwelveMonths":293.3,"epsForward":1.91,"regularMarketPreviousClose":1502.5,"bid":1440.0,"ask":1500.0,"bidSize":450,"askSize":650,"messageBoardId":"finmb_409115","regularMarketChangePercent":-1.2312812,"fiftyTwoWeekLowChange":533.9,"fiftyTwoWeekLowChangePercent":0.5619409,"priceHint":2,"fiftyTwoWeekHighChange":-50.5,"fiftyTwoWeekHighChangePercent":-0.032909743,"fiftyTwoWeekLow":950.1,"fiftyTwoWeekHigh":1534.5,"gmtOffSetMilliseconds":0,"trailingAnnualDividendYield":3.1946754E-4,"earningsTimestamp":1501120800,"trailingAnnualDividendRate":0.48,"trailingPE":5.059666,"exchange":"LSE","sharesOutstanding":1399869952,"bookValue":16.229,"twoHundredDayAverageChangePercent":0.21525227,"tradeable":false,"regularMarketPrice":1484.0,"regularMarketTime":1510247699,"regularMarketChange":-18.5,"regularMarketOpen":1498.5,"regularMarketDayHigh":1500.5,"regularMarketDayLow":1457.0,"regularMarketVolume":3333147,"market":"gb_market","fiftyDayAverage":1417.0695,"fiftyDayAverageChange":66.93054,"fiftyDayAverageChangePercent":0.04723166,"twoHundredDayAverage":1221.1456,"twoHundredDayAverageChange":262.85437,"marketCap":20774070272,"shortName":"ANGLO AMERICAN PLC ORD USD0.549","fullExchangeName":"LSE","longName":"Anglo American plc","financialCurrency":"USD","averageDailyVolume3Month":5587442,"averageDailyVolume10Day":5140918,"marketState":"POSTPOST","forwardPE":7.769634,"priceToBook":91.441246,"sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","exchangeDataDelayedBy":20,"symbol":"AAL.L"}],"error":null}}'

    #convenience wrapper function for the above, called in tests
    @staticmethod
    def createPriceDict(*args, **kwargs):
        return createPriceDict_b_f_dev2(*args, **kwargs)

    def test_same_share_repeated(self):
        r = self.createPriceDict(self.DATA_SAME_SHARE_REPEATED)
        self.assertEqual(r, {'AAL.L': ['1484.0', 'GBp'] })

    def test_mixed_query(self):
        r = self.createPriceDict(self.DATA_MIXED)
        self.assertEqual(r, {'AAL.L':    ['1484.0',   'GBp'],
                             'GBPUSD=X': ['1.315824', 'USD'],
                         })


###########################################################################
class test_cpd_known_anomalies(unittest.TestCase):

    # Known anomaly:
    #
    # https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8183&start=60#p93146 (vrdriver)
    #
    # https://query1.finance.yahoo.com/v7/finance/quote?symbols=42TE.L
    DATA_ANOMALY_42TE = '{"quoteResponse":{"result":[{"language":"en-US","quoteType":"BOND","currency":"GBP","regularMarketPreviousClose":129.5,"bid":0.0,"ask":0.0,"bidSize":1000,"askSize":3000,"messageBoardId":"finmb_3433747","tradeable":false,"fiftyTwoWeekHighChangePercent":0.0,"fiftyTwoWeekLow":129.5,"fiftyTwoWeekHigh":129.5,"fullExchangeName":"LSE","longName":"Co-operative Group Limited","fiftyTwoWeekLowChange":0.0,"priceHint":2,"market":"gb_market","marketState":"POSTPOST","earningsTimestamp":1474642740,"exchange":"LSE","sourceInterval":15,"exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"GMT","gmtOffSetMilliseconds":0,"exchangeDataDelayedBy":20,"regularMarketChangePercent":0.0,"shortName":"CO-OPERATIVE GROUP LIMITED 11% ","fiftyTwoWeekLowChangePercent":0.0,"fiftyTwoWeekHighChange":0.0,"regularMarketPrice":129.5,"regularMarketTime":1510245000,"regularMarketChange":0.0,"regularMarketOpen":125.25,"regularMarketDayHigh":129.5,"regularMarketDayLow":129.5,"regularMarketVolume":7099,"symbol":"42TE.L"}],"error":null}}'

    #convenience wrapper function for the above, called in tests
    @staticmethod
    def createPriceDict(*args, **kwargs):
        return createPriceDict_b_f_dev2(*args, **kwargs)

    def test_anomaly_42TE(self):
        r = self.createPriceDict(self.DATA_ANOMALY_42TE)
        self.assertEqual(r, { '42TE.L': ['129.5', 'GBp'] })  #currency reported in GBP not GBp


###########################################################################
if __name__ == '__main__':
    unittest.main()

###########################################################################


There are several tests that fail for one reason or other, thusly:

Code: Select all

$ python3 tests/test_createPriceDict.py -v
test_one_fx (__main__.test_cpd_currency_fx) ... ok
test_one_index (__main__.test_cpd_index) ... ok
test_anomaly_42TE (__main__.test_cpd_known_anomalies) ... FAIL
test_mixed_query (__main__.test_cpd_miscellaneous) ... ok
test_same_share_repeated (__main__.test_cpd_miscellaneous) ... ok
test_one_share (__main__.test_cpd_share) ... ok
test_two_shares (__main__.test_cpd_share) ... ok
test_empty (__main__.test_cpd_simple) ... ERROR
test_empty_symbol (__main__.test_cpd_simple) ... ok
test_garbage (__main__.test_cpd_simple) ... ERROR
test_known_symbol_wrong_exchange (__main__.test_cpd_simple) ... ok
test_unknown_symbol (__main__.test_cpd_simple) ... ok

======================================================================
ERROR: test_empty (__main__.test_cpd_simple)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "tests/test_createPriceDict.py", line 64, in test_empty
    r = self.createPriceDict('')
  File "tests/test_createPriceDict.py", line 61, in createPriceDict
    return createPriceDict_b_f_dev2(*args, **kwargs)
  File "tests/test_createPriceDict.py", line 23, in createPriceDict_b_f_dev2
    while data:
UnboundLocalError: local variable 'data' referenced before assignment

======================================================================
ERROR: test_garbage (__main__.test_cpd_simple)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "tests/test_createPriceDict.py", line 68, in test_garbage
    r = self.createPriceDict('garbage')
  File "tests/test_createPriceDict.py", line 61, in createPriceDict
    return createPriceDict_b_f_dev2(*args, **kwargs)
  File "tests/test_createPriceDict.py", line 23, in createPriceDict_b_f_dev2
    while data:
UnboundLocalError: local variable 'data' referenced before assignment

======================================================================
FAIL: test_anomaly_42TE (__main__.test_cpd_known_anomalies)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "tests/test_createPriceDict.py", line 184, in test_anomaly_42TE
    self.assertEqual(r, { '42TE.L': ['129.5', 'GBp'] })  #currency reported in GBP not GBp
AssertionError: {'42TE.L': ['129.5', 'GBP']} != {'42TE.L': ['129.5', 'GBp']}
- {'42TE.L': ['129.5', 'GBP']}
?                         ^

+ {'42TE.L': ['129.5', 'GBp']}
?                         ^


----------------------------------------------------------------------
Ran 12 tests in 0.004s

FAILED (failures=1, errors=2)


Two of the failures are that unitialized 'data' variable being discovered. Another one is the peculiarity of vrdriver's ticker 42TE, awaiting some way of dealing with it.

Hope it's useful
desmid

[Dochas]

Hi Itsallaguess
Using LibreOffice I obtain exactly the same output as NeilW. Following an earlier suggestion I also loaded ApacheOffice but cannot get the programme to run without the macros being disabled which is obviously no help at all. Sorry about that.
Dochas

[Itsallaguess]

I've uploaded a new version of the LibreOffice/OpenOffice HYPTUSS Beta here, so if anyone has been having issues with the previous Beta files could you please download and test the following b-g-dev3 version, and feedback your findings, then that would be great -

http://www.filedropper.com/hyptop-upversion-b-g-dev3

The above b-g-dev3 Beta release (LibreOffice and OpenOffice) contains the two fixes helpfully suggested by desmid in his earlier posts, and this runs as expected on my set-up.

Fantastic news to hear that kiloran is soon to be back involved, but I think it would be great if we could provide some feedback on the above b-g-dev3 release if possible, from those being affected by the previous issues in LibreOffice, to help give him a good running-start with his own investigations to follow.

Huge thanks again to desmid for his continued help with these issues, it's been great to have him on-board. His comments regarding logs are something I know kiloran was keen to look into, so I'm sure that will also help a great deal.

Can anyone having issues with Mac or Windows implementation of the previous LibreOffice Beta versions please try the above b-g-dev3 Beta and feed back their findings?

Thanks for everyone's patience who have been having issues with the previous Beta release, and I hope that we're making progress towards getting you all back up and running with the tool.

Cheers,

Itsallaguess

[NeilW]

I now get a pop up 'FT indices not retrieved - please try again later', which I 'OK' and then get 'Process finished', but no values have been updated.

Which makes me think that nothing is being retrieved at all by the code (hence the FTSE error). I get the same problem on the Watchlist.

[NeilW]

Any chance of getting the scripts and the tests in a Github repo outside the spreadsheet? Getting to the scripts is turning into a right pain. There is no edit function for python scripts in the Organise Macros dialog - even with the Java JDK installed. So I'm looking at having to rip apart the ODS file.

[desmid]

I now get a pop up 'FT indices not retrieved - please try again later', which I 'OK' and then get 'Process finished', but no values have been updated.

Which makes me think that nothing is being retrieved at all by the code (hence the FTSE error). I get the same problem on the Watchlist.

Indeed! Sadly, my mod was only a work around to warn of an issue rather than crashing. The offending URL inside HYPTUSS is:

Code: Select all

https://query1.finance.yahoo.com/v7/finance/quote?symbols=^FTSE,^FTAS


What do you get from a browser on that exact same device? If you can paste it here that might help.

desmid

[desmid]

Any chance of getting the scripts and the tests in a Github repo outside the spreadsheet? Getting to the scripts is turning into a right pain. There is no edit function for python scripts in the Organise Macros dialog - even with the Java JDK installed. So I'm looking at having to rip apart the ODS file.

Having it on GitHub would be great, and then istallaguess and kiloran will be absolutely inundated with pull requests. But I second the idea.

In the meantime:

Code: Select all

#unpack
unzip hyptuss.ods -d ./somedir

#only one file to look at: ./somedir/Scripts/python/HypTopUp.py
#copy this file to somewhere outside somedir

#EDIT
#do something drastic in the copy
#overwrite the real file with your modified copy

#pack
(cd ./somedir; zip -r ../test.ods *) && soffice test.ods

#test in LO
#goto EDIT
#done: never get here


LO is fussy about the files it finds in the zipfile (they must match the manifest therein) and will complain about a 'corrupted' spreadsheet if not. That's why I recommend editing an external copy of the work. If you get the corruption, just wipe the folder and rebuild it with a fresh unzip and again overwrite HypTopUp.py and pack again.

With that in place, you might like to add the logging code in a message above (obviously setting LOGFILE to somewhere on your machine) at the top of the file near the other imports, add a few logger.debug(stuff) calls inside writeTrackData() to print out the html returned by getHtml() and the contents of priceDict. Then we'll have a better idea of what's up.

desmid