Friday, August 17, 2018

Frequency function in Excel






Frequency of Stock Gains Over a 10-day 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 10-day 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 10-day period. Use the results to obtain the average, mean and median number of up days for all 10-day 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 Up-Day 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 up-day 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 6-day streak.   The 4-day 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.











No comments:

Post a Comment