How to Analyze 13F Reports

In this article, we explain how formthirteen.com analyzes 13F reports.

In the previous article How to Fetch and Parse 13F Reports, we explained how formthirteen.com parses and extracts information from 13F reports.

If you have any questions regarding this process or want to share your tips and tricks please contact us at [email protected].

Step one: Cleaning the data

Many 13F reports contain errors and inconsistencies. These errors can be detected and sometimes fixed by cleaning the data.

The following is a list of some of the more common errors we regularly find in 13F reports:

  • Non-standard names for stocks, e.g., "WELLS FARGO & CO NEW"
  • Invalid CUSIPs, e.g., omitting leading zeros is a common error
  • A mismatch between the actual number of holdings and the number of holdings reported in the 13F report's header:
<tableEntryTotal>2</tableEntryTotal>
  • A mismatch between the actual total value of holdings and the total value reported in the 13F report's header:
<tableValueTotal>106582947</tableValueTotal>
  • Incorrect market value of a security, for example, not reporting the value in thousands.

Some of these errors can be fixed automatically, however many can only be fixed manually.

After the data has been cleaned, we need to store it for later retrieval and analysis.

Step two: Storing the data

To analyze the data in a 13F report we need an easy way of retrieving the data we have extracted. The solution we have opted for is to store the data in an SQL database. The SQL database allows us to retrieve and analyze the data efficiently using the SQL language. For example, we can calculate the sum of the reported market value of a holding for a specific quarter.

To store the data in the SQL database, we first need to create a relational data model that describes the data and the relationships between the entities contained within the data.

The data inside a 13F report is composed of three main entities:

  • The companies table contains information about the companies that file the reports, including the CIK number
  • The reports table holds all the 13F reports we download, including the URL and raw content
  • The holdings table contains all holdings, one row per reported security

To create these database tables we can use the following SQL script to create a database schema that allows us to store the data:

-- Companies that file 13F reports
CREATE TABLE companies (
  id serial PRIMARY KEY,
  name text NOT NULL,
  cik character varying(255) NOT NULL UNIQUE
);
-- 13F reports
CREATE TABLE reports (
  id serial PRIMARY KEY,
  company_id integer NOT NULL REFERENCES companies,
  accession_number character varying(255) NOT NULL UNIQUE,
  content text NOT NULL,
  quarter date NOT NULL,
  filing_date timestamp without time zone NOT NULL,
  amendment boolean,
  url text NOT NULL UNIQUE
);
-- Holdings in 13F reports
CREATE TABLE holdings (
  id serial PRIMARY KEY,
  name text NOT NULL,
  class text NOT NULL,
  report_id integer NOT NULL REFERENCES reports,
  company_id integer NOT NULL REFERENCES companies,
  cusip character varying(255),
  value numeric(20,2) NOT NULL,
  shares integer NOT NULL,
  shares_type character varying(255),
  options_type character varying(255),
  manager text,
  quarter date NOT NULL
);

This is not the exact schema we use. However, it gives you an idea of what you need to store 13F reports in a SQL database.

Step three: Handling amendments and restatements

A 13F report can be incomplete or contain errors as we pointed out earlier. In these cases, the company that filed the report can decide to file a new amended report.

To know whether the 13F report is an amendment or not you need to check if the isAmendment element in the 13F report's header has the value "true":

<isAmendment>true</isAmendment>

If the value of "isAmendment" is true, the second step is to check the value of the "amendmentType" element:

<amendmentInfo>
  <amendmentType>NEW HOLDINGS</amendmentType>
</amendmentInfo>

There are two types of amendments that companies can file:

Type Description
NEW HOLDINGS The previously filed report did not contain all holdings.
RESTATEMENT The previously reported holdings are invalid. This report contains all holdings for the reported quarter.

formthirteen.com handles a "NEW HOLDINGS" report in exactly the same way as a normal report. We consolidate all holdings from the original report and one or more reports containing amendments into one report using SQL statements.

If the amendment type is "RESTATEMENT", we start by deleting all previous holdings for the quarter.

Deletion of all holdings for a specific quarter and company can be done with, for example, this SQL statement:

delete from holdings where company_id = $1 and quarter = $2

Step four: Consolidating the data

To get a complete view of a company's quarterly holdings we consolidate one or more 13F reports filed for the same quarter into one view.

We use SQL queries to consolidate the data. For example, this query calculates the total value and amount of shares separately for each company, quarter, and security:

 SELECT
    cusip, -- group by cusip, class and options type
    class,
    options_type,
    sum(value) AS value, -- calculate the value of all shares
    sum(shares) AS shares, -- calculate the amount of all shares
    max(name) AS name -- take the longest reported name
  FROM holdings
  GROUP BY
    cusip, class, options_type -- merge multiple rows for one security into one
  WHERE
    quarter = $1 AND company_id = $2

The results from this query are used to generate a view of a company's holdings for a specific quarter by consolidating multiple reports and rows into one.

Now that we have a complete view of the quarterly holdings we can perform more complex analysis of the data.

Step five: Analyzing the data

The data we collect at formthirteen.com is used to calculate the following basic information per reported security:

  • Market value
  • Share count
  • Portfolio weighting

We also calculate the quarterly change between quarters.

For example, ~22% of Berkshire Hathaway's portfolio was invested in Kraft Heinz in Q2 2016 and the total value of this holding was ~29 billion USD. Berkshire Hathaway did not buy any new shares, however, both the value and portfolio weighting of the holding increased by ~12%.

In addition to basic information, formthirteen.com also calculates the following statistics:

  • Portfolio turnover. Daily Journal's portfolio turnover has quite consistently been 0.0%.
  • Portfolio size. In Q3 2017, Berkshire Hathaway's portfolio was valued at 162 billion USD.
  • Portfolio concentration. For example, Aquamarine Capital Management has on average reported 15 holdings.
  • Number of holdings. Horizon Kinetics reported 507 securities in Q3 2017.

formthirteen.com also has a section named strategies where we list the following information which is calculated for all portfolios combined:

  • High-conviction holdings. This list shows the average portfolio weighting across all portfolios.
  • Popular holdings. This list shows the most popular holdings across all portfolios.
  • Top buys. The top new securities that were not reported in the previous quarter.

Summary

In summary, these are the steps we perform here at formthirteen.com to analyze 13F reports:

  • Step one: Cleaning the data
  • Step two: Storing the data
  • Step three: Handling amendments and restatements
  • Step four: Consolidating the data
  • Step five: Analyzing the data

If you have any questions regarding this process or want to share your tips and tricks you can contact us at [email protected].