HYPTUS v11.31 Run-time error 5
-
- 2 Lemon pips
- Posts: 143
- Joined: November 4th, 2016, 10:14 am
Re: HYPTUS v11.31 Run-time error 5
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.
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.
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: HYPTUS v11.31 Run-time error 5
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?NeilW wrote:I get the same error in the latest 5.3.7 'stable' release version.Itsallaguess wrote:
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).
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'.
Cheers,
Itsallaguess
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: HYPTUS v11.31 Run-time error 5
That's great news GN100, thanks for letting us know.GN100 wrote:
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
If you enter the following into an empty, unprotected cell, it will show the current date -GN100 wrote: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.
=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
-
- Posts: 29
- Joined: November 17th, 2016, 7:48 pm
Re: HYPTUS v11.31 Run-time error 5
dear itsallaguess
Have downloaded and installed Libre office 7.4.2 for mac and the same error occurs
Regards
Colin
Have downloaded and installed Libre office 7.4.2 for mac and the same error occurs
Regards
Colin
-
- Posts: 29
- Joined: November 17th, 2016, 7:48 pm
Re: HYPTUS v11.31 Run-time error 5
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
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
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: HYPTUS v11.31 Run-time error 5
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.
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.
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: HYPTUS v11.31 Run-time error 5
That's good news, thanks for letting us know.mc2fool wrote:
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.
Being able to stack the returned parameters will be useful to us I should imagine.
Cheers,
Itsallaguess
-
- Lemon Slice
- Posts: 811
- Joined: November 4th, 2016, 11:18 am
Re: HYPTUS v11.31 Run-time error 5
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.
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
-
- 2 Lemon pips
- Posts: 102
- Joined: October 24th, 2017, 3:29 pm
Re: HYPTUS v11.31 Run-time error 5
PeterGray wrote: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?
-
- Lemon Slice
- Posts: 755
- Joined: November 4th, 2016, 4:27 pm
Re: HYPTUS v11.31 Run-time error 5
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.Itsallaguess wrote: 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?
Trying to find a way of sandboxing it.
-
- Posts: 4
- Joined: December 5th, 2016, 1:48 pm
Re: HYPTUS v11.31 Data Server unreachable
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.
Obviously I checked connection and it was fine.
For info. Running spreadsheets on MS Excel 2010 under Windows 10.
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: HYPTUS v11.31 Data Server unreachable
That's an odd one, as I don't think we've had that error before.dowdman49 wrote:
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.
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
-
- Posts: 24
- Joined: November 13th, 2016, 3:51 pm
Re: HYPTUS v11.31 Run-time error 5
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?Itsallaguess wrote:Hi GN100,GN100 wrote: 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.
If you download eventide's 'GETDATA' add-in for Excel from here -
https://www.lemonfool.co.uk/viewtopic.p ... 190#p93095
GB
-
- Lemon Half
- Posts: 6209
- Joined: November 4th, 2016, 11:24 am
Re: HYPTUS v11.31 Run-time error 5
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.
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.
-
- 2 Lemon pips
- Posts: 102
- Joined: October 24th, 2017, 3:29 pm
Re: HYPTUS v11.31 Run-time error 5
mc2fool wrote: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.
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: HYPTUS v11.31 Run-time error 5
Hi GB,gbalin wrote:Bear with me if this is a stupid question, but is there a similar add-on/plug-in for LibreOffice?Itsallaguess wrote:
Hi GN100,
If you download eventide's 'GETDATA' add-in for Excel from here -
https://www.lemonfool.co.uk/viewtopic.p ... 190#p93095
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
-
- Lemon Half
- Posts: 8675
- Joined: November 4th, 2016, 1:16 pm
Re: HYPTUS v11.31 Run-time error 5
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.eventide wrote:That is why you need to use a dictionary not an array when handling the response.mc2fool wrote:
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.
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.
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
-
- Lemon Slice
- Posts: 301
- Joined: November 15th, 2016, 9:19 pm
Re: HYPTUS v11.31 Run-time error 5
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.Itsallaguess wrote: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 -Raptor wrote:
I have been promising to upgrade my HYPTUSS spreadsheet to the latest for a few years now so maybe it is time to.
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
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.
-
- 2 Lemon pips
- Posts: 219
- Joined: November 5th, 2016, 12:02 am
Re: HYPTUS v11.31 Run-time error 5
Big thanks Itsallaguess, Kiloran & eventide.Itsallaguess wrote: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
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
-
- 2 Lemon pips
- Posts: 219
- Joined: November 5th, 2016, 12:02 am
Re: HYPTUS v11.31 Run-time error 5
Also meant to say I save the HYPTUSS spreadsheet as a macro enabled workbook as it hugely cuts down the file-size.
midgesgalore
midgesgalore