Firm correlation (covariance) measure based on set of countries

[Navruz Khotamov]

Dear all,

I have firm-level data for multinationals. My data includes shares of sales for each firm in a specific host country and the growth rates of demand in that host country.

My data looks like this: (w_XXX is the sales share, and g_XXX is the growth rate).

* Example generated by -dataex-. For more info, type help dataex
clear
input long firm_id float(year w_CHN w_USA g_CHN g_USA)
10000016 2017          . .18852627         . .027771465
10000016 2018          .  .2161741         . .030423343
10000065 2010 .010309427         . .12010965          .
10000065 2011  .01036901         . .07751633          .
10000065 2012  .01096339         . .07946409          .
end

I am trying to construct diversification measures for each firm based on the set of countries they operate in weighted by the sales share in that country using the cross-country demand growth correlations. Ideally, the higher this measure the more correlated the shocks for the firm and hence the less diversified the firm. Each firm operates in many countries ranging from 4 to 70 countries, so the firm-specific measures should be based on the cross-country correlation of demand shocks in the countries where the firm operates weighted by the sales share.
I would very much appreciate any guidance on how this is possible to execute in Stata.

Thank you very much in advance.