Hi State Community,
I am trying to create a variable that represents the average of the prior 2 years of 'net trading', but have been unable to find advice online applicable to my dataset.

The dataset is a collection of trades for a class of investors from over two decades, with roughly 11 million observations.
Multiple trades by the same investor in the same company may be made in a single day. Therefore the dataset is abundant with repeated time values, thereby rendering xtset or tsset unsuitable.

The variable firmweeknettrade represents the total amount of 'buys' minus the total amount of 'sells' for a given company (denoted by isin_code_equity) in a given week (tryearweek).

Furthermore, trades in a given company vary in frequency over time. e.g. a company may experience an intense period (day/week) of trading, but not experience any trades the following period.

I am looking for the average of weekly net trading of the 2 years prior for each company. E.g. an observation of a trade of of company INE008I01018, in week 41 of 2007 will require the average firmweeknettrade of the prior 104 weeks. As seen in the datex, there are multiple observations in each week, therefore a solution will have to be careful of not using a weighted average of the weeks, but rather the singular value of each of the prior 104 weeks.

Data ex provided below, for brevity only the transaction date, company code, value of trade, year & week identifier, and net trade for that week are included.

The datex shows a very liquid company, with most trades occurring in weeks 2597 through to 2600. But the observations extend for multiple years for this company.

Code:
dataex tran_date isin_code_equity valueinrs tryearweek firmweeknettrade if isin_code_equity=="INE008I01018"
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float tran_date str12 isin_code_equity double valueinrs float(tryearweek firmweeknettrade)
18218 "INE008I01018"    3.564e+08 2593          .
18242 "INE008I01018"  20705494.29 2597 1317844480
18242 "INE008I01018"      3999144 2597 1317844480
18242 "INE008I01018" 610547921.63 2597 1317844480
18242 "INE008I01018"    1803374.5 2597 1317844480
18242 "INE008I01018"      4095300 2597 1317844480
18242 "INE008I01018"   3491598.15 2597 1317844480
18242 "INE008I01018"   6694744.19 2597 1317844480
18242 "INE008I01018"     20068950 2597 1317844480
18242 "INE008I01018" 125525534.05 2597 1317844480
18242 "INE008I01018"   43292325.7 2597 1317844480
18242 "INE008I01018"    952228.29 2597 1317844480
18242 "INE008I01018"   19154022.1 2597 1317844480
18242 "INE008I01018"  11216558.76 2597 1317844480
18242 "INE008I01018"  26286521.75 2597 1317844480
18242 "INE008I01018"     10775718 2597 1317844480
18242 "INE008I01018"   8266393.08 2597 1317844480
18242 "INE008I01018"   3490551.75 2597 1317844480
18242 "INE008I01018"      1806750 2597 1317844480
18242 "INE008I01018"   6070768.87 2597 1317844480
18242 "INE008I01018"       767725 2597 1317844480
18242 "INE008I01018"   8742068.09 2597 1317844480
18242 "INE008I01018"   1393645.83 2597 1317844480
18242 "INE008I01018"  14052535.23 2597 1317844480
18242 "INE008I01018"      3573805 2597 1317844480
18242 "INE008I01018"  15691113.09 2597 1317844480
18242 "INE008I01018"     73144500 2597 1317844480
18242 "INE008I01018"  11940998.25 2597 1317844480
18242 "INE008I01018"    4589273.6 2597 1317844480
18242 "INE008I01018"   7434519.33 2597 1317844480
18242 "INE008I01018"    2672629.7 2597 1317844480
18242 "INE008I01018"     24858600 2597 1317844480
18242 "INE008I01018"  87203423.55 2597 1317844480
18242 "INE008I01018"   1832435.88 2597 1317844480
18242 "INE008I01018"   4301633.24 2597 1317844480
18242 "INE008I01018"   2304118.98 2597 1317844480
18242 "INE008I01018"   2423189.02 2597 1317844480
18242 "INE008I01018"      8623930 2597 1317844480
18242 "INE008I01018" 218433881.52 2597 1317844480
18242 "INE008I01018"  18622661.42 2597 1317844480
18242 "INE008I01018" 114296432.28 2597 1317844480
18242 "INE008I01018"   28095279.8 2597 1317844480
18242 "INE008I01018" 108442304.02 2597 1317844480
18242 "INE008I01018"    286378500 2597 1317844480
18245 "INE008I01018"   16222222.5 2597 1317844480
18245 "INE008I01018"   3779148.24 2597 1317844480
18245 "INE008I01018"      9017800 2597 1317844480
18245 "INE008I01018"   6505355.35 2597 1317844480
18245 "INE008I01018"   1370479.08 2597 1317844480
18245 "INE008I01018"  18398445.27 2597 1317844480
18245 "INE008I01018"  30763467.12 2597 1317844480
18245 "INE008I01018"  31120559.38 2597 1317844480
18245 "INE008I01018"     44159000 2597 1317844480
18245 "INE008I01018"     13499100 2597 1317844480
18245 "INE008I01018" 101971080.86 2597 1317844480
18246 "INE008I01018"    918859.99 2597 1317844480
18246 "INE008I01018"     16658792 2597 1317844480
18246 "INE008I01018"     10444640 2597 1317844480
18246 "INE008I01018"      4146432 2597 1317844480
18248 "INE008I01018"      5933900 2598  304525824
18248 "INE008I01018"     15264360 2598  304525824
18249 "INE008I01018"     42121980 2598  304525824
18249 "INE008I01018"     73713465 2598  304525824
18252 "INE008I01018"   10491537.5 2598  304525824
18252 "INE008I01018"     20983075 2598  304525824
18253 "INE008I01018"     42724130 2598  304525824
18253 "INE008I01018"   13527941.8 2598  304525824
18253 "INE008I01018"    868159.83 2598  304525824
18253 "INE008I01018"      4272413 2598  304525824
18253 "INE008I01018"  40568079.69 2598  304525824
18254 "INE008I01018"     14492928 2598  304525824
18254 "INE008I01018"  34182313.83 2598  304525824
18254 "INE008I01018"   9298474.54 2598  304525824
18254 "INE008I01018"       654621 2598  304525824
18254 "INE008I01018"  54598527.25 2598  304525824
18254 "INE008I01018"  58773481.14 2598  304525824
18254 "INE008I01018"   3394269.41 2598  304525824
18254 "INE008I01018"  10255721.95 2598  304525824
18255 "INE008I01018"      2222069 2599   86625520
18255 "INE008I01018"     22162500 2599   86625520
18255 "INE008I01018"     21924345 2599   86625520
18255 "INE008I01018"     21875000 2599   86625520
18255 "INE008I01018"     22212370 2599   86625520
18255 "INE008I01018"      8884560 2599   86625520
18261 "INE008I01018"  20434867.23 2599   86625520
18261 "INE008I01018"    2169306.5 2599   86625520
18261 "INE008I01018"     16110675 2599   86625520
18261 "INE008I01018"   3527519.37 2599   86625520
18261 "INE008I01018"    646656.52 2599   86625520
18261 "INE008I01018"     673033.8 2599   86625520
18262 "INE008I01018"     46714260 2599   86625520
18262 "INE008I01018"     16081450 2599   86625520
18262 "INE008I01018"     25262061 2599   86625520
18262 "INE008I01018"     46505740 2599   86625520
18266 "INE008I01018"      8584580 2600   46597848
18266 "INE008I01018"     25356906 2600   46597848
18268 "INE008I01018"     21721414 2600   46597848
18269 "INE008I01018"      7823734 2600   46597848
18269 "INE008I01018"  939167.4375 2600   46597848
18269 "INE008I01018"    2180485.5 2600   46597848
end
format %d tran_date
format %twCCYY-ww tryearweek
Any advice would be appreciated, as my best guess right now is to construct a forloop for the issue, but unsure how to proceed on that also.

I am happy to provide a more thorough explanation of anything about the data if needed.

Kind Regards,
Nick