Hi,

I want to investigate the relationship between daily stock returns and ESG score (sustainability score) over a 10 year period. The ESG data is yearly per company and the returns are daily returns.
To do this I made portfolios with the lowest 10% in the first portfolio and the second lowest 10% in the second portfolio etc. For these portfolios, I computed the value-weighted returns based on their market capitalization. To see whether the sustainable firms have better stock returns than unsustainable firm, the variable GMB (green minus brown) is made where the return of the lowest 10% is deducted from the top 10% portfolio. The stock returns are daily and the ESG score is yearly, so every year other companies are in the top and lowest 10% portfolios. However, when I use the last row of the code provided below, the GMB variable has the same value for the whole year, so it is not sorted per portfolio. Is there a way to solve this issue and get the difference in value-weighted stock returns from the stainable firms and the unsustainable firms (GMB) per year?



Code:
/*Portfolio sorting */
gen sort_variable = ESG
sort ESG
astile portfolio = sort_variable, nq(10) by(Year)

/* compute value weighted weights */
egen weight = sum(Marketcap), by(Year portfolio)
replace weight = Marketcap / weight

/* compute portfolio returns */
gen weighted_returns = weight * Return
egen portfolio_returns = sum(weighted_returns), by(Date portfolio)

/* compute average cross-sectional sorting variable by portfolio */
egen avg_cross_sort_variable = mean(sort_variable), by(Date portfolio)

/* top-minus-bottom portfolio */
egen top = mean(cond(portfolio == 10, portfolio_returns, .)), by(Date)
egen bottom = mean(cond(portfolio == 1, portfolio_returns, .)), by(Date)
gen GMB = top - bottom