I have been trying to establish a formula to get median 10 years price/earnings ratio. If anyone help ?

Thank you in advance. Regards, aLL_oFFs

Jan 11, 2019 5:42:59 AM

sglinski

UNITED STATES
Joined: Feb 16, 2006
Post Count: 320
Offline

Re: Hi Friends,

Here is a 1st approximation to start:

LoopMedian("close(CTR*252)/EPSExclXor(CTR,ANN,FALLBACK)",10)

Apparently only EPS (not PE) offers an annual offset going back 10 years, so must divide it into closing price. I'd prefer an average (SMA) vs the close, but its offset can only go back 500 bars, about 2 years.LoopMedian function iterates over formula for CTR going from 0 to 9 (years back).Most years have 252 trading days, (e.g. 2017 is 251), so closing price will slip one day once in a while.

Jan 11, 2019 8:14:59 AM

Edit 1 times,
last edit by
sglinski
at Jan 11, 2019 8:16:54 AM

alloffs

TURKEY
Joined: Jun 24, 2014
Post Count: 5
Offline

Re: Hi Friends,

Thanks a lot for your help..Formula 01.PNG
(11547 bytes)
(Download count: 181)
(I was going to write this. But does not fit in row F)

Jan 11, 2019 8:27:50 AM

marco

UNITED STATES
Joined: Jan 1, 1970
Post Count: 6357
Offline

Re: Hi Friends,

Try to stick to the predefined factors as much as possible to avoid problems. The approach below may not work in some cases. In this case PEExclXorTTM is a good candidate because it's not affected by splits.

When you run this screen and set the report to be "Screen Factors" you will see in the column @pemedian IBM's median PE for the past three yearly samples. Please see the documentation for FHist & LoopMedian

Let me know if you need more assistance

Portfolio123 Staff.

Jan 11, 2019 4:31:21 PM

Edit 1 times,
last edit by
marco
at Jan 11, 2019 4:40:22 PM

alloffs

TURKEY
Joined: Jun 24, 2014
Post Count: 5
Offline

Re: Hi Friends,

Thanks a lot

Jan 13, 2019 3:10:06 PM

alloffs

TURKEY
Joined: Jun 24, 2014
Post Count: 5
Offline

Re: Hi Friends,

Hi Marco,

Sorry to bother you again. I just needed 10 years median PE and I put 10 take place 3 at the following formula you specified.

Showvar(@pemedian, LoopMedian("$PEhist",10,0,52,TRUE,TRUE)

I just got the same result like 3 years. Is it possible to see 10 year Median pe ?

Jan 14, 2019 4:56:47 AM

pdemartino

UNITED STATES
Joined: Sep 13, 2007
Post Count: 671Online

Re: Hi Friends,

It changes for me in at least one case. The calculation of the median using the form that is in the @pemedian function stops when it encounters a meaningless PE, so it's not going to change in every case.

Jan 14, 2019 9:41:33 AM

pdemartino

UNITED STATES
Joined: Sep 13, 2007
Post Count: 671Online

Re: Hi Friends,

I hate to contradict my boss, and do so with the utmost in respect and caution, but I wouldn't use his method. I would use sglinski's.

First, it's simpler. There's a lot to be said for that when I'm not sure that the extra effort is rewarded here.

Question: How, exactly, is Steve Job's release of the iPhone 3GS in 2009 affecting the price of AAPL today?

That's exactly what you're doing when you're looking at the P/E of 10 years ago in your formula. One can make a case that Apple's brand was getting more valuable or that its innovation pace has waned, but both of those are analysis over time, and you're not doing that. You're giving equal footing to the valuation of 10 years ago and of yesterday.

Remember that the markets are pricing stocks based on two things: Present total assets (the starting point) and projected growth of those assets through business operations. I get the idea of trying to test if a stock is expensive relative to historical valuation, but three years ago is already ancient history for this purpose. I personally wouldn't be looking back more than a year.

Finally, I think -- and I could be wrong here -- that the use of FHist is unnecessary in this case mathematically. If a company splits, the same ratio would be applied to numerator and denominator, so the PE should be more or less unaffected. That's for a simple split, of course, but if a company does some more complicated M&A activity then the whole analysis goes out the window anyway. I would also note that we can't easily detect those situations from the data.

Actually, thinking about it, I would include a check to make sure that you're using only positive EPS, though. Use this rule:

Thank you for your comments and formula. I agree with you companies can be different place when you compare 3-5 or 10 years. (New patents, acquiring companies at different sectors -it changes average pe, roe etc.)I try to use median value to exclude negative earnings years and some unmeaningful pe's like 1500 or 3000 when the company make small yearly profit.

My point was ; Even best companies like AAPL, GS or BRKA can be valued less than their historical average (or median9 PE. Sometimes not good reasons. (General market decline, sector ruling changes (ex Coal Industry Or Pharma)

As you pointed out you can miss good fashionable companies with this screen because of their pe looks higher. On the other hand you might find best bargains in this basket like HOG, ESRX (last year) AYI, FDX, UPS (currently)