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. coding mod for IT's

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

HYPTUSS. coding mod for IT's

#222685

Postby seagles » May 18th, 2019, 11:57 am

Leading on from a previous thread where Darka suggested a change and Itsallaguess suggesting a "code change" thread. I mentioned I had made a "minor change" to the VB code and data sheet to allow me to track ITs by a more varied title than lumping them all together as "Equity Investment Instruments".

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, :lol:

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"...)

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

Re: HYPTUSS. coding mod for IT's

#222713

Postby kiloran » May 18th, 2019, 12:48 pm

Many thanks for that, Seagles. A couple of thoughts:

  1. If you use the Watchlist sheet, a similar change needs to be made there
  2. Perhaps we (Itsallaguess and I) could incorporate this into a future release.
    Old Code:
    If equitytype = "Equity Investment Instruments" Then
    New Code:
    If equitytype = "Equity Investment Instruments" or equitytype Like "IT*" Then
    (I haven't checked this for precise syntax accuracy, but you get the idea)

--kiloran

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

Re: HYPTUSS. coding mod for IT's

#222752

Postby seagles » May 18th, 2019, 2:09 pm

I do not use watchlists but did see that would need to be changed. If you would incorporate that change in the future I would be over the moon. One less thing for me to change.

Btw, do you remember Raptor asking if changing the data sheet sectors and cut/paste all changed shares to the beginning would work. Well it does, you just need to make sure that any changes that are made to other fields are correlated. The only problem I had was you had to insert them after the first entry. This way I can cut paste my changed entries from one hyptuss to the new one, rather than have to change each individual one.

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

Re: HYPTUSS. coding mod for IT's

#222757

Postby Itsallaguess » May 18th, 2019, 2:36 pm

seagles wrote:
Hope this may be of interest to some out there.


A truly excellent idea seagles, and so simple to implement that I think we'll do as kiloran has suggested and bring this into the vanilla versions the next time we carry out a release.

I do prefer kiloran's idea that allows for the original 'Equity Investment Instrument' or a modified 'IT xxx sector' codification, so I think that's probably the way we'll implement this to give the most flexibility, and also a totally transparent change to anyone that prefers to stick with things as they stand.

Thanks for taking the time to post this coding idea, especially in a way that allows non-VB-coders to give this a go if they like the improvement.

I would also like to take this opportunity to *fully endorse* your suggestion that anyone wanting to give this VB-modification a go should do so on a fully-segregated back-up copy of their HYPTUSS tool, just in case anything goes awry....

Thanks again - great work....

Cheers,

Itsallaguess

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

Re: HYPTUSS. coding mod for IT's

#222770

Postby kiloran » May 18th, 2019, 3:10 pm

seagles wrote:I do not use watchlists but did see that would need to be changed. If you would incorporate that change in the future I would be over the moon. One less thing for me to change.

It's added to my to-do list

seagles wrote:Btw, do you remember Raptor asking if changing the data sheet sectors and cut/paste all changed shares to the beginning would work. Well it does, you just need to make sure that any changes that are made to other fields are correlated. The only problem I had was you had to insert them after the first entry. This way I can cut paste my changed entries from one hyptuss to the new one, rather than have to change each individual one.

Sorry, I have yet got around to checking all the code on the Excel and LibreOffice versions to ensure there are no gotchas, but it is on my to-do list

--kiloran

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

Re: HYPTUSS. coding mod for IT's

#222915

Postby TUK020 » May 19th, 2019, 8:50 am

kiloran wrote:[*]Perhaps we (Itsallaguess and I) could incorporate this into a future release.

--kiloran


Yes please.

good idea seagles

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

Re: HYPTUSS. coding mod for IT's

#231903

Postby kiloran » June 25th, 2019, 10:36 am

seagles wrote:Leading on from a previous thread where Darka suggested a change and Itsallaguess suggesting a "code change" thread. I mentioned I had made a "minor change" to the VB code and data sheet to allow me to track ITs by a more varied title than lumping them all together as "Equity Investment Instruments".

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, :lol:

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"...)

HYPTUSS updated. See: viewtopic.php?f=27&t=18244

--kiloran


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 9 guests