Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

New HYPTUSS versions 11.70 and b-l

Discussions regarding financial software
kiloran
Lemon Quarter
Posts: 4093
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

New HYPTUSS versions 11.70 and b-l

#243621

Postby kiloran » August 11th, 2019, 8:24 pm

Itsallaguess and I have made a few changes and improvements:

  1. We had a few reports that the original Digital Look and Webfg links were not always redirecting to the Sharecast website. viewtopic.php?f=27&t=18656
    We have removed all references to Digital Look and Webfg and replaced them with Sharecast
  2. The Digital Look Data Sheet has been renamed to Company Data Sheet
  3. Share prices in USD and EUR were being displayed in USD and EUR, which resulted in incorrect portfolio values. viewtopic.php?f=27&t=18874
    If USD or EUR is detected, the share price is now converted to pence (GBp), using the Yahoo exchange rates, and the share price is highlighted in red to indicate that the price is not the native quote price
  4. If Super-Sector is being displayed, forecast yields were not correctly retrieved. viewtopic.php?f=27&t=18656#p238719
    This has now been fixed. In addition, a previous enhancement which allowed the sector "Equity Investment Instruments" to be renamed "IT xxxx" has been extended to the Super-Sector.
  5. The Sector Weighting chart in the LibreOffice version was sometimes too wide. This has been fixed.
  6. Many moons ago, Raptor suggested an interesting usage of the Company Data Sheet. viewtopic.php?f=27&t=16674
    I've checked all the code, and after a small change to the LibreOffice version, I'm happy that this works fine in both Excel and LibreOffice.
    If you have customised values in the Company Data Sheet, these can be inserted at the top of the sheet, before the default values, and the HYPTUSS should work OK. So when you update to a new HYPTUSS version, just insert rows at the top of the Company Data Sheet, and paste in your customised data. There is no need to delete the shares from the default list...... the HYPTUSS looks down through the Company Data Sheet, starting from the first row, and uses the first occurrence it finds
  7. We found that version 11.69 contained a large number of empty hidden text boxes which resulted in a much larger filesize. No idea how they appeared but they have been cleaned out

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

--kiloran

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 210 times

Re: New HYPTUSS versions 11.70 and b-l

#244662

Postby torata » August 16th, 2019, 12:30 am

A report back that may or may not be relevant.

I upgraded to LO b-l from the b-f version, but am still unable to pick up dividends from dividenddata. They stopped updating suddenly a few months ago when using b-f ("no data" I think is what it reports), but I nonchalantly assumed that it was possibly a change in structure on the dividenddata site as I was, and still am, able to access the site with a browser, so there doesn't seem to be any IP address blocking happening.

I've reverted to investorease, as it was the default choice on b-l

LO version is 6.0.5.

torata

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: New HYPTUSS versions 11.70 and b-l

#244738

Postby Itsallaguess » August 16th, 2019, 11:07 am

torata wrote:
A report back that may or may not be relevant.

I upgraded to LO b-l from the b-f version, but am still unable to pick up dividends from dividenddata.

They stopped updating suddenly a few months ago when using b-f ("no data" I think is what it reports), but I nonchalantly assumed that it was possibly a change in structure on the dividenddata site as I was, and still am, able to access the site with a browser, so there doesn't seem to be any IP address blocking happening.

I've reverted to investorease, as it was the default choice on b-l


Hi torata,

I've just downloaded the vanilla B-L open-office version from this link -

http://lemonfoolfinancialsoftware.weebly.com/hyp-top-up.html

I've opened it up and just used the default BP single-share portfolio, and changed the 'Parameters' sheet setting to allow scraping of the Dividend Data dividends, and it works without any issues.

The results of this Dividend Data scrape can be see here -

https://i.imgur.com/mjjg6Ft.png

Can you please try the same process on the vanilla B-L download, without any alterations other than to change the 'Parameters' sheet setting, and report back your findings?

Cheers,

Itsallaguess

kiloran
Lemon Quarter
Posts: 4093
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: New HYPTUSS versions 11.70 and b-l

#244750

Postby kiloran » August 16th, 2019, 11:40 am

torata wrote:A report back that may or may not be relevant.

I upgraded to LO b-l from the b-f version, but am still unable to pick up dividends from dividenddata. They stopped updating suddenly a few months ago when using b-f ("no data" I think is what it reports), but I nonchalantly assumed that it was possibly a change in structure on the dividenddata site as I was, and still am, able to access the site with a browser, so there doesn't seem to be any IP address blocking happening.

I've reverted to investorease, as it was the default choice on b-l

LO version is 6.0.5.

torata

That's odd, torata, it's working fine for me. Windows 10, LibreOffice 6.2.4.2. I don't have LibreOffice 6.0.5 but I tried it on 6.0.0.3 and 6.1.42 and those are fine, retrieving data for 20 shares.
Dividend Data certainly still throws a tantrum if it feels it's being pushed too hard, as I discovered a couple of weeks ago when I was updating the Excel version of HYPTUSS..... it locked me out after relatively few requests, and that affected HYPTUSS and my usual browsers.

I'm certainly confused why HYPTUSS fails, but your browser is OK. Right now, I can only think of a couple of possibilities:
  1. The macro spoofs the browser user agent, to make the website think the request comes from a real browser. Maybe I need to dig into this, though that will be dabbling in dark arts and will require some magic incantations.
  2. What browser are you using? Does it have an active VPN extension? I know the Opera browser has a built-in VPN, so even if Dividend Data locks out my IP address, I can still access the site with my Opera browser

If you shut down your router, wait a minute or so, then reboot it (and hopefully get a new IP address), does that solve the problem?

--kiloran

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#244753

Postby seagles » August 16th, 2019, 11:58 am

Could it be related to this SSL type error? Just a thought.

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#244771

Postby seagles » August 16th, 2019, 1:00 pm

Finally made time to test new version. Tested using a "saved" set of known shares and this allows me to compare previous to new. Tested as much of the changes as I could and all worked fine, even Raptors datasheet changes. However, did get one run time error, on dividend sheet, using the "Corporate Investors web page", get Visual basic error, Run Time Error '9', subscript out of range. I do not use this now but used to (have this enabled on another spreadsheet that I use). Worked on "previous" spreadheet.

The only addition I needed to make was to copy across my previous "snapshot" from previous spreadsheet so as to keep track of snapshots, did not see that in the "User Guide".

Thanks both for all your efforts.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: New HYPTUSS versions 11.70 and b-l

#244782

Postby Itsallaguess » August 16th, 2019, 1:31 pm

seagles wrote:
Finally made time to test new version. Tested using a "saved" set of known shares and this allows me to compare previous to new. Tested as much of the changes as I could and all worked fine, even Raptors datasheet changes.

However, did get one run time error, on dividend sheet, using the "Corporate Investors web page", get Visual basic error, Run Time Error '9', subscript out of range. I do not use this now but used to (have this enabled on another spreadsheet that I use). Worked on "previous" spreadheet.


Thanks Seagles - great to hear that you're finding the tool useful, and of course we do really appreciate this sort of feed-back following a new release and a full working swap-over as you've done.

Regarding the 'Corporate Investors web page' issues, are you able to say which share it's now failing on?

It sounds like the company has changed it's investors web-page structure, that's all, and finding the new URL means we could update the next release with the correct one.

Cheers,

Itsallaguess

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#244784

Postby seagles » August 16th, 2019, 1:40 pm

Itsallaguess wrote:
seagles wrote:
Finally made time to test new version. Tested using a "saved" set of known shares and this allows me to compare previous to new. Tested as much of the changes as I could and all worked fine, even Raptors datasheet changes.

However, did get one run time error, on dividend sheet, using the "Corporate Investors web page", get Visual basic error, Run Time Error '9', subscript out of range. I do not use this now but used to (have this enabled on another spreadsheet that I use). Worked on "previous" spreadheet.


Thanks Seagles - great to hear that you're finding the tool useful, and of course we do really appreciate this sort of feed-back following a new release and a full working swap-over as you've done.

Regarding the 'Corporate Investors web page' issues, are you able to say which share it's now failing on?

It sounds like the company has changed it's investors web-page structure, that's all, and finding the new URL means we could update the next release with the correct one.

Cheers,

Itsallaguess


Tried various shares. Failed every one. So I have now gone back in and cut/paste the url for BP from datasheet into browser and that takes me to the correct web-page but fails on the spreadsheet.

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#244788

Postby seagles » August 16th, 2019, 1:45 pm

OK, found that Kiloran had changed one of the Digital Datasheet entries to Companyk datasheet by mistake. Changing it to "Company datasheet" fixed the problem.

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#244792

Postby seagles » August 16th, 2019, 2:02 pm

seagles wrote:OK, found that Kiloran had changed one of the Digital Datasheet entries to Companyk datasheet by mistake. Changing it to "Company datasheet" fixed the problem.


I lie. It now takes me always to WPPs web-site. Found that there is a missing line from the visual basic. Changed the following and it now works

For dlrowsdown = 2 To dllastrow

If CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 3)) = EPIC _
And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 5)) <> "" _
And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 7)) = "" Then
no_url = 1
End If

If CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 3)) = EPIC _
And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 5)) <> "" _

And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 7)) <> "" Then
website = Sheets("Company Data Sheet").Cells(dlrowsdown, 7)
End If

I hope that is it. Let me know if you want me to check anything else.

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: New HYPTUSS versions 11.70 and b-l

#244793

Postby Itsallaguess » August 16th, 2019, 2:05 pm

seagles wrote:
Found that there is a missing line from the visual basic. Changed the following and it now works

For dlrowsdown = 2 To dllastrow

If CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 3)) = EPIC _
And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 5)) <> "" _
And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 7)) = "" Then
no_url = 1
End If

If CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 3)) = EPIC _
And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 5)) <> "" _

And CStr(Sheets("Company Data Sheet").Cells(dlrowsdown, 7)) <> "" Then
website = Sheets("Company Data Sheet").Cells(dlrowsdown, 7)
End If

I hope that is it. Let me know if you want me to check anything else.


Thanks very much Seagles, I was just about to post the same fix, so we got there about the same time I think.

Yes - manually changing the above red VBA code will fix the current 11.70 Excel version for anyone wanting to use the 'Corporate Investors Web Page' button on the 'Dividends' sheet.

This issue isn't affecting any version earlier than 11.70.

Thanks again for your support and valuable feedback, it's very much appreciated.

Cheers,

Itsallaguess

kiloran
Lemon Quarter
Posts: 4093
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: New HYPTUSS versions 11.70 and b-l

#244898

Postby kiloran » August 16th, 2019, 8:34 pm

seagles wrote:Finally made time to test new version. Tested using a "saved" set of known shares and this allows me to compare previous to new. Tested as much of the changes as I could and all worked fine, even Raptors datasheet changes. However, did get one run time error, on dividend sheet, using the "Corporate Investors web page", get Visual basic error, Run Time Error '9', subscript out of range. I do not use this now but used to (have this enabled on another spreadsheet that I use). Worked on "previous" spreadheet.

The only addition I needed to make was to copy across my previous "snapshot" from previous spreadsheet so as to keep track of snapshots, did not see that in the "User Guide".

Thanks both for all your efforts.

Many thanks for the feedback, seagles.
I'll have the corrected version published later tonight, to fix the Corporate Investors Web Page issue.

Re the Snapshots issue in the User Guide, it looks OK to me. Here's an extract of page 21
.........
7. In the new “High Yield Portfolio” sheet, select cell F6 and paste the data
8. Copy the data on the old Tracking sheet to the new Tracking sheet
9. Copy the data on the old Snapshots sheet to the new Snapshots sheet
10. On the old Watchlist sheet, note row number of the last share (lastrow_1)
.......


Am I missing something?

--kiloran

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#244911

Postby seagles » August 16th, 2019, 9:11 pm

My mistake missed it. Sorry. Usually I do not read the user guide (was an engineer so ingrained) but was looking for something else. Never looked but did you update the datasheet part to copy in just the changes?

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 210 times

Re: New HYPTUSS versions 11.70 and b-l

#245163

Postby torata » August 18th, 2019, 8:13 am

kiloran wrote:
torata wrote:I upgraded to LO b-l from the b-f version, but am still unable to pick up dividends from dividenddata. They stopped updating suddenly a few months ago when using b-f ("no data" I think is what it reports), but I nonchalantly assumed that it was possibly a change in structure on the dividenddata site as I was, and still am, able to access the site with a browser, so there doesn't seem to be any IP address blocking happening.
LO version is 6.0.5.
torata


  1. The macro spoofs the browser user agent, to make the website think the request comes from a real browser. Maybe I need to dig into this, though that will be dabbling in dark arts and will require some magic incantations.
  2. What browser are you using? Does it have an active VPN extension? I know the Opera browser has a built-in VPN, so even if Dividend Data locks out my IP address, I can still access the site with my Opera browser

If you shut down your router, wait a minute or so, then reboot it (and hopefully get a new IP address), does that solve the problem?
--kiloran

Itsallaguess wrote:Can you please try the same process on the vanilla B-L download, without any alterations other than to change the 'Parameters' sheet setting, and report back your findings?
Itsallaguess


Hi Kiloran and IAG,
Sorry not to have good news to report

Basically no change: Vanilla version of HYPTUSS with just BT won't update dividends from dividenddata, but I can get on the site fine.

Browser is Firefox up to date, no VPN on it, but FWIW "ublock orgin", "ghostery" and "https everywhere". Set up for cookie handling is very strict.

I tried shutting down router, but reverse DNS is showing same IP address as before the shutdown. I have a feeling that I have a fixed IP address where I live as every apartment has it's own 100Mbps "direct connection to ISP" set up a few years back (so there's no sharing of lines in the apartment block). I may try a longer shut down (overnight) in the next few days to see if that makes a difference.

I also updated LO to latest stable 6.2.5 in case that made a difference.

PS I live overseas if that is important.

If it's an issue that doesn't seem to affect anyone else, then please leave it unless you like a challenge.

torata

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#245164

Postby seagles » August 18th, 2019, 8:33 am

I stopped using this option as on my old PC I had issues. As a test I tried it on this version. Of my 30 odd shares, 6 returned no info. I am running windows 10 and latest firefox. If of help will rerun test later and give you missing data epic?

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: New HYPTUSS versions 11.70 and b-l

#245175

Postby seagles » August 18th, 2019, 9:57 am

seagles wrote:I stopped using this option as on my old PC I had issues. As a test I tried it on this version. Of my 30 odd shares, 6 returned no info. I am running windows 10 and latest firefox. If of help will rerun test later and give you missing data epic?


Just rerun this with my full portfolio of 28 shares (not my test scenario) and all entries returned "no divi" in "Amount" column. If I click on "View Dividend" on the "High Yield Portfolio" sheet it correctly shows the dividend. I also downloaded a vanilla 11.71 and added 4 shares, got the same result. I assume it means I have been locked out.

kiloran
Lemon Quarter
Posts: 4093
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: New HYPTUSS versions 11.70 and b-l

#245219

Postby kiloran » August 18th, 2019, 1:09 pm

seagles wrote:
seagles wrote:I stopped using this option as on my old PC I had issues. As a test I tried it on this version. Of my 30 odd shares, 6 returned no info. I am running windows 10 and latest firefox. If of help will rerun test later and give you missing data epic?


Just rerun this with my full portfolio of 28 shares (not my test scenario) and all entries returned "no divi" in "Amount" column. If I click on "View Dividend" on the "High Yield Portfolio" sheet it correctly shows the dividend. I also downloaded a vanilla 11.71 and added 4 shares, got the same result. I assume it means I have been locked out.

When you talk about "this option" I guess you mean retrieving dividends from Dividend Data.

It sounded a bit odd (as does torata's report of the problem) so I had a quick look at this. It's very intriguing.

In the past, too many requests have caused the website to block further requests. I had assumed this was based purely on IP Address. I'm now not so sure. I set up a VPN (SurfEasy) to enable me to easily switch IP address without rebooting my router. I first tried using Excel 11.71 to get dividends, and after 21 requests for data, I was locked out. Yet, as you and Torata have pointed out, I could go back to the main HYP sheet, click on the View Dividends button, and my browser displayed the dividends from the website.
I then changed my IP address and tried again with LibreOffice and version b-l. I got exactly the same result.

I know from previous investigations a year or three ago, too many requests from a web browser also causes lockout, so my guess is that the lockout is based not just on IP address but also something else, probably related to the browser user agent I would guess.

Very frustrating, but also intriguing. I'll investigate this further in a more disciplined manner over the next few days. Not immediately, I've got a water leak to fix :cry:

--kiloran

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 210 times

Re: New HYPTUSS versions 11.70 and b-l

#278306

Postby torata » January 18th, 2020, 6:46 am

kiloran wrote:
seagles wrote:
seagles wrote:I stopped using this option as on my old PC I had issues. As a test I tried it on this version. Of my 30 odd shares, 6 returned no info. I am running windows 10 and latest firefox. If of help will rerun test later and give you missing data epic?


Just rerun this with my full portfolio of 28 shares (not my test scenario) and all entries returned "no divi" in "Amount" column. If I click on "View Dividend" on the "High Yield Portfolio" sheet it correctly shows the dividend. I also downloaded a vanilla 11.71 and added 4 shares, got the same result. I assume it means I have been locked out.

When you talk about "this option" I guess you mean retrieving dividends from Dividend Data.

It sounded a bit odd (as does torata's report of the problem) so I had a quick look at this. It's very intriguing.

In the past, too many requests have caused the website to block further requests. I had assumed this was based purely on IP Address. I'm now not so sure. I set up a VPN (SurfEasy) to enable me to easily switch IP address without rebooting my router. I first tried using Excel 11.71 to get dividends, and after 21 requests for data, I was locked out. Yet, as you and Torata have pointed out, I could go back to the main HYP sheet, click on the View Dividends button, and my browser displayed the dividends from the website.
I then changed my IP address and tried again with LibreOffice and version b-l. I got exactly the same result.

I know from previous investigations a year or three ago, too many requests from a web browser also causes lockout, so my guess is that the lockout is based not just on IP address but also something else, probably related to the browser user agent I would guess.

Very frustrating, but also intriguing. I'll investigate this further in a more disciplined manner over the next few days. Not immediately, I've got a water leak to fix :cry:

--kiloran


I'm picking this up again to ask if there was any progress?

As before, I still only get 'no divi' when using dividenddata to update dividends. But I am able to access the dividenddata website using a browser. Now that investorease is shut down, I'm limited to ADVFN, which seems less reliable in the data it provides.
I've established that I have a fixed IP address.
LibreOffice 6.2.5 and HYPTUSS is b-f

torata

torata
Lemon Slice
Posts: 521
Joined: November 5th, 2016, 1:25 am
Has thanked: 203 times
Been thanked: 210 times

Re: New HYPTUSS versions 11.70 and b-l

#278307

Postby torata » January 18th, 2020, 6:50 am

One other thing that may be relevant.
I've always gone direct to dividenddata, but I tried using the 'view dividends' button and that fails.

URL it produces is: https://www.dividenddata.co.uk/dividend ... hTerm=TATE

Response from the website is:
----------------------------------------------------
500
Looks like we're having some server issues
Please try reloading the page
----------------------------------------------------

torata

Itsallaguess
Lemon Half
Posts: 9129
Joined: November 4th, 2016, 1:16 pm
Has thanked: 4140 times
Been thanked: 10023 times

Re: New HYPTUSS versions 11.70 and b-l

#278383

Postby Itsallaguess » January 18th, 2020, 3:33 pm

torata wrote:
One other thing that may be relevant.

I've always gone direct to dividenddata, but I tried using the 'view dividends' button and that fails.

URL it produces is: https://www.dividenddata.co.uk/dividend-payment-date-search.py?searchTerm=TATE

Response from the website is:
----------------------------------------------------
500
Looks like we're having some server issues
Please try reloading the page
----------------------------------------------------


For info torata, the URL above is absolutely fine and is working for me currently, so given that you've got a fixed IP address then I suspect that this is the issue, and that your IP address is currently blocked from accessing the data that the HYPTUSS tool requires from the dividenddata site.

When this happens for those of us with non-fixed IP address web services, we're able to turn our broadband routers off for a minute and then turn them back on, which normally resets our IP address with a new variable address, removing the block that happens from frequent access to the dividenddata site.

Cheers,

Itsallaguess


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 4 guests