Open:FactSet Forum

Discovering hidden comps using industry revenue data

alternative-data
mergers
factset

(Christian Cifelli) #1

Merck and Co. (factset_entity_id = ‘000PQC-E’) announced Friday that it will buy privately held French company Antelliq Group for about 2.1 billion euros ($2.37 billion) to bolster its fast-growing animal health business. Antelliq Group makes digital identification products for livestock.

From FactSet’s Standard Datafeed, RBICS with Revenue, we can see that Merck has generated almost 10% of their revenue from their Veterinary Pharmaceuticals business.

SQL Query
  SELECT factset_entity_id,
         period_end_date,
	     bus_seg_name,
	     revenue_pct,
	     si.l6_id,
  	     rs.l6_name
    FROM rbics_v1.rbics_bus_seg_report AS sr 
    JOIN rbics_v1.rbics_bus_seg_item AS si
      ON si.report_id = sr.report_id
    JOIN rbics_v1.rbics_structure AS rs 
      ON rs.l6_id = si.l6_id 
   WHERE factset_entity_id = '000PQC-E'
     AND bus_seg_name like '%animal%' 
     AND sr.end_date is NULL
ORDER BY period_end_date desc

The Veterinary Pharmaceuticals sub-sector includes companies that manufacture veterinary pharmaceuticals and vaccines for the prevention and treatment of veterinary diseases.

Using this RBICS sub-sector, we can take this quick analysis a step to better understand who else generates revenue from this line of business to identify potential competitors.

SQL Query
  SELECT sr.factset_entity_id,se.entity_proper_name,eriod_end_date,si.l6_id,rs.l6_name,sum(revenue_pct) as l6_revenue_pct
    FROM rbics_v1.rbics_bus_seg_report AS sr 
    JOIN rbics_v1.rbics_bus_seg_item AS si
      ON si.report_id = sr.report_id
    JOIN rbics_v1.rbics_structure AS rs 
      ON rs.l6_id = si.l6_id 
    JOIN sym_v1.sym_entity AS se
      ON se.factset_entity_id = sr.factset_entity_id
	JOIN ( SELECT factset_entity_id, max(period_end_date) as max_date
	         FROM rbics_v1.rbics_bus_seg_report AS sr  
		     JOIN rbics_v1.rbics_bus_seg_item AS si
		       ON si.report_id = sr.report_id
		     JOIN rbics_v1.rbics_structure AS rs 
		       ON rs.l6_id = si.l6_id 
	        WHERE rs.l6_id = '351520301015' 
		 GROUP BY sr.factset_entity_id) AS a
	  ON a.factset_entity_id = sr.factset_entity_id
	 AND a.max_date = sr.period_end_date
   WHERE rs.l6_id = '351520301015' 
     AND sr.end_date is NULL
	 AND si.end_date is NULL
	 AND rs.end_date is NULL
	 AND period_end_date >= '2016-12-31'
GROUP BY sr.factset_entity_id,entity_proper_name,period_end_date,si.l6_id,l6_name
ORDER BY entity_proper_name asc, period_end_date desc