Generating a complex variable

[Rafal Krol]

Dear Statalisters,
I kindly ask you for help in generating a variable. I know the concept of generating variables, and function in the STATA package, such as mean etc., yet still I struggle with creating my variable of interest. I would be really happy if you could help.

I want to generate a new variable (waalpha) that is a weighted average of previous x (in my case 18) lagged values of the variable "alphaCAPM", but also including the exponential decay I previously calculated for them. My data is a panel data of multiple funds over multiple time periods. Alphas are calculated for my ID "fund" variable. To get a better understanding of my new variable I include a screenshot of my word equation (also it is easier than writing the equation in the forum window):

S stands for lags, and T stands for months.
Thank you in advance for helping me solving my case.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double fund float month double alphaCAPM float lambda
1000001 432    -.002701373570297651 -.007588116
1000001 433   -.0025708643291764595 -.007588116
1000001 434   -.0018413712171586622 -.007588116
1000001 435   -.0009240308657975996 -.007588116
1000001 436   -.0005416979007764056 -.007588116
1000001 437   -.0005719457268058992 -.007588116
1000001 438  -.00002746413095803049 -.007588116
1000001 439   -.0011871287524594515 -.007588116
1000001 440   -.0009586046866395265 -.007588116
1000001 441   -.0014385683142978025 -.007588116
1000001 442   -.0014901882342573062 -.007588116
1000001 443    -.001080221744722807 -.007588116
1000001 444   -.0005872822154177493 -.007588116
1000001 445   -.0017723203740694603 -.007588116
1000001 446    -.002554501163029965 -.007588116
1000001 447   -.0015473740529669128 -.007588116
1000001 448   -.0011939980131293673 -.007588116
1000001 449   -.0009654620551822497 -.007588116
1000001 450   -.0003828072799599326 -.007588116
1000001 451  -.00021131036787691918 -.007588116
1000001 452     .001772919145908462 -.007588116
1000001 453     .001922717187605713 -.007588116
1000001 454    .0017470698288023709 -.007588116
1000001 455    .0006499621644225836 -.007588116
1000001 456   .00018747957618198488 -.007588116
1000001 457   .00039080208029873553 -.007588116
1000001 458    .0010132756252694396 -.007588116
1000001 459    .0010152604866001808 -.007588116
1000001 460    .0013964474201855412 -.007588116
1000001 461 -.000045064871150612626 -.007588116
1000001 462   .00014795855285374554 -.007588116
1000001 463    -.002167172715683287 -.007588116
1000001 464   -.0044477989947047354 -.007588116
1000001 465    -.005332826767794981 -.007588116
1000001 466    -.005890226437018531 -.007588116
1000001 467     -.00574278045074255 -.007588116
1000001 468    -.005833186473791196 -.007588116
1000001 469    -.006024727254887575 -.007588116
1000001 470    -.008008771350671049 -.007588116
1000001 471    -.008277096972068694 -.007588116
1000001 472     -.00733976613947749 -.007588116
1000001 473    -.006271265128074107 -.007588116
1000001 474    -.006058418717555853 -.007588116
1000001 475    -.005405638490585254 -.007588116
1000001 476   -.0058860133118758574 -.007588116
1000001 477    -.006952614353365515 -.007588116
1000001 478     -.00757354316886627 -.007588116
1000001 479     -.00715707548947456 -.007588116
1000001 480    -.007157270232592813 -.007588116
1000001 481    -.006723248065839205 -.007588116
1000001 482    -.005797872772351394 -.007588116
1000001 483    -.006769661332139928 -.007588116
1000001 484    -.006518730223392388 -.007588116
1000001 485    -.006011414478228082 -.007588116
1000001 486     -.00502451772939131 -.007588116
1000001 487    -.006157928971972046 -.007588116
1000001 488    -.006091131001143321 -.007588116
1000001 489    -.005037749257197516 -.007588116
1000001 490    -.006174088731020292 -.007588116
1000001 491    -.007268233618972484 -.007588116
1000001 492    -.006526934214644872 -.007588116
1000001 493   -.0072871232064038805 -.007588116
1000001 494     -.00853546442575314 -.007588116
1000001 495     -.00913567858826602 -.007588116
1000001 496    -.009198987459079789 -.007588116
1000001 497    -.008805458784211066 -.007588116
1000001 498   -.0076656238368184755 -.007588116
1000001 499   -.0075383377387500065 -.007588116
1000001 500    -.007954146345337783 -.007588116
1000001 501    -.009432277530936901 -.007588116
1000001 502    -.007921344338794595 -.007588116
1000001 503     -.00730831586625454 -.007588116
1000001 504    -.007280499013873816 -.007588116
1000001 505    -.007601692095467731 -.007588116
1000001 506    -.007338229666688397 -.007588116
1000001 507    -.007400008946014738 -.007588116
1000001 508    -.005731113309093973 -.007588116
1000001 509    -.006098116764867867 -.007588116
1000001 510    -.005915732893490329 -.007588116
1000001 511    -.006066075023852369 -.007588116
1000001 512   -.0070838823051624115 -.007588116
1000001 513    -.005913646213949561 -.007588116
1000001 514    -.007568831328736323 -.007588116
1000001 515    -.006529699439278846 -.007588116
1000001 516    -.005690408285460886 -.007588116
1000001 517    -.005870369669622826 -.007588116
1000001 518    -.005262310218293396 -.007588116
1000001 519    -.005126225612184268 -.007588116
1000001 520    -.005484051325566173 -.007588116
1000001 521    -.004903400991317926 -.007588116
1000001 522    -.004770266438424701 -.007588116
1000001 523   -.0027207811672314446 -.007588116
1000001 524    -.001775878030256899 -.007588116
1000001 525   -.0015516293912698964 -.007588116
1000001 526   -.0002816828604423459 -.007588116
1000001 527     .001086736675345806 -.007588116
1000001 528   .00014758309488489374 -.007588116
1000001 529    .0011315038970520562 -.007588116
1000001 530    .0015815870734802802 -.007588116
1000001 531     .002390683877858531 -.007588116
end

Rafał

[Clyde Schechter]

Well, assuming that lambda is a constant within each fund and that there are no time-gaps in the data, you can do it this way:

by fund (lambda), sort: assert lambda[1] == lambda[_N]
gsort fund -month
by fund,  sort: gen weight = exp(-lambda *_n)

xtset fund month
assert r(gaps) == 0
by fund: gen running_denominator = sum(weight)
by fund: gen running_numerator = sum(weight*alphaCAPM)
gen wanted = (L1.running_numerator-L19.running_numerator)/(L1.running_denominator-L19.running_denominator)

Both of those assumptions are checked by the code and it will break if they are not met. If those assumptions are not met, the needed code will be substantially more complicated.

That said, I fear that either your formula is wrong, or your value of lambda is wrong, or you are mis-stating the problem. The formula you show, when used with a negative value of lambda, leads to values from the remote past to be weighted more heavily than values that are recent or current. Perhaps that is what is intended, but it seems strange: in most situations current values are weighted more heavily than those distant in time. For that to happen, you need the value of lambda here to be positive (or, equivalently, change the sign of lambda in the formula). Anyway, do check carefully on this matter before proceeding.

[Rafal Krol]

Thank you Clyde. I tried flipping the sign of lambda in the 3rd line, and the results are close to the same. Overall the code works! Thank you once again!