HYPTUS v11.31 Run-time error 5

[kiloran]

Hargreaves Lansdown seems to confirm the price for 42TE as being £126.25 (Sell) and £131 (Buy), which would make the mid-price around £128.625 -

Itsallaguess

I just did a quick check of a bunch of websites and those that show data for 42TE all show a price around £128.
Maybe vrdriver is far richer than he realised! Which pub do we meet at to celebrate?

--kiloran

[SDN123]

Windows 10 Enterprise
Excel 2016 32-bit

All seems to work well.

Outstanding job guys, thanks.

[GN100]

Try the following tickers on the new feed sources GN100 -

GBPEUR=X

Currently shows as 1.125814

GBPUSD=X

Currently shows as 1.3075656

Cheers,

Itsallaguess

Thanks for the prompt reply but I can't get those to work. I really don't know what to try them in as they don't fit into the new Beta HYPTUS. I appreciate that you guys have got more than enough work on your hands at the moment, so let's leave it for now. Maybe it could be incorporated later in to the HYPTUS when everything has settled down.

[kiloran]

Try the following tickers on the new feed sources GN100 -

GBPEUR=X

Currently shows as 1.125814

GBPUSD=X

Currently shows as 1.3075656

Cheers,

Itsallaguess

Thanks for the prompt reply but I can't get those to work. I really don't know what to try them in as they don't fit into the new Beta HYPTUS. I appreciate that you guys have got more than enough work on your hands at the moment, so let's leave it for now. Maybe it could be incorporated later in to the HYPTUS when everything has settled down.

Try this: https://query1.finance.yahoo.com/v7/fin ... X,GBPEUR=X

--kiloran

[mc2fool]

Try this: https://query1.finance.yahoo.com/v7/fin ... X,GBPEUR=X

--kiloran

For those that need/like to pore over JSON, I've found this site pretty useful: http://jsonviewer.stack.hu

E.g. http://jsonviewer.stack.hu/#http://query1.finance.yahoo.com/v7/finance/quote?symbols=GBPUSD=X,GBPEUR=X

[Itsallaguess]

Try the following tickers on the new feed sources GN100 -

GBPEUR=X

Currently shows as 1.125814

GBPUSD=X

Currently shows as 1.3075656

Cheers,

Itsallaguess

Thanks for the prompt reply but I can't get those to work.

I really don't know what to try them in as they don't fit into the new Beta HYPTUS.

I appreciate that you guys have got more than enough work on your hands at the moment, so let's leave it for now. Maybe it could be incorporated later in to the HYPTUS when everything has settled down.

Hi GN100,

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

viewtopic.php?p=93190#p93095

You can then use the add-in to retrieve any quote you like, including the above two currency levels.

For the two currency levels above, you'd just enter the following into two cells on a spreadsheet -

=getdata("GBPEUR=X")

=getdata("GBPUSD=X")

Doing so initially, with the add-in activated, will show the cells containing the following temporary text -

::queued::

If you then right-click your mouse, the add-in should have given you an additional context-menu option that says this -

TLF Refresh

If you select that option, the add-in will retrieve the appropriate quote data, and fill in the cells with the relevant information as explained at the bottom of eventide's post on the linked thread above.

Leaving out the second 'field' selection option, as we've done with the above two examples, will simply retrieve the 'last price', which may or may not be suitable for your needs.

The following options are also available for the second 'field' options if required (from the above linked post) -

Notes on Field Selection (case insensitive) in the function
1, "nm", "name": RETURNS SECURITY NAME
2, "b", "bid": RETURNS BID PRICE
3, "a", "ask", "offer": RETURNS ASK PRICE
4, "l", "last": RETURNS LAST PRICE
5, "c", "chg", "change": RETURNS CHANGE
6, "prvcls", "close", "previousclose": RETURNS PREVIOUS CLOSE
7, "daylow", "dl": RETURNS LOW OF DAY
8, "dayhigh", "dh": RETURNS HIGH OF DAY
9, "52wk_low", "52wl": RETURNS 52 WEEK CLOSING LOW
10, "52wk_high", "52wh" RETURNS 52 WEEK CLOSING HIGH
11, "delay", "exchangedelay": RETURNS EXCHANGE DELAY IN MINUTES
12, "ccy", "currency": RETURNS CCY OF QUOTES (GBP, GBp, USD etc)
13, "type", "quotetype": RETURNS SECURITY TYPE (STOCK, CURRENCY, INDEX, FUTURE ETC)

So, for instance, if we wanted to get the 'bid' price for Vodafone, we'd use the following in a cell on your spreadsheet with the downloaded 'Getdata' add-in -

=getdata("VOD.L", "b")

which currently returns the figure "212.5" after a 'TLF Refresh' process has been carried out.

Hope the above helps you, and also anyone else that would like to use the fantastic, and very versatile 'getdata' add-in from the above link in their own spreadsheets.

It can be used completely separately from the HYPTUSS tool, so please give it a go and see if it helps.

It can handle a huge amount of tickers, in many different cells on your spreadsheet, and is lightning fast when retrieving the price data, so anyone wanting to carry out this type of price-retrieval will do well to give the above a go and familiarise themselves with the fairly simple process.

Cheers,

Itsallaguess

[Itsallaguess]

Try this: https://query1.finance.yahoo.com/v7/fin ... X,GBPEUR=X

For those that need/like to pore over JSON, I've found this site pretty useful: http://jsonviewer.stack.hu

E.g. http://jsonviewer.stack.hu/#http://query1.finance.yahoo.com/v7/finance/quote?symbols=GBPUSD=X,GBPEUR=X

That's a very useful link mc2fool, especially for testing/diagnostic purposes, so thanks very much for letting us know about it.

Cheers,

Itsallaguess

[NeilW]

Downloaded the Libreoffice version and run it here on 5.3.3.2 on the Mac with just the default 100 share BP entry.

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 '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 1098, in HypUpdPriPy_1
  File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 660, in createPriceDict
)

[bailey56]

I am getting the same error as NeilW using the LibreOffice version (using just the default BP entry). Windows 10 and LibreOffice Portable Version: 5.4.1.2.

Thanks for all the efforts in resolving this.

bailey

[ExFM]

Hi

Thank you for looking at this; I do appreciate everything that Itsallaguess and kiloran do on this and for all the other contributors. I originally thought this was issue was part of a conspiracy by Itsallaguess to make people not look in November; it has worked for me

I have one suggestion. There is code to adjust for GBP and GBp and get this all in the them format. This only holds true if the QuoteType is Equity. If the QuoteType is Bond then this should not apply.

so by adding this to the dictionary:

json_quotetype = Split(Split(str(n), "quoteType"":""")(1), """")(0)

dict.Add key:=json_epic, Item:=json_price & "|" & json_curr & "|" & json_quotetype

then this can be added:

If dict.Exists(ticker) Then
share_price = Left(dict(ticker), InStr(1, dict(ticker), "|") - 1)
share_currency = Mid(dict(ticker), InStr(1, dict(ticker), "|") + 1, 3)
QuoteType = Right(dict(ticker), (Len(dict(ticker)) - InStr(1, dict(ticker), "|") - 4))

If share_currency = "GBP" And QuoteType = "EQUITY" Then
Cells(rowsdown, 5) = share_price * 100
Else
Cells(rowsdown, 5) = share_price
End If

this will avoid making bonds worth 100 time too much.

Not sure if this helps. I have modified my sheet so much I cannot decided if I should change my sheet or go back to the standard and add in the bits I still use. Hopefully this may help some.

Cheers

ExFM

[FarmerTom]

Great job folks, well done.
Libreoffice on Ubuntu 14.04

[Itsallaguess]

There is code to adjust for GBP and GBp and get this all in the them format. This only holds true if the QuoteType is Equity. If the QuoteType is Bond then this should not apply.

so by adding this to the dictionary:

json_quotetype = Split(Split(str(n), "quoteType"":""")(1), """")(0)

dict.Add key:=json_epic, Item:=json_price & "|" & json_curr & "|" & json_quotetype

then this can be added:

If dict.Exists(ticker) Then
share_price = Left(dict(ticker), InStr(1, dict(ticker), "|") - 1)
share_currency = Mid(dict(ticker), InStr(1, dict(ticker), "|") + 1, 3)
QuoteType = Right(dict(ticker), (Len(dict(ticker)) - InStr(1, dict(ticker), "|") - 4))

If share_currency = "GBP" And QuoteType = "EQUITY" Then
Cells(rowsdown, 5) = share_price * 100
Else
Cells(rowsdown, 5) = share_price
End If

this will avoid making bonds worth 100 time too much.

Thanks for that ExFM, and I think this might help explain the issue we were discussing earlier where vrdriver was having an issue with one of his price returns, which does turn out to have the 'BOND' entry in the server data.

I shall raise this with kiloran and we'll do some further tests, but on initial viewing this looks like it might be a sensible approach to deal with the issue, so thanks very much for your efforts above.

Of course vrdriver is welcome to make the above modifications to the Excel VBA in his Beta version (both in the main HYP sheet and on the Watchlist sheet), and it should sort out the problem he was seeing with his 42TE entry.

Cheers,

Itsallaguess

[Itsallaguess]

I am getting the same error as NeilW using the LibreOffice version (using just the default BP entry). Windows 10 and LibreOffice Portable Version: 5.4.1.2.

Hi bailey,

I see that the LibreOffice version that you're using isn't as recent as the Portable download that's available from here (5.4.2) -

https://portableapps.com/apps/office/li ... e_portable

Just to help in the diagnosis of this problem, and seeing that a few others have the same error report but on MAC machines, could I please ask if you'd be willing to download the latest portable version above and see if that still gives you the same error?

Thanks for your help in trying to investigate this. Kiloran's input over the next few days may be very limited, so I'm hoping to diagnose what we can whilst that's the case.

I note that there are others using this Beta release with LibreOffice that aren't seeing this error, so I'm hoping it might be version-related on the LibreOffice side of things, rather than anything in the code itself.

Cheers,

Itsallaguess

[bailey56]

Hi bailey,

I see that the LibreOffice version that you're using isn't as recent as the Portable download that's available from here (5.4.2) -

https://portableapps.com/apps/office/li ... e_portable

Just to help in the diagnosis of this problem, and seeing that a few others have the same error report but on MAC machines, could I please ask if you'd be willing to download the latest portable version above and see if that still gives you the same error?

Thanks for your help in trying to investigate this. Kiloran's input over the next few days may be very limited, so I'm hoping to diagnose what we can whilst that's the case.

I note that there are others using this Beta release with LibreOffice that aren't seeing this error, so I'm hoping it might be version-related on the LibreOffice side of things, rather than anything in the code itself.

Cheers,

Itsallaguess

Thanks Itsallaguess - upgrading to the latest portable version fixed it. Not getting that error now.

Many thanks!

bailey

[Itsallaguess]

Using Mac os High Sierra, Libre office 5.3.2.2

Following message in trying to update prices

com.sun.star.uno.RuntimeException: Error during invoking function HypUpdPriPy_1 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 870, in invoke
ret = self.func( *args )
File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 1098, in HypUpdPriPy_1
File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 660, in createPriceDict
)

dividends update works OK

Hi Colin,

We've fixed this issue from a different user who had the same error but on a Windows machine.

Updating to the latest version of LibreOffice portable download (5.4.2) fixed the issue for him, and I'm hoping that a similar route to success may be available to users of the Mac operating systems as well.

Are you able to tell me if you're using the latest version of LibreOffice that Mac OS users have available?

If there is a more up-to-date version available to you, could I please ask that you try that and see if the issue still persists?

Cheers,

Itsallaguess

[Itsallaguess]

Downloaded the Libreoffice version and run it here on 5.3.3.2 on the Mac with just the default 100 share BP entry.

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 '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 1098, in HypUpdPriPy_1
  File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 660, in createPriceDict
)

Hi Neil,

I don't know too much about the LibreOffice versions available to Mac users, but the exact issue you've described has been fixed by a Windows user updating to the latest version of the Windows LibreOffice portable download (5.4.2).

Can you please check if you're using the latest version of LibreOffice available to you, either in a normal installation or in 'Portable-App' format, and if there is a more recent version available, could I please ask if you could test it and see if the above error is still a problem in those latest versions?

Cheers,

Itsallaguess

[Itsallaguess]

Thanks Itsallaguess - upgrading to the latest portable version [of LibreOffice] fixed it. Not getting that error now.

Many thanks!

Thanks for coming back and letting us know bailey, that's great news.

Some Mac users on the thread have had the same error report, so I've asked them to see if they've got a version-upgrade path available in the Mac arena, and to see if the error goes away in a similar way if they update their installs as well.

Cheers,

Itsallaguess

[eventide]

To the developers on here, if you're using a python backend to open office you really should be able to implement full getdata functionality in less than 20 lines of code. I dont know why all this ungainly string handling quoted above is necessary (as it unfortunately is in vba). I wrote a python getdata implementation exposed to excel via xlwings, which included asynchronous automatic pricing updates. This used the google API when it was available, as it had live UK prices. Just like the current server you are using it spat out a json list of dictionaries which I just packed into a top level dictionary accessible via the ticker key.

Here's the python file as an example, although it is twice the size you need if you aren't implementing automated price updates or need the xlwings decorators, which I don't recommend anyway as (1) 20 min delay anyway and (2) you'll annoy the api in a hurry. Feel free to hack it.

https://www.dropbox.com/s/j2lzs33dz7sf1 ... or.py?dl=0

[Itsallaguess]

Here's the python file as an example, although it is twice the size you need if you aren't implementing automated price updates or need the xlwings decorators, which I don't recommend anyway as (1) 20 min delay anyway and (2) you'll annoy the api in a hurry. Feel free to hack it.

https://www.dropbox.com/s/j2lzs33dz7sf1 ... or.py?dl=0

Thanks very much for that eventide, I'm sure it'll be very useful to us.

I'm not quite sure how the python side of things is being implemented to be honest, although I know kiloran is a big fan of dictionaries, so if he's not using them already for this new price-feed solution, then your file above will be a useful way to do so, I'm quite sure.

I'm not sure if kiloran will be around for a couple of days, so I just wanted to say thanks, and let you know we'll take a look at your helpful pointers in the link above.

Cheers,

Itsallaguess

[NeilW]

I don't know too much about the LibreOffice versions available to Mac users, but the exact issue you've described has been fixed by a Windows user updating to the latest version of the Windows LibreOffice portable download (5.4.2).

I get the same error in the latest 5.3.7 'stable' release version.

com.sun.star.uno.RuntimeException: Error during invoking function HypUpdPriPy_1 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 1098, in HypUpdPriPy_1
  File "vnd.sun.star.tdoc:/1/Scripts/python/HypTopUp.py", line 660, in createPriceDict
)

5.4.2 is 'technology preview'.