HYPTUSS suggestion re Snapshots

[staffordian]

I use the HYPTUSS regularly and am very grateful to both developers for their incredible and ongoing work.

Just in case they were starting to rest on their laurels ( ), I thought I'd throw out a suggestion, both to see if they think it's feasible and also to canvass other user's views on it.

I think the snapshot feature would be easier to use if the order in which they appear on the page was reversed, with the most recent appearing at the top, rather than having to scroll down to the bottom to see the latest one.

Would it be significantly more difficult to program, and does anyone else agree, or is it just me?

Staffordian

[Itsallaguess]

I think the snapshot feature would be easier to use if the order in which they appear on the page was reversed, with the most recent appearing at the top, rather than having to scroll down to the bottom to see the latest one.

Would it be significantly more difficult to program, and does anyone else agree, or is it just me?

Hi staffordian,

It's a good suggestion, and I can see that once a few portfolio snapshots might have been taken, then having to keep scrolling to the bottom of the 'Snapshots' sheet to see the most recently captured data might be a bit of a pain, but I wonder if we might be able to deliver a solution to that problem with a very simple fix, where we can keep the order of the snapshots as they currently stand, but tell the tool to automatically scroll to the last snapshot-capture each time the 'Snapshots' sheet is selected by the user?

This would be such a simple improvement that you could actually give it a manual test on a COPY of your current HYPTUSS spreadsheet if you like, by following this set of instructions -

1. Make a COPY of your current HYPTUSS spreadsheet, and name it clearly so that you know you'll be working on the scrap COPY...

2. Close all other versions of the tool, and OPEN the COPY that you've created above

3. Enable Macros

4. Press ALT+F11 to open the VBA Editor

5. In the VBA Editor, and in the left hand PROJECT window, double-click on the project related to the COPY of the HYPTUSS file you've got open

6. When asked for the VBA password, enter 'pleaseletmein' (please note that we've always classed our tool as being 'open-source', but we still password the VBA side of things to help avoid unnecessary user-issues, but we''re always keen for that VBA password to be known to anyone wishing to take a look at the underlying code, especially given the potential sensitivity of some of the data being held in the tool by users of it...)

7. In the left-hand window of the VBA editor, double-click on the 'Microsoft Excel Objects' folder, to gain visibility of the underlying worksheets

8. Double-click on 'Sheet(9)Snapshots' in the left hand VBA window, under the 'Microsoft Excel Objects' folder

9. In the right-hand coding area for the 'Snapshots' sheet, COPY and PASTE the following complete chunk of VBA code in bold -

Private Sub Worksheet_Activate()

Set r = Cells.Find(What:="Snapshot Date", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

ActiveWindow.ScrollRow = r.Row - 1

End Sub


Private Sub Worksheet_Deactivate()

ActiveWindow.ScrollRow = 1

End Sub

10. Carry out a SAVE, both in the VBA Editor and on your Excel spreadsheet COPY

11. Swap a few times between the main 'High Yield Portfolio' sheet and the 'Snapshots' sheet

12. Confirm that even if you manually scroll to the top of the 'Snapshots' sheet, and then activate the 'High Yield Portfolio' sheet, then the next time that you visit the 'Snapshots' sheet, it automatically scrolls to the last snapshot taken...

Let me know how you get on with the above?

It might seem a little complicated when initially reading the above instructions, but it's fairly straightforward really, and this feels like a much simpler solution than us having to re-code and reverse the whole snapshots process, so your thoughts on this as a possible improvement to the issue would be welcome.

Cheers,

Itsallaguess

[staffordian]

Hi itsallaguess,

Wow. Thanks for the prompt and comprehensive reply!

I shall try this later and I agree, it's a neat way of achieving what I was driving at.

After posting it occurred to me that my suggestion was a little impractical because of the problem of trying to reorder the existing snapshots, regardless of how complex or otherwise changing the code to out new ones at the top might be.

Thanks,

Staffordisn

[staffordian]

Whilst I'm at it, I may see if I can adapt your instructions to add similar functionality to the page with FTSE comparisons too...

[Itsallaguess]

Whilst I'm at it, I may see if I can adapt your instructions to add similar functionality to the page with FTSE comparisons too...

Yep, you should have no problem modifying the VBA above to suit that process as well, and applying it against the appropriate worksheet in the VBA editor.

Let me know how you get on with both anyway - I'd be interested to hear if this delivers near to your original requirements.

Cheers,

Itsallaguess

[staffordian]

Thank you. It will be this evening at the earliest, but I'll certainly report back.

[kiloran]

Another non-macro option is CTRL-END
This takes you to the last cell in the sheet which contains data, though you'll perhaps have to scroll-left afterwards.

Works on Excel and LibreOffice

--kiloran

[kempiejon]

Another non-macro option is
This takes you to the last cell in the sheet which contains data, though you'll perhaps have to scroll-left afterwards.

Works on Excel and LibreOffice

--kiloran

I was going to suggest that, not sure about LibreOffice but to avoid the scroll left after CTRL-END the HOME button will take you to the beginning of the row.

[staffordian]

Another non-macro option is CTRL-END
This takes you to the last cell in the sheet which contains data, though you'll perhaps have to scroll-left afterwards.

Works on Excel and LibreOffice

--kiloran

Don't spoil my fun, I was looking forward to playing about under the bonnet of the HYPTUSS

[Urbandreamer]

Whilst I'm at it, I may see if I can adapt your instructions to add similar functionality to the page with FTSE comparisons too...

While we are making our own personal modifications, which of course we need to copy* into the latest version when we upgrade, can I describe what I have done.

I attempted to unitise my portfolio a while ago and to chart the performance against the FTSE. Unfortunately over time the results become fairly meaningless. My latest version uses a pivot table to feed the chart. In that manner I can adjust the chart by changing the pivot table filter start date.

* I believe that some have instead used a spreadsheet linked to HYPTUSS to unitise their portfolio, but that's not how I did it.

[staffordian]

Whilst I'm at it, I may see if I can adapt your instructions to add similar functionality to the page with FTSE comparisons too...

While we are making our own personal modifications, which of course we need to copy* into the latest version when we upgrade, can I describe what I have done.

I attempted to unitise my portfolio a while ago and to chart the performance against the FTSE. Unfortunately over time the results become fairly meaningless. My latest version uses a pivot table to feed the chart. In that manner I can adjust the chart by changing the pivot table filter start date.

* I believe that some have instead used a spreadsheet linked to HYPTUSS to unitise their portfolio, but that's not how I did it.

I used to have a heavily modified version of the HYPTUSS. I didn't (couldn't!) modify the coding, but I'd added all sorts of additional worksheets linked to the originals.

It got so complex that I couldn't easily deal with HYPTUSS updates, so following a suggestion on this board, I simply separated them and created a "links" worksheet on my custom spreadsheet, which pulled in the relevant information from the HYPTUSS.

What I then managed to do - and don't ask me how! - was add a macro and button to my spreadsheet which opened the HYPTUSS so I only need open mine and click the button to open then use the HYPTUSS.

Works well for me

[staffordian]

Hi itsallaguess,

I've updated the VBA as you suggested and it works a treat, and does all I wanted - a far neater solution than my suggestion.

Many thanks for your help.

I didn't manage to adapt the code to do the same on the FTSE-HYP Tracking worksheet as I don't understand the syntax of VBA, but using CTRL and the Down arrow works fine so I didn't try too hard

Staffordian

[Itsallaguess]

I've updated the VBA as you suggested and it works a treat, and does all I wanted - a far neater solution than my suggestion.

Many thanks for your help.

I didn't manage to adapt the code to do the same on the FTSE-HYP Tracking worksheet as I don't understand the syntax of VBA, but using CTRL and the Down arrow works fine so I didn't try too hard

That's great news - thanks for letting us know that you're happy with the solution.

If you're keen to get the FTSE-HYP Tracking sheet working in the same automatic way, then you can COPY and PASTE the following VBA onto the 'FTSE-HYP Tracking' sheet under the 'Microsoft Excel Objects' section in the VBA Editor, and it should give you what you're after -

Option Explicit

Private Sub Worksheet_Activate()

Dim r as Double

r = Sheets("FTSE-HYP Tracking").Cells(Sheets("FTSE-HYP Tracking").Rows.Count, "A").End(xlUp).Row

ActiveWindow.ScrollRow = r - 20

End Sub

Note that there should already be an 'Option Explicit' in there on the FTSE-HYP Tracking sheet VBA, and it only wants to appear once, but I also include it in the above bold text just for completeness, as that's what it should finally look like once you've done a COPY and PASTE onto that sheet in the VBA Editor.

The section of VBA code that says 'r-20' towards the end is just trying to centralise the last row containing tracking data in the middle of your monitor, so depending on where you want that last row to be automatically situated, you can have a play around with the numerical figure if you want to re-position where that last row of data is displayed.

Cheers,

Itsallaguess

[staffordian]

Thanks again, itsallaguess. I shall use that.

As a matter of interest, is it something you might incorporate into future versions or should I keep notes to ensure I remember what to do to amend my copy?

Staffordian

[Itsallaguess]

As a matter of interest, is it something you might incorporate into future versions or should I keep notes to ensure I remember what to do to amend my copy?

I think you're probably best to keep some notes for now, and we've got this thread to refer back to if needed also.

The only way we'd incorporate these tweaks into the vanilla versions would be via a couple of additional options on the 'Parameters' sheet, I think.

That way, users wouldn't see any changes by default, but there'd perhaps be a couple of user-settings to enable both the Snapshot and FTSE-HYP Tracking sheet 'Auto-scroll on sheet activation', which would then turn on the features that we've manually introduced here.

We'll keep it on the back-burner for now, especially since we've just had a recent release, but thanks for raising it and testing out the solutions - it's always nice to get feedback like this and to be able to help improve things where users have particular niggles, and if it helps to introduce people to the VBA side of things too, then all the better...

Cheers,

Itsallaguess

[kiloran]

Thanks again, itsallaguess. I shall use that.

As a matter of interest, is it something you might incorporate into future versions or should I keep notes to ensure I remember what to do to amend my copy?

Staffordian

I have added this to my list of potential enhancements to HYPTUSS so it will be considered next time we release a new version.
I agree with IAAG, probably make it a user option on the Parameters sheet

--kiloran

[staffordian]

If you're keen to get the FTSE-HYP Tracking sheet working in the same automatic way, then you can COPY and PASTE the following VBA onto the 'FTSE-HYP Tracking' sheet under the 'Microsoft Excel Objects' section in the VBA Editor, and it should give you what you're after -

Option Explicit

Private Sub Worksheet_Activate()

Dim r as Double

r = Sheets("FTSE-HYP Tracking").Cells(Sheets("FTSE-HYP Tracking").Rows.Count, "A").End(xlUp).Row

ActiveWindow.ScrollRow = r - 20

End Sub

Note that there should already be an 'Option Explicit' in there on the FTSE-HYP Tracking sheet VBA, and it only wants to appear once, but I also include it in the above bold text just for completeness, as that's what it should finally look like once you've done a COPY and PASTE onto that sheet in the VBA Editor.

The section of VBA code that says 'r-20' towards the end is just trying to centralise the last row containing tracking data in the middle of your monitor, so depending on where you want that last row to be automatically situated, you can have a play around with the numerical figure if you want to re-position where that last row of data is displayed.

Cheers,

Itsallaguess

Hi itsallaguess,

I just want to thank you again for this; needless to say it works a treat, and a value of 20 puts the last row of data pretty much where I want it.

Staffordian

[kiloran]

If you're keen to get the FTSE-HYP Tracking sheet working in the same automatic way, then you can COPY and PASTE the following VBA onto the 'FTSE-HYP Tracking' sheet under the 'Microsoft Excel Objects' section in the VBA Editor, and it should give you what you're after -

Option Explicit

Private Sub Worksheet_Activate()

Dim r as Double

r = Sheets("FTSE-HYP Tracking").Cells(Sheets("FTSE-HYP Tracking").Rows.Count, "A").End(xlUp).Row

ActiveWindow.ScrollRow = r - 20

End Sub

Note that there should already be an 'Option Explicit' in there on the FTSE-HYP Tracking sheet VBA, and it only wants to appear once, but I also include it in the above bold text just for completeness, as that's what it should finally look like once you've done a COPY and PASTE onto that sheet in the VBA Editor.

The section of VBA code that says 'r-20' towards the end is just trying to centralise the last row containing tracking data in the middle of your monitor, so depending on where you want that last row to be automatically situated, you can have a play around with the numerical figure if you want to re-position where that last row of data is displayed.

Cheers,

Itsallaguess

Hi itsallaguess,

I just want to thank you again for this; needless to say it works a treat, and a value of 20 puts the last row of data pretty much where I want it.

Staffordian

And it's very future proof.
Specifying "Dim r as Double" means it can handle 1.79769313486232E308 rows. That's.....a LOT

--kiloran

[staffordian]

If you're keen to get the FTSE-HYP Tracking sheet working in the same automatic way, then you can COPY and PASTE the following VBA onto the 'FTSE-HYP Tracking' sheet under the 'Microsoft Excel Objects' section in the VBA Editor, and it should give you what you're after -

Option Explicit

Private Sub Worksheet_Activate()

Dim r as Double

r = Sheets("FTSE-HYP Tracking").Cells(Sheets("FTSE-HYP Tracking").Rows.Count, "A").End(xlUp).Row

ActiveWindow.ScrollRow = r - 20

End Sub

Note that there should already be an 'Option Explicit' in there on the FTSE-HYP Tracking sheet VBA, and it only wants to appear once, but I also include it in the above bold text just for completeness, as that's what it should finally look like once you've done a COPY and PASTE onto that sheet in the VBA Editor.

The section of VBA code that says 'r-20' towards the end is just trying to centralise the last row containing tracking data in the middle of your monitor, so depending on where you want that last row to be automatically situated, you can have a play around with the numerical figure if you want to re-position where that last row of data is displayed.

Cheers,

Itsallaguess

Hi itsallaguess,

I just want to thank you again for this; needless to say it works a treat, and a value of 20 puts the last row of data pretty much where I want it.

Staffordian

And it's very future proof.
Specifying "Dim r as Double" means it can handle 1.79769313486232E308 rows. That's.....a LOT

--kiloran

Mmm. So, if I update it every day, it will stop working once I get beyond a few thousand million years old?

I hate all this stuff that's not built to last