FFIEC 031 – Call Report ontology

Abstract

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.

Background

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.

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:

• 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

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)

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.

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.

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

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.

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.

References

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

Appendix

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
WHERE {
# 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

MDRM	Value	Short_Definition	Call_Schedule	Line_Number
RCON9999	20160930	Reporting date	ENT	1
RSSD9050	623	FDIC certificate number	ENT	2
RSSD9017	DEUTSCHE BANK TRUST COMPANY AMERICAS	Legal title of bank	ENT	3
RSSD9130	New York	City	ENT	4
RSSD9200	NY	State abbreviation	ENT	5
RSSD9220	10005	Zip code	ENT	6
RCON9224	8EWQ2UQKS07AKK8ANH81	Legal Entity Identifier (LEI) (Report only if your institution already has an LEI.)	ENT	7
RCON6979	TRUE	Comments?	NARR	1
RCON2200	43281000000	In domestic offices (sum of totals of columns A and C from Schedule RC-E, part I)	RC	13a
RCON6631	36536000000	Noninterest-bearing	RC	13a1
RCON6636	6745000000	Interest-bearing	RC	13a2
RCONB993	1093000000	Federal funds purchased in domestic offices	RC	14a
RCONB987	0	Federal funds sold in domestic offices	RC	3a
RCON0020	1575000000	Cash items in process of collection and unposted debits	RCA	1a
RCON0080	1000000	Currency and coin	RCA	1b
RCON0090	25896000000	Balances due from Federal Reserve Banks	RCA	4
RCON0010	27498000000	Total	RCA	5
RCON2165	0	Lease financing receivables (net of unearned income)	RCCI	10
RCON2123	0	LESS: Any unearned income on loans reflected in items 1-9 above	RCCI	11
RCON2122	13560000000	Total loans and leases, net of unearned income (item 12, column A must equal Schedule RC, sum of items 4.a and 4.b)	RCCI	12
RCONF158	0	1-4 family residential construction loans	RCCI	1a1
RCONF159	185000000	Other construction loans and all land development and other land loans	RCCI	1a2
RCON1420	0	Secured by farmland (including farm residential and other improvements)	RCCI	1b
RCON1797	515000000	Revolving, open-end loans secured by 1-4 family residential properties and extended under lines of credit	RCCI	1c1
RCON5367	3187000000	Secured by first liens	RCCI	1c2a
RCON5368	23000000	Secured by junior liens	RCCI	1c2b
RCON1460	935000000	Secured by multifamily (5 or more) residential properties	RCCI	1d