The Lemon Fool

Something strange has happened with ITs.I have three HYPTUSS spreadsheets. Today I run update yields, 2 run clean. The 3rd (mine) failed with a "Run Time Error 424", whilst processing the ITs in the list. Debug came up with:-
' Debug.Print Len(table_data(0))
Dim item As Integer
For item = 1 To Len(table_data(0))
* If table_data(0).Children(item).innerText = "Dividend yield (%)" Then * failing line
yield_string = table_data(1).Children(item).innerText / 100
Exit For
End If
Next item

So I downloaded the latest and greatest and added the ITs still failed. see list of ITs added.

BP
DIG
HFEL
MRCH
MYI
SCF
JEGI
MNKS
SMT
FCIT
UKW
MCT


Any chance it can be looked at. I tried to narrow down which IT maybe causing this but it seems to be independant of which ITs are in the list. It works ok with 1 or 2 then fails and with different ITs in the list. Was going to do some more work but have something important to do before end of day.

seagles wrote:Something strange has happened with ITs.I have three HYPTUSS spreadsheets. Today I run update yields, 2 run clean. The 3rd (mine) failed with a "Run Time Error 424", whilst processing the ITs in the list. Debug came up with:-
' Debug.Print Len(table_data(0))
Dim item As Integer
For item = 1 To Len(table_data(0))
* If table_data(0).Children(item).innerText = "Dividend yield (%)" Then * failing line
yield_string = table_data(1).Children(item).innerText / 100
Exit For
End If
Next item

So I downloaded the latest and greatest and added the ITs still failed. see list of ITs added.

BP
DIG
HFEL
MRCH
MYI
SCF
JEGI
MNKS
SMT
FCIT
UKW
MCT


Any chance it can be looked at. I tried to narrow down which IT maybe causing this but it seems to be independant of which ITs are in the list. It works ok with 1 or 2 then fails and with different ITs in the list. Was going to do some more work but have something important to do before end of day.

Hi Seagles. I checked your list on v11.78. JEGI returned n/a but did not produce an error. The last share, MCT, did fail with the same error that you found. I'll have a look at it.

--kiloran

kiloran wrote:

seagles wrote:
Any chance it can be looked at.

I tried to narrow down which IT maybe causing this but it seems to be independant of which ITs are in the list. It works ok with 1 or 2 then fails and with different ITs in the list.

I checked your list on v11.78. JEGI returned n/a but did not produce an error.

The last share, MCT, did fail with the same error that you found. I'll have a look at it.

It looks like they've added a rogue character to the end of the AIC 'Dividend Yield (%)' header-field, so currently none of the AIC-generated yields will be getting returned correctly, unfortunately...

Changing the following line in the AIC-yield section of code gets it all back up and running OK, and hopefully also covers any future alterations if they remove the character or add to their current collection...

Change -

If table_data(0).Children(item).innerText = "Dividend yield (%)" Then

To -

If Left(table_data(0).Children(item).innerText, 18) = "Dividend yield (%)" Then

Just noting though, that on my Office 2007 set-up, I don't actually see the original run-time error being generated, but just see the AIC yield-retrieval not working (yield sticks at N/A).

The above VBA fix gets the AIC yield-retrieval working again for me, and hopefully fixes the error as well, for those that are seeing it?

Cheers,

Itsallaguess

Worked fine on my HYPTUSS. Strange how the other HYPTUSS worked and that contains ITs as well? Maybe not all entries contain the "spurious" character.
I was going to say that I thought it was MCT entry as I have had problems with them before. Kilorans last update added for the AIC use of yields for ITs fixed that problem. However, it failed even when I removed MCT from the list. Wonder if it is worth a PM to the AIC guru on TLF?

I added change to one of my other HYPTUSS (daughters portfolio) and it worked there as well. When I went to the 3rd one found it was an old version (granddaughters portfolio) but that only contains 3 entries (will now add "update hyptuss" to my todo list).

Thanks guys for fast response, will now continue with month end updates.

seagles wrote:
Worked fine on my HYPTUSS.

Strange how the other HYPTUSS worked and that contains ITs as well?

Maybe not all entries contain the "spurious" character.

It'll be more down to the fact that the AIC-yield process is what we're hoping to be the catch-all figure if the previous Sharecast IT-yield processes don't have a viable yield to use, so it's likely that your previously working portfolios simply didn't access that later AIC process, having been satisfied by being able to use the Sharecast yields with those IT holdings.

On top of that, there seems to be a slight difference in how different versions of Office are handling this spurious character issue, but I think the simplest way to get rid of that particular divergence is to also just test the field with the new 'LEFT'' method in the above VBA fix, and we can then hopefully kill two birds with one stone and also protect a little against any similar header-related issues in the future...

Thanks for the feedback that you've got it working OK now - hopefully Kiloran can also verify that the fix works for him too.

Cheers,

Itsallaguess

Itsallaguess wrote:It'll be more down to the fact that the AIC-yield process is what we're hoping to be the catch-all figure if the previous Sharecast IT-yield processes don't have a viable yield to use, so it's likely that your previously working portfolios simply didn't access that later AIC process, having been satisfied by being able to use the Sharecast yields with those IT holdings.

On top of that, there seems to be a slight difference in how different versions of Office are handling this spurious character issue, but I think the simplest way to get rid of that particular divergence is to also just test the field with the new 'LEFT'' method in the above VBA fix, and we can then hopefully kill two birds with one stone and also protect a little against any similar header-related issues in the future...

Thanks for the feedback that you've got it working OK now - hopefully Kiloran can also verify that the fix works for him too.

Cheers,

Itsallaguess

My daughters HYPTUSS is set-up same as mine. Kilorans change to use AIC was independant of what Sharecast returned, it checked the "parameter" he added and if it was an IT went to AIC and "grabbed" their yield and overwrote the Sharecast. Kiloran will be able to confirm that though.

I agree with your thoughts though. When I coded I always took into account that there were a lot of "bad" programmers out there and many "mismatched" database. My peers always thought I over-engineered my code but "code errors" later in life proved my code was a lot more resilient than most. I always put that down to me starting as a hardware engineer and migrated to software (then a db specialist), thus I knew how good/bad hardware was and took that into account when coding.

I had read when checking the run time error that different versions did seem to handle it differently and could be related to that too.

seagles wrote:
My daughters HYPTUSS is set-up same as mine.

Kilorans change to use AIC was independant of what Sharecast returned, it checked the "parameter" he added and if it was an IT went to AIC and "grabbed" their yield and overwrote the Sharecast.

Not quite - there's an over-ride in the Parameters to always look at the AIC figure if that parameter is set, but even when it's set to 'FALSE', it will then try to use the Sharecast yield figure if there's one available for a particular IT, but where Sharecast might currently have an IT yield showing as 'n/a' on that Sharecast page, the HYPTUSS tool will also then go and use the AIC yield figure, regardless of the Parameter setting.

When we look at the Sharecast page for MCT, we can see that this is what's happening, as it's currently showing as a 'n/a' latest yield -



Source - MCT Sharecast Page - https://www.sharecast.com/equity/Middlefield_Canadian_Income_Pcc

If both yours and your daughters Office versions are the same, then it might be one of the VBA 'Reference' modules that's misaligned between the two versions, which can also affect spurious issues like this.

Cheers,

Itsallaguess

Looks like my assistant ( ) has found the problem. I'll need to do some more serious testing when I get time, but I get the feeling that the cause of the failure is not consistent.... sometimes one share, sometimes another, though I suspect IAAG's fix will overcome that.

A quick check shows that the LibreOffice version also has a problem with some ITs, though I think for a slightly different reason.

The watchlist suffers from the same problem, by the way

I'll work on a fix for both and publish updated versions

--kiloran

For me, it is CTY that will not download, I have to enter it manualy.

john

johnstevens77 wrote:For me, it is CTY that will not download, I have to enter it manualy.

john

I've fixed both Excel and LibreOffice versions and just doing final tests and clean-up. CTY works fine.
I expect to publish tomorrow

--kiloran

seagles wrote:Something strange has happened with ITs.I have three HYPTUSS spreadsheets. Today I run update yields, 2 run clean. The 3rd (mine) failed with a "Run Time Error 424", whilst processing the ITs in the list.

Updated HYPTUSS has been published. See: viewtopic.php?f=27&t=34642

--kiloran