New HYPTUSS versions 11.30 and b-d

[kiloran]

Well, today is midsummer's day, which means we are now heading towards winter. To cheer you up after that gloomy thought, we have a new toy for you to play with.

Itsallaguess and I have been slaving away over hot computers in recent months, with the result that the High Yield Portfolio Top-Up Spreadsheet (HYPTUSS) has been upgraded to version 11.30 for Excel and version b-d for LibreOffice/OpenOffice. See http://lemonfoolfinancialsoftware.weebl ... op-up.html

Here's a summary of the changes:

1. We have added Investorease as a source of dividends, in addition to ADVFN and Dividend Data. Many of you are aware of the problems with Dividend Data locking users out if too many requests are made. Investorease does not seem to suffer from that problem, the data accuracy is generally pretty good, and it also has the benefit that it normally differentiates between ordinary and special dividends (Dividend Data lumps them together).
2. You can now add cash to the portfolio. It is treated like a share, so you add the ZCASH share symbol. This was suggested by Cyberchrome in this post on TMF http://boards.fool.co.uk/hyptus-thoughts-13377927.aspx
   Note that the share price for ZCASH is fixed at 100p, so you change the amount by changing the quantity.
3. The Tracking sheet has been enhanced. Each time you update prices or forecast yields on the High Yield Portfolio sheet, we append a row to the tracking sheet, and this now includes the portfolio running yield, forecast dividends, and a list of all the shares in the portfolio, so you can see the historic constituents of your portfolio.
4. Yahoo discontinued the chart capability a few months ago. We now use LiveCharts as the source.
5. We have added the ability to compare the price with the FTSE-100 on the single chart, to match the capability on All Charts
6. The Sector Weighting chart has been changed from a pie chart to a bar chart. We think this looks rather cleaner, especially with many sectors
7. The LibreOffice/OpenOffice version looked a bit clunky on Linux. I've changed the font to Liberation Sans and I think it now looks a lot cleaner.
8. We now have a User Guide! It's in PDF format. Not perhaps as entertaining as Lord Of The Rings, but hopefully useful to you.
9. The HYPTUSS and User Guide are now combined into a zip archive for you to download, to help ensure that you have the up-to-date User Guide when you download the HYPTUSS.

We've tested the new version fairly thoroughly over recent weeks, on various versions of Windows, Linux Mint and Ubuntu, and also with various versions of Excel, LibreOffice and OpenOffice. Fingers crossed it's reasonably bug-free.

There is one issue that has not been fixed in this release.... the problem of Yahoo showing ETF prices in pounds, and most other shares in pence. We'll start to look at this to determine the cleanest and simplest solution.

--kiloran

[Itsallaguess]

Itsallaguess and I have been slaving away over hot computers in recent months...

<COUGH / SPLUTTER>

I think what you meant to say, Kiloran is that yet again you did all the heavy-lifting whilst I did some testing around the edges of all your hard work....

If anyone finds a use for this tool, then can I please ask you all to rec Kiloran's post, if you've not done so already, so he can hopefully see how grateful we all are for having such a brilliant guy on the Lemon Fool team?

Link here for anyone who's not yet rec'd the latest version-release post -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=5871

Thanks again Kiloran, you're one in a million.

Cheers,

Itsallaguess

[monabri]

Downloaded and now in use!!

Thanks for all the hard work!

monabri


(didn't read the manual though...! )

[spiderbill]

Downloaded and will install tonight when work allows.

Needless to say I, and I'm sure many others, am immensely grateful to both of you for the work you continue to put into this wonderfully useful tool. If it were a shareware program (remember them?) I'd have paid for it gladly.

To keep on improving it when Yahoo and co persist in wrecking their facilities shows true dedication. Well done guys! (with a bias towards Kiloran at Istallaguess's request )

Spiderbill

[OLTB]

As above really - thanks to both Kiloran and Itsallaguess for the undoubted hours spent updating and tinkering (oops, a naughty non-HYP word) on the above spreadsheet. Your work does make a difference to an IT Luddite, and it is appreciated.

Cheers, OLTB.

[kiloran]

I think what you meant to say, Kiloran is that yet again you did all the heavy-lifting whilst I did some testing around the edges of all your hard work....

Itsallaguess

I have an ulterior motive..... I am retired, so if I wasn't working on HYPTUSS, I'd probably have to take my wife shopping

--kiloran

[Phileasrob]

I don't post often but have to on this occasion just to say thank you!

[mrbrightside]

Many thanks for a fantastic update. The portfolio snapshot feature is excellent, my Dividend forecast is now functional again, I like the inclusion of a User Guide (saves me looking up 'How to Upgrade') and yes the LibreOffice version looks great.

Thanks to you both for all your hard work.

[Itsallaguess]

Many thanks for a fantastic update. The portfolio snapshot feature is excellent

Thanks for that.

We're hoping that the portfolio snapshot (number of shares and price) might help with anyone who ever wants to perform a back-tested XIRR process, as hopefully having that type of information to hand as easily as it is now might help with some of the leg-work required for the XIRR data-gathering.

I'm sure it'll come in handy for lots of other reasons too, so it's nice to hear that you'll find it useful.

Cheers,

Itsallaguess

[staffordian]

May I add my thanks to kiloran for his hard work and to itsallaguess for whatever he did

I don't update each time a new version is issued because I have tailored it to such an extent that it takes me hours to do so, but this version seems like a "must have" so I know how I shall be spending the next few long summer evenings before the nights draw in as winter approaches

Staffordian

[mrbrightside]

Many thanks for a fantastic update. The portfolio snapshot feature is excellent

I'm sure it'll come in handy for lots of other reasons too, so it's nice to hear that you'll find it useful.

Indeed. I think this feature will be invaluable for folk wishing to retrospectively unitise their portfolio. Why, oh why oh why wasn't this available back in 2003 ?

[Urbandreamer]

Indeed. I think this feature will be invaluable for folk wishing to retrospectively unitise their portfolio. Why, oh why oh why wasn't this available back in 2003 ?

Or many of the other new features that I'm slavouring over.

I can now chart running yield/forcast income, except that I have obviously no history!

Can I request a minor tweak for the next update. Date stamp when "Update dividends" is operated on the dividends page. I can never remeber how long it is since I last used it!

Re customisation and updates: I find that it takes me two attempts. The first is real hard work and fails. The second, after I've thought about it, is quick and easy.

Can I also say thanks for the manual! It cut 20 min of my update process!

[kiloran]

Can I request a minor tweak for the next update. Date stamp when "Update dividends" is operated on the dividends page. I can never remeber how long it is since I last used it!

Sounds simple to do. I've added it to my to-do list for discussion for a future update.

--kiloran

[kiloran]

We're hoping that the portfolio snapshot (number of shares and price) might help with anyone who ever wants to perform a back-tested XIRR process, as hopefully having that type of information to hand as easily as it is now might help with some of the leg-work required for the XIRR data-gathering.

I'm sure it'll come in handy for lots of other reasons too, so it's nice to hear that you'll find it useful.

Cheers,

Itsallaguess

Here's an idea for the portfolio snapshot data.....

We can write a macro that looks at the changes in portfolio over time and compute a Tinker Factor. Anyone with a score over 100 gets reported to Pyad

--kiloran

[bailey56]

Wonderful! It works great. Thank you very much!

[Raptor]

Just got time to test this version on my "test data".
1. First time I run "Get Yahoo Prices", in the tracking sheet the forecast yield was 1223.24% and dividend was £3,175,383.82.... Yes, yes, yes, hang on even I am not that good, run it again and it was correct, so deleted first entry. Also note that if any share gets the "red" for latest the 2 fields in tracking show VALUE# when no figures returned (accurate but worth noting).
2. Tested "update dividends". With ADVFN got 3 entries for July (all correct), with Investorease got 2 hits, DL got the jackpot of 5, all of them seem correct comparing them to Morningstar.
3. Sector weightings like the pie chart, pity not big enough to actually view all sector's? Any chance making the "box" bigger or contents "smaller"?
4. All charts gives me a run time error 76. Excel 2007, Windows VISTA (I know it is very old and keep meaning to go and buy something new), not a problem for me as never use this feature, worked on older versions.

Raptor.

[Raptor]

Slight change to above as found where the "VALUE#" came from and added another point 5......

1. First time I run "Get Yahoo Prices", in the tracking sheet the forecast yield was 1223.24% and dividend was £3,175,383.82.... Yes, yes, yes, hang on even I am not that good, run it again and it was correct, so deleted first entry.
2. Tested "update dividends". With ADVFN got 3 entries for July (all correct), with Investorease got 2 hits, DL got the jackpot of 5, all of them seem correct comparing them to Morningstar.
3. Sector weightings like the pie chart, pity not big enough to actually view all sector's? Any chance making the "box" bigger or contents "smaller"?
4. All charts gives me a run time error 76. Excel 2007, Windows VISTA (I know it is very old and keep meaning to go and buy something new), not a problem for me as never use this feature, worked on older versions.
5. Why when running "Get digital look forecast yields" does it put an entry in the tracking? Should it?
6. If you run "Get Yahoo Prices" and the "running yield" and "forecast dividends" show VALUE#, this is what appears in tracking sheet. This happens as one of my IT's does not return any values and I pump in the current years values (when I remember that is).


Raptor.

[kiloran]

Slight change to above as found where the "VALUE#" came from and added another point 5......

1. First time I run "Get Yahoo Prices", in the tracking sheet the forecast yield was 1223.24% and dividend was £3,175,383.82.... Yes, yes, yes, hang on even I am not that good, run it again and it was correct, so deleted first entry.
Yes, this is normal. When you update the prices, you get the correct portfolio value but the yields may not be up-to-date. When you update the yields, the prices may not be up-to-date. I tend to update prices and then immediately update yields, but others may have different habits. Only the individual user can decide what data on the Tracking sheet is correct, and then delete unwanted rows. See Page 8 of the User Guide
2. Tested "update dividends". With ADVFN got 3 entries for July (all correct), with Investorease got 2 hits, DL got the jackpot of 5, all of them seem correct comparing them to Morningstar.
In January I did an analysis of dividends for 27 shares, comparing III, Investorease, ADVFN, Dividend Data, Digital Look, Money AM and YouInvest. On the basis of that, we chose Investorease as the third source of dividends, since it was correct for 26 shares. The only one it missed was VMID which is an ETF, also missed by all except III. I checked my 39-share HYP about a month ago and it was fine with Investorease and Dividend Data.
I think you have been particularly unlucky with your 5, but the bottom line is that no web site is infallible, except perhaps the company website
3. Sector weightings like the pie chart, pity not big enough to actually view all sector's? Any chance making the "box" bigger or contents "smaller"?
I guess you mean the bar chart. I have a test HYPTUSS set up with 23 sectors and it displays fine on my Windows 7 / Excel 2010 laptop, almost filling the screen. How many sectors do you have? Maybe we could try to set the window size based on each users screen size, though at the moment I don't know how to do that
4. All charts gives me a run time error 76. Excel 2007, Windows VISTA (I know it is very old and keep meaning to go and buy something new), not a problem for me as never use this feature, worked on older versions.
A long shot..... this might be due to path lengths. All Charts creates a file HYP_Charts.html in the same folder as the HYPTUSS,
then tries to open that file using the default browser. I think Windows (32-bit) is limited to path lengths of 255 characters. Might be wrong. Do you have your HYPTUSS in a folder many layers deep, like C:/aaa/bbb/ccc/ddd/eee/fff/ggg/hhh/iii/jjj/kkk/lll/ettcetcetc/HYPTUSS.XLS
5. Why when running "Get digital look forecast yields" does it put an entry in the tracking? Should it?
Yes. See page 8 of the User Guide
6. If you run "Get Yahoo Prices" and the "running yield" and "forecast dividends" show VALUE#, this is what appears in tracking sheet. This happens as one of my IT's does not return any values and I pump in the current years values (when I remember that is).
Again, see Page 8 of the User Guide.

Raptor.

Hope this helps. And many thanks for the feedback.

--kiloran

[Raptor]

Slight change to above as found where the "VALUE#" came from and added another point 5......

1. First time I run "Get Yahoo Prices", in the tracking sheet the forecast yield was 1223.24% and dividend was £3,175,383.82.... Yes, yes, yes, hang on even I am not that good, run it again and it was correct, so deleted first entry.
Yes, this is normal. When you update the prices, you get the correct portfolio value but the yields may not be up-to-date. When you update the yields, the prices may not be up-to-date. I tend to update prices and then immediately update yields, but others may have different habits. Only the individual user can decide what data on the Tracking sheet is correct, and then delete unwanted rows. See Page 8 of the User Guide
2. Tested "update dividends". With ADVFN got 3 entries for July (all correct), with Investorease got 2 hits, DL got the jackpot of 5, all of them seem correct comparing them to Morningstar.
In January I did an analysis of dividends for 27 shares, comparing III, Investorease, ADVFN, Dividend Data, Digital Look, Money AM and YouInvest. On the basis of that, we chose Investorease as the third source of dividends, since it was correct for 26 shares. The only one it missed was VMID which is an ETF, also missed by all except III. I checked my 39-share HYP about a month ago and it was fine with Investorease and Dividend Data.
I think you have been particularly unlucky with your 5, but the bottom line is that no web site is infallible, except perhaps the company website
3. Sector weightings like the pie chart, pity not big enough to actually view all sector's? Any chance making the "box" bigger or contents "smaller"?
I guess you mean the bar chart. I have a test HYPTUSS set up with 23 sectors and it displays fine on my Windows 7 / Excel 2010 laptop, almost filling the screen. How many sectors do you have? Maybe we could try to set the window size based on each users screen size, though at the moment I don't know how to do that
4. All charts gives me a run time error 76. Excel 2007, Windows VISTA (I know it is very old and keep meaning to go and buy something new), not a problem for me as never use this feature, worked on older versions.
A long shot..... this might be due to path lengths. All Charts creates a file HYP_Charts.html in the same folder as the HYPTUSS,
then tries to open that file using the default browser. I think Windows (32-bit) is limited to path lengths of 255 characters. Might be wrong. Do you have your HYPTUSS in a folder many layers deep, like C:/aaa/bbb/ccc/ddd/eee/fff/ggg/hhh/iii/jjj/kkk/lll/ettcetcetc/HYPTUSS.XLS
5. Why when running "Get digital look forecast yields" does it put an entry in the tracking? Should it?
Yes. See page 8 of the User Guide
6. If you run "Get Yahoo Prices" and the "running yield" and "forecast dividends" show VALUE#, this is what appears in tracking sheet. This happens as one of my IT's does not return any values and I pump in the current years values (when I remember that is).
Again, see Page 8 of the User Guide.

Raptor.

Hope this helps. And many thanks for the feedback.

--kiloran

Will check out sectors etc later. I am an engineer by trade never read a user guide.... hahaha

Raptor.

[Itsallaguess]

3. Sector weightings like the pie chart, pity not big enough to actually view all sector's?

Any chance making the "box" bigger or contents "smaller"?

You can follow the instructions below to have a go yourself Raptor?

1. On a copy of your HYPTUSS spreadsheet file (for safety purposes...), go to 'Format / Sheet / Unhide', and select the hidden sheet called 'Sector Graph'. This will let you manipulate the bar-chart that's shown on the 'Sector Weighting' window.

2. Select the 'Sector Graph' sheet.

3. Left-click your mouse to select in the central area of the bar-chart window. You should see the border of the bar-chart area become highlighted, with some little black-boxes on the highlighted edges of the area, on the corners and in the centres of the borders.

4. Left-click and hold the mouse button to select the top/centre black adjustment-box, and drag your mouse to enlarge the bar-chart area up into the blank area of the wider chart window, You'll see an extra border as you're dragging your mouse, to indicate where the top percentage-axis markers will be shown, so just stop short with the upper highlighted area somewhere near the top of the larger blank chart window. Let go of the left mouse-button when you're happy with the upper adjustment.

5. Similarly, left-click and hold the mouse button and select and drag the bottom/centre black adjustment-box, and drag your mouse up a little bit to create a bit of a gap at the bottom of the larger chart-area window. With both of these two last steps, I was left with a gap of about an inch on my 24" monitor, between the upper axis headers and the top of the larger chart area, and also an inch at the bottom, between the last sector bar and the bottom of the larger chart area.

6. When you've done the above, go back to the main 'High Yield Portfolio' sheet, and click on the 'Sector Weighting' button to see how your adjustments in steps 4 and 5 have worked out. You may want to revisit the adjustments, depending on how it looks, but you should be able to fix your issue with some sectors dropping off the bottom if you've got a wide-ranging portfolio in terms of different sectors.

7. When you're happy that the Sector Weighting chart now looks better (and it might be worth leaving a little more of a gap at the bottom, to cater for 'new' sectors in the future, don't forget....) then you can hide the 'Sector Graph' sheet again by selecting the 'Sector Graph' sheet, and then performing a 'Format / Sheet / Hide' process, which will then hide the selected sheet.

8. If all the above works successfully, you can then rename the copy of your HYPTUSS that you've been working on for this minor alteration, and turn this copy into your main spreadsheet.

Do you want to give the above a go and let us know how you get on?

Kiloran, is this alteration something we can get into the next release, as there does seem to be a little bit of wasted space at the top of the Excel sector-bar-chart that we can use to help give more scope for anyone with larger portfolios in terms of numbers-of-sectors?

That testing-team eh, I'll be having a word with them as soon as I, erm, see him....

Cheers,

Itsallaguess