HYPTUS v11.31 Run-time error 5

[GN100]

Many thanks Itsallaguess for guiding me through. My portfolio monitoring tool is now all set up and running and ready for tomorrow morning. I link the USD/EUR/GBP through to my holiday trips costs Excel sheet so even that is now back working.

One further Q to make the situation perfect - any ideas on how to get a current date into the portfolio sheet? I can insert it easily enough by hand but if it's do able then it would be great.

[Itsallaguess]

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'.

Thanks Neil. It's not clear from your post, unless I'm misreading it, but are you able to check the Beta HYPTUSS in the 5.4.2 'technology preview' version on the Mac machines?

Cheers,

Itsallaguess

[Itsallaguess]

My portfolio monitoring tool is now all set up and running and ready for tomorrow morning. I link the USD/EUR/GBP through to my holiday trips costs Excel sheet so even that is now back working

That's great news GN100, thanks for letting us know.

One further Q to make the situation perfect - any ideas on how to get a current date into the portfolio sheet?

I can insert it easily enough by hand but if it's do able then it would be great.

If you enter the following into an empty, unprotected cell, it will show the current date -

=TODAY()

Doing so in my copy of the Excel HYPTUSS Beta gives the following -

05/11/2017

You should be able to right-click the cell and convert the date format if you need it to display the date differently.

Cheers,

Itsallaguess

[Whatsup]

dear itsallaguess

Have downloaded and installed Libre office 7.4.2 for mac and the same error occurs

Regards

Colin

[Whatsup]

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

[mc2fool]

There's a thread on the US TMF boards on the dropping of the Yahoo CSV API, http://boards.fool.com/has-yahoo-made-a ... e#32881913, in which I discover that the JSON API has a fields parameter.

E.g. if all you want is the price you can add &fields=regularMarketPrice to the URL. You don't get only the price but you do get a lot less than the whole kaboodle. http://jsonviewer.stack.hu/#http://quer ... arketPrice

It seems to give you everything related to a field so, e.g., if you add regularMarketDayHigh you also get 52 week high info http://jsonviewer.stack.hu/#http://quer ... ketDayHigh but it does look like using &fields can help cut down, if not eliminate, the amount of unwanted data received.

[Itsallaguess]

There's a thread on the US TMF boards on the dropping of the Yahoo CSV API, http://boards.fool.com/has-yahoo-made-a ... e#32881913, in which I discover that the JSON API has a fields parameter.

E.g. if all you want is the price you can add &fields=regularMarketPrice to the URL. You don't get only the price but you do get a lot less than the whole kaboodle. http://jsonviewer.stack.hu/#http://quer ... arketPrice

It seems to give you everything related to a field so, e.g., if you add regularMarketDayHigh you also get 52 week high info http://jsonviewer.stack.hu/#http://quer ... ketDayHigh but it does look like using &fields can help cut down, if not eliminate, the amount of unwanted data received.

That's good news, thanks for letting us know.

Being able to stack the returned parameters will be useful to us I should imagine.

Cheers,

Itsallaguess

[PeterGray]

Thanks all for the various hints on getting this sorted.

As my existing code works by taking an array of epics and then handling the returned array of results, I've been working on a way of using the "query1.finance.yahoo.com....." to return and handle an array of results. That's meant a bit of learning with handling the JSON structures returned, but once cracked it doesn't take a lot of code to do it.

I'm attaching a short code snippet which might be useful to those who have spreadsheets and code of their own that needs rejigging to get it work with the "query.finance.yahoo" call. Hope it may prove useful.

It takes a comma delimited epic code list, created however you choose, and then uses a ScriptControl to parse the returned JSON data. Because one of the nested fields is an array it can't be handled directly in VBA, so the JScript call set up in the ScriptControl is used to convert the "results" field to a VBA array which can then be iterated through, and the data used as desired.

Code: Select all

Rem Fire the query off to Yahoo
    URL = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=" & epic_list & "&fields=regularMarketPrice"
    MyHTTP.Open "GET", URL, False
    MyHTTP.send

Rem Get the data
    rsp = MyHTTP.responseText
    
Rem set up the ScriptControl
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"
    sc.AddCode "function setArray(ja) {var dict = new ActiveXObject('Scripting.Dictionary');for (var i=0;i < ja.length; i++ )dict.add(i,ja[i]);return dict.items();}"
    
Rem parse JSON into obj
    Set obj = sc.Eval("(" + rsp + ")")
    
Rem convert to the result key to a VBA array for handling here
    recordArray = sc.Run("setArray", obj.quoteResponse.result)
    For i = 0 To UBound(recordArray)
        price = recordArray(i).regularMarketPrice
        epic = recordArray(i).symbol
        
        Rem Update your sheet, or whatever

    Next i

[eventide]

Thanks all for the various hints on getting this sorted.

As my existing code works by taking an array of epics and then handling the returned array of results, I've been working on a way of using the "query1.finance.yahoo.com....." to return and handle an array of results. That's meant a bit of learning with handling the JSON structures returned, but once cracked it doesn't take a lot of code to do it.

I'm attaching a short code snippet which might be useful to those who have spreadsheets and code of their own that needs rejigging to get it work with the "query.finance.yahoo" call. Hope it may prove useful.

It takes a comma delimited epic code list, created however you choose, and then uses a ScriptControl to parse the returned JSON data. Because one of the nested fields is an array it can't be handled directly in VBA, so the JScript call set up in the ScriptControl is used to convert the "results" field to a VBA array which can then be iterated through, and the data used as desired.

Code: Select all

Rem Fire the query off to Yahoo
    URL = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=" & epic_list & "&fields=regularMarketPrice"
    MyHTTP.Open "GET", URL, False
    MyHTTP.send

Rem Get the data
    rsp = MyHTTP.responseText
    
Rem set up the ScriptControl
    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"
    sc.AddCode "function setArray(ja) {var dict = new ActiveXObject('Scripting.Dictionary');for (var i=0;i < ja.length; i++ )dict.add(i,ja[i]);return dict.items();}"
    
Rem parse JSON into obj
    Set obj = sc.Eval("(" + rsp + ")")
    
Rem convert to the result key to a VBA array for handling here
    recordArray = sc.Run("setArray", obj.quoteResponse.result)
    For i = 0 To UBound(recordArray)
        price = recordArray(i).regularMarketPrice
        epic = recordArray(i).symbol
        
        Rem Update your sheet, or whatever

    Next i

Although this may work as intended, isn't it a serious amount of inefficient code overhead to extract a single field from the server response? Why would anyone choose to pull data like this?

[NeilW]

Thanks Neil. It's not clear from your post, unless I'm misreading it, but are you able to check the Beta HYPTUSS in the 5.4.2 'technology preview' version on the Mac machines?

I haven't done that yet because I don't want to ruin my stable installation. I'm never too enamoured with the upgrade process on a MAC, so I'm sure that a downgrade is going to be problematic.

Trying to find a way of sandboxing it.

[dowdman49]

My error is "Data Server unreachable. Check internet connection"
Obviously I checked connection and it was fine.

For info. Running spreadsheets on MS Excel 2010 under Windows 10.

[Itsallaguess]

My error is "Data Server unreachable. Check internet connection"
Obviously I checked connection and it was fine.

For info. Running spreadsheets on MS Excel 2010 under Windows 10.

That's an odd one, as I don't think we've had that error before.

Everything is still working with the server, so it sounds like something more local than that.

Are you able to temporarily disable your Firewall and see if that allows it to work? I'm wondering if there's a traffic rule on your Firewall that's either not been given permission to talk to the outside world via Excel, or if there's already a block on that application.

Cheers,

Itsallaguess

[gbalin]

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 -

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

[mc2fool]

Couple of quick gotchas I've come across in updating the VB in my own Excel sheets to the Yahoo JSON API...

Firstly, if you put in a duff (non-existent) symbol then sometimes you get no JSON for that symbol and other times you a very abbreviated JSON section.

Secondly, if you include a currency in the list it always comes first, irrespective of where it was in the list.

E.g. http://jsonviewer.stack.hu/#http://quer ... arketPrice

Neither AAT.L or NONE.L exist but the former gives some result while the latter doesn't, and the GBPUSD rate appears first even though it is last in the list.

[eventide]

Couple of quick gotchas I've come across in updating the VB in my own Excel sheets to the Yahoo JSON API...

Firstly, if you put in a duff (non-existent) symbol then sometimes you get no JSON for that symbol and other times you a very abbreviated JSON section.

Secondly, if you include a currency in the list it always comes first, irrespective of where it was in the list.

E.g. http://jsonviewer.stack.hu/#http://quer ... arketPrice

Neither AAT.L or NONE.L exist but the former gives some result while the latter doesn't, and the GBPUSD rate appears first even though it is last in the list.

That is why you need to use a dictionary not an array when handling the response. The dictionary key (ie ticker) is position insensitive so it doesn't matter what order the server return is, nor does it matter if the fields are different (or missing) per ticker. If you are not familiar with all of this (you may be of course), but are using excel, you might want to use the addin which handles bad tickers or missing fields.

[Itsallaguess]

Hi GN100,

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

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

Bear with me if this is a stupid question, but is there a similar add-on/plug-in for LibreOffice?

Hi GB,

I don't think there is at the moment, no, and to be honest I'm not sure if LibreOffice has the same add-in functionality as the Excel tool, so this is probably one for kiloran to answer, given that he's much more au fait with the LibreOffice tool than me.

Cheers,

Itsallaguess

[Itsallaguess]

Couple of quick gotchas I've come across in updating the VB in my own Excel sheets to the Yahoo JSON API...

Firstly, if you put in a duff (non-existent) symbol then sometimes you get no JSON for that symbol and other times you a very abbreviated JSON section.

Secondly, if you include a currency in the list it always comes first, irrespective of where it was in the list.

E.g. http://jsonviewer.stack.hu/#http://quer ... arketPrice

Neither AAT.L or NONE.L exist but the former gives some result while the latter doesn't, and the GBPUSD rate appears first even though it is last in the list.

That is why you need to use a dictionary not an array when handling the response.

The dictionary key (ie ticker) is position insensitive so it doesn't matter what order the server return is, nor does it matter if the fields are different (or missing) per ticker.

If you are not familiar with all of this (you may be of course), but are using excel, you might want to use the addin which handles bad tickers or missing fields.

The Excel HYPTUSS process involves a dictionary, and even though the built-in dictionary functionality is quite basic in Excel VBA, it does the job and handles missing tickers well.

In fact we're delimiting a couple of bits of data in the Excel VBA dictionary, and then splitting them back out as we want them, so although it's fairly basic in it's default state, we can manipulate it to do what we're after.

I know kiloran is a big fan of dictionaries as well, so I assume there's some use of them in the LibreOffice/OpenOffice solution too.

Cheers,

Itsallaguess

[JohnnyCyclops]

I have been promising to upgrade my HYPTUSS spreadsheet to the latest for a few years now so maybe it is time to.

I meant to come back to this earlier comment Raptor, just to remind everyone that I created an online video tutorial clearly showing the HYPTUSS transfer process for when these issues mean we've got to migrate to the next working version -

https://vimeo.com/232215342

It really is a fairly simple process for anyone just using the vanilla tool, so hopefully the above helps to allay any fears.

[....]

Cheers,

Itsallaguess

Huge thanks for getting the Beta out so quickly. The video was useful too. I noticed you circling the mouse cursor around what you wanted viewers to focus on.

If you're using Windows there's a setting to flash a circle around the cursor to draw attention to it. You can find it under Control Panel > Mouse > Pointer Options. Then touching the Ctrl key puts a quick circle around the cursor.

[midgesgalore]

We're pleased to report that we've got a couple of Beta versions of the new HYPTUSS tool for people to have a play with.

We'd normally do some more testing to be honest, and then try to get some finalised versions out, but Kiloran and I have some time-pressures that mean this is a little more difficult at the moment, and we're hoping that some community-Beta-testing might be a good way to get these releases out as quick as we can, given the impact the recent Yahoo issue has caused.

If anyone would like to take a look at the Beta versions, then they can be downloaded from this test-page on the Weebly site -

http://lemonfoolfinancialsoftware.weebl ... _page.html
...
...
If there's any questions, comments, or feedback from the above Beta versions, then please just let us know.

Cheers,

Itsallaguess

Big thanks Itsallaguess, Kiloran & eventide.

I gave the beta version HYPTUSS a whirl today and no problems other than forgetting the excel 97 version baulks at my conditional formatting and reporting ovelay.
All is good and fairly quick to implement if you ignore initial customisation issues.

A very minor point is the Digital Look data sheet entry for "Henty Boot" still has the old EPIC symbol of BHY instead of the newer BOOT.
It is such a low yield that probably no HYPer would have noticed the change and I remember I just updated my copy in the 11.31 release.
I just added that comment for completeness of my spreadsheet-buster exercise.

The rest of the completeness is I checked this on a windows 10 OS using excel 2010 (32 bit)

Warm regards, midgesgalore

[midgesgalore]

Also meant to say I save the HYPTUSS spreadsheet as a macro enabled workbook as it hugely cuts down the file-size.

midgesgalore