Creating Lagged Variables for Last Month of Financial Year

[Scott McFaul]

Hi there,

I need to create lagged variables to run as part of a Fama-MacBeth regression, and asreg doesn't seem to like L.variable formats. So I am creating them individually.

I have financial data by permanent number (permno) and also date (1963m7 to 2018m12). Some of this information is annual data, matched for the specific company for all 12 months of each fiscal year. So CAPEX for permno 10001 is the same for all months of 1986 when the financial year ends in December, for example.

When I try to create one month (frequency is monthly, 1 period) lags, it will use the previous month, which is not what I want. I also can't use lags of 12 months, because that will not create a lag from the last year's financial statement data.

I need a command that says if the CAPEX value for a month (t) is different to the CAPEX value of the next month (t+1), use the value of CAPEX for month t as the lagged value for each of the next 12 months (t+1 through t+12), and then it'll repeat the process for the following financial year. I need to run this by permno too. The best I have come up with is: by permno: gen capex_lag=L.capex. Something using an if statement that checks for unique consecutive observations?

Thank you for your help.

Scott

[Clyde Schechter]

Please post back with example data, and use the -dataex- command to do that. Make sure your example includes a few years worth of data and covers some permnos that have fiscal years that do not coincide with calendar years. Finally, this is going to be very difficult, perhaps impossible, unless there is a variable in the data set that identifies the month in which each permno closes its fiscal year. If you have such a variable, be sure to include it in your example.

If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Scott McFaul]

Hi Clyde,

Thank you. I'm also not able to create a variable for the end of the year for some reason. I can create a lead variable, but it will not work when checking if they are different. This is what I want to create:

capx	capx_lead	end_year
1	1	0
1	1	0
1	2	1
2	2	0
2	2	0
2	2	0

Commands:
sort permno
by permno: gen capx_lead=capx[_n+1]
gen end_year=1 if capx!=capx_lead

But this last line does not work. Neither does capx-capx_lead==0 to get ones in the non-year ends, etc.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date str4 year str2 month double(permno vol ret shrout) float(price mktcap lnSize lnBTM) double capx float(capx_lead opex) double(cogs sale seq xsga) float(BTM period t gamble alcohol tobacco gun ffuel)
314 "1986" "03" 10028  868    .2222222238779068 12849   1.375 17667.375  9.779475  -2.696087   .03   .03  1.099761 1.325 1.674 1.192  .516  .06746899 1 273 0 0 0 0 1
315 "1986" "04" 10028 1094    .1818181872367859 12849   1.625 20879.625  9.946529  -2.863141   .03   .03  1.099761 1.325 1.674 1.192  .516  .05708915 1 274 0 0 0 0 1
316 "1986" "05" 10028 1939    .1538461595773697 12849   1.875 24091.875  10.08963  -3.006242   .03   .03  1.099761 1.325 1.674 1.192  .516  .04947726 1 275 0 0 0 0 1
317 "1986" "06" 10028  753  -.13333334028720856 14534   1.625  23617.75 10.069754  -2.986366   .03   .03  1.099761 1.325 1.674 1.192  .516  .05047052 1 276 0 0 0 0 1
318 "1986" "07" 10028 1651   -.5769230723381043 14534   .6875  9992.125  9.209553 -2.1261647   .03   .03  1.099761 1.325 1.674 1.192  .516  .11929394 1 277 0 0 0 0 1
319 "1986" "08" 10028 2411  -.04545454680919647 14534  .65625 9537.9375  9.163033 -2.0796447   .03   .03  1.099761 1.325 1.674 1.192  .516   .1249746 1 278 0 0 0 0 1
320 "1986" "09" 10028 1097   -.0476190485060215 14534    .625   9083.75  9.114243 -2.0308545   .03   .03  1.099761 1.325 1.674 1.192  .516  .13122334 1 279 0 0 0 0 1
321 "1986" "10" 10028  455   .10000000149011612 14534   .6875  9992.125  9.209553 -2.1261647   .03   .03  1.099761 1.325 1.674 1.192  .516  .11929394 1 280 0 0 0 0 1
322 "1986" "11" 10028  225  -.09090909361839294 14534    .625   9083.75  9.114243 -2.0308545   .03   .03  1.099761 1.325 1.674 1.192  .516  .13122334 1 281 0 0 0 0 1
323 "1986" "12" 10028  658  -.20000000298023224 14534      .5      7267  8.891099  -1.807711   .03 1.503  1.099761 1.325 1.674 1.192  .516  .16402917 1 282 0 0 0 0 1
324 "1987" "01" 10028  495                    0 14534      .5      7267  8.891099  -2.751214 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .06385028 1 283 0 0 0 0 1
325 "1987" "02" 10028  300                    0 14534      .5      7267  8.891099  -2.751214 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .06385028 1 284 0 0 0 0 1
326 "1987" "03" 10028 1128                 .375 15684   .6875  10782.75  9.285703 -3.1458185 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .04303169 1 285 0 0 0 0 1
327 "1987" "04" 10028  472    .3181818127632141 15684  .90625 14213.625  9.561956  -3.422072 1.503 1.503 1.0293719 7.715 8.103  .464  .626 .032644734 1 286 0 0 0 0 1
328 "1987" "05" 10028 1503                    0 15684  .90625 14213.625  9.561956  -3.422072 1.503 1.503 1.0293719 7.715 8.103  .464  .626 .032644734 1 287 0 0 0 0 1
329 "1987" "06" 10028 1991   .17241379618644714 16984  1.0625   18045.5  9.800652  -3.660767 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .02571278 1 288 0 0 0 0 1
330 "1987" "07" 10028  742  -.23529411852359772 16984   .8125   13799.5  9.532388  -3.392503 1.503 1.503 1.0293719 7.715 8.103  .464  .626 .033624407 1 289 0 0 0 0 1
331 "1987" "08" 10028 1213 -.057692307978868484 16984 .765625 13003.375  9.472964 -3.3330796 1.503 1.503 1.0293719 7.715 8.103  .464  .626 .035683043 1 290 0 0 0 0 1
332 "1987" "09" 10028 1699  -.22448979318141937 17204  .59375 10214.875    9.2316  -3.091716 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .04542395 1 291 0 0 0 0 1
333 "1987" "10" 10028  544  -.31578946113586426 17204  .40625  6989.125  8.852111  -2.712226 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .06638885 1 292 0 0 0 0 1
334 "1987" "11" 10028  260   .23076923191547394 17204      .5      8602   9.05975 -2.9198654 1.503 1.503 1.0293719 7.715 8.103  .464  .626  .05394094 1 293 0 0 0 0 1
335 "1987" "12" 10028  701                .0625 17204  .53125  9139.625  9.120375   -2.98049 1.503   .05 1.0293719 7.715 8.103  .464  .626  .05076795 1 294 0 0 0 0 1
336 "1988" "01" 10028  103  -.11764705926179886 17204  .46875  8064.375  8.995212  -1.853175   .05   .05 1.0293499 9.733 10.46 1.264 1.034  .15673874 1 295 0 0 0 0 1
337 "1988" "02" 10028  672   .06666667014360428 17204      .5      8602   9.05975 -1.9177134   .05   .05 1.0293499 9.733 10.46 1.264 1.034  .14694257 1 296 0 0 0 0 1
338 "1988" "03" 10028 1638               .21875 16404 .609375  9996.188  9.209959 -2.0679224   .05   .05 1.0293499 9.733 10.46 1.264 1.034  .12644821 1 297 0 0 0 0 1
339 "1988" "04" 10028 1036  .025641025975346565 16404    .625   10252.5  9.235277 -2.0932403   .05   .05 1.0293499 9.733 10.46 1.264 1.034    .123287 1 298 0 0 0 0 1
340 "1988" "05" 10028  276   .02500000037252903 16404 .640625 10508.813   9.25997 -2.1179328   .05   .05 1.0293499 9.733 10.46 1.264 1.034     .12028 1 299 0 0 0 0 1
341 "1988" "06" 10028  332 -.024390242993831635 16525    .625 10328.125  9.242626 -2.1005895   .05   .05 1.0293499 9.733 10.46 1.264 1.034  .12238427 1 300 0 0 0 0 1
342 "1988" "07" 10028  735   -.2750000059604645 16525 .453125  7487.891  8.921042  -1.779006   .05   .05 1.0293499 9.733 10.46 1.264 1.034   .1688059 1 301 0 0 0 0 1
343 "1988" "08" 10028 1685  -.31034350395202637  2754   1.875   5163.75  8.549418 -1.4073818   .05   .05 1.0293499 9.733 10.46 1.264 1.034  .24478334 1 302 0 0 0 0 1
end
format %tm date

I cannot find any permnos with financial years ending in months other than December. They were present in a prior dataset, and may be excluded from this dataset. I cannot say for sure, and cannot check all permnos.

Thank you.

Scott

[Scott McFaul]

Hi Clyde,

I have generated my end of financial year variable using: gen end_year=1 if float(capx)!=float(capx_lead). They needed to be specified as both float(), to the same degree of accuracy, for the "not equal" statement to work. I do indeed have financial years ending in all months of the year. I just need help creating the if statement to create lags across the financial year with the end of year data (i.e. when end_year=1). Thank you.

Scott

[Scott McFaul]

Hi Clyde,

I was able to create my lagged variables by:
xtset permno t
by permno: gen capx_lag=L.capx if L.end_year==1
by permno: replace capx_lag=L2.capx if L2.end_year==1

Then repeating for all 12 lags (L.12). Lagging the end_year variable to match the same lag as the capx value allowed for matching to 1 for each month of observations, and therefore the replacement of capx_lag occurred. I guess I solved it for myself

I'll leave it here in case it helps someone else.

Scott

[Nick Cox]

In #5 note that

by permno

does no harm but is not needed. Time series operators automatically are applied separately within each panel.

In #4 if all financial years end in December then

bysort permno year (month) : gen end_year = _n == _N 

would seem a more transparent method.

I don't know why you are holding year and month as string. Sooner or later you may need to destring them.

[Scott McFaul]

Hi Nick,

Thank you for your note regarding by permno. Not all financial years ended in December, but that is another way I could do it. year and month were used to construct date, they are not used in any future codes but there in case I need them, obsolete. Additionally, how do I rewrite the variables in the same format/font as you did in the post above?

Scott

[Nick Cox]

FAQ Advice #12 explains about CODE formatting.