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

Why median?

For discussion of the practicalities of setting up and operating income-portfolios which follow the HYP Group Guidelines. READ Guidelines before posting
Forum rules
Tight HYP discussions only please - OT please discuss in strategies
Breelander
Lemon Quarter
Posts: 4179
Joined: November 4th, 2016, 9:42 pm
Has thanked: 1000 times
Been thanked: 1855 times

Re: Why median?

#36108

Postby Breelander » March 4th, 2017, 12:29 am

I just put the formula =MEDIAN(cur_val) in the unused cell B1.

...and the text 'Median' in C1. You could also use =AVERAGE(cur_val) in another cell (B4, perhaps?).

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

Re: Why median?

#36123

Postby Itsallaguess » March 4th, 2017, 6:16 am

Breelander wrote:
It's even easier than that. Look at cell B3 (the 'Portfolio Value') on the High Yield Portfolio sheet. The formula is =SUM(cur_val) where (conveniently) cur_val is the range you need. I just put the formula =MEDIAN(cur_val) in the unused cell B1.


Thanks for bringing the topic of named ranges up Bree, but just to complicate things a little bit your suggestion above is only currently valid for the OpenOffice version of the tool, as there's no cur_val named range set up by default in the Excel version.

Kiloran and I should probably align the two sets up at some point, and we might even think about adding some extra default definitions as well, such as the ones being discussed here (HYP_MEDIAN / HYP_AVERAGE) if people would find that useful, and help with some of the manual work being discussed here?

Anyhow, for anyone wanting to insert a cur_val named range into their Excel version as it stands, to enable them to then insert stuff like =MEDIAN(cur_val) anywhere else on their Excel worksheets, they can use the instructions below to manually add the cur_val named range into their own Excel spreadsheets -

1. Open the HYPTUSS tool.

2. Using the drop-down menu, go to INSERT / NAME / DEFINE

3. In the 'Define Name' window, enter cur_val as the named range

4. In the lower 'Refers to' box, copy and paste this ----------------> ='High Yield Portfolio'!$G:$G

5. In the 'Define Name' window, select the 'Add' button to add the cur_val named range to the existing list.

6. Close the 'Define Name' window

7. To verify that the above processes has worked, enter the following somewhere to the right of the main portfolio sheet --------------> =MEDIAN(cur_val)

8. If all is well, you should then see the MEDIAN figure for the range of share-values in your HYP portfolio.

If someone could please check the above process in their Excel version and report back if they get it working, that would be great?

I'm using a quite old version of Excel, so the path to the 'Define Name' window through the ribbon for newer versions can be found here -

https://support.office.com/en-gb/articl ... create_nam

Cheers,

Itsallaguess

Moderator Message:
The setting up or changing hyptuss is not a topic for hyp practical. There is a board for financial software discussions. Raptor.

Arborbridge
The full Lemon
Posts: 10376
Joined: November 4th, 2016, 9:33 am
Has thanked: 3604 times
Been thanked: 5233 times

Re: Why median?

#36136

Postby Arborbridge » March 4th, 2017, 9:01 am

Thanks Bree, that's useful to know. You learn something every day from the Lemon Fool - here actually aided by being slightly off topic, though still part of HYP practical 8-)

miner1000
2 Lemon pips
Posts: 180
Joined: November 4th, 2016, 1:36 pm
Has thanked: 10 times
Been thanked: 129 times

Re: Why median?

#36343

Postby miner1000 » March 5th, 2017, 4:55 am

Whilst parts of this thread are somewhat interesting, I think the main thing to take from these discussions is that one or two on this board perhaps need to get out more. Golf is good and darts not bad if you like a tipple. :D
And I really am not trying to be mean.

Arborbridge
The full Lemon
Posts: 10376
Joined: November 4th, 2016, 9:33 am
Has thanked: 3604 times
Been thanked: 5233 times

Re: Why median?

#36346

Postby Arborbridge » March 5th, 2017, 8:20 am

And I really am not trying to be mean.


And I'm sure you are correct. We get a bit obsessive at times.

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Why median?

#36352

Postby staffordian » March 5th, 2017, 9:01 am

miner1000 wrote:Whilst parts of this thread are somewhat interesting, I think the main thing to take from these discussions is that one or two on this board perhaps need to get out more. Golf is good and darts not bad if you like a tipple. :D
And I really am not trying to be mean.

Did you mean to post that?

Not sure what the average person will make of it :D

Staffordian

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

Re: Why median?

#36426

Postby TUK020 » March 5th, 2017, 3:41 pm

He really is not trying to be mean, as he is median......

johnw11
Posts: 40
Joined: December 1st, 2016, 7:02 am
Has thanked: 22 times
Been thanked: 19 times

Re: Why median?

#36515

Postby johnw11 » March 5th, 2017, 9:53 pm

My HYP has 26 shares in it with 2 outliers in the form of Lloyds and S32.

The difference between the Average and Median figure is about 5%. I actually remove the 2 outliers from my calculations and that reduced the difference to about 0.5%. So for practical purposes the difference is meaningless.

I've looked back to the early days of the Portfolio 9-10 years ago and the difference was 10-15% over the year.

midgesgalore
Lemon Slice
Posts: 250
Joined: November 5th, 2016, 12:02 am
Has thanked: 268 times
Been thanked: 71 times

Re: Why median?

#36546

Postby midgesgalore » March 6th, 2017, 12:49 am

Itsallaguess wrote:If someone could please check the above process in their Excel version and report back if they get it working, that would be great?

Cheers,

Itsallaguess

Moderator Message:
The setting up or changing hyptuss is not a topic for hyp practical. There is a board for financial software discussions. Raptor.


In any case it does work

midgesgalore

Urbandreamer
Lemon Quarter
Posts: 3122
Joined: December 7th, 2016, 9:09 pm
Has thanked: 347 times
Been thanked: 1025 times

Re: Why median?

#36558

Postby Urbandreamer » March 6th, 2017, 7:30 am

Late to the party as usual!

Many of us have a firm rule to prevent one share becoming the bulk of the portfolio. Ie trim if 2 x median.
Ok yes this is timkering, but let us ignore that for the moment.

If you replace that rule with 2 x average, then the price of that share will effect the level at which you consider trimming it.

As it happens I don't actually use the median for that instead relying on a "gut" level, which stangely is close to the median. However as median is easy to calculate I'm going to do so in future in case my diet throws my portfolio off.

vrdiver
Lemon Quarter
Posts: 2574
Joined: November 5th, 2016, 2:22 am
Has thanked: 552 times
Been thanked: 1212 times

Re: Why median?

#36595

Postby vrdiver » March 6th, 2017, 9:49 am

In my HYPTUS I have added an analytics tab, one cell of which calculates the mean of my largest 20 holdings (as this is what I use when considering if a share is too large a holding, risk-wise).

The formulae is:
=(LARGE(M:M,1)+LARGE(M:M,2)+LARGE(M:M,3)+LARGE(M:M,4)+LARGE(M:M,5)+LARGE(M:M,6)+LARGE(M:M,7)+LARGE(M:M,8)+LARGE(M:M,9)+LARGE(M:M,10)+LARGE(M:M,11)+LARGE(M:M,12)+LARGE(M:M,13)+LARGE(M:M,14)+LARGE(M:M,15)+LARGE(M:M,16)+LARGE(M:M,17)+LARGE(M:M,18)+LARGE(M:M,19)+LARGE(M:M,20))/20"

I could make M:M a named range, but the formulae is still "clunky". Any suggestions for a more elegant variant appreciated!

VRD

StepOne
Lemon Slice
Posts: 668
Joined: November 4th, 2016, 9:17 am
Has thanked: 195 times
Been thanked: 185 times

Re: Why median?

#36673

Postby StepOne » March 6th, 2017, 1:41 pm

vrdiver wrote:Any suggestions for a more elegant variant appreciated!VRD


Not really, but why not just sort your spreadsheet by total holding value, then just take the mean of the first 20 cells?

Or I would have another column, with a formula which evaluates to the Holding value, if it is greater than or equal large(M:M,20), or zero. i.e.;

=IF(M2>=LARGE(M:M,20),M2,0) - for row 2.

Then, just SUM that column and divide by 20 (probably safer to divide by COUNT(new column) in case the 20th and 21st holdings are exactly the same size)

StepOne

vrdiver
Lemon Quarter
Posts: 2574
Joined: November 5th, 2016, 2:22 am
Has thanked: 552 times
Been thanked: 1212 times

Re: Why median?

#36740

Postby vrdiver » March 6th, 2017, 5:48 pm

StepOne wrote:
vrdiver wrote:Any suggestions for a more elegant variant appreciated!VRD


why not just sort your spreadsheet by total holding value, then just take the mean of the first 20 cells?

Or I would have another column, with a formula which evaluates to the Holding value, if it is greater than or equal large(M:M,20), or zero. i.e.;

=IF(M2>=LARGE(M:M,20),M2,0) - for row 2.

Then, just SUM that column and divide by 20 (probably safer to divide by COUNT(new column) in case the 20th and 21st holdings are exactly the same size)

StepOne


Thanks for that. I sort by a variety of columns, depending on what I'm looking at (Yield, XIRR, Sector Income Contribution etc.) so I wanted the top20 Median to survive different sort sequences. I like the "If" statement approach; it's more logical and easier to tweak should I want to play with a different number than the top 20 holdings, so I think I'll take up your offer of the alternative approach!

VRD

Arborbridge
The full Lemon
Posts: 10376
Joined: November 4th, 2016, 9:33 am
Has thanked: 3604 times
Been thanked: 5233 times

Re: Why median?

#36774

Postby Arborbridge » March 6th, 2017, 7:56 pm

It seems Raptor's moderation note has fallen on deaf eyes ;)

Over to Financial Software, the man said.

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

Re: Why median?

#36783

Postby Raptor » March 6th, 2017, 8:44 pm

Arborbridge wrote:It seems Raptor's moderation note has fallen on deaf eyes ;)

Over to Financial Software, the man said.


Seems you are right not only on this thread. Might be time I move over and let someone else with the time and authority to take it on. Any volunteers?

Raptor.

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

Re: Why median?

#36829

Postby Itsallaguess » March 7th, 2017, 5:13 am

Arborbridge wrote:
It seems Raptor's moderation note has fallen on deaf eyes ;)

Over to Financial Software, the man said.


You're quite right Arb, I think rather than clog this board up any further with discussions around spreadsheet functionality, we're best to continue that over on the Financial Software Board.

I've started a thread here on the Financial Software Board for people that are wanting to carry on discussing this area of portfolio spreadsheet work -

viewtopic.php?f=27&t=3749

Apologies to Raptor for the continued meanderings. Please keep up the good work that you're doing on the mod side of things, we do appreciate that it's like herding cats at times.... :)

Cheers,

Itsallaguess

Breelander
Lemon Quarter
Posts: 4179
Joined: November 4th, 2016, 9:42 pm
Has thanked: 1000 times
Been thanked: 1855 times

Re: Why median?

#36896

Postby Breelander » March 7th, 2017, 12:05 pm

Itsallaguess wrote:Please keep up the good work that you're doing on the mod side of things, we do appreciate that it's like herding cats at times.... :)


https://www.youtube.com/watch?v=Pk7yqlTMvp8


Return to “HYP Practical (See Group Guidelines)”

Who is online

Users browsing this forum: No registered users and 17 guests