New HYPTUSS versions 11.77 and b-q

[kiloran]

Apologies for the long delay, but I've now published HYPTUSS versions 11.77 and b-q at: http://lemonfoolfinancialsoftware.weebl ... op-up.html

The changes are:

1. Fixed the problem with the Overview "Generate Lemon Fool Table" button on the Excel version. See viewtopic.php?f=27&t=32740
   This was caused by a Windows bug from Windows 8 onwards which affects some versions of Excel. I've written a totally different piece of code which works around the bug.
2. Stockopedia have changed the layout of their web page for dividends so I've updated HYPTUSS to handle this. I've also discovered that the data on the new web page is rather flaky for non-sterling dividends; I'll raise this issue with them when I've found a sensible way to contact them
3. Obtaining yields for Investment Trusts has been enhanced. Previously, HYPTUSS tried to get forecast or latest yields from Sharecast, but if this resulted in a "n/a" response for ITs, it then got yields from the AIC website. As published, the new HYPTUSS works the same way, but if you change cell B11 on the Parameters sheet from FALSE to TRUE, yields for ITs will always be taken from AIC. Thanks to seagles for this idea.
4. Yields on the LibreOffice version are now 2 decimal places to align with the Excel version
5. I discovered that Sharecast sometimes has 3 dividend records for the same payment date. HYPTUSS previously assumed that there would be a maximum of two (normal dividend plus a special). The new version of HYPTUSS will now report as many dividends as there might be for a single payment date

Migration from your current version is described on page 26 of the User Guide, which includes a useful video created by Itsallaguess

--kiloran

[seagles]

You are welcome. I was going to look at that piece of code when AIC had data fro JEGI. You have saved me doing it, so a big thank you. Will do upgrade of my "test" data later. Your efforts are much appreciated.

[seagles]

Have just done a first pass and everything worked as expected. Using parameter B11 to true got the correct yields from AIC, it also "fixed" a problem I have had for a long time (which I had modified the code previously, so another change no longer needed) which was with MCT which always produced ???? in the HYPTUSS due to Sharecast not set-up properly for MCT, so code did not look it up in AIC.
Will continue to "migrate" my data.

[seagles]

One suggestion for your next change. The BUTTON "Get Sharecast yields", change to just "Get Yields", as with the new parameter this could be AIC (mind you it could have been on previous incarnations of HYPTUSS).

[Grumpsimus]

Many thanks for the update. I have just updated my two portfolios to version b-q and can confirm that everything works as expected. Just a pity that it was on a day when shares dived because of the dreadful events in the Ukraine.

Thanks again for all you hard work with this update.

[daveh]

A couple of questions:

I'm still using version 11.52 (mostly because I'd have to redo all the links to my own spreadsheet) but thought I'd have ago at setting up the latest version. I have a number of ETFs and preference shares that I added manually to the company data sheet page in 11.52, v.52 successfully downloads prices (except for AV.B), but I had to manually add yield info.

I followed the instructions for transferring over the data for the new version and again prices for the ETFs are downloaded but no yield info and the yield data stops downloading when the block of ETFs are reached, so shares in the list after the ETFs don't download yield info.
Is there anyway for me to add ETFs so their yield information is collected?

If necessary I can reorder the list so the ETFs and prefs are at the end so all the share data is downloaded first.

ETFs are VWRL, EMDV, IAPD and IDVY prefs are LLPC LLPD and AV.B

[jeff50]

Thank you for your sterling work on the update.
Regards
Jeff

[kiloran]

A couple of questions:

I'm still using version 11.52 (mostly because I'd have to redo all the links to my own spreadsheet) but thought I'd have ago at setting up the latest version. I have a number of ETFs and preference shares that I added manually to the company data sheet page in 11.52, v.52 successfully downloads prices (except for AV.B), but I had to manually add yield info.

I followed the instructions for transferring over the data for the new version and again prices for the ETFs are downloaded but no yield info and the yield data stops downloading when the block of ETFs are reached, so shares in the list after the ETFs don't download yield info.
Is there anyway for me to add ETFs so their yield information is collected?

If necessary I can reorder the list so the ETFs and prefs are at the end so all the share data is downloaded first.

ETFs are VWRL, EMDV, IAPD and IDVY prefs are LLPC LLPD and AV.B

HYPTUSS was never intended to handle ETFs, but despite that we do have a few in there such as VWRL and VMID. Retrieving prices from Yahoo is OK, but Sharecast does not have any yield info for ETFs, so HYPTUSS just displays "????" in blue to highlight the problem and allow a manual entry. I have VWRL myself and I've never found a problem where subsequent shares fail to display data. That does sound odd, I wonder if it's Excel or OS related? I use Excel 2010 and 2016 on Windows 10.
I could look at adding in ETF yield info if you can suggest a source of data.... I've looked quite a few times and never found anything suitable for downloading

--kiloran

[daveh]

A couple of questions:

I'm still using version 11.52 (mostly because I'd have to redo all the links to my own spreadsheet) but thought I'd have ago at setting up the latest version. I have a number of ETFs and preference shares that I added manually to the company data sheet page in 11.52, v.52 successfully downloads prices (except for AV.B), but I had to manually add yield info.

I followed the instructions for transferring over the data for the new version and again prices for the ETFs are downloaded but no yield info and the yield data stops downloading when the block of ETFs are reached, so shares in the list after the ETFs don't download yield info.
Is there anyway for me to add ETFs so their yield information is collected?

If necessary I can reorder the list so the ETFs and prefs are at the end so all the share data is downloaded first.

ETFs are VWRL, EMDV, IAPD and IDVY prefs are LLPC LLPD and AV.B

HYPTUSS was never intended to handle ETFs, but despite that we do have a few in there such as VWRL and VMID. Retrieving prices from Yahoo is OK, but Sharecast does not have any yield info for ETFs, so HYPTUSS just displays "????" in blue to highlight the problem and allow a manual entry. I have VWRL myself and I've never found a problem where subsequent shares fail to display data. That does sound odd, I wonder if it's Excel or OS related? I use Excel 2010 and 2016 on Windows 10.
I could look at adding in ETF yield info if you can suggest a source of data.... I've looked quite a few times and never found anything suitable for downloading

--kiloran

Thanks for the reply. I've played around with the order in the latest version of HYPTUSS and the problem seems to be just EMDV, and I've put that last so everything gets downloaded first, I now get ???? for all the other ETF yields ( I've added in IAPD, IDVY, EMDV and LLPC to the company data sheet) but for EMDV its yield is blank and the download stops with a run error, but the Yahoo price button downloads a price correctly. That just leaves AV.B which fails to download a price, so I've deleted it at the moment.

For ETF yields I've also not found one place that gives yield info for all the ETFs I'm interested in so I either calculate my own historic yield or get it off the companies website or hargreaves website and then enter by hand.

[kiloran]

A couple of questions:

I'm still using version 11.52 (mostly because I'd have to redo all the links to my own spreadsheet) but thought I'd have ago at setting up the latest version. I have a number of ETFs and preference shares that I added manually to the company data sheet page in 11.52, v.52 successfully downloads prices (except for AV.B), but I had to manually add yield info.

I followed the instructions for transferring over the data for the new version and again prices for the ETFs are downloaded but no yield info and the yield data stops downloading when the block of ETFs are reached, so shares in the list after the ETFs don't download yield info.
Is there anyway for me to add ETFs so their yield information is collected?

If necessary I can reorder the list so the ETFs and prefs are at the end so all the share data is downloaded first.

ETFs are VWRL, EMDV, IAPD and IDVY prefs are LLPC LLPD and AV.B

HYPTUSS was never intended to handle ETFs, but despite that we do have a few in there such as VWRL and VMID. Retrieving prices from Yahoo is OK, but Sharecast does not have any yield info for ETFs, so HYPTUSS just displays "????" in blue to highlight the problem and allow a manual entry. I have VWRL myself and I've never found a problem where subsequent shares fail to display data. That does sound odd, I wonder if it's Excel or OS related? I use Excel 2010 and 2016 on Windows 10.
I could look at adding in ETF yield info if you can suggest a source of data.... I've looked quite a few times and never found anything suitable for downloading

--kiloran

Thanks for the reply. I've played around with the order in the latest version of HYPTUSS and the problem seems to be just EMDV, and I've put that last so everything gets downloaded first, I now get ???? for all the other ETF yields ( I've added in IAPD, IDVY, EMDV and LLPC to the company data sheet) but for EMDV its yield is blank and the download stops with a run error, but the Yahoo price button downloads a price correctly. That just leaves AV.B which fails to download a price, so I've deleted it at the moment.

For ETF yields I've also not found one place that gives yield info for all the ETFs I'm interested in so I either calculate my own historic yield or get it off the companies website or hargreaves website and then enter by hand.

For AV.B, I guess you mean this: https://finance.yahoo.com/quote/AV-B.L
For HYPTUSS, you need symbol AV-B, then HYPTUSS automatically adds the .L suffix. That should hopefully provide the price for you

For EMDV, I could not find a Sharecast web page, maybe due to the really poor Sharecast search facility. What do you have for the URL suffix (Column A) for EMDV in your Company Data Sheet?

--kiloran

[daveh]

For ETF yields I've also not found one place that gives yield info for all the ETFs I'm interested in so I either calculate my own historic yield or get it off the companies website or hargreaves website and then enter by hand.
For AV.B, I guess you mean this: https://finance.yahoo.com/quote/AV-B.L
For HYPTUSS, you need symbol AV-B, then HYPTUSS automatically adds the .L suffix. That should hopefully provide the price for you

For EMDV, I could not find a Sharecast web page, maybe due to the really poor Sharecast search facility. What do you have for the URL suffix (Column A) for EMDV in your Company Data Sheet?

--kiloran

Yes that's the AV.B I mean. I tried to find it on Yahoo but failed, but I see my problem I was searching with and used AV.B rather than AV-B. I'll have a play with that one.

I used
/etf/SSGA_SPDR_ETFS_Europe_I_SPDR_SP_Emerging Markets_Dividend_Aristocrat_UCIT_ETF

I basically used the same format as for UKDV. I did the same with IDVY and IAPD just used the same format as IUKD which seems to have worked. SPDR® S&P® Emerging Markets Dividend Aristocrats UCITS ETF is what State Street call it and it is in the Europe I umbrella fund. maybe I'm missing the s on the end of Aristocrats? No that didn't work still stops when it reaches EMDV

[kiloran]

For ETF yields I've also not found one place that gives yield info for all the ETFs I'm interested in so I either calculate my own historic yield or get it off the companies website or hargreaves website and then enter by hand.
For AV.B, I guess you mean this: https://finance.yahoo.com/quote/AV-B.L
For HYPTUSS, you need symbol AV-B, then HYPTUSS automatically adds the .L suffix. That should hopefully provide the price for you

For EMDV, I could not find a Sharecast web page, maybe due to the really poor Sharecast search facility. What do you have for the URL suffix (Column A) for EMDV in your Company Data Sheet?

--kiloran

Yes that's the AV.B I mean. I tried to find it on Yahoo but failed, but I see my problem I was searching with and used AV.B rather than AV-B. I'll have a play with that one.

I used
/etf/SSGA_SPDR_ETFS_Europe_I_SPDR_SP_Emerging Markets_Dividend_Aristocrat_UCIT_ETF

I basically used the same format as for UKDV. I did the same with IDVY and IAPD just used the same format as IUKD which seems to have worked. SPDR® S&P® Emerging Markets Dividend Aristocrats UCITS ETF is what State Street call it and it is in the Europe I umbrella fund. maybe I'm missing the s on the end of Aristocrats? No that didn't work still stops when it reaches EMDV

I tried your data for the URL Suffix and the HYPTUSS worked as expected, returning ???? for the yield. I had a gander at Sharecast for that URL and got "404 not found". That should not affect HYPTUSS since it traps the error and returns ????

I had a further look at Sharecast and finally found the correct page, despite the useless search facility. The correct URL for EMDV is https://www.sharecast.com/etf/SSGA_SPDR ... ETF/prices
Doesn't solve your problem with a lack of yields in Sharecast, of course.

I played around a bit more and could not get HYPTUSS to fail as you described. I tried EMDV at the start of the list, in the middle, and at the end, and it worked fine.

I've done some more digging on ETF yields, and found a couple of candidates:
https://www.hl.co.uk/shares/shares-sear ... m-mkts-div
https://uk.investing.com/etfs/spdr-s-p- ... ividend-uk
Inevitably, they report different yields
The only way to implement this would be to add another column to the Company Data Sheet. Not a massive change, I suspect, so I've added this to my to-do list (well, maybe my something-to-think-about list)

--kiloran

[TUK020]

Just downloaded the latest version b-q for Libre Office Version: 6.4.7.2, running on Linux Mint.
Working better.Retrieves prices on shares ok, but when it goes for dividend/yield scrape, now gets down to 26th share on the list RMMC IT before it freezes (previously was CTY at 7th on the list).
Any further info to help on this?

[kiloran]

Just downloaded the latest version b-q for Libre Office Version: 6.4.7.2, running on Linux Mint.
Working better.Retrieves prices on shares ok, but when it goes for dividend/yield scrape, now gets down to 26th share on the list RMMC IT before it freezes (previously was CTY at 7th on the list).
Any further info to help on this?

I'm stumped by this one. I've tested:
Laptop 1 Windows 10 LibreOffice 7.3.0.3
Laptop 1 Windows 10 LibreOffice 6.3.3.2
Laptop 1 Linux Mint LibreOffice 7.2.5.2
Laptop 1 Linux Ubuntu LibreOffice 7.2.5.2
Laptop 2 Windows 10 LibreOffice 6.2.2.2

All retrieved the yield impeccably (0.0% for RMMC with Parameters B11 = FALSE or TRUE)

I used the following sequence of 34 shares:

ADM
AZN
BA
BDEV
BHP
BP
BLND
BVIC
BT-A
CTY
GSK
HSBA
LGEN
MRC
MRCH
MONY
NG
PSON
LWDB
RIO
RMMC
RMG
SGE
SBRY
SDRC
SGRO
SHEL
TATE
TW
TSCO
ULVR
UU
VWRL
VOD

Baffling! Out of interest, what happens if you make RMMC the first share?

--kiloran

[TUK020]

Just downloaded the latest version b-q for Libre Office Version: 6.4.7.2, running on Linux Mint.
Working better.Retrieves prices on shares ok, but when it goes for dividend/yield scrape, now gets down to 26th share on the list RMMC IT before it freezes (previously was CTY at 7th on the list).
Any further info to help on this?

I'm stumped by this one. I've tested:
Laptop 1 Windows 10 LibreOffice 7.3.0.3
Laptop 1 Windows 10 LibreOffice 6.3.3.2
Laptop 1 Linux Mint LibreOffice 7.2.5.2
Laptop 1 Linux Ubuntu LibreOffice 7.2.5.2
Laptop 2 Windows 10 LibreOffice 6.2.2.2

All retrieved the yield impeccably (0.0% for RMMC with Parameters B11 = FALSE or TRUE)

I used the following sequence of 34 shares:

ADM
AZN
BA
BDEV
BHP
BP
BLND
BVIC
BT-A
CTY
GSK
HSBA
LGEN
MRC
MRCH
MONY
NG
PSON
LWDB
RIO
RMMC
RMG
SGE
SBRY
SDRC
SGRO
SHEL
TATE
TW
TSCO
ULVR
UU
VWRL
VOD

Baffling! Out of interest, what happens if you make RMMC the first share?

--kiloran

Ooops, Sorry, I didn't twig that it was atually stalling on the one prior to RMMC, which was RIT Capital Partners. It managed to retireve the latest/fcst yield, and cover, but not the PE.
I deleted and added this and a couple of other shares (to get it at the bottom, rather than first), and realised it then stalled the same way on Scottish Mortgage IT

[Arborbridge]

Apologies for the long delay, but I've now published HYPTUSS versions 11.77 and b-q at: http://lemonfoolfinancialsoftware.weebl ... op-up.html


--kiloran

Thanks for the latest version which has resolved my problem with the run stopping at MRC/LWDB in ITs.

Arb.

[kiloran]

Just downloaded the latest version b-q for Libre Office Version: 6.4.7.2, running on Linux Mint.
Working better.Retrieves prices on shares ok, but when it goes for dividend/yield scrape, now gets down to 26th share on the list RMMC IT before it freezes (previously was CTY at 7th on the list).
Any further info to help on this?

I'm stumped by this one. I've tested:
Laptop 1 Windows 10 LibreOffice 7.3.0.3
Laptop 1 Windows 10 LibreOffice 6.3.3.2
Laptop 1 Linux Mint LibreOffice 7.2.5.2
Laptop 1 Linux Ubuntu LibreOffice 7.2.5.2
Laptop 2 Windows 10 LibreOffice 6.2.2.2

All retrieved the yield impeccably (0.0% for RMMC with Parameters B11 = FALSE or TRUE)

I used the following sequence of 34 shares:

ADM
AZN
BA
BDEV
BHP
BP
BLND
BVIC
BT-A
CTY
GSK
HSBA
LGEN
MRC
MRCH
MONY
NG
PSON
LWDB
RIO
RMMC
RMG
SGE
SBRY
SDRC
SGRO
SHEL
TATE
TW
TSCO
ULVR
UU
VWRL
VOD

Baffling! Out of interest, what happens if you make RMMC the first share?

--kiloran

Ooops, Sorry, I didn't twig that it was atually stalling on the one prior to RMMC, which was RIT Capital Partners. It managed to retireve the latest/fcst yield, and cover, but not the PE.
I deleted and added this and a couple of other shares (to get it at the bottom, rather than first), and realised it then stalled the same way on Scottish Mortgage IT

OK, I can reproduce this. I noticed that RCP and SMT have rather extreme P/E on the Sharecast website (-3,075.0 and 1613.7 respectively) and I suspect that the comma may be causing the failure.
Should be easy to fix but I'll be 300 miles from home over the next week so it might be next weekend before I can seriously look at this (mum's 98th birthday takes priority over HYPTUSS ), Maybe later today if my suitcase packing goes to plan.

--kiloran

[TUK020]

Just downloaded the latest version b-q for Libre Office Version: 6.4.7.2, running on Linux Mint.
Working better.Retrieves prices on shares ok, but when it goes for dividend/yield scrape, now gets down to 26th share on the list RMMC IT before it freezes (previously was CTY at 7th on the list).
Any further info to help on this?

I'm stumped by this one. I've tested:
Laptop 1 Windows 10 LibreOffice 7.3.0.3
Laptop 1 Windows 10 LibreOffice 6.3.3.2
Laptop 1 Linux Mint LibreOffice 7.2.5.2
Laptop 1 Linux Ubuntu LibreOffice 7.2.5.2
Laptop 2 Windows 10 LibreOffice 6.2.2.2

All retrieved the yield impeccably (0.0% for RMMC with Parameters B11 = FALSE or TRUE)

I used the following sequence of 34 shares:

ADM
AZN
BA
BDEV
BHP
BP
BLND
BVIC
BT-A
CTY
GSK
HSBA
LGEN
MRC
MRCH
MONY
NG
PSON
LWDB
RIO
RMMC
RMG
SGE
SBRY
SDRC
SGRO
SHEL
TATE
TW
TSCO
ULVR
UU
VWRL
VOD

Baffling! Out of interest, what happens if you make RMMC the first share?

--kiloran

Ooops, Sorry, I didn't twig that it was atually stalling on the one prior to RMMC, which was RIT Capital Partners. It managed to retireve the latest/fcst yield, and cover, but not the PE.
I deleted and added this and a couple of other shares (to get it at the bottom, rather than first), and realised it then stalled the same way on Scottish Mortgage IT

OK, I can reproduce this. I noticed that RCP and SMT have rather extreme P/E on the Sharecast website (-3,075.0 and 1613.7 respectively) and I suspect that the comma may be causing the failure.
Should be easy to fix but I'll be 300 miles from home over the next week so it might be next weekend before I can seriously look at this (mum's 98th birthday takes priority over HYPTUSS ), Maybe later today if my suitcase packing goes to plan.

--kiloran

Thanks.
I guess PEs in the '000s is stretching the label on the tin (High Yield PTUSS)!
R

[daveh]

Thanks for the help. Changed the first column in the Company data sheet for EMDV as suggested and the yield scrape no longer stops there and I get a blue box with ???? as with the other ETFs. I added AV.B to the company data sheets as AV-B and that works for the prices, but now the yield scrape fails there with a Runtime error '424' Object required error message. I'll have a play with what I put in the first column for AV.B as I think that is probably where the problem is. But as AV.B is now last and it collects all the prices and all the yields (or ??? for ETFs) for everything else I can just add in the AV.B yield by hand as its easy to calculate being a pref. with a known coupon.

Thanks

[kiloran]

Just downloaded the latest version b-q for Libre Office Version: 6.4.7.2, running on Linux Mint.
Working better.Retrieves prices on shares ok, but when it goes for dividend/yield scrape, now gets down to 26th share on the list RMMC IT before it freezes (previously was CTY at 7th on the list).
Any further info to help on this?

I'm stumped by this one. I've tested:
Laptop 1 Windows 10 LibreOffice 7.3.0.3
Laptop 1 Windows 10 LibreOffice 6.3.3.2
Laptop 1 Linux Mint LibreOffice 7.2.5.2
Laptop 1 Linux Ubuntu LibreOffice 7.2.5.2
Laptop 2 Windows 10 LibreOffice 6.2.2.2

All retrieved the yield impeccably (0.0% for RMMC with Parameters B11 = FALSE or TRUE)

I used the following sequence of 34 shares:

ADM
AZN
BA
BDEV
BHP
BP
BLND
BVIC
BT-A
CTY
GSK
HSBA
LGEN
MRC
MRCH
MONY
NG
PSON
LWDB
RIO
RMMC
RMG
SGE
SBRY
SDRC
SGRO
SHEL
TATE
TW
TSCO
ULVR
UU
VWRL
VOD

Baffling! Out of interest, what happens if you make RMMC the first share?

--kiloran

Ooops, Sorry, I didn't twig that it was atually stalling on the one prior to RMMC, which was RIT Capital Partners. It managed to retireve the latest/fcst yield, and cover, but not the PE.
I deleted and added this and a couple of other shares (to get it at the bottom, rather than first), and realised it then stalled the same way on Scottish Mortgage IT

OK, I can reproduce this. I noticed that RCP and SMT have rather extreme P/E on the Sharecast website (-3,075.0 and 1613.7 respectively) and I suspect that the comma may be causing the failure.
Should be easy to fix but I'll be 300 miles from home over the next week so it might be next weekend before I can seriously look at this (mum's 98th birthday takes priority over HYPTUSS ), Maybe later today if my suitcase packing goes to plan.

--kiloran

The problem was indeed the comma in the forecast cover and PE, causing a problem when converting data to a numeric format. I've fixed this and published LibreOffice version b-r at http://lemonfoolfinancialsoftware.weebl ... op-up.html

--kiloran