My IT portfolio in HYPTUSS looks like this:-
If you just change the datasheet the VB code will not be able to grab the yield etc. So I change the datasheet to:-
Code: Select all
Dunedin Income Growth Inv Trust | DIG | IT UK Income | DIG
Henderson Far East Income Ltd. | HFEL | IT Asia/Pacific | HFEL
JPMorgan European Inv Trust Income Shares | JETI | IT European Income & Growth | JETI
Middlefield Canadian Income | MCT | IT North American | MCT
Murray International Trust | MYI | IT Global Income | MYI
Merchants Trust | MRCH | IT UK Income | MRCH
The change to the VB code is a simple one line change. First you need to enable "developer" option. This varies from version to version. I have just this week upgraded laptop (new Lenovo) and purchased the new office version. To add developer on this version, go to file, then options, click customize ribbon and tick the developer box. This will now appear at the top of the spreadsheet. Click on this, then choose Visual Basic, then click on VBA project, now enter the password "pleaseletmein". Click on sheet1. Now you need to find "If equitytype = "Equity Investment Instruments" (I this using ctrl +F, find, with equitytype and next till I find it). This need to be changed. see below for the way I did it.
'If equitytype = "Equity Investment Instruments" Then'
If equitytype Like "IT*" Then 'Change by SE to take into account different IT's'
The first line is the unchanged code. I leave changed code as text (always nice to be able to revert if it goes belly up) and add why changed.
REMEMBER TAKE A COPY OF YOU SPREADSHEET BEFORE MAKING ANY CHANGES
There may be an easier way of doing this but my Visual Basic skills were learned over 30 years ago. More of an assembler, cobol man myself,
Hope this maybe of interest to some out there. I have made other changes to make my life easier but this is the only VB change I have made on the "virgin" HYPTUSS. I have used the vloookup function a lot as I have "legacy" spreadsheets from the dark ages that I want to still be able to use, so I use the vlookup function to fill the number of shares field from other spreadsheets. I also use vlookup from other spreadsheets to point to my "virgin" HYPTUSS to grab data like yields and prices that I use for other functions. On other spreadsheets I have used functionality from HYPTUSS but customized the look and feel of the buttons etc. I also use conditional formatting on these other spreadsheets to make it easier (by colour) to set some "conditions" that make it easier to see at a glance whether I need to do anything. It has been an interesting road to get to this point, but I do not intend to do anything else. I leave all the new bells and whistles to the "experts" -kiloran and itsallaguess. Many thanks to both of them for their help in the past.
Just in case anyone spots that the yields do not match what you get from digitallook, these are from AIC as MCT returns n/a and SCF returns half of what it should be? I only bother changing them occasionally as have less need to view my spreadsheets now (thanks to "forget it november"...)