Spreadsheet advice

[gbalin]

Hi.
I have built up a rather messy spreadsheet over time, tracking the performance of my equities, funds and foreign equities. What I would like to do is sort them into growth and HYPs. I can create a column to say which each type is, but can anyone advise me if there is a way to get a new sheet to extract the HYPs from the main sheet so I can start messing around with yields and such like?
TIA
GB

[Itsallaguess]

I have built up a rather messy spreadsheet over time, tracking the performance of my equities, funds and foreign equities.

What I would like to do is sort them into growth and HYPs.

I can create a column to say which each type is, but can anyone advise me if there is a way to get a new sheet to extract the HYPs from the main sheet so I can start messing around with yields and such like?

Some blokes around these parts have written a specific tool to help with HYP portfolio tracking, amongst other things.

I think someone said it'll automatically download Yahoo price information and Digital Look forecast-yield information for you as well as do loads of other cool stuff.

It might be worth taking a look at the Lemon Fool Software Repository -

http://lemonfoolfinancialsoftware.weebl ... op-up.html

There's some screen-shots on there to give you a taste of what it can do -

http://lemonfoolfinancialsoftware.weebl ... shots.html

There's an instruction manual in the ZIP download, so getting going is really easy, so I've heard.

An option for you might be to have a play with that, and if you like it then just transfer your existing sheets into the tool, and go from there, perhaps cross-referencing if you need to.

If you've any specific questions around the tool, I think they prefer discussion over on the Financial Software Board, rather than clogging up the HYP Practical Board (they like to keep this board free of software-stuff, so they can discuss how many days they've been good and not tinkered with their HYP....:O) -

viewforum.php?f=27

Cheers,

Itsallaguess

[monabri]

Not quite sure what you want here but here's a few suggestions:

(1) You could transfer HYP fund information onto a new worksheet (call it worksheet 1) and leave everything else on the original worksheet within the same spreadsheet. Then you could have a further new "summary sheet" which summarises the 2 sheets.

or
(2) You could add an extra column to specify what type of fund it is and then use the standard Excel (or open office) filters to select the fund type thus hiding info on the other types of funds.

or (perhaps what you're looking for)
(3) You could simply create a new worksheet (call it "Hyp Funds") and then list out all you HYP shares( eg BP VOD IMB BATS etc etc).
Then, by the name of each company, you could find an empty cell and enter the "=" sign (omit the " ") and then click onto the sheet where the info
on that HYP share is stored - or specifically, the cell in which the info is stored. Then hit return. This will "feed" the info back to your new sheet.

or
(4) use the vlookup command (tutorials on YouTube)

regards
monabri

[gbalin]

Many thanks for the useful suggestions. A lot of study and playing around is ahead of me, I see. Good job I've retired!
GB

[Alaric]

I can create a column to say which each type is, but can anyone advise me if there is a way to get a new sheet to extract the HYPs from the main sheet so I can start messing around with yields and such like?

Provided you haven't set up any formulae that use absolute row references, you could just sort by your new column. That will put all the HYP flagged rows in one block to be cut, copied or modified as you see fit. Take a copy (Save as) of your master sheet first though.

Assuming you have tens rather than hundreds of holdings, just making a copy of the master sheet and deleting all the non-HYP rows would also work if a little slow and tedious.

[FarmerTom]

Do you know one sheet can refer to entries on another?
e.g. =$Sheet1.F3

And you can do if-then-else programming using something like this:-
=IF(F38="HYP",G38,H38)
Put this into a cell and it will take the value from G38 if F38 has 'HYP' in it,
otherwise it will take the value from H38.

You can also put constants in there:-
=IF(F38="HYP",0,1)

Hope these bits help

[gbalin]

Thanks alaric and fathertom. That is sort of where I am at the moment, looking up another sheet but what i would like to do is have the second sheet automatically populated from the first when i add new stuff. I think that is where the if-then-else might come in handy. Or I need to start learning pivot tables [eek]
GB

[Alaric]

what i would like to do is have the second sheet automatically populated from the first when i add new stuff.

Perhaps you should look into recording, editing and running macros and tying them to buttons. So you set up a button "Extract HYP data" and it does whatever it is you want to see.

[gbalin]

Now that's a thought...
GB

Moderator Message:
been away, this is nit a hyp practical post so moving. Raptor