EDGAR Fundamentals From 1994-2007 – The Hardest Problem I Ever Worked On

Hey,

I wanted to share the development journey I’ve lived through now so anyone who ever attempts that in the future has something to hang onto.

The problem:
Fundamentals for US companies from 1994-2007 are completely unstructured text and HTML files.

A structure to them (XBRL) was only enforced in 2009. So if you want fundamentals for the time before, there aren’t many options available.

Looking at these filings, it quickly becomes clear that parsing them in any reasonable fashion is a monster project that requires very ugly parsing code and an endless amount of cleaning, filtering, anomaly detection and layer upon layer of solutions for very specific formatting options.

Multiple formats (FDS, plain text, HTML tables) requires solving similar problems in multiple ways, because sometimes there just isn’t a generic solution for a problem.

But that didn’t stop me from attempting it as solving complex problems is what I like doing.

So I designed my own parser.

The basic approach in a nutshell:

1. Pre-processing – you cut filings into their sections and remove entire sections from it that lack any sort of language indicating consolidated statements; the better you do here, the easier everything gets downstream
2. Extract all statement candidates into grids and score them – Scoring goes up if certain accounting calculations add up, certain keywords are present, periods are present, etc. – this has grown over time and is designed to separate real consolidated statements from selected financial data tables (those are the ones you don’t want – but they are hard to cut out). Also important: This is where the different formats unify: HTML tables become grids, text tables become similar grids, EX27 exhibits too etc.
   1. Detect periods – Detecting correctly which periods are present in a table (9M/6M/3M/FY/TTM/stubs from restructuring, etc.) is a very hard problem
   2. Detect consolidated columns vs. subsidiary columns (these columns you don’t want)
   3. Detect the multiplier (thousands, millions, etc.) – This is very important and complex to solve. Sometimes that information is in the table header, sometimes within the periods, sometimes below, sometimes completely absent. Cross checks with FDS blocks where the multiplier is often stated can help deriving it
   4. Retain name information – Turned out to be of utmost importance. Sometimes names are split across multiple lines; Sometimes the single name alone appears in multiple blocks across the table, so that label alone is not enough. You need to join it with the full headline chain based on indentation, headline rules like UPPERCASE, L E T T E R S E P A R A T I O N, headlines with colons:, etc. – if you don’t get precise here, your extracted fact names are not actionable and contain lots of false positives that will ruin the entire pipeline
   5. Deal with filer typos – Since this was all structured, it is not hard to imagine that a lot of filings contain typos: numbers using dots instead of commas, numbers having whitespaces in them, etc. Some of those you can automatically detect and fix. This is the right place to do this generically.
3. Merge grids – some filers split a single statement into multiple tables across pages – those need merging and I do this based on score proximity and period alignment
4. Run anomaly detections – I’ve created a list of different anomalies I check for. The idea is to check for certain patterns, and allow on failure to retain one fact, a complete grid row, a complete grid, or the entire extracted dataset of a filing. It depends on which check fails
   1. balance sheet identities
   2. income statement identities
   3. cash flow identities
   4. cross period identities
   5. EX27 exhibit identities
   6. numeric fact sanity checks
5. Pre-Normalization – This layer is all about transforming the hundreds of different names used across all filings into a single technical name like “revenue”; In my pipeline, this layer isn’t the real normalization layer. I retain a prefix for each fact indicating the source format it came from. Examples are “fds:is:revenue“, “html:is:revenue“, etc.; The layer runs on everything that was extracted and per Pre-normalized fact, it has multiple layers to decide: positive regex list, negative regex list, a decide() function that has access to the entire filing context (this is where custom checks are done like unit expectations, value sign expectations, etc.). It also contains a layer that can yield a match to another Pre-Normalized fact in certain conditions like for general-and-administrative-expenses and selling-general-and-administrative-expenses. After this step is done, my data is ready to go through the same statement normalization flow the XBRL data goes through without any special handling needed anymore.
6. Hand-Check and pin filings – Whenever I solve a complex problem for a filing, I pin the result as a permanent unit-test to see when new parser changes break it again. I’ve built up a set of over 100 hand-checked filings this way and it is still expanding daily. But this step has been fundamental to retain the quality you reach
7. Make scanning all filings fast – I’ve developed a way to locally parse and store the result of all ~360,000 filings and ~57,000,000 facts. Alongside the scan, I store deep run metrics that allow me to quickly check the data quality from one iteration to the next. One full run takes ~35 minutes.

I sell clean fundamental data for a living and really want to solve this problem once and for all at scale. After having gone through this, please all make sure that we never ever allow this to happen again XD. We must demand data like this to always be structured, because this data not being structured is almost the same as it not existing.

My expectation is that it will still need a good amount of testing even after all of these solutions are in place. There is just no way to ensure full data integrity for over 50 million facts extracted from unstructured data. That brings me to the last layer in place:

Manual overrides

This layer allows me to override all sorts of things for a single filing, or an entire filer (CIK). Things like the multiplier, certain fact names, fact values, blacklisting certain things, etc.
It is that layer that will come into action once the parser has reached a final stage. Every invalid fact or inconsistency after a release of the data can be addressed individually. And I can already tell you that some issues you will find in the filings simply aren’t solvable generically. As much as I want to, some issues simply lack any sort of signal for you to hang onto for a generic solution. Those go into this layer.

I’m really curious if anyone else has done this in the past. What were your approaches and layers? Did you manage to finish it? How do you ensure a certain level of data quality?

If you are interested in more deep details, just ask away.