HYPTUS price lookup problem

[spiderbill]

Anyone else seeing errors using HYPTUS today?

When I attempt to get the latest prices I get a long error message, the first few lines being

com.sun.star.uno.RuntimeException: Error during invoking function HypUpdPriPy in module vnd.sun.star.tdoc:/3/Scripts/python/HypTopUp.py (<class 'urllib.error.URLError'>: <urlopen error unknown url type: https>
File "C:\Program Files\LibreOffice 5\program\pythonscript.py", line 870, in invoke
ret = self.func( *args )
File "vnd.sun.star.tdoc:/3/Scripts/python/HypTopUp.py", line 827, in HypUpdPriPy
File "vnd.sun.star.tdoc:/3/Scripts/python/HypTopUp.py", line 500, in getHtml
File "C:\Program Files\LibreOffice 5\program\python-core-3.3.0\lib\urllib\request.py", line 156, in urlopen
return opener.open(url, data, timeout)

Running the latest version on LibreOffice Version: 5.3.2.2 (x64) on Win 10

I run three separate versions (2 are for various types of watchlist that I prefer to have separately) and the problem is occuring on all three. Was working ok yesterday. Of course it could easily be a Yahoo problem - those are not exactly rare!

TIA

Spiderbill

[kiloran]

Yes, I can see the problem too, though it was working fine yesterday while I was doing a load of testing. The Excel version appears to be OK.

I'll look at it over the next few hours

--kiloran

[kiloran]

In fact the same problem affects every single one of my OpenOffice spreadsheets, so it's not specific to HYPTUSS. It's the same with all versions of OpenOffice and LibreOffice so it does sound like a Yahoo issue. Does seem odd that Excel is OK, so It would appear to be some new incompatibility between Yahoo and OpenOffice/LibreOffice python.

Investigations continue

--kiloran

[mike]

I use the open office LivePrice which I believe also scrapes yahoo, and can report no problem there if that's any help.

W7, OO 4.1.3
Mike

[kiloran]

I use the open office LivePrice which I believe also scrapes yahoo, and can report no problem there if that's any help.

W7, OO 4.1.3
Mike

Yes, same here, Mike. The difference is that LivePrice uses an OpenOffice Basic macro to download data from Yahoo. Most other tools such as HYPTUSS use Python macros and these exhibit the failure.

I tried running a copy of the HYPTUSS macro in plain vanilla Python and it works fine, so it seems to be related to the OpenOffice/LibreOffice implementation

I've tried various tricks but no success so far. My brain is hurting already.

--kiloran

[kiloran]

HYPTUSS price quote does work normally using Linux, so it appears to be something related to Windows.

--kiloran

[kiloran]

This is looking like a hard nut to crack!
It worked yesterday. It doesn't work today, yet there have been no changes to the OS (Windows 7) nor any of the software on my laptop.

It works using Linux on the same laptop.

I tried Windows 10 on the laptop, with OpenOffice, and it worked fine the first time, but resolutely failed on further attempts.

Googling the error message suggests that it may be an SSL (Secure Sockets Layer) protocol problem, a topic I know absolutely nothing about. Yet!

A possible workaround for the moment...
The HYPTUSS still has the original Basic macros which were used prior to version a-n in Sep 2013, and the price update Basic macro still seems to work.
To run it with LibreOffice:

1. Select the "High Yield Portfolio" sheet
2. TOOLS/MACROS/RUN MACRO
3. Hyp Top-up version xxx / Standard/HypUpdPri/UpdatePrice ...... RUN

Hopefully that will work for the moment

--kiloran

[Breelander]

This is looking like a hard nut to crack!
It worked yesterday. It doesn't work today, yet there have been no changes to the OS (Windows 7) nor any of the software on my laptop.

Worrying, so I have just tried mine which has always worked before. Win10, LibreOffice Version: 4.3.5.2, HYPTUSS b-b

and... it worked normally. Got prices and yields. Go figure.

PS: also work in OfficeLibre Version: 5.0.1.2

[Breelander]

PPS: Also works with LibreOffice Version: 5.3.3.2

All my copies of LibreOffice are the Portable Apps versions.

[Dochas]

Hi Kiloran
I am using hyp_top-up_version-b-c-2.ods on a mac OS Sierra 10.12.2 and see a similar problem when updating prices. It began Tuesday May 28. All was OK on Sunday May 26. So it is not just a windows issue.
Updating Dividends works. Digital Look forecast yields also works. So it seems to be a yahoo problem
Dochas

[NeilW]

Same problem here on a Mac with LibreOffice. It's an SSL certificate verification error.

[NeilW]

Error is:

com.sun.star.uno.RuntimeException: Error during invoking function HypUpdPriPy in module
vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py (<class 'urllib.error.URLError'>: <urlopen error
[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:720)>
  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 503, in HypUpdPriPy

[spiderbill]

Hi Kiloran

Thanks for looking at it so quickly. The workaround on the old macros works for now so that's good.

I did wonder when I saw the line in the error message <urlopen error unknown url type: https> if it was that Yahoo had switched one of their pages from http to https and maybe there was a hard coded requirement for http in the macros, but I'm no Python programmer so I could easily be wrong. When Bree reported his set-up was working ok I discounted that but NeilW's different error message (thanks Neil) from his Mac setup seems to confirm that it it is related to the https arrangements - maybe Yahoo have misconfirgured their certificate?

cheers
Spiderbill

[NeilW]

The URL has been redirected to an https: url

Hypertext Transfer Protocol
    HTTP/1.1 301 Moved Permanently
    Date: Thu, 01 Jun 2017 09:16:51 GMT
    Via: http/1.1 media-router19.prod.media.ir2.yahoo.com (ApacheTrafficServer [c s f ]), http/1.1 e14.ycpi.lob.yahoo.com (ApacheTrafficServer [cMsSfW])
    Server: ATS
    Set-Cookie: B=4shf7lpcivms3&b=3&s=3u; expires=Fri, 01-Jun-2018 09:16:51 GMT; path=/; domain=.yahoo.com
    Location: https://finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv

[spiderbill]

The URL has been redirected to an https: url

Aha! Well spotted Neil.

[NeilW]

Haha. Then it redirects again to another URL


The document you requested has moved to a new location. The new location is "http://download.finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv"

[NeilW]

So if we change 'quote.yahoo.com' to 'download.finance.yahoo.com' we get to avoid a mad chain of redirects including a dip in and out of an SSL link.


neil@xenial-vm:~$ curl --verbose -L "http://quote.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv"
* Trying 217.12.15.37...
* Connected to quote.yahoo.com (217.12.15.37) port 80 (#0)
> GET /d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv HTTP/1.1
> Host: quote.yahoo.com
> User-Agent: curl/7.47.0
> Accept: */*
>
< HTTP/1.1 301 Redirect
< Date: Thu, 01 Jun 2017 09:48:38 GMT
< Connection: keep-alive
< Via: http/1.1 media-router-rc2.prod.media.ir2.yahoo.com (ApacheTrafficServer [c s f ])
< Server: ATS
< Cache-Control: no-store
< Location: http://finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv
< Content-Type: text/html
< Content-Language: en
< Content-Length: 346
<
* Ignoring the response-body
* Connection #0 to host quote.yahoo.com left intact
* Issue another request to this URL: 'http://finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv'
* Trying 87.248.116.11...
* Connected to finance.yahoo.com (87.248.116.11) port 80 (#1)
> GET /d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv HTTP/1.1
> Host: finance.yahoo.com
> User-Agent: curl/7.47.0
> Accept: */*
>
< HTTP/1.1 301 Moved Permanently
< Date: Thu, 01 Jun 2017 09:48:38 GMT
< Via: http/1.1 media-router42.prod.media.ir2.yahoo.com (ApacheTrafficServer [c s f ]), http/1.1 e34.ycpi.amb.yahoo.com (ApacheTrafficServer [cMsSfW])
< Server: ATS
< Location: https://finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv
< Content-Length: 0
< Age: 0
< Connection: keep-alive
<
* Connection #1 to host finance.yahoo.com left intact
* Issue another request to this URL: 'https://finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv'
* Found bundle for host finance.yahoo.com: 0x56192c533f60 [can pipeline]
* Trying 87.248.116.11...
* Connected to finance.yahoo.com (87.248.116.11) port 443 (#2)
* found 173 certificates in /etc/ssl/certs/ca-certificates.crt
* found 697 certificates in /etc/ssl/certs
* ALPN, offering http/1.1
* SSL connection using TLS1.2 / ECDHE_RSA_AES_128_GCM_SHA256
* server certificate verification OK
* server certificate status verification SKIPPED
* common name: *.yahoo.com (matched)
* server certificate expiration date OK
* server certificate activation date OK
* certificate public key: RSA
* certificate version: #3
* subject: C=US,ST=CA,L=Sunnyvale,O=Yahoo! Inc.,CN=*.yahoo.com
* start date: Tue, 09 May 2017 00:00:00 GMT
* expire date: Thu, 22 Jun 2017 12:00:00 GMT
* issuer: C=US,O=DigiCert Inc,OU=www.digicert.com,CN=DigiCert SHA2 High Assurance Server CA
* compression: NULL
* ALPN, server accepted to use http/1.1
> GET /d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv HTTP/1.1
> Host: finance.yahoo.com
> User-Agent: curl/7.47.0
> Accept: */*
>
< HTTP/1.1 301 Redirect
< Date: Thu, 01 Jun 2017 09:48:38 GMT
< Strict-Transport-Security: max-age=0
< Via: http/1.1 media-router53.prod.media.ir2.yahoo.com (ApacheTrafficServer [c s f ]), https/1.1 e1.ycpi.amb.yahoo.com (ApacheTrafficServer [cMsSf ])
< Server: ATS
< Cache-Control: no-store
< Location: http://download.finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv
< Content-Type: text/html
< Content-Language: en
< Content-Length: 355
< Age: 2
< Connection: keep-alive
<
* Ignoring the response-body
* Connection #2 to host finance.yahoo.com left intact
* Issue another request to this URL: 'http://download.finance.yahoo.com/d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv'
* Trying 87.248.116.11...
* Connected to download.finance.yahoo.com (87.248.116.11) port 80 (#3)
> GET /d/quotes.csv?s=^FTSE,^FTAS&f=sl1&e=.csv HTTP/1.1
> Host: download.finance.yahoo.com
> User-Agent: curl/7.47.0
> Accept: */*
>
< HTTP/1.1 200 OK
< Date: Thu, 01 Jun 2017 09:48:39 GMT
< P3P: policyref="https://policies.yahoo.com/w3c/p3p.xml", CP="CAO DSP COR CUR ADM DEV TAI PSA PSD IVAi IVDi CONi TELo OTPi OUR DELi SAMi OTRi UNRi PUBi IND PHY ONL UNI PUR FIN COM NAV INT DEM CNT STA POL HEA PRE LOC GOV"
< Cache-Control: private, no-cache, no-store
< Content-Length: 32
< Content-Type: application/octet-stream
< Age: 3
< Via: http/1.1 media-router-api5.prod.media.ir2.yahoo.com (ApacheTrafficServer [cMsSf ]), http/1.1 e16.ycpi.amb.yahoo.com (ApacheTrafficServer [cMsSf ])
< Server: ATS
< Connection: keep-alive
<
"^FTSE",7542.37
"^FTAS",4124.90

[kiloran]

Well, I think NeilW has cracked it, which is good because my head was starting to spin with this one.

The macros download the data using a URL like: http://quote.yahoo.com/d/quotes.csv?s=BP.L&f=sl1&e=.csv
and this is exhibiting the problem.

Changing the URL to: http://download.finance.yahoo.com/d/quo ... sl1&e=.csv
appears to solve the problem. I'll do more testing.

What is addling my brain:

1. Why does the Basic script work on the original URL yet Python now doesn't?
2. Why does it work on Linux Mint and Ubuntu, yet not on Windows 7 or Windows 10?
3. Why did it work when I first tried it on Windows 10, yet failed on the second and subsequent attempts just seconds later?
4. Why does it work when I run the macro using the Python installation embedded within LibreOffice and OpenOffice, yet fails when I run the macro from within LibreOffice and OpenOffice which uses exactly the same Python?

And that's just for starters. I suspect some form of black magic is involved and it's best not to probe too deeply lest I release some malign spirits.

Anyway, I'll test this further and hopefully release a new version today or tomorrow.
Many many thanks for all the great feedback

--kiloran

[Breelander]

I suspect some form of black magic is involved and it's best not to probe too deeply lest I release some malign spirits.

I'm feeling left out - I can't reproduce this problem whatever I try

Win10 15063.332, any version of Libre Office Portable, any HYPTUSS up to and including a freshly downloaded b-c-2.

[kiloran]

I'm feeling left out - I can't reproduce this problem whatever I try
Win10 15063.332, any version of Libre Office Portable, any HYPTUSS up to and including a freshly downloaded b-c-2.

I can absolutely guarantee that when I release the update, it will work fine for all of us. Except you

--kiloran