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

HYPTUSS - introduction of portfolio-review 'Overview' sheet?

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

HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#193795

Postby Itsallaguess » January 15th, 2019, 7:22 pm

Hi there,

TUK020 got in touch with Kiloran and I a few days ago with what we both think is a great idea for an improvement to the HYPTUSS tool.

He told us that he's always had problems with trying to collate a redacted, tabular view of his HYPTUSS portfolio so that he could post it here for peer-review and discussions, and he asked if there was a possibility of us including some sort of 'Overview' sheet in the tool itself, that could perhaps help with that process.

Whilst the Lemon Fool Software Repository (http://lemonfoolfinancialsoftware.weebly.com/) already contains some really useful tools to help with converting spreadsheet-data into a tabular-data format that's compatible with posts on this website, Kiloran and I both agree that this particular process might then be frustrated somewhat, where people might rightly want to redact some of the more sensitive financial data that exists by default on the main data-sheet of the HYPTUSS tool, and we think we might be able to kill two birds with one stone in the tool itself, if we can perhaps provide a pre-redacted 'Overview' sheet, and perhaps at the same time include a more automated process that will convert the redacted portfolio-data into tabular data for posting here.

There's two reasons for me posting about the above here -

1. Before we start to develop this improvement, we'd like to confirm if the current users of the tool would find it beneficial.

2. Kiloran and I would also like to involve the wider HYPTUSS user-base in agreeing a suitable format for the 'Overview' sheet of data. We're concious of the fact that large, wide tables are both difficult to post and also difficult to absorb from a readers point of view, so collectively finding the 'sweet-spot' for that 'Overview sheet' data is something we'd like to pursue before we start to develop anything.

Over the Christmas period I enjoyed reading many of the portfolio-reviews that were posted on this great web-site, and as a starter-for-ten in terms of the sort of succinct 'HYP portfolio-review' type of data that I think might perhaps be useful to see on such an 'Overview sheet', I'd like to offer up the link below for Breelander's HYPish Portfolio - Christmas Review 2018 -

https://www.lemonfool.co.uk/viewtopic.php?f=15&t=15389

I think that table of portfolio data more or less tells us everything we might want to see in terms of useful 'portfolio review' data.

I'd like to propose, just as a starter for this discussion, that this is a good blueprint for the type of development being discussed here. It tells us the following HYP information -

Share
EPIC
Sector
Capital Weighting
Income Weighting
Forecast Yield


Bree's table also tells us 'Historic Yield', but I'm not sure that column of data would be something that everyone would either have at their disposal, or would always like to see shown in all cases, so I think the above list would probably make a good starting point for this 'Overview sheet' proposal, in that it gives a good level of portfolio-data, with any sensitive financial data removed, and would make a good sized table in terms of posting up and people taking cognisance of.

Kiloran and I would welcome any views on TUK020's great proposal, and also regarding what sort of data might be warranted on the new worksheet, given the above.

With thanks to Bree for unwittingly providing such a great HYP demo-review for us to use as a base for this discussion...

Cheers,

Itsallaguess

Raptor
Lemon Quarter
Posts: 1621
Joined: November 4th, 2016, 1:39 pm
Has thanked: 139 times
Been thanked: 306 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#193805

Postby Raptor » January 15th, 2019, 7:45 pm

What you suggest is also what I post for most portfolio reviews. However, have never had a problem in hiding fields and copying data to your excellent tabulator tool.

Raptor.

Edited. Btw have locked topic on hyp practical for you.

TUK020
Lemon Quarter
Posts: 2039
Joined: November 5th, 2016, 7:41 am
Has thanked: 762 times
Been thanked: 1175 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#193824

Postby TUK020 » January 15th, 2019, 8:47 pm

Itsallaguess wrote:i'd like to propose, just as a starter for this discussion, that this is a good blueprint for the type of development being discussed here. It tells us the following HYP information -

Share
EPIC
Sector
Capital Weighting
Income Weighting
Forecast Yield


tsallaguess


Spot on for the data fields. Also agree that Bree's overview is an excellent model.
If I try to think wider and come up with ideas for embellishments, the main thing I come up with is to freeze the overview snapshot, so that another can be compared with it a year later. However, this feels like it might be quite a lot of complication.

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#193830

Postby kiloran » January 15th, 2019, 9:02 pm

TUK020 wrote:If I try to think wider and come up with ideas for embellishments, the main thing I come up with is to freeze the overview snapshot, so that another can be compared with it a year later. However, this feels like it might be quite a lot of complication.

An interesting idea which might not be too complicated to implement (I may regret saying that!)

The initial concept, as I understand it, is to have a button on the "High Yield Portfolio" sheet which, when clicked, produces the redacted snapshot on the new "Overview" sheet and also in a format which can be copied and pasted directly into a TLF post.

This idea could perhaps then be extended so that when the button is clicked, instead of writing directly to the "Overview" sheet, it appends the new snapshot below the previous snapshot. It would also then require a datestamp for each snapshot but that's simple enough. This doesn't sound like a big complication if there is interest in the idea.

--kiloran

Smautf
Posts: 46
Joined: January 12th, 2017, 10:10 pm
Has thanked: 5 times
Been thanked: 132 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194061

Postby Smautf » January 16th, 2019, 6:06 pm

Just a note to say that, although I am no spreadsheet wizard - far from it - I cobbled together a similar tab on my own HYPTUSS (Open Office version) a couple of years ago, and this is what I use to create my sporadic portfolio reports.

Fields are exactly the same as you propose except that I also calculate weight and value by sector (and, in two cases, supersectors) as well as by individual shareholding.

Copying the "running yield" box onto this tab would might also be a good idea, as I like to report that figure, too, and I assume it is of interest to others.

If you were to create such functionality I am sure that it would be useful to many. Even better if it automatically created correctly-formatted posts - I cut and paste my data into your Table Formatter when I construct my posts.

For reference, the most recent of these is here : viewtopic.php?f=15&t=15468

Your efforts are much appreciated !

Yours,

Chris

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194064

Postby Itsallaguess » January 16th, 2019, 6:19 pm

Smautf wrote:
Copying the "running yield" box onto this tab would might also be a good idea, as I like to report that figure, too, and I assume it is of interest to others.

If you were to create such functionality I am sure that it would be useful to many. Even better if it automatically created correctly-formatted posts - I cut and paste my data into your Table Formatter when I construct my posts.


Thanks for your post Chris - this is just the sort of constructive suggestion that we were hoping to garner from discussing this improvement here, before implementation begins.

I think your idea of including the 'Running Yield' figure is a great one which we'll plan to bring into the new sheet.

With regards to the automatically-created correctly-formatted posts, this is a key element of this new proposal - we're not quite sure how we'll manage the copy-and-paste element yet, but we'll come up with something that will hopefully help a great deal with people who might currently struggle to get their portfolio-reviews onto these boards.

Thanks again - much appreciated.

Cheers,

Itsallaguess

midgesgalore
2 Lemon pips
Posts: 249
Joined: November 5th, 2016, 12:02 am
Has thanked: 266 times
Been thanked: 71 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194157

Postby midgesgalore » January 17th, 2019, 12:29 am

TUK020 wrote:Spot on for the data fields. Also agree that Bree's overview is an excellent model.
If I try to think wider and come up with ideas for embellishments, the main thing I come up with is to freeze the overview snapshot, so that another can be compared with it a year later. However, this feels like it might be quite a lot of complication.


I think I would use such a tool for my review purposes.
All I currently do is keep track of all dividends paid each month and record these for each ongoing month so I have a notional idea if my dividends are increasing annually (or not). I am not one for endless in-depth analysis of my HYP. Some of the beautiful graphs and reviews of some posters are truly wonderful creations, and an interesting read, but my personal interests mainly stop at the bottom line of my income rising or not.

An automatic snapshot generated at my end of year would be an improvement in the quality of what I currently record.
I suppose I could do a similar report for myself. Copy my HYPTUSS portfolio tab and then paste as simple text into a fresh document.
A button click to do this, as proposed by TUK020, would likely help my review process discipline.

midgesgalore

HillManMill
Lemon Pip
Posts: 58
Joined: November 8th, 2016, 8:36 am
Has thanked: 273 times
Been thanked: 22 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194452

Postby HillManMill » January 18th, 2019, 9:26 am

love the idea of the snapshots particularly that of storing a sequence of snapshots.

I run HYPUTUS [excel version - 32 bit excel 2010] and use it to feed info into my own management sheets I also customise HYPTUS by use of conditional formatting colour code the HYP page to give me at a glance views. I am sure others also link HYPTUS to their own spreadsheets but appreciate that they will look at the data in different ways.

May I make a couple of cheeky suggestions please:
* is there advantage to issue future excel versions in the .XMLM format to improve fit with linked spreadsheets/condtional formatting.

*could switching of auto calc from within the macros whilst they run speed up their running.

Thanks

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194476

Postby kiloran » January 18th, 2019, 11:48 am

HillManMill wrote:May I make a couple of cheeky suggestions please:
* is there advantage to issue future excel versions in the .XMLM format to improve fit with linked spreadsheets/condtional formatting.

*could switching of auto calc from within the macros whilst they run speed up their running.

Thanks

Many thanks for the suggestions. Keep them coming.

.xmlm format. I don't recognise this format and Googling it didn't help. Maybe you mean .xlsm or .xlm. Either way, we use .xls since some users have Excel 2000 or 97 which don't recognise xlsm, which I think was introduced with Excel 2007. You could save the HYPTUSS in .xlsm format if you wish, I don't think that will cause any problems (and it will reduce the file size considerably).

Re auto-calc, I don't think it would help. The vast majority of the delay is getting data returned from web sites (and in some cases we introduce a deliberate delay so that we don't overload the website). Once data has been returned, subsequent processing is very quick. Perhaps if you have a very big HYP and lots of additional processing such as conditional formatting, it might make a difference, so we could perhaps investigate this idea if it is easy to implement.

--kiloran

Mentallurgist
Posts: 28
Joined: November 5th, 2016, 10:30 am
Has thanked: 27 times
Been thanked: 14 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194831

Postby Mentallurgist » January 19th, 2019, 2:42 pm

I think the Overview sheet is a great idea !! I've wanted to post my HYP (+a couple of ITs) for a while and get some constructive criticism, A standardised click and post would certainly help me out.

I've just updated my Hyptuss after a long absence and SLA is tripping me up and I have recently added European Assets Trust EAT and cant seem to find them on the list. Any advice would be more than welcome.

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194833

Postby kiloran » January 19th, 2019, 2:45 pm

Mentallurgist wrote:I've just updated my Hyptuss after a long absence and SLA is tripping me up and I have recently added European Assets Trust EAT and cant seem to find them on the list. Any advice would be more than welcome.

For SLA, see: viewtopic.php?f=27&t=14331

For EAT, see item 3 in the FAQ: http://lemonfoolfinancialsoftware.weebl ... p-faq.html

I'll perhaps look at it later

--kiloran

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194836

Postby Itsallaguess » January 19th, 2019, 2:54 pm

Mentallurgist wrote:
I think the Overview sheet is a great idea !! I've wanted to post my HYP (+a couple of ITs) for a while and get some constructive criticism, A standardised click and post would certainly help me out.

I've just updated my Hyptuss after a long absence and SLA is tripping me up and I have recently added European Assets Trust EAT and cant seem to find them on the list. Any advice would be more than welcome.


Hi Mentallurgist,

Off to watch the footy now, so can't post as fully as I'd prefer to, but if you look at the latest WebFG links for both SLA and EAT, you should be able to alter the current URL in the first DataSheet column to pick up what looks like a change to the SLA one currently listed, and also hopefully be able to build up a new line-entry at the bottom of the current DataSheet data for EAT -

SLA - https://tinyurl.com/yag6qqgt

EAT - https://tinyurl.com/y7mz9mg3

Load those up into your browser tabs, and inspect the browser URL's - you'll hopefully see that the SLA one has changed from that seen on the datasheet.

You'll need to build a new line-entry for EAT, but hopefully the above link to the EAT URL should help you do so, copying the format of information from the existing entries already found on the DataSheet.

Of course, if anything regarding the above isn't clear then please let me know and I'll try to help out further.

EDIT - just spotted that Kiloran has also provided some help with this too, so hopefully you'll have enough to get going - let us know if not.

As for the Overview sheet - things are progressing, so thanks for your post suggesting this is going to be a useful addition to the existing functionality.

Cheers,

Itsallaguess

Mentallurgist
Posts: 28
Joined: November 5th, 2016, 10:30 am
Has thanked: 27 times
Been thanked: 14 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194844

Postby Mentallurgist » January 19th, 2019, 3:17 pm

Thanks for the VERY prompt and helpful replies ,

Cheers, M

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194932

Postby kiloran » January 19th, 2019, 9:36 pm

Mentallurgist wrote:I've just updated my Hyptuss after a long absence and SLA is tripping me up and I have recently added European Assets Trust EAT and cant seem to find them on the list. Any advice would be more than welcome.

I've added EAT to the Digital_Look_Data_Sheet.txt at http://lemonfoolfinancialsoftware.weebl ... op-up.html
SLA data was verified as correct

--kiloran

HillManMill
Lemon Pip
Posts: 58
Joined: November 8th, 2016, 8:36 am
Has thanked: 273 times
Been thanked: 22 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194935

Postby HillManMill » January 19th, 2019, 10:17 pm

kiloran wrote:
.xmlm format. I don't recognise this format and Googling it didn't help. Maybe you mean .xlsm or .xlm. Either way, we use .xls since some users have Excel 2000 or 97 which don't recognise xlsm, which I think was introduced with Excel 2007. You could save the HYPTUSS in .xlsm format if you wish, I don't think that will cause any problems (and it will reduce the file size considerably).

Re auto-calc, I don't think it would help. The vast majority of the delay is getting data returned from web sites (and in some cases we introduce a deliberate delay so that we don't overload the website). Once data has been returned, subsequent processing is very quick. Perhaps if you have a very big HYP and lots of additional processing such as conditional formatting, it might make a difference, so we could perhaps investigate this idea if it is easy to implement.

--kiloran


Thanks for the reply.

Yep sorry I meant .xlsm typeslexia I am afraid. Noted .xls is more of a lowest common denominator format.

I choose to keep the HYPTUSS as an .xls I had a problem when I tried to save as .xlsm a few years back and haven't revisited .xlsm recently. I used to have a couple of additional sheets in my HYPTUS versions. One sheet used a macro to insert a column and paste time identified prices copied from HYP and watchlist into the sheet. I have conditional formatting on that page so I get an at a glance view of the price trends. My other added page picked half a dozen rows and produced time based graphs of say 6 stocks [this was useful to, for example, look at relative price performance of B7 ITs]. My implementation was a bit clunky certainly not up to your standards but it did the job. Unfortunately my extra pages seemed to get corrupted when saving as .xls running from excel 2010. So I now keep them in a separate spreadsheet and import HYPTUSS data into that. I came to this after following your guys, it certainly makes migrating versions of HYPTUS easier.

I run about 45 lines on HYP and 35 on watchlist. Yes, I had noticed that there were delays built in to some macros, as you say to ensure that web data is retrieved successfully I guess this particulary goes for yield? But aren't the built in delays less on the simple price scrape spreadsheet? Am I correct to think length of required delays might depend on the speed of the users broadband? I have a hunch that the delays could be reduced and the yield updates would still work on my broadband [basic BT fibre service].

I was noticing delays when updating price data on HYPTUS, I could see the macro 'pausing' as excel started to recalculate. I was however, running with a 3.5mb .xlsm linked at the same time as my modded HYPSTUS [coming in at 10meg] so I might have been 'stretching' my PC. I had a cheap 3 year old 64bit PC running 8GB ram. I have tried turning auto calc off at the beginning of some of my macros in my linked sheet and back on at the end - it did seem to deliver speed benefits! I have now upgraded to a better PC and things run faster.

Thanks once again.

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194954

Postby Itsallaguess » January 20th, 2019, 5:31 am

HillManMill wrote:
Yes, I had noticed that there were delays built in to some macros, as you say to ensure that web data is retrieved successfully I guess this particulary goes for yield?

But aren't the built in delays less on the simple price scrape spreadsheet?

Am I correct to think length of required delays might depend on the speed of the users broadband? I have a hunch that the delays could be reduced and the yield updates would still work on my broadband [basic BT fibre service].


You've misunderstood the use of the delay built into the yield scrape process.

Whilst the price scrape can be carried out in one go via Yahoo, the yields required a lengthy http request for each and every entry in our portfolios, and so the time-delay built into the yield process is purely so that we're not hammering the data-provider unnecessarily with multiple http requests over a short period of time.

We'd very much prefer if those http delays remained in place, and that people didn't reduce or remove them...

We owe a duty to the people who provide the free data that this tool uses, to ensure that our use of that data reduces, as far as possible, any adverse effects it might have on their websites, and these delays are a key element of that process.

Whilst we have always been absolutely transparent in terms of allowing access to the HYPTUSS tool coding, we also recognise that by doing so we must fully trust our users to behave appropriately in their use of the tool, especially those with a coding background, and we very much hope that these built-in delays are maintained for the very important reason explained above.

I should add here, that one data-source we use are known to block user-IP addresses for too-frequent http-requests, which means the tool will stop working for users affected by those IP-blockings.....

Cheers,

Itsallaguess

EssDeeAitch
Lemon Slice
Posts: 655
Joined: August 31st, 2018, 9:08 pm
Has thanked: 268 times
Been thanked: 251 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#194956

Postby EssDeeAitch » January 20th, 2019, 7:31 am

Just want to say thanks to all who create and maintain these tools. Much appreciated.

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#195003

Postby kiloran » January 20th, 2019, 11:52 am

HillManMill wrote:
Yes, I had noticed that there were delays built in to some macros, as you say to ensure that web data is retrieved successfully I guess this particulary goes for yield? But aren't the built in delays less on the simple price scrape spreadsheet? Am I correct to think length of required delays might depend on the speed of the users broadband? I have a hunch that the delays could be reduced and the yield updates would still work on my broadband [basic BT fibre service].

I'd like to reinforce the comments from Itsallaguess regarding reducing the delays. These delays are nothing to do with the user's broadband, it's all about the web servers.

Some websites don't like automated scraping of data and we sometimes have to jump through hoops to fool the websites that the requests for data come from manual browsing (websites can detect the browser that is used). And it was clear on one occasion that a website had adapted to reject our modifed code, so other workarounds had to be developed. Inventing new workarounds gets progressively harder.

Please don't mess about with the delays and the web-scraping code. You may get away with it for a while, but ultimately mess it up for everyone.

--kiloran

HillManMill
Lemon Pip
Posts: 58
Joined: November 8th, 2016, 8:36 am
Has thanked: 273 times
Been thanked: 22 times

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#195083

Postby HillManMill » January 20th, 2019, 5:01 pm

KIloran, Itsallaguess,
Point noted re fiddling with delays to auto price scrapes. I have not done this and will not do so.

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

Re: HYPTUSS - introduction of portfolio-review 'Overview' sheet?

#196714

Postby Itsallaguess » January 26th, 2019, 4:52 pm

Just thought I'd provide an update on progress with these improvements.

We're currently testing the following new functionality -

1. Sector / Super-Sector toggle on the main portfolio sheet - This has been introduced to help with the new 'Overview' sheet (more on that later), but has some additional benefits such as feeding through to the existing 'Sector Weighting' chart, which will now show the portfolio weighting chart in either Sector or Super-Sector mode, depending on the toggle at the time -

Main portfolio sheet in 'Sector' mode - https://i.imgur.com/gvTnXmG.png

Sector Weighting chart in 'Sector' mode - https://i.imgur.com/DWds49o.png

Main portfolio sheet in 'Super-Sector' mode - https://i.imgur.com/fpxk7x1.png

Sector Weighting chart in 'Super-Sector' mode - https://i.imgur.com/ktaD91j.png

2. New 'Overview' sheet - This new sheet will provide a portfolio overview to help with posting our HYP portfolios into a Lemon Fool post. It will be generated in either Sector or Super-Sector mode, depending on the toggle being used on the main sheet. The overview table is initially generated in the same order as that seen on the main portfolio sheet, but it can then be ordered and grouped by sector/super-sector if required by using a separate order-button. Another separate button can then be used to generate the tabular text-data of the overview, which can then simply be 'pasted' into a regular Lemon Fool post -

Overview sheet in Sector mode - https://i.imgur.com/oBJfBcm.png

Overview sheet in Super-Sector mode - https://i.imgur.com/8my7La7.png

Overview sheet sorted by Sector - https://i.imgur.com/UE8SA87.png

Lemon Fool table posted after using 'Generate Table' button - https://i.imgur.com/MgMIDGf.png

3. New 'Snapshots' sheet - A new 'Take Snapshot' button on the main portfolio sheet will now compile running-snapshots of the main portfolio view, putting any new snapshot underneath the last snapshot stored on the 'Snapshot' sheet. Each snapshot will be dated, so hopefully this will help with ongoing portfolio management where users wish to either store their snapshots or re-visit them at some future date -

Snapshot sheet showing a recent snapshot - https://i.imgur.com/ln5Rl0J.png

Cheers,

Itsallaguess


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 7 guests