The Largest Public Companies and their Investors graph uses the following MySQL tables: edgar.market_cap_two2, website.allpubcomps_new, crown.13f_shares, and adv_13f_match.thirteenf_groups. The steps taken are to find the top shareholders in the 50 largest public companies in the United States.

edgar.market_cap_two2 is the DataLab’s MySQL table that has the market cap, share price, and industry for 19000+ companies over a 12 year span.

website.allpubcomps_new is the DataLab’s MySQL table that has general information on public companies.

crown.13f_shares is the DataLab’s MySQL table that has the securities of institutional investment managers containing equity assets under management (AUM) of at least $100 million in value (Form 13F filers). This data describes how the manager exercises investment discretion, the name and class of the security, the CUSIP number, the number of shares as of the end of the calendar quarter for which the report is filed, and the total market value.

adv_13f_match.thirteenf_groups is the DataLab’s MySQL table that has general information on the Form 13F filers (the connections among 13F filers, their names, phone numbers, and addresses).

Below we will show our process on how we use the Form 13F data to calculate how much stake shareholders have in the 50 largest public companies:

CREATE TABLE derek.companies_shareholders
SELECT   issuer_cik,
         crsp_issuer_name AS issuer,
         cshoq            AS total_shares,
         temptableb.cik   AS investor_cik,
         Sum(shares)      AS investor_shares_owned,
         Sum(value)       AS investor_shares_value
FROM     (
                  SELECT   industry,
                           market_cap_two2.sic,
                           market_cap AS mktcap,
                           market_cap_two2.cik,
                           cshoq
                  FROM     edgar.market_cap_two2,
                           website.allpubcomps_new
                  WHERE    market_cap_two2.cik = allpubcomps_new.cik
                  AND      d = "2014-12-31"
                  AND      market_cap IS NOT NULL
                  AND      prccm < 3000
                  ORDER BY market_cap DESC
                  LIMIT    60) AS temptablea,
         (
                SELECT *
                FROM   crown.13f_shares
                WHERE  callput = 1
                AND    period = "2014-12-31") AS temptableb
WHERE    temptablea.cik = issuer_cik
GROUP BY issuer_cik,
         temptableb.cik
ORDER BY mktcap DESC,
         investor_shares_owned DESC;

derek.companies_shareholders is the DataLab’s MySQL table which is first composed of the merging between edgar.market_cap_two2 and website.allpubcomps_new to give us the market cap, shares outstanding, and share price for the 60 companies with the largest market caps in the fourth quarter of 2014. Then, we merge this data with crown.13f_shares which will contain information about the securities that shareholders have on these 60 companies.

Below we calculate the percent ownership that each shareholder has:

ALTER TABLE derek.companies_shareholders
  ADD percent_shares_owned DECIMAL(42, 5);

UPDATE derek.companies_shareholders
SET    percent_shares_owned = investor_shares_owned * 100;

UPDATE derek.companies_shareholders
SET    percent_shares_owned = percent_shares_owned / total_shares;

and rank the top 20 shareholders for each of the public companies:

SET @num := 0, @type := '';

CREATE TABLE derek.topissuers_investors
  SELECT issuer,
         issuer_cik,
         investor_cik,
         row_number
  FROM   (SELECT *,
                 @num := IF(@type = issuer, @num + 1, 1) AS row_number,
                 @type := issuer                         AS dummy
          FROM   derek.companies_shareholders
          WHERE  issuer IS NOT NULL) AS tmp_table
  WHERE  row_number <= 20;

and get the names of the investors:

ALTER TABLE derek.topissuers_investors
  ADD company_name VARCHAR(255) after issuer_cik;

ALTER TABLE derek.topissuers_investors
  ADD group_id INT(11) after investor_cik;

ALTER TABLE derek.topissuers_investors
  ADD INDEX (investor_cik);

UPDATE derek.topissuers_investors AS temptablea
       JOIN adv_13f_match.thirteenf_groups AS temptableb
         ON investor_cik = cik
SET    temptablea.company_name = temptableb.company_name,
       temptablea.group_id = temptableb.group_id;

and remove the public companies that are also institutional investment managers:

CREATE TABLE derek.tempciks
  SELECT issuer_cik
  FROM   derek.topissuers_investors
  WHERE  issuer_cik IN (SELECT investor_cik
                        FROM   derek.topissuers_investors)
  GROUP  BY issuer_cik;

DELETE FROM derek.topissuers_investors
WHERE  issuer_cik IN (SELECT *
                      FROM   derek.tempciks);

DROP TABLE derek.tempciks;

Now that we have ranked the investors that have the largest stake in the top 50 public companies, we can show some interesting data.

The shareholders at the center of the corporate governance world are the usual suspects: Vanguard, State Street, BNY Mellon, etc. who hold major shares in large public companies.