FFIEC 031 – Call Report ontology


The FFIEC Call Report Ontology is an RDF/OWL representation of Form FFIEC 031, which is used for banks with both domestic and foreign offices.

For banks in the U.S., one of the key reports required to be filed is the quarterly Consolidated Report of Condition and Income, generally referred to as the call report or RC report.

This article describes the ontology classes and how to query the FFIEC data set with SPARQL.


The Financial Regulation set of ontology modules started out as an operational ontology for Hedge Fund compliance. We had a need to directly tie encoded rules to their requirement specified for Investment Companies & Advisers in the Code of Federal Regulations (CFC) and United States Code (USC) paragraphs. The CF/USC class structure is generic for all laws and regulations. So, we extracted Financial Regulation Ontology for reuse in all Financial Industries. The operational ontologies for investment management have RDF/OWL versions of forms ADV (advisers) and PF (private funds). People asked for a Banking example and we now provide for FFIEC 031.

Ontology XBRL load and generation

Form FFIEC 031 Call Report

This report collects basic financial data from commercial banks in the form of a balance sheet, an income statement, and supporting schedules. The Report of Condition schedules provide details on assets, liabilities, and capital accounts. The Report of Income schedules provide details on income and expenses.

This report is a primary source of financial data used for the supervision and regulation of banks and is used as an editing benchmark for many other reports. (Federal Reserve). The Federal Deposit Insurance Corporation (FDIC) audits filed call reports. The Federal Financial Institutions Examination Council (FFIEC) is the formal interagency body. The choice for FFIEC 031 as a bank example is opportunistic because the FFIEC publishes forms, consolidated reports, and individual institution records in XBRL, XSD, and XML. We are familiar with the requirements of Comprehensive Capital Analysis and Review (CCAR) work. FFIEC 031 values feed into FFIEC 101, the Regulatory Capital Reporting for Institutions Subject to the Advanced Capital Adequacy Framework, and must reconcile with the Federal Reserve FR-Y-9C, Treasury TIC reports, and other filings.

The Central Data Repository on the FFIEC website provides downloads for the taxonomy and data for individual institutions:

FFIEC website – download taxonomy
  • Taxonomy – a Zip file with XSD schema for FFIEC extensions to XBRL instance, linkbase, versioning and the Call Report Micro Data Reference Manual (MDRM) elements.
  • Individual Institutions
    An XBRL instance file for the selected bank period and report.
    An Excel worksheet of sections, line numbers, descriptions, and reported values for human consumption
FFIEC website – download individual institution report

Proof of Concept

The concept of semantic processing of forms is that information can be (better) represented in Ontology Web Language (OWL) than conventional syntax only XML. We reverse engineer the taxonomy into a set of OWL (TTL turtle notation) files. The filed XBRL provides RDF instances for the ontology classes. Having classes and instances, we can browse the information and run SPARQL queries.

The proof of the concept is to reproduce the FFIEC Excel worksheet with a SPARQL query in the individual institution filing. We compare the original FFIEC worksheet with the Excel exported from the ontology tool.

Ontology files and classes

A set of classes in the form directory represents the FFIEC taxonomy. Please refer to the Financial Regulation Ontology Tutorial for:

  1. Introduction to OWL and set-by-step instructions for Ontology Editor, Protégé, and Query tools. The chapter also covers the domain reference ontologies:
    FIBO – Financial Industry Business Ontology
    LKIF – Legal Knowledge Interchange Format
  2. Loading laws and regulations XML into the ontology.

This prototype ontology of the Call Report is not a re-design. The structure closely resembles the XBRL/XML original because, in the future, we also want to generate filings from the ontology and export them into the XBRL instance files. Compared to CFR and USC, the FFIEC 031 corresponds to RDF staging, not the target legal ontology. (e.g. CFR-2015-title12-vol2-part217.ttl not FRO_CFR_Title_12_Part_217.ttl)

Financial Regulation Ontology Logical Integration Model

FFIEC extends the XBRL structure for reports, schedule line items, and columns. The linkbase classes hold instances to store formulas and connect them to items.

FFIEC 031 ontology classes

The MDRM is represented as a long list of element classes. The reported values are instances of the MDRM class. We only show one filing (Deutsche Bank Trust Americas Corp) here.

FFIEC MDRM items become ontology classes

XBRL classes are populated with instances to represent the structure of the form and reports.

FFIEC instantiation of XBRL schema classes

Querying instance data

Ontology tools TopBraid Composer and the open source Protégé facilitate SPARQL queries of the loaded ontology. Just follow the instructions of the FRO tutorial chapter 1 and use Data_and_Taxonomy.ttl to load the ontology online. Note that there are no semantic enhancements at this point. All joins are simple text label comparisons as in the original source.

The query in the appendix and here for download reproduces the FFIEC spreadsheet for an individual bank. The query directory has the result set Excel for the FFIEC report as well as an expanded version (SELECT *) that contains all resources joined.

Here are the FFIEC spreadsheet and the Ontology result set in Excel side by side.

FFIEC institution Excel report matches Ontology query results

Conclusion and Outlook

Bank Regulation Ontology has reproduced the FFIEC dataset for an individual bank DBTCA.

The next steps for 2017 are

  1. Enhance the raw import with semantics. First, we add object properties directly linking concepts, items, link base, formulas, and presentation. This will replace the string comparisons in the query with type-controlled relationships. Then, we can also depict the graph of connected FFIEC classes and individuals.
  2. Populate LKIF and FIBO
    Load the taxonomy into LKIF  This is the same as we did for a real ontology model of USC and CFR.
    Load the report financial information into FIBO classes. The Fund Ontology already has an example of FIBO populated with Investment Advisers from the Securities and Exchange Commission form ADV (advisers) compilation report.
  3. This is the reverse direction of step two: we populate the Call Report classes with financial information from the FIBO instance.


Financial Regulation Ontology Tutorial (online PowerPoint and softcopy):

FFIEC Call Report Modernization Project – CDR Interchange Specification Version 1.02
(An excellent introduction to the XBRL report structure)

IFRS and XBRL: How to improve Business Reporting through Technology and Object Tracking
Kurt Ramin, Cornelis Reiman
Wiley, 2013


SPARQL query on the FFIEC 031 ontology

# The Call Report query reproduces the Excel single Financial Institution report (e.g. Call_Cert623_093016.SDF)
# form the raw XBRL/XML ontology import. The result only contains columns with a value (no redacted or derived items). 
SELECT  ?Call_Date ?Bank_RSSD_Identifier ?MDRM ?Value ?Short_Definition ?Call_Schedule ?Line_Number
# The call date and RSSD are global identifiers of the file.
   	?end_date_inst a br-call-db:endDate .
	?end_date_inst composite:child ?end_date_node .
	?end_date_node sxml:text ?Call_Date .
	?ident a br-call-db:identifier .
	?ident composite:child ?ident_node .
	?ident_node sxml:text ?Bank_RSSD_Identifier .
# We start top-down with the schedules. Add a line here to retrieve a particular schedule only.
	?schedule a ffiec-cr-v129:schedule .
	?schedule composite:child ?schedule_node .
	?schedule_node a sxml:TextNode .
	?schedule_node sxml:text ?Call_Schedule .
# The schedule is composite child of the Linkbase reference.
	?reference  composite:child ?schedule .
	?reference a xbrl-linkbase:reference .
#  From here we navigate to line and column and report name
	?reference  composite:child ?line .
	?line a ffiec-cr-v129:line .
	?line composite:child ?line_node .
	?line_node a sxml:TextNode .
	?line_node sxml:text ?Line_Number .
	?reference  composite:child ?column .
	?column a ffiec-cr-v129:column .
	?column composite:child ?column_heading .
	?reference  composite:child ?report .
	?report a ffiec-cr-v129:report .
	?report composite:child ?report_name .
# The Label reference leads to the reference arcs 
	?reference xlink:label-reference ?label_ref .
	?ref_arc xlink:to-referenceArc ?label_ref .
	?ref_arc a xbrl-linkbase:referenceArc .
	?ref_arc xlink:arcrole-referenceArc "http://www.xbrl.org/2003/arcrole/concept-reference"^^xsd:string .
	?ref_arc xlink:from-referenceArc ?lb_label_loc .
# The linkbase label location leads to the Location of the column
	?lb_loc xlink:label-loc ?lb_label_loc .
	?lb_loc a xbrl-linkbase:Loc .
 	?loc_ref xbrl-linkbase:locRef ?lb_loc .
 	?loc_ref a xbrl-linkbase:PresentationLink .
  	?loc_ref xlink:role-presentationLink "http://www.ffiec.gov/xbrl/report/column"^^xsd:string .
# From the linkbase we navigate to the Presentation Arc and its reference
  	?pres_arc xlink:to-presentationArc ?lb_label_loc .
  	?pres_arc a xbrl-linkbase:PresentationArc .
	?pres_arc_ref xbrl-linkbase:presentationArcRef ?pres_arc .
   	?pres_arc_ref xlink:role-presentationLink "http://www.ffiec.gov/xbrl/report/line"^^xsd:string .
  	?pres_arc xlink:from-presentationArc ?from_arc .
  	?label_arc xlink:from-labelArc ?from_arc .
	?caption xbrl-linkbase:labelArcRef ?label_arc .
# The caption should be an extended reference of caption (leaving this out will retireve both caption and instruction text)
	ffiec-cr-v129-cap:r xbrl-linkbase:extendedRef ?caption .
   	?label_arc a xbrl-linkbase:LabelArc .
  	?label_arc xlink:to-labelArc ?label_label .
  	?lb_label xlink:label-label ?label_label .
 	?lb_label a xbrl-linkbase:Label .
   	?lb_label composite:child ?lb_label_node .
	?lb_label_node a sxml:TextNode .
	?lb_label_node sxml:text ?Short_Definition .
# Finally, we get the MDRM and its value
	BIND ( fn:substring (?lb_label_loc, 4) AS ?MDRM) .
	?lb_loc xlink:label-loc ?lb_label_loc .

	?ffiec_concept rdfs:label ?MDRM .
	?ffiec_concept rdfs:subClassOf xbrl_inst:Item .
	?concept_inst a ?ffiec_concept .
	?concept_inst dtype:value ?Value
ORDER BY ?Call_Schedule ?Line_Number

SPARQL result set

RCON999920160930Reporting dateENT1
RSSD9050623FDIC certificate numberENT2
RSSD9130New YorkCityENT4
RSSD9200NYState abbreviationENT5
RSSD922010005Zip codeENT6
RCON92248EWQ2UQKS07AKK8ANH81Legal Entity Identifier (LEI) (Report only if your institution already has an LEI.)ENT7
RCON220043281000000In domestic offices (sum of totals of columns A and C from Schedule RC-E, part I)RC13a
RCONB9931093000000Federal funds purchased in domestic officesRC14a
RCONB9870Federal funds sold in domestic officesRC3a
RCON00201575000000Cash items in process of collection and unposted debitsRCA1a
RCON00801000000Currency and coinRCA1b
RCON009025896000000Balances due from Federal Reserve BanksRCA4
RCON21650Lease financing receivables (net of unearned income)RCCI10
RCON21230LESS: Any unearned income on loans reflected in items 1-9 aboveRCCI11
RCON212213560000000Total loans and leases, net of unearned income (item 12, column A must equal Schedule RC, sum of items 4.a and 4.b)RCCI12
RCONF15801-4 family residential construction loansRCCI1a1
RCONF159185000000Other construction loans and all land development and other land loansRCCI1a2
RCON14200Secured by farmland (including farm residential and other improvements)RCCI1b
RCON1797515000000Revolving, open-end loans secured by 1-4 family residential properties and extended under lines of creditRCCI1c1
RCON53673187000000Secured by first liensRCCI1c2a
RCON536823000000Secured by junior liensRCCI1c2b
RCON1460935000000Secured by multifamily (5 or more) residential propertiesRCCI1d