organize 25 portfolios (the BE/ME) portfolios into* a (5*5) square matrix* based on the size

[Sarah Hussein]

The data consist of 878 monthly observations, but Betas have to be calculated for each portfolio in two sub-samples separately (1928:12-1963:6 and 1963:7-2001:12.) So for example, for portfolio FFS1BM1 I should get one cash-flow beta for years 1928:12-1963:6, another for the years 1963:7-2001:12 and discount-rate beta for the years 1963:7-2001:12 and different for years 1963:7-2001:12 (the same refers to any other portfolio in the data set; in total 45 portfolios). So for example for the first sub-sample I should get : 25 cash-flow Betas and 25 discount-rate Betas for 25 portfolios denoted with variables from FFS1BM1 to FFS5BM5 and 20 cash-flow Betas and 20 discount-rate Betas for 20 portfolios denoted with variables RISK1 to RISK20. (The same applies to the second subsample).
Here the formulas :

Beta _{i, CF} = Cov(r_i,t, N_CF,t) / Var (N_CF,t - N_DR,t) + Cov(r_i,t,N_CF,t-1) / Var (N_CF,t - N_DR,t)

Beta _{i, DR}= Cov(r_i,t, -N_DR,t) / Var (N_CF,t - N_DR,t) + Cov(r_i,t,-N_DR,t-1) / Var (N_CF,t - N_DR,t)

Cov and Var denote sample covariance and variance.
Subscript i denotes observation for particular month; r_{i, t} denotes return of the portfolio (e.g. FFS1BM1) in particular month , CF = cash-flow and DR =discount-rate.
In the numerator of the Beta _{i, DR} the covariance is calculated between portfolio return and good news about discount rates ( - N_{DR, t}) ( thats why each observation of N_DR enters formula with a minus).

The each Beta denominator ( variance of unexpected market return) : Var (N_CF,t - N_DR,t), can be equivalently written as Var ( R_Me - E_t-1R_Me)

The second part of each Beta formula ( marked in red) includes one lag of N_CF,t (N_{CF, t-1}) and equivalently for N_{DR, t.}
Adding one lag is motivated by the possibility that, especially during the early years of sample period not all assets were traded frequently and synchronously.

for that I used this code:
////////////////////////////////////////
////////Measuring CF and DF betas////////
///////////////////////////////////////

gen int mdate = ym(floor(Date/100), mod(Date, 100))
format mdate %tm
assert !missing(mdate)
//drop Date
order mdate, first

//calculte the betas for each portfolio in each year of the two subsamples
gen int year = year(dofm(mdate))

//GO TO LONG LAYOUT
rename (FFS* RISK*) return=
reshape long return, i(mdate) j(portfolio) string

capture program drop betas
program define betas
tsset mdate
gen delta = N_cf - N_dr
summ delta
local denom = r(Var)
corr return N_cf, cov
local cf1 = r(cov_12)
corr return L1.N_cf, cov
local cf2 = r(cov_12)
gen beta_cf = (`cf1' + `cf2')/`denom'
corr return N_dr, cov
local dr1 = r(cov_12)
corr return L1.N_dr, cov
local dr2 = r(cov_12)
gen beta_dr = (`dr1' + `dr2')/`denom'
drop delta
gen beta = beta_dr + beta_cf
exit
end
//ssc install runby
runby betas, by(year portfolio)
duplicates drop beta_cf beta_dr, force
*
I have 45 portfolios: 25 BEME portfolios and 20 Risk portfolios,
I had before a time series data, however after that code I have now a panel data,

SoI have to organize 25 portfolios (the BE/ME) portfolios into a (5*5) square matrix based on the size
Can anyone help me with that

[Sarah Hussein]

* Example generated by -dataex-. To install: ssc install dataex
clear
input long Date double(R_Me TY PE VS EtR_Me N_dr N_cf Rrf FFS1BM1 FFS1BM2 FFS1BM3 FFS1BM4 FFS1BM5 FFS2BM1 FFS2BM2 FFS2BM3 FFS2BM4 FFS2BM5 FFS3BM1 FFS3BM2 FFS3BM3 FFS3BM4 FFS3BM5 FFS4BM1 FFS4BM2 FFS4BM3 FFS4BM4 FFS4BM5 FFS5BM1 FFS5BM2 FFS5BM3 FFS5BM4 FFS5BM5 RISK1 RISK2 RISK3 RISK4 RISK5 RISK6 RISK7 RISK8 RISK9 RISK10 RISK11 RISK12 RISK13 RISK14 RISK15 RISK16 RISK17 RISK18 RISK19 RISK20)