5 year Average Price to Sales

Research
1

Is there any way to get a value for 5 year Average Price to Sales for use in the screener? I haven’t been able to figure out a formula for this.

2

Simplest way at the moment is to use several FHist() which calculates a ratio X-weeks ago and then do an average.

Here’s a screen for the DOW jones that average 5 yearly values: https://www.portfolio123.com/app/screen/summary/109116

Here are the rules

showvar(@P2SY1, fhist("Pr2SalesTTM ",52)) // 1Y ago
showvar(@P2SY2, fhist("Pr2SalesTTM ",104)) // 2Y ago
showvar(@P2SY3, fhist("Pr2SalesTTM ",156)) // 3Y ago
showvar(@P2SY4, fhist("Pr2SalesTTM ",208)) // 4Y ago
showvar(@P2SY5, fhist("Pr2SalesTTM ",260)) // 5Y ago
showvar(@avgP2S5Y, AVG (Pr2SalesTTM ,@P2SY1, @P2SY2, @P2SY3, @P2SY4, @P2SY5) )

NOTE: you can only run this screen after 1/1/2004

3

Am I mistaken or is this screener averaging 6 years instead of 5 years?

4

You are right HH. @P2SY5 should be dropped from the formula.
Steve

5

Thanks for the help!

6

Thanks for the posting. Struggled with this one, and boom, found what I was looking for.

7

I’d use the loopAvg() function to achieve this in a single line.

Of course, I’d have to workaround the nested quotes limitation by eliminating FHist

8

Maybe I have this wrong, but doesn’t the full description of FHist() say you aren’t supposed to use “split-sensitive” data?

I think Price/Sales would qualify as “split-sensitive”, wouldn’t it?

9

Oh, wait. I get it. Earnings per Share would be split sensitive, but not a ratio like PE or PS, because BOTH the Price and Sales (or Earnings) would be divided by the same number of shares, point in time, right?

10

The LoopAvg formula would be LoopAvg (“Close(63*Ctr)/Eval(EPSExclXor(Ctr,TTM) > 0, EPSExclXor(Ctr,TTM), NA)”,20,0). This gets you twenty data points rather than five.

Another way to get this is Avg (PEHigh, PELow). See documentation: https://www.portfolio123.com/doc/doc_detail.jsp?factor=PEHigh&popUpFullDesc=1 The disadvantage of this is that the average of the high and the low is not usually the average of all the months. The advantage of this is that this method relies on sixty data points.

I would advise looking for the median (LoopMedian) rather than the average just so you avoid stuff like CWST, which has an average five-year PE of approximately 251,000,000. Using median that drops down to 229.

11

Oops. That was the formula for five-year-average PE. Substitute Sales for EPSExclXor for five-year-average P/S. Sorry.