Testing for Seasonality In Excel

A favourite Bloomberg function on the sell side seems to be SEAG . This function shows seasonality for a selected security and is popular among sales desks, particularly in quiet markets. Below is an example of the function for EURUSD 1year ATM volatility over the past 10 years.

Shot1

It is apparent that January tends to be a soft month for vols while vols tend to increase in May.

In excel we can improve on this method by testing the statistical significance of this seasonality. The method that follows can be expanded to a wide variety of hypothesis that can be tested easily in excel.

To test for statistical significance we can set up a regression equation that tests the conditional mean of the observed changes in a security (in our example EURUSD 1y I.Vol) based on the month of a year. The equation to be tested is:

Eq1

Where

Eq2

With this set up each Beta coefficient is a test for any statistical significance of a seasonal pattern. This regression model can be easily set up in excel. It can be automated to update dynamically for different time series using LINEST function but here we will just show an example using Data Analysis functionality in excel.

The first step is to set up the data in excel to run regression analysis.

1) Load in monthly data into a spread sheet. In the screen shot thats in column Q and R. In the column next to it calculate monthly changes in the implied volatility.

2) Now we need to add a column for each month where the first column equals 1 in the row where the month is January and zero otherwise. The second column equals 1 when the row is for the month of February and zero otherwise. Repeat for all the months. In the below screen shot we can use an IF statement to test the month and make the necessary calculation.

1

 

Now that we have our dummy variables set up we can run the regression model.

3) Go to Data ribbon and Data Analysis button. Select Regression from the list and press OK

Shot3

4) Input the monthly change column range for Y Range and the dummy variables range in X Range fields. Make sure you select the column labels in the range and select the Labels checkbox. Also it is important to select Constant is Zero checkbox. Since we have 12 explanatory variables adding a constant will over-specify the regression equation and you will have erroneous results.

2

The output is presented below.

3

We can see that according to the p-value there is indeed a statistically significant seasonality in the implied volatility for the month of January, May, and November.

To help us visualize the results we can set up a graph of the coefficients that we have estimated along with the 95% confidence band. Adding some descriptive statistics can help with the analysis. We can also plot a time series for a selected month.

Shot6

Some useful resources:

Excellent introductory econometrics book by Chris Brooks: http://www.cambridge.org/us/academic/subjects/economics/finance/introductory-econometrics-finance-3rd-edition?format=PB

Advertisements

12 thoughts on “Testing for Seasonality In Excel

      1. Can you help me if this is the case: The dependent variable is PX_Last, what will be the independent variables be? From Jan to Dec or from Jan to Nov? Intercept not set to zero?

        Like

      2. If intercept not set to zero you need to use 11 months not 12. but your interpretation of the coefficient changes. if you estimate model using feb to dec with intercept then the coefficient estimates how much higher or lower returns are in the other months OVER the jan returns. hope that makes sense

        Like

      3. Thanks for the quick response. If I will only include Feb to Dec as independent variables, how would I know the p-value of Jan? If it is significant or not?

        Like

      4. you are testing all other months returns over jan. if you are looking at monthly seasonality there is no reason to omit jan as a dummy variable. include 12 months and leave out the intercept

        Like

  1. Dear brother
    Could you please provide me with some detail about the software that used to do the example of the function for EURUSD 1year ATM volatility over the past 10 years
    Thank you in advance

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s