New HYPTUSS versions 11.65 and b-j

[kiloran]

Way back in January, TUK020 got in touch with Itsallaguess and me with some ideas to enhance the HYPTUSS. Users talk.... we listen! For the past few months, we've been burning the midnight oil, slaving over our keyboards, sustained only by the odd beer and single malt.

So, here is HYPTUSS version 11.65 (excel) and b-j (OpenOffice/LibreOffice) with some new functionality. The changes are:

1. Addition of an Overview sheet, This provides a summary of the High Yield Portfolio Sheet by company and sector, with the ability to easily format the data for adding to a Lemon Fool post.
2. Addition of a Snapshots sheet. This allows the accumulation of datestamped copies of the High Yield Portfolio to allow comparison of the portfolio constituents and value over time
3. Ability to switch between Sectors and Super-Sectors
4. Divedend Data recently changed the structure of their website so dividends could no longer be retrieved. The macros have been modified to accomodate this change
5. Changing the "Instructions" button on the "High Yield Portfolio" sheet to a "User Guide" button which links directly to the User Guide at: http://lemonfoolfinancialsoftware.weebl ... op-up.html
6. For the OpenOffice/LibreOffice version, some macros such as Add/Delete share have been changed from Basic to Python. This should have no effect on the user, but the new macros are a little bit snappier in operation and use coding more consistent with the rest of the HYPTUSS
7. A button to provide a solution to the Brexit problem. (just kidding. Though it would be nice )

The new versions are available here: http://lemonfoolfinancialsoftware.weebl ... op-up.html packaged as zip files with the User Guide (you will read it, won't you )

The process to migrate to the new version is unchanged, but this short video may help if you are unsure: https://vimeo.com/232215342
Or refer to Page 21 of the User Guide for step-by-step instructions.

Please, please, please let there be no calamitous bugs, we have done a lot of testing. Honest.

And thanks to TUK020 for the suggestions.

Have fun, fellow HYPers

--kiloran

[Itsallaguess]

• Ability to switch between Sectors and Super-Sectors

For info, the above new 'Sector / Super-Sector' functionality can be seen being used in the post linked below, where the 'Sector Weighting' chart will show the different portfolio weightings depending on the current sector / super-sector option being used when activated -

https://www.lemonfool.co.uk/viewtopic.php?f=31&t=16638&p=206459p206459#p206459

The 'Overview' sheet will also use either the sector or super-sector configuration as well, depending on the current setting when each overview is generated.

I should also add that the sector / super-sector naming convention is still user-editable on the Digital Look Data Sheet, for anyone that wishes to alter the current allocated sectors for any listed company.

As always, Kiloran and I are keen to hear any feedback from early adopters of this new HYPTUSS release....

Cheers,

Itsallaguess

[EssDeeAitch]

OK, it looks like I am the first to test and post my (very small) HYP using the "Generate Lemon Fool Table" functionality so here it is.

Good work IAAG and kiloran, many thanks

                                                                                Value    Div   Fcst 
Share                           Epic    Sector                                 %Total %Total  Yield
Greene King                     GNK     Travel & Leisure                        17.0%  13.0%   5.2%
Imperial Brands                 IMB     Tobacco                                 12.6%  14.4%   7.8%
Rio Tinto                       RIO     Mining                                  13.9%  11.8%   5.8%
Royal Dutch Shell 'B'           RDSB    Oil & Gas Producers                     11.4%  10.4%   6.2%
Standard Life Aberdeen plc      SLA     Life Insurance                           9.0%  12.4%   9.4%
Vodafone Group                  VOD     Mobile Telecommunications               11.3%  15.6%   9.4%
WPP                             WPP     Media                                   10.6%  10.9%   7.0%
National Grid                   NG      Multiutilities                          14.2%  11.7%   5.6%

Portfolio Running Yield = 6.82%

                                                 Value     Div
Sector                                          %Total  %Total
Travel & Leisure                                 17.0%   13.0%
Tobacco                                          12.6%   14.4%
Mining                                           13.9%   11.8%
Oil & Gas Producers                              11.4%   10.4%
Life Insurance                                    9.0%   12.4%
Mobile Telecommunications                        11.3%   15.6%
Media                                            10.6%   10.9%
Multiutilities                                   14.2%   11.7%
Total                                           100.0%  100.0%

Note: 1...'Value %Total' is the portfolio value of the share as a % of the total portfolio
      2...'Div %Total' is the expected dividend of the share based on forecast yield
                       as a % of the total portfolio expected dividend

[Breelander]

As always, Kiloran and I are keen to hear any feedback from early adopters of this new HYPTUSS release....

Well, HYPTUSS b-j passes the 'Bree' test . Took a while to migrate as I have a lot of my own 'additions' to migrate, mainly in columns AA:AF (and a few other cells) on the High Yield Portfolio sheet.

Compare this Overview with my lovingly hand-crated one using the same data, looks to be the same (better, actually, appears the dividend I gave for PSN was in error ).

                                                                                Value    Div   Fcst 
Share                           Epic    Sector                                 %Total %Total  Yield
Persimmon                       PSN     Household Goods & Home Construction      6.9%  12.7%  10.4%
Diageo                          DGE     Beverages                                6.1%   2.5%   2.3%
Electrocomponents               ECM     Support Services                         5.5%   2.7%   2.8%
Unilever                        ULVR    Food Producers                           5.3%   3.3%   3.5%
Reckitt Benckiser Group         RB      Household Goods & Home Construction      5.0%   2.5%   2.9%
BAE Systems                     BA      Aerospace & Defence                      4.9%   4.2%   4.9%
IMI                             IMI     Industrial Engineering                   4.8%   3.7%   4.3%
LLoyds Banking Group 9.75% Non  LLPD    Not Defined                              4.5%   5.2%   6.6%
Royal Dutch Shell 'B'           RDSB    Oil & Gas Producers                      4.5%   4.9%   6.1%
Halfords Group                  HFD     General Retailers                        4.1%   5.3%   7.2%
United Utilities Group          UU      Gas, Water & Multiutilities              4.1%   3.5%   4.9%
GlaxoSmithKline                 GSK     Pharmaceuticals & Biotechnology          4.1%   3.8%   5.3%
Rio Tinto                       RIO     Mining                                   3.8%   3.9%   5.8%
Vodafone Group                  VOD     Mobile Telecommunications                3.8%   6.2%   9.4%
Aviva                           AV      Life Insurance                           3.7%   5.3%   8.1%
BT Group                        BT-A    Fixed Line Telecommunications            3.7%   4.6%   7.0%
Centrica                        CNA     Gas, Water & Multiutilities              3.3%   5.5%   9.4%
SSE                             SSE     Electricity                              3.3%   3.8%   6.6%
British Land Company            BLND    Retail REITs                             3.3%   3.1%   5.3%
Lloyds Banking Group            LLOY    Banks                                    2.6%   2.5%   5.5%
RSA Insurance Group             RSA     Nonlife Insurance                        2.4%   2.4%   5.6%
Marks and Spencer Group         MKS     General Retailers                        2.3%   2.7%   6.6%
Pearson                         PSON    Media                                    2.3%   1.0%   2.5%
Tesco                           TSCO    Food & Drug Retailers                    1.6%   0.9%   3.1%
Wood Group (John)               WG      Oil Equipment, Services & Distribution   1.2%   1.1%   5.0%
De La Rue                       DLAR    Support Services                         1.2%   1.3%   6.1%
Sainsbury (J)                   SBRY    Food & Drug Retailers                    0.9%   0.8%   4.7%
Barclays                        BARC    Banks                                    0.7%   0.5%   4.6%

Running Yield:    5.66%

                                                 Value    Div
Sector                                          %Total %Total
Food & Drug Retailers                             2.6%   1.7%
Aerospace & Defence                               4.9%   4.2%
Gas, Water & Multiutilities                       7.4%   9.1%
Pharmaceuticals & Biotechnology                   4.1%   3.8%
Life Insurance                                    3.7%   5.3%
Not Defined                                       4.5%   5.2%
Media                                             2.3%   1.0%
Food Producers                                    5.3%   3.3%
General Retailers                                 6.5%   8.0%
Oil Equipment, Services & Distribution            1.2%   1.1%
Industrial Engineering                            4.8%   3.7%
Support Services                                  6.7%   4.0%
Fixed Line Telecommunications                     3.7%   4.6%
Mining                                            3.8%   3.9%
Mobile Telecommunications                         3.8%   6.2%
Retail REITs                                      3.3%   3.1%
Oil & Gas Producers                               4.5%   4.9%
Nonlife Insurance                                 2.4%   2.4%
Beverages                                         6.1%   2.5%
Banks                                             3.2%   3.0%
Household Goods & Home Construction              11.9%  15.3%
Electricity                                       3.3%   3.8%

Note: 1...'Value %Total' is the portfolio value of the share as a % of the total portfolio
      2...'Div %Total' is the expected dividend of the share based on forecast yield
                       as a % of the total portfolio expected dividend

[Raptor]

Someone pointed out that LLPD is not defined as Bank sector.

Raptor.

[Breelander]

Someone pointed out that LLPD is not defined as Bank sector.

Raptor.

Should it be? 'Fixed interest' may be a more appropriate 'sector name' for preference shares (not all are issued by 'banks').

Strictly speaking, 'Not defined' is correct, as they are not included in the Industry Classification Benchmark.

https://en.wikipedia.org/wiki/Industry_ ... _Benchmark

[uspaul666]

Well if it comes to that, SLA doesn’t do insurance, according to pyad. (I’ll get my coat)

[Raptor]

Someone pointed out that LLPD is not defined as Bank sector.

Raptor.

Should it be? 'Fixed interest' may be a more appropriate 'sector name' for preference shares (not all are issued by 'banks').

The FTSE web-site has no sector or sub-sector defined for these shares. I think it is therefore an area for "user defined". For me it is still financials and banks but I do not own any so will leave it up to the spreadsheet oweners to decide if it needs to be changed.

Raptor.

[Breelander]

Advance warning for Kiloran/Itsallaguess

The ICB structure has been revised and will change from 1st July.

ICB Structure and Code Changes – July 1, 2019. For additional details, please contact: info@ftserussell.com

To help clients transition to the new ICB structure, the legacy ICB Universe product will continue to be provided to clients in parallel until 2020...

https://www.ftserussell.com/financial-d ... chmark-icb

[Raptor]

Advance warning for Kiloran/Itsallaguess

The ICB structure has been revised and will change from 1st July.

ICB Structure and Code Changes – July 1, 2019. For additional details, please contact: info@ftserussell.com

To help clients transition to the new ICB structure, the legacy ICB Universe product will continue to be provided to clients in parallel until 2020...

https://www.ftserussell.com/financial-d ... chmark-icb

This one caught my eye

20103020 Marijuana Producers SubSector (4) New "4Q2018 Update: New Subsector

Split from RGS 0410856 - Tobacco."

Raptor.

[mrbrightside]

This is a marvelous upgrade to the HYPTUSS particularly for someone is is a) very lazy and b) loves pouring over statistics. FWIW here is the breakdown of my SIPP started back in 2003.

                                                                                Value    Div   Fcst 
Share                           Epic    Sector                                 %Total %Total  Yield
Admiral Group                   ADM     Nonlife Insurance                        2.4%   2.8%   6.4%
Anglo American                  AAL     Mining                                   6.2%   4.7%   4.2%
AstraZeneca                     AZN     Pharmaceuticals & Biotechnology          3.3%   2.1%   3.5%
BAE Systems                     BA      Aerospace & Defence                      2.5%   2.3%   5.0%
Balfour Beatty                  BBY     Construction & Materials                 2.4%   1.0%   2.3%
Barclays                        BARC    Banks                                    0.2%   0.2%   4.7%
Barratt Developments            BDEV    Household Goods & Home Construction      2.4%   3.2%   7.5%
BHP Group                       BHP     Mining                                   3.2%   5.0%   8.7%
BP                              BP      Oil & Gas Producers                      4.0%   4.3%   5.9%
British American Tobacco        BATS    Tobacco                                  5.3%   6.5%   6.8%
British Land Company            BLND    Retail REITs                             2.0%   1.9%   5.3%
BT Group                        BT-A    Fixed Line Telecommunications            2.2%   2.7%   7.0%
Centrica                        CNA     Gas, Water & Multiutilities              0.7%   1.1%   9.5%
GlaxoSmithKline                 GSK     Pharmaceuticals & Biotechnology          1.8%   1.8%   5.3%
Greene King                     GNK     Travel & Leisure                         3.5%   3.3%   5.2%
Hays                            HAS     Support Services                         1.9%   2.0%   6.1%
HSBC Holdings                   HSBA    Banks                                    3.8%   4.3%   6.3%
Imperial Brands                 IMB     Tobacco                                  3.5%   4.8%   7.8%
Land Securities Group           LAND    Industrial & Office REITs                2.8%   2.7%   5.4%
Legal and General Group         LGEN    Life Insurance                           6.0%   7.0%   6.5%
Marston's                       MARS    Travel & Leisure                         1.5%   2.1%   7.7%
Morrison (Wm) Supermarkets      MRW     Food & Drug Retailers                    2.6%   1.5%   3.1%
National Grid                   NG      Multiutilities                           2.1%   2.1%   5.6%
Next                            NXT     General Retailers                        2.6%   1.5%   3.2%
Pearson                         PSON    Media                                    2.2%   1.0%   2.5%
Rio Tinto                       RIO     Mining                                   2.8%   2.9%   5.8%
Royal Dutch Shell 'B'           RDSB    Oil & Gas Producers                      3.3%   3.6%   6.2%
Sainsbury (J)                   SBRY    Food & Drug Retailers                    1.5%   1.3%   4.8%
Severn Trent                    SVT     Gas, Water & Multiutilities              2.2%   2.0%   4.9%
SSE                             SSE     Electricity                              2.1%   2.5%   6.7%
Standard Life Aberdeen plc      SLA     Life Insurance                           1.3%   2.2%   9.4%
Tate and Lyle                   TATE    Food Producers                           2.2%   1.7%   4.4%
Tesco                           TSCO    Food & Drug Retailers                    1.5%   0.9%   3.2%
TP ICAP                         TCAP    Financial Services                       0.7%   0.8%   5.9%
Unilever                        ULVR    Food Producers                           4.7%   3.0%   3.5%
United Utilities Group          UU      Gas, Water & Multiutilities              3.1%   2.7%   4.9%
Vodafone Group                  VOD     Mobile Telecommunications                1.9%   3.1%   9.4%
William Hill                    WMH     Travel & Leisure                         1.4%   1.4%   5.6%

Running Yield:    5.57%

                                                 Value    Div
Sector                                          %Total %Total
Nonlife Insurance                                 2.4%   2.8%
Mining                                           12.2%  12.5%
Pharmaceuticals & Biotechnology                   5.2%   3.8%
Aerospace & Defence                               2.5%   2.3%
Construction & Materials                          2.4%   1.0%
Banks                                             4.1%   4.5%
Household Goods & Home Construction               2.4%   3.2%
Oil & Gas Producers                               7.3%   7.9%
Tobacco                                           8.8%  11.3%
Retail REITs                                      2.0%   1.9%
Fixed Line Telecommunications                     2.2%   2.7%
Gas, Water & Multiutilities                       6.0%   5.9%
Travel & Leisure                                  6.5%   6.9%
Support Services                                  1.9%   2.0%
Industrial & Office REITs                         2.8%   2.7%
Life Insurance                                    7.3%   9.2%
Food & Drug Retailers                             5.6%   3.6%
Multiutilities                                    2.1%   2.1%
General Retailers                                 2.6%   1.5%
Media                                             2.2%   1.0%
Electricity                                       2.1%   2.5%
Food Producers                                    6.9%   4.7%
Financial Services                                0.7%   0.8%
Mobile Telecommunications                         1.9%   3.1%

[granretire]

Digital data sheet shows BHP instead of BLT for BHP Billiton

OK I know what to do, but there may be other name changes that I haven't found.

Thanks for the hyptuss version

[kiloran]

Digital data sheet shows BHP instead of BLT for BHP Billiton

OK I know what to do, but there may be other name changes that I haven't found.

Thanks for the hyptuss version

BHP is correct.
BLT changed to BHP: viewtopic.php?f=27&t=14838

--kiloran

[granretire]

Apologies - I'm out-of-date! Will try and pay attention next time

[Raptor]

Done a vanilla update of my portfolio with the new spreadsheet.

Thanks boys. Will now go back and check my changes to datasheet and vba. All looks good will post my portfolio using this next month.

Did notice duplicates in the datasheet is there a reason for this. Not that it affects me as I do not have any of the shares, TCAP springs to mind.

Raptor.

[Itsallaguess]

Someone pointed out that LLPD is not defined as Bank sector.

Should it be? 'Fixed interest' may be a more appropriate 'sector name' for preference shares (not all are issued by 'banks').

The FTSE web-site has no sector or super-sector defined for these shares. I think it is therefore an area for "user defined". For me it is still financials and banks but I do not own any so will leave it up to the spreadsheet oweners to decide if it needs to be changed.

If anyone would prefer any of the sector or super-sector definitions to be something else, then the tool is actually designed so that users can re-define the default definitions for these fields, as explained in the second post on this thread -

I should also add that the sector / super-sector naming convention is still user-editable on the Digital Look Data Sheet, for anyone that wishes to alter the current allocated sectors for any listed company.

https://www.lemonfool.co.uk/viewtopic.php?p=206916#p206881

Cheers,

Itsallaguess

[Raptor]

Have now updated everything and can say I like the changes. All work well. Although I have a request for additional "sorts" on the "overview screen".

Mind you used the "User Guide" to make sure that I had everything copied correctly this time. Did notice:-

15. If you have a customised version of the Digital Look Data Sheet (DLDS)
1. On the old DLDS, select all cells, right-click and select “Clear all contents”
2. On the new DLDS, select all cells, right-click and select “Copy”
3. On the old DLDS, select cell A1, right-click and select “Paste”

Be aware if you do this you will have problems with the new functions of super sectors (I just copied my changes and added the super sectors seperately. Lucky for me only a few).

Raptor.

[spiderbill]

Usual kudos on the latest version. Very useful new features.

I've run into a small issue which, along with a Microsoft update and a Libre Office update had me puzzled for a while but I've now managed to isolate.

I have a Canadian/US share in my portfolio - Sun Life Canada (SLF) - which I've manually added to the data sheet and have been running satisfactorily for many years and versions. I couldn't retrieve the share price of course, but I just added that manually from my other spreadsheet after HYPTUS had retrieved all the others. (and I had to do a currency conversion first anyway)

However with the new version the very presence of SLF causes an error message when trying to get the prices - saying that there are "problems retrieving prices from Yahoo. Please try again" - but crucially no longer getting the prices of the other shares and wiping out what was in that field for them all.

I can get around it by deleting the SLF ticker entry before pressing the Get Prices button and then adding it back after it's finished, so it's not a deal-breaker, but I thought I'd mention it since it feels likely that the function has been recoded in a way that makes any failure on one share, even when it's the last one in the table, disrupt all the others as well. It may not be possible to avoid this effect but if it is then it might be as well to do so to get better error handling.

Just to be clear that I certainly don't expect an overseas share price retrieval capability - that's beyond the spec of the tool and there would be the currency conversion to contend with. (Eventide's Excel addon does retrieve it but of course it's not involved in doing all the other stuff that HYPTUS does.) I'm just mentioning it in case of other problems possibly cropping up that might be harder to diagnose otherwise.

[kiloran]

I have a Canadian/US share in my portfolio - Sun Life Canada (SLF) - which I've manually added to the data sheet and have been running satisfactorily for many years and versions. I couldn't retrieve the share price of course, but I just added that manually from my other spreadsheet after HYPTUS had retrieved all the others. (and I had to do a currency conversion first anyway)

However with the new version the very presence of SLF causes an error message when trying to get the prices - saying that there are "problems retrieving prices from Yahoo. Please try again" - but crucially no longer getting the prices of the other shares and wiping out what was in that field for them all.

Excel or LibreOffice? Which version?

I don't think we made any changes to the price retrieval process, so this does seem a little odd.
Which version of HYPTUSS were you previously using?

--kiloran

[spiderbill]

Excel or LibreOffice? Which version?

I don't think we made any changes to the price retrieval process, so this does seem a little odd.
Which version of HYPTUSS were you previously using?
--kiloran

Hi kiloran

Libre Office, just updated to 6.1.5.2 (x64) from 5.4-ish
Previous HYPTUSS is b-h
Never had a similar occurance through many versions. Got a few contradictory results as I was trying to isolate it so eventually cleared it out and added each share in manually, testing retrieval after each addition. Of course SLF was the last one and that's when it failed.