Please help with P2Book relative to historical ratios

Is it possible to calculate the historical price to book ratio over say the past 5 years? I tried to use:
LoopSum(“(BookVal(CTR,TTM)/Shares(CTR,Qtr))/CloseAdj(CTR*63)” ,4,4,4)
But the system didn’t like it. CloseAdj() has been depreciated. But the Close() function doesn’t do the job either because it is adjusted for splits and dividends.

Is there a way?

Chipper - Close is the closing price you would have seen at the time. Forward splits and dividends are undone. So it is what you want to use.
Steve

Steve, then how would you write the TR over the past 20 bars (=trading days) lagged by 1 week? I guess it’s not ‘Close(5)/Close(25)’.

Maybe use BVPS5YAvg and a corresponding avg for prices?

Steve I confirmed that Close(20) is the adjusted closing price.
Check out https://www.portfolio123.com/app/screen/summary/105910 Notice how BLK has a Close(20) of 297.14 This matches the adjusted closing price on May 19, 2014 as per http://finance.yahoo.com/q/hp?s=BLK+Historical+Prices

This means that Close(20)/Close(0) is measuring total returns. Therefore BookVal(4,Qtr)/Close(252) is distorted by dividend and split adjustments.

Marco, how do you use the avg for prices, if the actual prices are unavailable. Is the function CloseExDiv() what I’m looking for? I read the description half a dozen times and I’m still not sure. Here’s the description:
CloseExDiv()

[quote]
Historical close unadjusted by dividends…
[/quote]Sounds like it’s exactly what I need. But then the description continues:[quote]
…When this function is evaluated in the past it reverses out future splits (if any).
[/quote]I read that a dozen times and I am still not sure what that means. ‘Future Splits’? The future didn’t happen yet. And why do both Close() and CloseExDiv() ‘reverse out future splits’ (whatever that means)?

Clear descriptions can save you and us a lot of time later trying to describe what you meant all along. To paraphrase a software engineer at Google “Documentation is written once, and read many times”

Does it mean that it is ‘the [unadjusted] closing price that you would have seen at the time’ like Steve puts it?

Or perhaps it means that it is the price adjusted to what they would have been if all stock splits had happened already? If so then we are still looking for a function that can return ‘the [unadjusted] closing price that you would have seen at the time’. Maybe we need to insert the SplitFactor() Function into my formula to make it
(BookVal(4,Qtr)/Shares(4,Qtr)) / (Close(252)*SplitFactor(252))
Hmm… Is it correct to multiply by SplitFactor() or do we divide by SplitFactor()? Perhaps we divide by SplitFactor()/100?‘[:-/]’

Looks like you are right Chipper. Close(0) is PIT but apparently not Close(20), at least for dividends.

I read CloseExDiv() as being what you want.

Thinking about it some more, it’s probably better to use

Avg( Pr2BookQ , FHist(“Pr2BookQ”,52) , FHist(“Pr2BookQ”,104) )

This calculates the average point-in-time Pr2BookQ for the past 2 years including the latest value. FHist doesn’t like values greater than 104. Not sure why, I 'll change it to at least 156 which could give you three years.

The problem when doing average of factors that use price & fundamentantals is that the price needs to be aligned up with the fundamentals. Other providers use the period-end-date for the price, but that could be very wrong. FHist() takes care of using the latest price & latest fundamentals.

One cavet with FHist is that the starting date of your sim needs to be two years later so that if can calculate (or three when I allow three).

Thanks Marco. FHist() is the most accurate way but as you say it only goes back 2-3 years and would not begin until 2-3 years into the data. (Is there a reason for the 3 year limit?) On the other hand using the approximate ratios over the past 5-7 years is more accurate but in a different way.

Is it a big deal to extend FHist() to add more years and to substitute approximate values for the first few years of data? I see a lot of potential here. For example we can find the best valuation ratio for a stock by choosing the one that showed the least amount of fluctuation historically.

As you probably know, different stocks work better with different valuation ratios. For example banks often trade on multiples of Price/Book while consumer staples such as KO (Coca Cola) trade on multiples of Price/Earnings. There is no accurate way to use the industry classifications to choose the best ratio because of flaws in Compustat’s classification system.

With a longer FHist() that is useable for at least 10 years of backtesting, we can compare for example the
StdDev( FHist(“Pr2BookQ”, 52), FHist(“Pr2BookQ”, 522), FHist(“Pr2BookQ”, 523), FHist(“Pr2BookQ”, 524), FHist(“Pr2BookQ”, 525) )
to
StdDev( FHist(“PEExclXorTTM”, 52), FHist(“PEExclXorTTM”, 522), FHist(“PEExclXorTTM”, 523), FHist(“PEExclXorTTM”, 524), FHist(“PEExclXorTTM”, 525) )
and use the valuation ratio that has the lower standard deviation.

Thanks.

Chaim, just curious. Were you able to come up with a formula methodology for 5-10 year Avg, std dev, etc , for various valuation ratios?

As an aside, P123 does have some data because you can see it on an individual ticker basis in the panels. See Valuation ratios going back to 2007. If they could just extract that yearly data and make it avaiable as a factor, that would help a lot.

Anyway, any luck with this?

David, to get the true historical price for accurate comparisons with historical line items use: CloseExDiv(BarsAgo) * SplitFactor(BarsAgo) Ex: P/E Ratio 1 year ago:

(CloseExDiv(252) * SplitFactor(252)) / Eval(EPSExclXor(4,Qtr) > 0, EPSExclXor(4,Qtr) / Close(252), NA) Remember, this only works for line items. A much easier way is to just use the FHist() function as Marco suggested :).
Chaim

Got it. Thanks. I’ll try it.