Rolling window regression with panel data

[Jennifer Xie]

Hi there,

I am running a three-year window regression of operating profit on sales by using quarterly data for each firm over three year window. I will have to obtain the coefficient of sales as well.

The sample period is from 2007 to 2014. For each firm each year, it has 4 quarterly data. I'm using three year forward data to calculate, for example, using the quarterly data in 2007,2008,2009 to calculate the relationship between profit and sales for a firm in 2007.

Can anyone tell me how to use -rolling- in my case?

Regards,
Jennifer

[Nick Cox]

I doubt I understand this correctly, but here is code to regress profit on sales in the next 12 quarters. I am not an economist, but I can't see the causality there.

I am using rangestat from SSC. I've not looked at rolling for some time, but there is a personal bias in there. In your case an extra option such as by(firm) would pin it down.

webuse turksales, clear 
set seed 2803 
gen profit = exp(rnormal()) 

rangestat (reg) profit sales, interval(t 1 12)

[Jennifer Xie]

Thanks Nick,

Here is how my data looks like. I want to get the coefficient of sales for each firm with three-year window. For example, firm 2 in 2007 using firm2's 2007,2008,2009 data; firm 2 in 2008 using firm 2's 2008, 2009, 2010 data. Your codes above use 12 as interval, but for some firms in my data, they don't less than 12 quarters in three-year window. How can I address this issue?

Stkcd	Accper	year	code	sales	Operating Profit
2	2007-03-31	2007	22007	4114138824	829587980
2	2007-06-30	2007	22007	1.1097E+10	2541192211
2	2007-09-30	2007	22007	1.4173E+10	2976200412
2	2007-12-31	2007	22007	3.5527E+10	7652897500
2	2008-03-31	2008	22008	6400435840	1028143668
2	2008-06-30	2008	22008	1.7255E+10	3327479351
2	2008-09-30	2008	22008	2.2542E+10	3758330382
2	2008-12-31	2008	22008	4.0992E+10	6364789552
2	2009-03-31	2009	22009	8164318689	1231593896
2	2009-06-30	2009	22009	2.1809E+10	4190846870
2	2009-09-30	2009	22009	2.9544E+10	5022741585
2	2009-12-31	2009	22009	4.8881E+10	8685082798
2	2010-03-31	2010	22010	7504969755	1541948905
2	2010-06-30	2010	22010	1.6766E+10	4118305474
2	2010-09-30	2010	22010	2.238E+10	5008143983
2	2010-12-31	2010	22010	5.0714E+10	1.1895E+10
2	2011-03-31	2011	22011	7970255508	1648888823
2	2011-06-30	2011	22011	1.9989E+10	4417871993
2	2011-09-30	2011	22011	2.9308E+10	5603083791
2	2011-12-31	2011	22011	7.1783E+10	1.5763E+10
2	2012-03-31	2012	22012	1.0346E+10	2036895217
2	2012-06-30	2012	22012	3.0723E+10	6012329738
2	2012-09-30	2012	22012	4.6128E+10	8152930727
2	2012-12-31	2012	22012	1.0312E+11	2.1013E+10
2	2013-03-31	2013	22013	1.4E+10	2395144446
2	2013-06-30	2013	22013	4.139E+10	7105211008
2	2013-09-30	2013	22013	6.3415E+10	9970471510
2	2013-12-31	2013	22013	1.3542E+11	2.4261E+10
2	2014-03-31	2014	22014	9497216582	2017750434
2	2014-06-30	2014	22014	4.0962E+10	6856260391
2	2014-09-30	2014	22014	6.314E+10	9557660290
2	2014-12-31	2014	22014	1.4639E+11	2.4979E+10
2	2015-03-31	2015	22015	8894338157	1169822712
2	2015-06-30	2015	22015	5.0267E+10	8718934359
2	2015-09-30	2015	22015	7.9596E+10	1.2354E+10
2	2015-12-31	2015	22015	1.9555E+11	3.3123E+10
2	2016-03-31	2016	22016	1.4611E+10	1646600784
2	2016-06-30	2016	22016	7.4795E+10	9904605249
2	2016-09-30	2016	22016	1.1705E+11	1.5787E+10
2	2016-12-31	2016	22016	2.4048E+11	3.9024E+10

[Nick Cox]

It's your project. You can stick to reporting regressions only when based on a full set or report incomplete regressions with a count.

[Jennifer Xie]

Sorry, what I mean is what code shall I use for the ones with Less than 12 quarters when running the regression. Is there any code to just identify the three-year interval instead of 12 quarters?

[Clyde Schechter]

There are two issues here. First, it turns out that all though you only have one observation per quarter (in your example), your dates are apparently daily dates (or, worse, strings that look like daily dates). So before you do anything, you need to calculate a new variable qdate, that is a quarterly date. Assuming you are starting from an actual Stata internal format numerical date variable, the -qofd()- function will get you that. (If you're starting from a string date variable then first calculate a daily date variable using the -daily()- function and then apply qofd() to that.)

Once you have quarterly dates, you need to understand how the -interval()- option of -rangestat- works. By specifying -12 to -1 you are not specifying 12 observations. You are specifying all observations that fall between 1 and 12 quarters before the date of the current observation. If there are fewer than 12 of those, then your regression will be based on fewer than 12 observations. (Similarly, if you have more than one observation per quarter, the regression could be based on more than 12 observations: however many fall in that three year period.)

Note also that part of the output you will get from -rangestat- is the variable reg_nobs, which tells you how many quarters were actually included in the regression for that observation.


In the future, please use the -dataex- command to post example data. The data listing you show in #3 is difficult enough for human eyes to work with; importing it into Stata to actually work out some demonstration code to show you would be a tedious and frustrating task. When you use -dataex-, you enable those who want to help you to create a complete and faithful (e.g. I wouldn't be wondering if your date variable is string or numeric) replica of your Stata example. ) So please run -ssc install dataex- to get the command, and then run -help dataex- to see the simple instructions for using it. Always use -dataex- when posting example Stata data here. Thank you.

[Jennifer Xie]

@Clyde Schechter, Thanks. I use rangestat (reg) operatingprofit sales, interval ( year 1 3) by( stkcd). The results reg_obs gives me 12 intervals till year 2013. But from 2014 the observation become 8, 2015 become 4, and 2016 shows nothing. Anything wrong with my code？

[Clyde Schechter]

What do you want your code to do? Your code does a rolling regression, within observations defined by a variable stkcd. For each observation, the regression sample is those observations where year ranges between the next year and three years hence, inclusive. For example, for 2013, you are regressing in those observations from 2014, 2015, and 2016. If that's what you want, then the number of observations you report for the various years looks right to me. If you have four observations in each year, and if your data ends with 2016, then I would certainly expect that in 2014 you would have only 8 observations (four each in 2015 and 2016, but nothing for 2017) and in 2016 you would have none (because there is no data for 2017, 2018, or 2019).

[Jennifer Xie]

Thanks, now i get it. I used the wrong interval. I want to regress for 2013 by using 2013, 2014 and 2015 observations. So in my case, I should use inverval（year 0 2）? Please confirm if i understand correctly.

[Clyde Schechter]

Yes, that is correct.