How to Do a Runs Test in MS Excel 2007

This video shows you how to do a runs test in the basic package of MS Excel 2007 (i.e, without an Add-In). The purpose of a runs test is to determine if a set of binary data collected over time is random or not. The data I used represents the natural gas used in my home on a daily basis over a period of 30 days - starting near the end of January and ending near the end of February. The units of gas are in 100s of cubic feet.

Category:

Education

Tags:

• Runs
• Test

[ProfTDub]

First, find the area to the right of z = 1.29696 under a standard normal curve. The answer is 0.0973. If you did a one-sided test (where the alternative hypothesis is "the process has more runs than expected for a random process"), then the p-value for the test is 0.0973. Since that value's greater than the typical significance level of 0.05, you would not reject the idea that the process is truly random. The 2-sided p-value is 2*0.0973 = 0.1946 which is > 0.05, too, so don't reject random.

[ProfTDub]

There are some variations of the Runs Test. Most common is to use the mean, but the median may be preferable if you have outliers in your data. It's fine to use the Runs Test on returns. Keep in mind this is an unsophisticated statistic for testing randomness. You might look into autocorrelation. You can use the Runs Test on weekly data.

[lordjoesnow]

@ProfTDub so should i be doubling my P value?

[ProfTDub]

@lordjoesnow You should use a two-sided p-value if you're not sure - before collecting your data - whether you would have fewer or more runs than expected for a random process. Some returns have a slight positive autocorrelation over time, so you could justify a one-sided p-value (assuming there'd be less than expected runs compared to a random process).

[ProfTDub]

Generally, stock prices follow a random walk, so their differences would form a random process. Over longer time periods, stock prices may show increases in variation (violating the random walk model form), so transforming them into natural log units (almost equivalent to calculating the returns) puts them back into the random walk form. Then, the differences of the transformed series should be a random process.

[nichtschwimmer0605]

Yes, I'm analyzing returns! Was surprised as well, but for both examples, German Dax over 3 years daily returns and a German share over the same period, the results are more runs than expected.

I would interpret Z=2,58 and the other which is Z=0,58 as follows: at a significance level of 5% Z should be between -1,96 and +1,96 (following Z table) Index return doesn't follow random walk, Company's return does by Z prediction approach rather than p-value approach. Am I on the right track?

[ProfTDub]

@nichtschwimmer0605 Yes, that sounds right. So, you were trying to see if the index follows a random walk by seeing if the index returns follow a random process? A random walk has differences (price changes) that form a random process, but not necessarily returns (price change over previous price).

[lordjoesnow]

@ProfTDub do i get this 0.0973 by looking in the tables?

[lordjoesnow]

my z values are figures such as 1.29696011 and for another ln returns series i have 0.31624858 and then another has a z of 0.62716022. These are all positive so i minus 1 from them but my first value is still positive. Do you think maybe i made a mistake in the calculation? that particular series has 189 runs when its expected was 176 so i'd expect that it isnt particularily random but can't figure out what the z values are saying or how to get the correct p value and what that means

[lordjoesnow]

@ProfTDub ah okay. So the p-value in a one sided test shows how probable it is that the runs are less than expected. The 2 sided p-value shows how probable it is that the runs are less or more than expected? because im doing the financial crisis and i have sub samples for before, after and during the financial crisis ill do one sided. thanks for all your help

[lordjoesnow]

also does this work for weekly data?



[lordjoesnow]

hey, the wiki page says that we use the median rather the average? Also im using this method on ln returns? is that advisable?

[Raid3222]

Hi there,

I really appreciate your help by posting this great video. I do have a question. Once I get a number of p-value. what does that mean. How can i know this is really random or not that much. How can I know how the randomness is?