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