The Lemon Fool

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.

NeilW wrote:

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

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

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

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

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.

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

dear itsallaguess

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

Regards

Colin

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

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.

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.

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

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.

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?

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?

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.

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.

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.

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

Itsallaguess wrote:

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.

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

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.

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.

gbalin wrote:

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

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

eventide wrote:

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.

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

Itsallaguess wrote:

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.

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.

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

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

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

midgesgalore