In this article, we explain how formthirteen.com analyzes 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:
- A mismatch between the actual total value of holdings and the total value reported in the 13F report's header:
- 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":
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:
|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.
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].