New Point In Time functions to calculate , for example , 3Y average PE

Dear All,

We released several functions that work really well for calculating averages of price ratios since they operate point in time. You may of course find other uses for them. You will find them in the reference at FORMULA FUNCTIONS->INDIVIDUAL SECURITY. The “Full Description” reference is included below.

Let us know what you think.


These functions return the specific statistic of the values of “formula” computed in the past using our point-in-time historical engine. The function should NOT be used with split sensitive data, like earnings per share, as it they do not adjust for splits between the point in time date samples. They should also probably not be used with some estimate data since you will accessing different fiscal period.

These functions are typically used to calculate historical statistics for factors that include price , like Price to Sales. For historical statistics that only use financial statement data see the “Loop Historical” functions.

FHistAvg(“formula”, samples[, sample_offset=1, NAPct=20])
FHistMed(“formula”, samples[, sample_offset=1, NAPct=20])
FHistMin(“formula”, samples[, sample_offset=1, NAPct=20])
FHistMax(“formula”, samples[, sample_offset=1, NAPct=20])

Parameters

formula: the factor or formula you want evaluated in the past
samples: number of samples from 2-60
sample_offset: weekly offset of the samples 1-52
NAPct: the maximum percentage of values that can be NA 0-80

For example the average Pr2Sales ratio for the past 3 years can be calculated as

FHistAvg(“Pr2SalesTTM”,39,4)

We use 39 samples since 39 samples offset by 4 weeks = 156 weeks, which corresponds to 3Y.

cool, thank you Marco!!!

Is there any interest in FHistStdDev?

EDIT: Nevermind. I’ll prototype it first.

Does it make sense do to something like this vs Industry?


FHistAvg makes a lot of sense for ratios with price or market cap or EV in them, since those change daily. For sales growth, which changes quarterly, you should use LoopAvg.

For example, LoopAvg(“SalesGr%(Ctr,TTM)”,12,0) will give you the average TTM sales growth over the last three years.

Industrial factors - like SalesGr%TTMInd - aren’t usable w/ Loop* since they don’t support offset indexing. That is, there’s no place for the Loop* CTR to go. Use FHist* instead. To the Q, yes, it does make sense.

So… one “yea” and one “nah”. Which is it?

Great new addition. Many thanks

Is there a reason a true offset isn’t offered. The second parameter to FHist* seems to be a sampling period and not a starting offset.

I very much appreciate the new functionality
I would like to make sure my intuition about FHistAvg is correct.
Would this statement:
(FCFQ / EV + FHistAvg(“FCFQ / EV”,3,12)) / 2
be the equivalent of:
(FCFQ / EV (0,qtr) + FCFQ / EV (1,qtr) + FCFQ / EV (2,qtr) + FCFQ / EV (3,qtr)) / 4
If such a (0,qtr) nomenclature exited for PIT factors.
I guess I am not sure where FHistAvg(“FCFQ / EV”,3,12) as written begins, (0,qtr) or (1,qtr)?

Thank you,
Mark

LoopAvg needs a CTR in the formula. otherwise you are just averaging the same value

Formula should look something like this “SALES(CTR,TTM)”. PLease see the Full Description reference for the Loop functions

You’re right, I was wrong. I apologize. I also fixed my post above.

The first value in FHistAvg will be the current value. So if you’re looking for the average of the most recent four quarters of FCFQ/EV, you’d just use FHistAvg(“FCFQ/EV”,4,13).

Yuval,

Thank you. I did not realize FHistAvg included the current quarter. That help me a great deal.

Mark

Because EV is involved you cannot use LoopAvg which is more accurate to calculate “the average of the past 4 quarters”. So you are stuck with FHistAvg.

However, please note that the formula FHistAvg(“FCFQ/EV”,4,13) is not always spanning “4 quarters”. It’s simply the average of 4 point-in-time values 13 weeks apart. Companies don’t file on the same date every year, and data vendors delays vary. Quite often you will not cover 4 quarters (something like 30% of the time in fact)

To check that your 4 samples are spanning all 4 quarters you can use this trick

FHistSum(“qtrcomplete”,4,13) = 10

Since Q numbers 4+3+2+1 = 10. It’s all just for fun though. If you want the “1 year average of FCFQ/EV” I suggest spanning ~ 13 months and using more samples. Something like this

FHistAvg(“FCFQ/EV”,14,4)

That’s 14 samples every 4 weeks spanning 56 weeks.

Marco,

Thanks for the tip.

Mark