Hello,

i need the industry concentration (Herfindahl Index), calculated as the sum of squared market shares (based on annual sales (WC01001)) in the firm's industry sector (SIC-Code) per year. The index is between 0 (perfect competition) and 10.000 (monopoly) since market shares in percentage are multiplied by 100.


How can I get the herfindahl index for each year?

I have the following sheet:
isin company_name year WC01001 SIC_Code
BSP951331318 UNITED INTERNATIONAL 2014 1757001 1
BSP951331318 UNITED INTERNATIONAL 2015 1758568 1
BSP951331318 UNITED INTERNATIONAL 2016 1998846 1
CH0136071542 Cavotec SA 2015 2167291 3
CH0136071542 Cavotec SA 2016 2002564 3
CH0136071542 Cavotec SA 2017 2049372 3
CH0256424794 Oriflame Cosmetics S.A 2015 11307275 5
CH0256424794 Oriflame Cosmetics S.A 2016 11828624 5
CH0256424794 Oriflame Cosmetics S.A 2017 13154648 5
DK0010006329 SANTA FE GROUP A/S 2014 2520000 12
DK0010006329 SANTA FE GROUP A/S 2015 2786839 12
DK0010006329 SANTA FE GROUP A/S 2016 2520217 12
DK0010008028 F E BORDING A/S 2014 621147 3
DK0010008028 F E BORDING A/S 2015 610803 3
DK0010008028 F E BORDING A/S 2016 627518 3
DK0010025113 ARKIL HOLDING A/S 2014 2871394 12
DK0010025113 ARKIL HOLDING A/S 2015 3346768 12
DK0010025113 ARKIL HOLDING A/S 2016 3017006 12
DK0010027671 NTR HOLDING AS 2014 89500 2
DK0010027671 NTR HOLDING AS 2015 89500 2
DK0010027671 NTR HOLDING AS 2016 80100 2
DK0010125848 RIAS A/S 2014 279346 5
DK0010125848 RIAS A/S 2015 265806 5
DK0010125848 RIAS A/S 2016 266244 5
DK0010128008 SILKEBORG IF INVEST 2014 59363 12
DK0010128008 SILKEBORG IF INVEST 2015 61597 12
DK0010128008 SILKEBORG IF INVEST 2016 61183 12
DK0010129089 BRD KLEE A/S 2014 154191 9
DK0010129089 BRD KLEE A/S 2015 157089 9
DK0010129089 BRD KLEE A/S 2016 174743 9
DK0010181759 CARLSBERG A/S 2014 64506000 1
DK0010181759 CARLSBERG A/S 2015 65354000 1
DK0010181759 CARLSBERG A/S 2016 62614000 1
DK0010201102 KOEBENHAVNS 2014 3867500 12
DK0010201102 KOEBENHAVNS 2015 4061900 12
DK0010201102 KOEBENHAVNS 2016 4421900 12
DK0010212224 Intermail AS 2014 416016 3
DK0010212224 Intermail AS 2015 366955 3
DK0010212224 Intermail AS 2016 330700 3