New HYPTUSS versions 11.54 and b-i

[kiloran]

Yahoo has had some problems correctly returning some share prices and indices over the past month or so, resulting in potentially incorrect or incomplete data in HYPTUSS.

Itsallaguess and I have modified the HYPTUSS macros so that the user is now warned if Yahoo does not return all share prices and indices. If there is a problem returning prices, we usually find that one or more further attempts will succeed.... the problem appears to be completely random. There does seem to be a particular problem with the FTSE All-share index (^FTAS) which is used on the Tracking Sheet.

We have made Yahoo aware of the problem, but they aren't exactly renowned for rapidly fixing problems, so we may have to live with it for a while.

The new versions of HYPTUSS are available at: http://lemonfoolfinancialsoftware.weebl ... op-up.html

--kiloran

[Itsallaguess]

The new versions of HYPTUSS are available at: http://lemonfoolfinancialsoftware.weebl ... op-up.html

For anyone wanting to update to the latest version, the transfer process is really simple, but if anyone is put off by the prospect then hopefully this short video will help -

https://player.vimeo.com/video/232215342

There's also text-instructions for the transfer process in the help documentation that's included with each HYPTUSS download zip file.

Cheers,

Itsallaguess

[Raptor]

Thanks guys. Stripped the vba from 11.54 and inserted in my 11.52. Works fine, took me 4 attempts to get TATE to update but it worked in the end.

Much appreciated for the work you do. Saves me a lot of time.

Raptor.

(for information, I do this as I have made a few additions to me HYPTUSS and found it easier this way then to "upgrade" and add in all my Excel and VBA changes).

[DiamondEcho]

It's probably about time I updated the version I use, but alas once again I've added months of notes, added back-ground pages etc.
Hence I'm interested in Raptor's comment, is that simply a case of copying the latest VBA and overwriting the old? Of course even if that's so I'd need to check the precise version I'm using before I could judge if it would work, but maybe it'd be a Plan-B approach for some others.

[Itsallaguess]

It's probably about time I updated the version I use, but alas once again I've added months of notes, added back-ground pages etc.

Hence I'm interested in Raptor's comment, is that simply a case of copying the latest VBA and overwriting the old?

Of course even if that's so I'd need to check the precise version I'm using before I could judge if it would work, but maybe it'd be a Plan-B approach for some others.

In some cases, the VBA for a particular button may well be transferable between versions, but this will not apply to all situations, and whilst in this particular case the process would work, so long as Raptor's also remembered to update the VBA for the Yahoo button on the 'Watchlist' sheet, we'd have to say that we wouldn't be able to support this upgrade-process if anyone else chooses to take that particular route...

'Carried out at owners-risk' is about as good as we could say, unfortunately....

Cheers,

Itsallaguess

[Raptor]

It's probably about time I updated the version I use, but alas once again I've added months of notes, added back-ground pages etc.

Hence I'm interested in Raptor's comment, is that simply a case of copying the latest VBA and overwriting the old?

Of course even if that's so I'd need to check the precise version I'm using before I could judge if it would work, but maybe it'd be a Plan-B approach for some others.

In some cases, the VBA for a particular button may well be transferable between versions, but this will not apply to all situations, and whilst in this particular case the process would work, so long as Raptor's also remembered to update the VBA for the Yahoo button on the 'Watchlist' sheet, we'd have to say that we wouldn't be able to support this upgrade-process if anyone else chooses to take that particular route...

'Carried out at owners-risk' is about as good as we could say, unfortunately...
Cheers,

Itsallaguess

Agreed, I have checked the changes and the difference to the vba that I have added fits well with 11.54. I would not recommend it unless you are a vba wiz. I do upgrade to new versions when the changes get major and then copy my excel changes across, plus my modified data sheets. Last time was in Jan.

Itsallaguess_ do not use the watchlist function on "this" spreadsheet. But the variable yahoo_fail took me by surprise, so module1 updated....

Did test vanilla (last time I use vanilla too expensive now, i'll get my coat, hahaha) 11.54 first and the upgrade works well, added the shares that were causing problems with posters.

Raptor

[Breelander]

...for information, I do this as I have made a few additions to me HYPTUSS and found it easier this way then to "upgrade" and add in all my Excel and VBA changes

All my customisations are 'Excel' (actually, LibreOffice) ones, so this upgrade was straight forward for me. I haven't made any 'vba' changes, though for me that would have to be Python ones - still trying to get my head round the Python environment

[staffordian]

It's probably about time I updated the version I use, but alas once again I've added months of notes, added back-ground pages etc.
Hence I'm interested in Raptor's comment, is that simply a case of copying the latest VBA and overwriting the old? Of course even if that's so I'd need to check the precise version I'm using before I could judge if it would work, but maybe it'd be a Plan-B approach for some others.

I too used to baulk at upgrading as I had customised the spreadsheet to such an extent.

Then someone suggested such an obvious solution, I can't believe I hadn't already thought of it.

I copied all of my customised spreadsheet into a totally new spreadsheet, but added a new worksheet which I called Links.

The Links worksheet has my shares in the same order as they appear in the TUSS, and for each share, there are the columns I use from the TUSS, eg share price, yield etc.

The formulae simply link to the relevant cell in the TUSS and all the links in my spreadsheet point to the Links worksheet rather than the TUSS itself.

I then incorporated a macro button on the front page of my new spreadsheet, which when clicked opened the TUSS.

In this way, I can simply open my spreadsheet, click the macro button and the TUSS opens. Clicking the Update Prices button not only updates the TUSS, but my open spreadsheet, so I then simply save and close the TUSS and it leaves my spreadsheet updated and ready to use.

When a new version of the TUSS is released, all I do is rename my existing TUSS to TUSSold, and save the newly downloaded version with the previous name, so preserving all the links to my spreadsheet.

It's quicker to do than describe

[Raptor]

It's probably about time I updated the version I use, but alas once again I've added months of notes, added back-ground pages etc.
Hence I'm interested in Raptor's comment, is that simply a case of copying the latest VBA and overwriting the old? Of course even if that's so I'd need to check the precise version I'm using before I could judge if it would work, but maybe it'd be a Plan-B approach for some others.

Like staffordian, I have "linked" my old spreadsheets that have been customised to provide a better view of each of my portfolios (IT's, Shares) I did this with the VLOOKUP function that way the HYPTUSS can be in any order. I will, at sometime, get around to doing exactly the same as staffordian. So can always have a "virgin" HYPTUSS with all the bells and whistles.

Raptor.

[Raptor]

It's probably about time I updated the version I use, but alas once again I've added months of notes, added back-ground pages etc.
Hence I'm interested in Raptor's comment, is that simply a case of copying the latest VBA and overwriting the old? Of course even if that's so I'd need to check the precise version I'm using before I could judge if it would work, but maybe it'd be a Plan-B approach for some others.

Like staffordian, I have "linked" my old spreadsheets that have been customised to provide a better view of each of my portfolios (IT's, Shares) I did this with the VLOOKUP function that way the HYPTUSS can be in any order. I will, at sometime, get around to doing exactly the same as staffordian. So can always have a "virgin" HYPTUSS with all the bells and whistles.

Raptor.

As weather was a bit naff this morning did as promised. I now have the latest HYPTUSS with all my shares and modifications done. One "error" that did show up was that I got the "Yahoo price update has failed" message even though it in fact had successfully updated all prices? I thought it was maybe a one off but it happened a couple of times during my testing. Seems that maybe the yahoo_fail was not reset correctly, but do not have time at moment to do any digging. Just posted this in case anyone else notices this...

Raptor.

[kiloran]

One "error" that did show up was that I got the "Yahoo price update has failed" message even though it in fact had successfully updated all prices? I thought it was maybe a one off but it happened a couple of times during my testing. Seems that maybe the yahoo_fail was not reset correctly, but do not have time at moment to do any digging. Just posted this in case anyone else notices this...

Raptor.

When HYPTUSS prices are updated, the process also retrieves FTSE index price and updates the Tracking Sheet. The ^FTAS index does seem to have particular problems at Yahoo these days, so it's more than likely that if all of the portfolio prices are there, the problem is in the tracking sheet. I've seen that problem a lot.

--kiloran

[Breelander]

more than likely that if all of the portfolio prices are there, the problem is in the tracking sheet. I've seen that problem a lot.
--kiloran

And very helpfully, your error message says so...

Problems retrieving index values from Yahoo.
This affects the Tracking Sheet
Please try again

Should we expect a different message if it affects the Portfolio sheet? I only ask because so far it's always been the ftas that given me problems (about 1 time in 3).

[Raptor]

One "error" that did show up was that I got the "Yahoo price update has failed" message even though it in fact had successfully updated all prices? I thought it was maybe a one off but it happened a couple of times during my testing. Seems that maybe the yahoo_fail was not reset correctly, but do not have time at moment to do any digging. Just posted this in case anyone else notices this...

Raptor.

When HYPTUSS prices are updated, the process also retrieves FTSE index price and updates the Tracking Sheet. The ^FTAS index does seem to have particular problems at Yahoo these days, so it's more than likely that if all of the portfolio prices are there, the problem is in the tracking sheet. I've seen that problem a lot.

--kiloran

Just went back in and you are right the Tracking sheet has "lots" of missing FTSE Index price fields. As I do not use that (at the moment) will work around. Thanks for quick response, much appreciated.

Raptor.

BTW Did notice that I get the Forecast Yields successful message even though MCT doesn't have ANY..... Again I add that back in manually.

[kiloran]

more than likely that if all of the portfolio prices are there, the problem is in the tracking sheet. I've seen that problem a lot.
--kiloran

And very helpfully, your error message says so...

Problems retrieving index values from Yahoo.
This affects the Tracking Sheet
Please try again

Should we expect a different message if it affects the Portfolio sheet? I only ask because so far it's always been the ftas that given me problems (about 1 time in 3).

In fact it's a subtle(?) difference between the Excel and LibreOffice versions.

The LO version displays

"Problems retrieving prices from Yahoo.
Please try again"

if there are problems with the portfolio, and

"Problems retrieving index values from Yahoo.
This affects the Tracking Sheet
Please try again"

if there are problems with the tracking sheet.

The Excel version displays

Yahoo price scrape has failed - please run again

in both cases.

I think we should hire a new development team capable of coordinationg their efforts
Sorry!

--kiloran

[LoongGoh]

Thanks Kiloran for the updated spreadsheet. I can't seem to find Ferguson though. Any chance you can have a look at this please?

Thanks.


David

[Itsallaguess]

I can't seem to find Ferguson though.

Any chance you can have a look at this please?

Number 3 in the Frequently Asked Questions section deals with adding a new share to the list -

3. I want to add a share but it is not available in the list

The list of stocks is defined by the sheet "Digital Look Data Sheet". A new share can be added by inserting a row into this sheet.

(instructions...)

http://lemonfoolfinancialsoftware.weebl ... p-faq.html

Cheers,

Itsallaguess

[LoongGoh]

It worked brilliantly. Thanks!

David

[Fluke]

Thanks as always.

I've just uploaded my portfolio to this latest version (b-i) and I get a clean sweep of #REF! down columns M, N and O, also in cell P4. Had this on the b-h version too only it was a bit more specific - Err:504 - is this a Mac/LibreOffice incompatibility thing maybe? I'm on MacOS 10.13.4.

[kiloran]

Thanks as always.

I've just uploaded my portfolio to this latest version (b-i) and I get a clean sweep of #REF! down columns M, N and O, also in cell P4. Had this on the b-h version too only it was a bit more specific - Err:504 - is this a Mac/LibreOffice incompatibility thing maybe? I'm on MacOS 10.13.4.

Hi Fluke.
I don't have access to a Mac but the LibreOffice version is capable of working on a Mac. See: viewtopic.php?f=27&t=10892#p128660

What version of LibreOffice are you using? I'm currently on 6.0.2.1 on Windows 7.

First, can you confirm if the data in columns E-L is OK. If so, check the following.

Cell    Contents
M6     =RANK(J6,fcst_range)
N6     =M6+I6
O6     =RANK(N6,tuo_range,1)
P4     =SUM(For_Div)

If that is OK, I suspect there is a problem with Named Ranges.

Go to SHEET/NAMED RANGES AND EXPRESSIONS/MANAGE. Widen the window so that you can see the Range or Formula Expression.

You should see the following:

Name          Expression
fcst_range   OFFSET('High Yield Portfolio'.$J$6,0,0,cnt_stocks,1)
tuo_range    OFFSET('High Yield Portfolio'.$N$6,0,0,cnt_stocks,1)
for_div      OFFSET($P$6,0,0,cnt_stocks,1)
cnt_stocks   (COUNTA('High Yield Portfolio'.$C$2:$C$521))

I suspect that one or more of these are corrupted and show #REF!
You can edit them to the correct data, or download a fresh version of HYPTUSS b-i

I downloaded a fresh copy of b-i just now to verify it and it is OK

--kiloran

[Fluke]

Kiloran,

After I posted about the errors I was getting using the HYPTUSS I did my top up anyway and then, not needing the spreadsheet for a while, completely forgot about my post and to check for replies! I've just seen your detailed and useful response and followed up immediately. First thing I did was check the version of LibreOffice I was using and it was a few versions behind yours so I downloaded the latest version (6.0.4). Then I re-downloaded the HYPTUSS (version b-i), added a few shares, clicked get share price then clicked get latest forward yield, and lo and behold the errors have gone. God knows what that was but whatever it was affected the previous version of the spreadsheet too. Maybe it was me, I did do a bit of sorting, maybe that confused things. I don't know but it's all working fine now.

Thanks once again.