Use past quarter average of data to regress with next quarter data.

[Jonathan Back]

Hello all,

I am a newbie to STATA and struggling with a peculiar problem. Hoping for some insight from you all.

I am looking to see the effect of past 3 quarter average of Profit on Share price

I want to do a simple regression of:

Y = a + b x + e

Y is Profit and X is share price and are values we have from data.

But I want to take the past 3 simple average of y and regress it with next period value of x. if we are in time t, Y is past 3 quarter profit (t-3+ t-2+t-1)/3 and x is value of x in time t i.e. current quarter stock price.

I want to do this for all the stocks in my datasheet. The data is in rows for each quarter (kindly see below) for each particular stock.

My data is in the following format: The stock id and data run across sequentially.
So I want to regress average profit from quarter (Q1+Q2+Q3)/3 on Share price in Q4.

Stock_ID	Date	Fiscal_Qtr	Profit_Y	Price_X
1091	20170228	2017Q1	14.4	34.42
1091	20170531	2017Q2	16.3	34.94
1091	20170831	2017Q3	11	36.06
1091	20171130	2017Q4	13.3	41.58
1256	20170228	2017Q1	480	36.6
1256	20170531	2017Q2	863	42.96
1256	20170831	2017Q3	942	35.48
1256	20171130	2017Q4	597	53.63
2891	20170228	2017Q1	3.021	16.59
2891	20170531	2017Q2	4.493	15.59
2891	20170831	2017Q3	3.703	13.4
2891	20171130	2017Q4	1.86	15.54

Thanks for your attention,
Jonathan

[Nick Cox]

Please read all of the FAQ Advice at https://www.statalist.org/forums/help Being new here is fine, but it does imply even more need to read and follow all the advice given, including especially giving a data example that is explicit about your variables.

[Jonathan Back]

Thanks Nick, Firstly now I know to address the software as Stata.

Please find example of what I am trying to do:

For Stock_ID 1091:

Y = Average of past 3 quarter Profit_Y i.e. value from Date 20170228, 20170531 and 20170831
Y = (14.4+16.3+11)/3
X = current quarter value of Price_x i.e. 41.58 (from Date 20171130)
Y = a + b x + e

I am interested in saving this regression value of b with 'Date' field of X variable used (20171130) and Stock_ID (1091 in this example).

Can I do a rolling regression so it stores all values of b for each subsequent run as my original dataset has a long time line of quarterly data for multiple stocks.

I want to then do the same for the next Stock_ID 1256 and so on for all the stocks in my dataset.

Thanks,
J

Dataset:

 
Stock_ID
Date
Fiscal_Qtr
Profit_Y
Price_X

1091
20170228
2017Q1
14.4
34.42

1091
20170531
2017Q2
16.3
34.94

1091
20170831
2017Q3
11
36.06

1091
20171130
2017Q4
13.3
41.58

1256
20170228
2017Q1
480
36.6

1256
20170531
2017Q2
863
42.96

1256
20170831
2017Q3
942
35.48

1256
20171130
2017Q4
597
53.63

2891
20170228
2017Q1
3.021
16.59

2891
20170531
2017Q2
4.493
15.59

2891
20170831
2017Q3
3.703
13.4

2891
20171130
2017Q4
1.86
15.54

[Phil Bromiley]

First, you need to xtset your data for stock_id and Fiscal_qtr. Then Stata can easily do lags for you. You might look at rangestat or rolling - they might do what you want.

[David Benson]

You might also try asreg or asrol (SSC install asrol) written by Attaullah Shah

You might also take a look at posts here and here

Also, it would be *super* helpful if you use Stata's dataex command to share data here. I created a YouTube video on using dataex here. (I made it too long, feel free to watch at 2x speed, and the 1st six minutes are probably all you need )

dataex stock_id date_old fiscal_qtr profit_y price_x   // data shared via  -dataex-. To install: ssc install dataex
clear
input int stock_id long date_old str6 fiscal_qtr float(profit_y price_x)
1091 20170228 "2017Q1"  14.4 34.42
1091 20170531 "2017Q2"  16.3 34.94
1091 20170831 "2017Q3"    11 36.06
1091 20171130 "2017Q4"  13.3 41.58
1256 20170228 "2017Q1"   480  36.6
1256 20170531 "2017Q2"   863 42.96
1256 20170831 "2017Q3"   942 35.48
1256 20171130 "2017Q4"   597 53.63
2891 20170228 "2017Q1" 3.021 16.59
2891 20170531 "2017Q2" 4.493 15.59
2891 20170831 "2017Q3" 3.703  13.4
2891 20171130 "2017Q4"  1.86 15.54
end

* I didn't know if your dates were already in Stata format (and if I paste them in from your table they are not)
tostring date_old, gen(date_string)
gen date = date( date_string, "YMD")
format date %td
gen qtr = qofd( date)
format qtr %tq
sort stock_id date

* Create the prior 3-qtr average using subscripts
bysort stock_id (date): gen avg_profit3 = (profit_y[_n-1] + profit_y[_n-2] + profit_y[_n-3]) / 3

* Create the prior 3-qtr average using xtset then lags
xtset stock_id qtr
gen lag_profit3 = (l.profit_y + l2.profit_y + l3.profit_y) / 3   /* That's the lowercase letter "L"  */

. list stock_id date qtr profit_y price_x avg_profit3 lag_profit3 , noobs sepby(stock_id) abbrev(12)

  +--------------------------------------------------------------------------------+
  | stock_id        date      qtr   profit_y   price_x   avg_profit3   lag_profit3 |
  |--------------------------------------------------------------------------------|
  |     1091   28feb2017   2017q1       14.4     34.42             .             . |
  |     1091   31may2017   2017q2       16.3     34.94             .             . |
  |     1091   31aug2017   2017q3         11     36.06             .             . |
  |     1091   30nov2017   2017q4       13.3     41.58          13.9          13.9 |
  |--------------------------------------------------------------------------------|
  |     1256   28feb2017   2017q1        480      36.6             .             . |
  |     1256   31may2017   2017q2        863     42.96             .             . |
  |     1256   31aug2017   2017q3        942     35.48             .             . |
  |     1256   30nov2017   2017q4        597     53.63      761.6667      761.6667 |
  |--------------------------------------------------------------------------------|
  |     2891   28feb2017   2017q1      3.021     16.59             .             . |
  |     2891   31may2017   2017q2      4.493     15.59             .             . |
  |     2891   31aug2017   2017q3      3.703      13.4             .             . |
  |     2891   30nov2017   2017q4       1.86     15.54         3.739         3.739 |
  +--------------------------------------------------------------------------------+

[River Huang]

Please "ssc install rangestat" first, and try

gen yq = quarterly(fiscal_qtr, "YQ")
format yq %tq
rangestat (mean) profit_y (count) profit_y, interval(yq -3 -1) by(stock_id)
replace profit_y_mean = . if profit_y_count < 3

[Jonathan Back]

Thanks a lot Phil (#4) I am exploring rangestat.

Thanks David (#5) for your pointer on dataex and your video. It was clear and I have now downloaded dataex and asrol and exploring the asrol features. Also thanks for your code. It is really helpful. I am a newbie to State (coming from a C++ background) some of the syntax is so unique that it catches me off guard. Your patience and help is really appreciated. I have been pouring over the Stata manuals to also explore but working out the problem seems to be the best way to learn it.

Thanks River (#6) range stat is also a good method to learn and will be really useful to me. thanks for your code. it was really useful.

I really appreciate the patience and help of everyone here. I hope soon, I will be able to contribute and help newbies here and do my part.