Frequency of Stock Gains Over
a 10day Period
Assignment: Download daily adjusted close stock prices
for a stock or fund for the past year.
Calculate the number of days the stock goes up every 10day period. Demonstrate how the Excel frequency function
can be used to calculate the likelihood that the stock goes up 0, 1, 2, …… 10
days for every 10day period. Use the results to obtain the average, mean and
median number of up days for all 10day periods in the sample.
Setting up the spreadsheet: I downloaded
data for VDE Vanguard Energy Fund over the past year. The date is in column A. The adjusted close stock price calculation
is in column B. A dummy variable set to
1 if the stock price went up in a day is in column C. The sum of the dummy variable over 10 day or
total up days from last 10 days is in column D.
The eleven possible outcomes for number of up days in the last 10 days
(0 to 10), which is the bin range for the frequency function is listed in
column E.
The input section of the database
for several rows looks like this.
Date

Adj Close

Dummy if VDE increased

Number of days Stock increased in last 10 days


8/16/17

82.68


8/17/17

81.52

0


8/18/17

82.05

1


8/21/17

81.49

0


8/22/17

82.15

1


8/23/17

82.53

1


8/24/17

82.52

0


8/25/17

83.04

1


8/28/17

82.56

0


8/29/17

82.46

0

BIN


8/30/17

82.52

1

5

0

8/31/17

83.13

1

6

1

9/1/17

83.94

1

6

2

9/5/17

84.37

1

7

3

9/6/17

85.77

1

7

4

9/7/17

85.77

0

6

5

9/8/17

84.79

0

6

6

9/11/17

85.64

1

6

7

9/12/17

86.25

1

7

8

9/13/17

87.48

1

8

9

9/14/17

87.95

1

8

10

9/15/17

88.08

1

8


9/18/17

88.56

1

8


9/19/17

88.78

1

8

The Frequency function:
The frequency function has
two arrays as inputs.
The first array is the data for the number of
days where the stock increased over the last 10 days. This data is in cells D12:D253 in the
spreadsheet.
The second array the bin range lists the 11
possible outcomes 0 to 10 for the number of possible up days out of 10 days. The bin range for this spreadsheet is
e12:e22.
The syntax for the frequency
function is =FREQUENCY(D12:D253,E12:E22).
When inputting the frequency
function, you must create a range equal in size to the bin range and use control,
shift, return or if you are on a MAC command shift return.
The frequency function gives
the number of items in each bin. I
copied the bin label and calculated the percent of items in each bin.
Results for VDE:
VDE UpDay Frequency Over 10 days


Value

# up days in a row
out of 10 days

Prob for number of up days in a row

0

0

0.000

1

0

0.000

2

3

0.012

3

24

0.099

4

54

0.223

5

48

0.198

6

55

0.227

7

26

0.107

8

25

0.103

9

7

0.029

10

0

0.000

242

1.000

The frequency distribution
data presented here covers 242 days starting with 8/30/2017 and ending with 8/15/18.
Some observations on the VDE streaks:
The bottom tail of the streak
distribution is small. O observations
with no up days out of 10, 0 observations with 1 upday at of 10 and 3
observations with one up day out of 10.
The top tail of the streak
distribution is a bit larger but also small.
There are 0 days with 10 straight wins and 7 days with 9 of 10 wins.
The mean value of the number
of winning days out of 10 days is 5.4.
This was obtained from SUMPRODUCT(J12:J22,H12:H22).
The mode outcome is a 6day
streak. The 4day streak outcome is a close
second.
The median is 5 days. This is the smallest outcome where 50
percent or more of the outcomes are <=5.
More on winning streak stock outcomes:
The winning streak outcome
for stocks is funds is a potentially valuable indicator for investors seeking
to buy low and sell high and for day traders.
It is one of the statistics in combination with departures from previous
high prices or previous 52 low prices that I like to look at.
I am in the process of
updating winning streak outcome charts for several funds and will post results on
www.financememos.com next week.