FFIEC 031 – Call Report ontology

Abstract

The FFIEC Call Report Ontology is a 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 Industry. The operational ontologies for investment management have RDF/OWL version 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 record in XBRL, XSD and XML. We are familiar with the requirements from 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 section, line number, description 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 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 report, schedule line item and column. 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 FFIEC report as well as an expanded version (SELECT *) that contains all resources joined.

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

FFIEC institution Excel report match Ontology query results

Conclusion and Outlook

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

Next steps for 2017 are

  1. Enhance the raw import with semantics. First to add object properties directly linking concepts, items, link base,  formulas, 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. To populate the Call Report classes with financial information from FIBO instance. This is the reverse direction of step two.


References

Financial Regulation Ontology Tutorial (online PowerPoint and softcopy):

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

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

Appendix

  1. The SPARQL query
    also available for download in the query directory.
  2. A snapshot of the Query result set. The full excel can be downloaded here.

  1. SPARQL query on 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

    2. Abridged version of the 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