HYPTUS v11.31 Run-time error 5

[biffinsbridge]

Thankyou very much Itsallaguess for the new beta version.
I have it up & running perfectly after a couple of trys!!
Best regards,
Biffinsbridge.

[NeilW]

Perhaps the easiest way around this is to catch the exception if it occurs and retry with an unverified context, so that only broken CA cert databases proceed unverified.

https://www.python.org/dev/peps/pep-047 ... patibility

[NeilW]

Where are you seeing this, Neil? I'm not sure which message you are replying to. It does sound rather like the bug I reported to LibreOffice. I have a work-around for Windows, don't know if it might apply to a Mac

From the HYPTUSS log file which I was about to mail, but I'll paste here.

17:37:48 Line: 602 getHtml started for url https://query1.finance.yahoo.com/v7/fin ... EUR=X,BP.L
17:37:48 Line: 617 Starting attempt 1 webTimeOut = 10
17:37:48 Line: 659 Exception e = <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:719)>
17:37:48 Line: 617 Starting attempt 2 webTimeOut = 20
17:37:48 Line: 659 Exception e = <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:719)>
17:37:48 Line: 617 Starting attempt 3 webTimeOut = 40
17:37:48 Line: 659 Exception e = <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:719)>
17:37:48 Line: 617 Starting attempt 4 webTimeOut = 80
17:37:48 Line: 659 Exception e = <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:719)>
17:37:48 Line: 617 Starting attempt 5 webTimeOut = 160
17:37:48 Line: 659 Exception e = <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:719)>

[mc2fool]

In both my Excel VB code and my Perl scripts I'm using just plain http://query1.finance.yahoo.com/v7/etc and it's all working fine so you might well be able to avoid any SSL issues by simply not using SSL (https)....

[desmid]

Perhaps the easiest way around this is to catch the exception if it occurs and retry with an unverified context, so that only broken CA cert databases proceed unverified.

Marvellous...

1. Sometimes I'm glad I don't do this lark for a living... That goes too deep in the wrong direction for me. Interested to see if that ignore context thing works, but mcfool's suggestion (http protocol works fine for now; only Yahoo urls with https in the code anyway) is the one I'd go with.

2. Kiloran - you've regressed the (downstream of this) bug in createPriceDict() lines 680/686, data used before initialisation (when the re.search fails to match). It'll bite again one day!

desmid

[NeilW]

Looks like the http request is indeed being answered directly rather than via a redirect to the SSL url. So that would solve the issue and we get to kick the can until they make SSL compulsory

$ curl -v http://query1.finance.yahoo.com/v7/fina ... FTSE,^FTAS
*   Trying 87.248.114.12...
* Connected to query1.finance.yahoo.com (87.248.114.12) port 80 (#0)
> GET /v7/finance/quote?symbols=^FTSE,^FTAS HTTP/1.1
> Host: query1.finance.yahoo.com
> User-Agent: curl/7.47.0
> Accept: */*
> 
< HTTP/1.1 200 OK
< X-Content-Type-Options: nosniff
< X-Yahoo-Request-Id: cf9has9d0it9c
< Cache-Control: max-age=1, stale-while-revalidate=15
< Content-Type: application/json; charset=utf-8
< Vary: Origin
< Content-Length: 2502
< Date: Mon, 13 Nov 2017 10:34:20 GMT
< Age: 0
...

[kiloran]

Many thanks to NeilW and mike for runing the b-f-dev3 version and providing the logfile results. This looks for all the world like the LibreOffice bug (and apparently OpenOffice also) that I uncovered a few months ago when I was rebuilding the Week Ahead functionality.

When LibreOffice sees an https url, it calls an SSL library, and it should use the SSL library installed with LibreOffice. Due to the bug, LibreOffice may find another SSL library installed by other software or by a LibreOffice extension, instead of the built-in LibreOffice SSL library.
In the case of Yahoo, as pointed out by mc2fool and NeilW, it looks like we can replace the https url with an http url and therefore bypass the bug (this wasn't possible with the Week Ahead stuff, I seem to remember). I'll look at this later today and hopefully publish an updated version.

You may find that you can get around the bug by running LibreOffice in Safe Mode (I don't think OpenOffice has a Safe Mode):

1. Open LibreOffice Calc (a new document, not HYPTUSS)
2. "HELP/RESTART IN SAFE MODE", then Restart
3. When the new window pops up, click "Continue in Safe Mode"
4. You will get a new Calc document, but macros will be disabled.
5. "TOOLS/OPTIONS/LIBREOFFICE/SECURITY/MACRO SECURITY" ... set to medium
6. "FILE/OPEN" and select your HYPTUSS
7. Run the Yahoo Price Update and let us know if it now works

With a bit of luck, we'll soon have this bug beaten into submission

--kiloran

[Breelander]

...This looks for all the world like the LibreOffice bug (and apparently OpenOffice also) that I uncovered a few months ago when I was rebuilding the Week Ahead functionality.

When LibreOffice sees an https url, it calls an SSL library, and it should use the SSL library installed with LibreOffice. Due to the bug, LibreOffice may find another SSL library installed by other software or by a LibreOffice extension, instead of the built-in LibreOffice SSL library...

...that may also explain why I don't see the bug - I use LibreOffice Portable, it's not installed as such, so it's unlikely to see 'other installed software'.

[Whatsup]

Dear Kiloran

re yohyr instructions

To change the macro level on MAC it is under Preferences, Security in the Libreoffice menu not the tools menu

Unfortunately that still does not work giving the same error when trying to download the prices

Regards

Colin

[kiloran]

interestingly having tried to update forecasts yields it does it but at he end of the procedure gives the following message

com.sun.star.uno.RuntimeException: Error during invoking function HypFcstYldPy in module vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py (<class 'UnboundLocalError'>: local variable 'data' referenced before assignment
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 1148, in HypFcstYldPy
File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 1292, in HypFcstYldPy_1
File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 522, in writeTrackData
File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 660, in createPriceDict
)

Regards
Colin

Yes, that's understandable, Colin. When updating Forecasts, it tries to grab FTSE index values from Yahoo and update the tracking sheet. It's the same problem as updating Yahoo prices.

--kiloran

[kiloran]

Hi GN100,

If you download eventide's 'GETDATA' add-in for Excel from here -

https://www.lemonfool.co.uk/viewtopic.p ... 190#p93095

As ever, in awe of all your work and expertise. Bear with me if this is a stupid question, but is there a similar add-on/plugin for LibreOFfice?

GB

I've vaguely pondered this in the past, and following eventide's brilliant work, I definitely want to have a look at this in an idle moment. But right now, there are NO idle moments, not even an idle microsecond. Maybe one to look at when I can no longer stand the rubbish on the telly at christmas!

--kiloran

[kiloran]

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.

I'll ponder that at some point, though I'm not convinced by the benefits. If you do want to play with the code, this is my modus operandi (on Windows 7)

1. open the HYPTUSS as an archive (I use 7-zip), navigate to Scripts/python and extract HypTopUp.py to folder X on your desktop (see below). Close the archive
2. Edit HypTopUp.py using your favourite text editor and save the changes (no need to close)
3. Open HYPTUSS, then TOOLS/MACROS/RUN MACROS. Expand My Macros, and you should see HypTopUp. Click on this, then run the macro you want
4. When you are happy, open the HYPTUSS as an archive again, and replace the HypTopUp.py in the archive with your desktop copy. DO NOT CHANGE THE NAME. HypTopUp.py and it's case-sensitive
5. Close the archive and you are good to go

In step 2, you can name the file whatever you want, and use the renamed file in step 3.
Personally, in step 4, I drag the renamed version into the archive, delete the HypTopUp.py in the archive, then rename the new file.

Location for Folder X:
On windows, this is C:\Users\YourAccount\AppData\Roaming\LibreOffice\4\user\Scripts\python
You may have to create the \Scripts\python folder, and it is CASE SENSITIVE

I'm sure a bit of Googling will reveal the equivalent location for a Mac or Linux

--kiloran

[kiloran]

2. Kiloran - you've regressed the (downstream of this) bug in createPriceDict() lines 680/686, data used before initialisation (when the re.search fails to match). It'll bite again one day!

desmid

Not sure what you mean, desmid, I haven't changed this bit of code.
Perhaps you are referring to the change you suggested in createPriceDict, if the regex fails so data is not initialised. In which, I'm working on it. As I mentioned, the original code was published in a real rush and I had no time to add any error trapping and suchlike.

Hopefully I'll have a new update later today which resolves this

--kiloran

[kiloran]

...This looks for all the world like the LibreOffice bug (and apparently OpenOffice also) that I uncovered a few months ago when I was rebuilding the Week Ahead functionality.

When LibreOffice sees an https url, it calls an SSL library, and it should use the SSL library installed with LibreOffice. Due to the bug, LibreOffice may find another SSL library installed by other software or by a LibreOffice extension, instead of the built-in LibreOffice SSL library...

...that may also explain why I don't see the bug - I use LibreOffice Portable, it's not installed as such, so it's unlikely to see 'other installed software'.

The portable versions aren't immune to the problems, Bree. I normally use the desktop version of LibreOffice, but I also have many versions in portable format (and also OpenOffice portable). When I first encountered the bug, the portable versions also exhibited the problem. Various flavours of LO 5 failed, all of the LO 4 versions were OK.

It seems to be other bits of software on a PC which reveals the bug, which explains why some people see the bug and others don't

--kiloran

[kiloran]

I've published a new version for LibreOffice/OpenOffice (LO/OO)... Hyp Top-Up Version-b-f-dev4.ods
It's available at: http://lemonfoolfinancialsoftware.weebl ... _page.html.

I've changed the URLs for price retrieval from Yahoo, from https to http. It does seem to me that the SSL problem with LO/OO has not been fixed, but this may be an acceptable workaround. I've also implemented more error trapping so if there is a problem, there should be a soft landing rather than throwing all of its toys out of the pram and collapsing in an ungainly heap.

For those of you with ongoing problems, can you please test it and report back? For the rest of you, there have been no fundamental changes, but if you have the time it would be good if you could test it anyway to make sure I haven't broken anything else.

I still haven't managed to read and understand all that desmid has written (apologies!) but I have all the posts on my to-do list, I suspect I can learn a lot from you.

--kiloran

[JMN2]

Kiloran, brings in the prices in the latest still version of Libreoffice, no error messages here. Cheers!

[kiloran]

Kiloran, brings in the prices in the latest still version of Libreoffice, no error messages here. Cheers!

My memory fails me, and I don't have time to go through all the previous posts..... did it work for you previously, or has this fixed your problem?

--kiloran

[desmid]

2. Kiloran - you've regressed the (downstream of this) bug in createPriceDict() lines 680/686, data used before initialisation (when the re.search fails to match). It'll bite again one day!

desmid

Not sure what you mean, desmid, I haven't changed this bit of code.
Perhaps you are referring to the change you suggested in createPriceDict, if the regex fails so data is not initialised. In which, I'm working on it. As I mentioned, the original code was published in a real rush and I had no time to add any error trapping and suchlike.

Hopefully I'll have a new update later today which resolves this

--kiloran

[EDIT: I just saw you made the change in b-f-dev4 - great! - still, the following might be useful, or not for some.]

I think there's a misunderstanding. It's not intended to be critical, and apologies if I sound that way. I'm tremendously grateful for your work on all this and your SimpleYahooPriceScape was the intro I needed to get started on my own spreadsheets. Do you know, I'd no idea how even to add a button to a spreadsheet? - never even done it before.

The issue with the uninitialised variable is just a (common, everyone does it, I do it) syntax error in that routine. The variable 'data' is used in the loop, but it only comes into existence if the regular expression matches in one side of a branch:

Code: Select all

    matchObj = re.search( r'.*?\[(.*?)\].*', html)
    #data = ''   #initialise in case re.search fails
    if matchObj:
        data = matchObj.group(1)
        
    while data:
        do stuff with data

The other, implicit side of the branch is when the re.search fails, and then 'data' simply never comes into being, yet the loop tries to evaluate it and throws a fatal exception. One fix is the one-liner commented out above (or make the second branch explicit and initialise data inside that, but that's two lines, or return the empty dict - that'd be ok too.)

Why does Python behave this way? Not sure - I'm just reading this at the mo': https://eli.thegreenplace.net/2011/05/1 ... -in-python

Ideally createPriceDict should receive valid data - that seems to be your angle - quite right! , but it cannot be guaranteed. What is valid data anyway? Well, a piece of JSON, yet createPriceDict is the very thing that tries to parse JSON and currently crashes (UnBoundLocalError - this should not happen) because of a tiny, easily made, easily spotted and reversed, syntax error.

Again, HYPTUSS is great - thank you for making it.

Also, sorry for being so long-winded.

desmid

[JMN2]

Kiloran, brings in the prices in the latest still version of Libreoffice, no error messages here. Cheers!

My memory fails me, and I don't have time to go through all the previous posts..... did it work for you previously, or has this fixed your problem?

--kiloran

Did not work for me previously. Prices and yields work now.

[desmid]

...
I still haven't managed to read and understand all that desmid has written (apologies!) but I have all the posts on my to-do list, I suspect I can learn a lot from you.

--kiloran

Likewise - quite apart from all the HYP knowledge, I just looked into the Excel version using 'oletools' to dump out the VBA - I've never seen it before and wouldn't know where to begin. It's quite a feat writing and maintaining two versions of a program in utterly different languages - hats off!

When I posted about logging, I'd not at that point noticed you'd already built some infrastructure to do that. My suggestion was to use the built-in 'logging' module, which is just as standard a python component as, say 'os' or 'sys' or 're' and one wouldn't normally want to re-implement those.

Similarly, 'unittest' is standard, batteries included, and all the kids these days use that. I wish I'd known about it years ago. They look utterly daft and excruciatingly boring, but the ease and confidence with which one can then develop new, or modify old forgotten, code is quite extraordinary. Not a panacea because you still need integration testing and suchlike, but powerful nonetheless.

My two penn'orth

desmid