counting no. of years of consecutive growth

Research
1

Hi All:

How can I create a formula that creates an output of no of years of growth in say revenues or EPS.

so if rev in 19> rev in 18 > rev in 17 but not > rev in 16 the returned value would be 2

Thanks so much for your help!

2 
LoopSum("Sales(CTR,ANN)>Sales(CTR+1,ANN)",10)

The usual use case is to find companies that have had X or greater increases in the last 10 years. So for 8, that would be:

LoopSum("Sales(CTR,ANN)>Sales(CTR+1,ANN)",10)>=8
3

Thanks Paul!

4

I’m curious how one would do the subject’s stated question? Suppose I only want to know the number of years of CONSECUTIVE growth? Last 8 years? Last 10 years? Last 20 years? So, with the first decrease, the loop would need to quit counting any further increases.

5

That’s

LoopSum("Eval(Sales(CTR,ANN)>Sales(CTR+1,ANN),Sales(CTR,ANN)>Sales(CTR+1,ANN),NA)",10,0,1,0,1)>=8

using the original examples.

So let me break that LoopSum down.

The first parameter is the quoted string that you’re analyzing. Note that CTR has replaced the offset, and that to indicate the prior period we’ve added one to CTR. I’ve also changed it so that it returns NA instead of false using the Eval function.

The second parameter is the number of times that you’re going to look back. In this case, the 10 is 10 years because we’re using fundamental formulas and their ANN parameters.

The third parameter is the starting value of the loop. The zero means to start with the most recent period, and is the default.

The fourth parameter is the increment of the loop. We’re counting individual years, so it’s 1 in this case, and 1 is also the default. If you were using technical functions then you could use something like 5 to represent weeks.

The fifth parameter is the NA treatment. Set to zero, the default, it will skip NAs, while set to 1 NAs will return NAs. That’s much more important for, say, LoopAvg, where it would be the difference between SUM/4 or SUM/5. It doesn’t really matter in this case because skipping it and returning an NA will be functionally equivalent for the sum.

The sixth parameter is the break parameter. If set to zero, the default, it will continue counting if it encounters an NA. If set to 1, as it is in this new example, it will stop counting when it gets an NA. With the way that we’ve changed the quoted string it will return NA when the prior year is no longer less than the current year.

Note that parameters three through six are optional. In this example parameters three through five are kept at their defaults, but because we changed parameter six we needed to include all of them.

6

Can be simplified to this

showvar(@consec,loopsum(“eval(sales(ctr,ANN)>sales(ctr+1,ANN),1,NA)”,10,0,1,TRUE,TRUE))

No need to re-evaluate the condition. @consec holds the number of consecutive increases. I made a public screen here

Below is a screenshot of companies in SP500 showing the # of consecutive increases and total increases.

Capture.JPG
Capture.JPG1735×1002 169 KB

7

Whoever thought of adding the break parameter deserves a pay raise.

BTW, there’s no need to repeat the Sales comparison. This is equivalent and a bit clearer, me thinks;

LoopSum("Eval(Sales(CTR,ANN)>Sales(CTR+1,ANN),1,NA)",10,0,1,0,1)>=8

Walter

8

Theoretically, sales could be equal. Nyah.

EDIT: Which wouldn’t change anything, I realized. Never mind.