Bank Ontology

Loading XBRL Call Reports into FIBO

The tutorial explains how to import the taxonomy schema and move instance data into the Financial Business Ontology (FIBO).

The example is FFIEC031, the Consolidated Report of Condition and Income, generally referred to as the Call Report or RC report that US Banks must file quarterly.

To understand the context better view or download the PDF with speaker notes.

The online PowerPoint below is a backup for class presentations. For best viewing at the bottom right click Maximize-presentation to maximize. Then in MS-Powerpoint online start slide show start-slide-show and show notes.

Presentation text:

  1. FIBO relationship to other Financial Industry Standards The XBRL Bank Call Report (FFIEC 031) in FIBO Jurgen Ziemer Ontologist at Jayzed Data Models Inc.
  2. Finance key point FIBO is key to better Data Management http://finregont.com Getting started and integrating industry standards can be a challenge.
  3. Finance key point You are here to leverage FIBO
  4. Finance key point Complexity overwhelms Conventional Data Management. Consolidated Life New York offices, 1960. (Jack Lemon in Billy Wilder’s “The Apartment”, MGM) Hundreds of people mapping between numerous heterogeneous systems, languages, components…
  5. Finance key point Industry standards and regulations drive FIBO programs. FIBO resolves data management complexity. Standards Regulations FIBO In Semantic Compliance® everything is a triple.
  6. Finance key point Onboarding industry standards, compliance forms and reports into FIBO is an easy 2-step process. Source Ontology Staging FIBO Extract Transform Load The XBRL and Call Report ontology are Open Source. (same MIT license as FIBO) 1 2 Semantic ETL
  7. Finance key point Extract the industry standard XBRL regulatory report FFIEC 031 into Ontology Staging. Reverse engineer OWL Staging Call Report 031 Federal Financial Institutions Examination Council Federal Deposit Insurance Corporation
  8. Program explanation XBRL is the global standard to exchange business reports. eXtensible Business Reporting Language XBRL Ontology is a complete 1-to-1 representation of the XBRL schema XML/XSD RDF/OWL
  9. Architecture details XBRL separates reporting items from presentation, calculation and edit checks. Xlink is a W3C extension to XML that provides methods for creating links between documents linkbase utilizes Xlink with a framework to define roles and relations of instance items for presentation, calculation, and edit checks. instance defines the basic reporting items with their meta-data, context units, datatypes and format. Benefit: We only have to load instance data into the ontology, independent of the report structure.
  10. Architecture details Each namespaces in the XML schema files becomes an ontology file. Xlink.xsd linkbase.xsd instance.xsd XBRL source schema Topbraid XSD import XBRL ontologies
  11. Architecture details XSD complex types and attribute groups become ontology classes. The sxml:tag refers to the original XSD component.
  12. Program explanation XBRL taxonomies define specific sets of reports. Call Report GAAP IFRS Solvency imports The OWL taxonomy representation extends the XBRL base classes with subtypes for specific reporting items, sections, tables, and calculations
  13. Architecture details FFIEC provides schema and Bank call reports
  14. Architecture details The Bank ontology has an OWL version of the FFIEC taxonomy The XBRL instance ontology FFIEC extensions to XBRL Item Types FFIEC Micro Data Reference Manual (MDRM) concepts that define instance items. >4,200 subclasses of instance:item The reverse engineered XBRL instance (JPM Chase 09/30/2016) that hold the data values.
  15. Architecture details The FFIEC instance file is a simple list of reporting items values. < cc:RSSD9017 contextRef=”CI_852218_2016-09-30″> JPMorgan Chase Bank, National Association</cc:RSSD9017> The import generates instances of the FFIEC concept class – here RSSD9017 (Legal Title of bank).
  16. Program explanation A query on the populated ontology matches the FFIEC individual institution download. Institution Name JPMORGAN CHASE BANK, NATIONAL ASSOCIATION City COLUMBUS State OH Zip Code 43240 Call Report Date 9/30/2016 Short_Definition Value Reporting date 20160930 FDIC certificate number 628 Legal title of bank JPMorgan Chase Bank, National Association City Columbus State abbreviation OH Zip code 43240 FFIEC download. Ontology query results
  17. Architecture details We reverse engineer the FFIEC taxonomy into OWL The taxonomy ontologies import XBRL-linkbase: edit checks, calculations, presentation, definitions, captions, instructions, and edit check messages.
  18. Architecture details We validate the import reproducing the FFIEC report in SPARQL, the ontology query language SELECT ?Call_Date ?Bank_RSSD_Identifier ?MDRM ?Value ?S hort_Definition ?Call_Schedule ?Line_Number WHERE { ?end_date_inst a instance:ContextPeriodType . ?end_date_inst instance:instant ?instant . BIND (xsd:string(?instant) as ?Call_Date) . ?ident a instance:ContextEntityType-identifier . ?ident composite:child ?ident_node . ?ident_node sxml:text ?Bank_RSSD_Identifier . … BIND ( fn:substring (?lb_label_loc, 4) AS ?MDRM) . ?lb_loc ffiec-w3-xlink:label-loc ?lb_label_loc . ?ffiec_concept rdfs:label ?MDRM . ?ffiec_concept rdfs:subClassOf* instance:ItemAttrs . ?concept_inst a ?ffiec_concept . ?concept_inst composite:child ?text_node . ?text_node sxml:text ?Value . } ORDER BY ?Call_Schedule ?Line_Number Similar to SQL, the SELECT specifies the result set columns The WHERE clause joins the triplets. ORDER BY provides the sorting.
  19. Architecture details The FFIEC excel (SDF on top) and SPARQL result set. https://bankontology.com/ br/query/Call%20Report% 20JPMC%2020160930%2 0query%20results.xlsx https://cdr.ffiec.gov/public /ManageFacsimiles.aspx
  20. Finance key point Transform XBRL staging data and load into FIBO classes. FIBO Inference Engine SPARQL Mapping OWL Staging
  21. Program explanation We graphically map FFIEC XBRL MDRM into FIBO classes. OWL Staging FIBO
  22. Architecture details The Legal Entity ID provides the URI local name The argument is the value of the Legal Entity Identifier in reporting item RSSD9017. The template builds the URI as FIBO-prefix : FIBO-class _ “LEI” argument http://www.omg.org/spec/EDMC-FIBO/BE/LegalEntities/LEIEntities/LegalEntityIdentifier_LEI_7H6GLXDRUGQFU57RNE97
  23. Architecture details The data property mapping assigns the MDRM value to the FIBO unique identifier. The mapping context from Staging class to FIBO class Source and target data property
  24. Architecture details SPARQL rules specify complex data transformations. The hasSourceInstance object property provides data lineage. For every FIBO instance, we have a link to its original instance in the Call Report. The BIND function uses the mapping context to determine the FIBO target instance
  25. Program explanation The inference engine (Reasoner) populates FIBO. FIBO Inference Engine SPARQL Mapping OWL Staging We validate FIBO data, mapping, and lineage.
  26. Architecture details The Legal Entity Identifier class has a new instance The source instance points to the original FFIEC instance. A SPARQL rule has populated the link to the FIBO Corporation instance. The unique identifier shows the correct LEI.
  27. Architecture details The resource graph shows populated FIBO instances and their relationships.
  28. Architecture details The data query traverses the joins and shows data properties institution_name JPMorgan Chase Bank, National Association lei 7H6GLXDRUGQFU57RNE97 country_name United States state_name Ohio issued_capital $176,083,000,000 fdic_certificate 628 regulator FDIC registration_directory FDIC Institution Directory SELECT ?institution ?lei ?country_name ?state_name ?issued_capital ?fdic_certificate ?regulator ?registration_directory WHERE { ?corporation fibo-fnd-aap-agt:hasName ?institution . ?corporation fibo-be-le-fbo:hasRegisteredAddress ?registered_address . …. ?institution_directory rdfs:label ?registration_directory . }
  29. Architecture details The mapping query lists source to target class. Mapping is in triples.
  30. Architecture details The lineage graph connects FIBO to Call Report instances via the hasSourceInstance property SELECT ?fibo_class ?fibo_inst ?call_cert_inst ?ffiec_class ?xml_tag WHERE { ?fibo_inst fro-ref:hasSourceInstance ?call_cert_inst . ?call_cert_inst a ?ffiec_class . ?fibo_inst a ?fibo_class . ?ffiec_class sxml:tag ?xml_tag } Linage is in triples.
  31. Architecture details The lineage query results trace the FIBO instance back to its XBRL source record. [fibo_class] fibo_inst call_cert_inst ffiec_class xml_tag fibo-be-le-cb: StockCorporation fibo-be-le-cb: StockCorporation_LEI_7H6G LXDRUGQFU57RNE97< file:///BankOntology/data/Call_ Cert628_093016.xml#r-2169> ffiec-concept:RSSD9017 http://www.ffiec.gov/xbrl/call/co ncepts#RSSD9017 fibo-be-le-lei: LegalEntityIdentifier fibo-be-le-lei: LegalEntityIdentifier_LEI7H6 GLXDRUGQFU57RNE97< file:///BankOntology/data/Call_ Cert628_093016.xml#r-1084> ffiec- concept:RCON9224 http://www.ffiec.gov/xbrl/call/co ncepts#RCON9224 fibo-fbc-fct-fse: DepositoryInstitution fibo-fbc-fct-fse: DepositoryInstitution_LEI_7 H6GLXDRUGQFU57RNE97< file:///BankOntology/data/Call_ Cert628_093016.xml#r-2169> ffiec-concept:RSSD9017 http://www.ffiec.gov/xbrl/call/co ncepts#RSSD9017 fibo-fbc-fct-usjrga: FDICCertificateNumber fibo-fbc-fct-usjrga: FDICCertificateNumber_628< file:///BankOntology/data/Call_ Cert628_093016.xml#r-642> ffiec-concept:RSSD9050 http://www.ffiec.gov/xbrl/call/co ncepts#RSSD9050 FIBO Ontology Staging XBRL Source
  32. Finance key point The reverse – populate the Call Report out of FIBO. FIBO OWL Staging FFIEC 031 Call Report ExportSemantic ETL
  33. Program explanation Map FIBO into FFIEC 031 staging classes. Our example, the FIBO registration address maps to 3 FFIEC MDRMs.
  34. Architecture details The mapping is a reversal of the Staging to FIBO mapping context. Unfortunately there is no “reverse mapping” ETL button. We have to specify • How to build (match) the Staging URI • Transform FIBO formats into FFIEC formats. • The UPDATE operation is still a challenge. The Staging to FIBO mapping context query, provides our to-do list.
  35. Architecture details Test case: JPM Chase moves to Vermont We change the bank’s city, subdivision and postal code.
  36. Architecture details We run the inference engine and examine results. The resource form for city is an instance of MDRM RSSD9130. The text node has changed to Montpelier (capital of Vermont).
  37. Program explanation Export the staging into FFIEC compliant XBRL. Line 1968 shows the updated city. The XBRL is valid.
  38. Architecture details We export the modified staging OWL into XML. We invoke the export dialog for the staging OWL file. The root instance “#r” will export all semantic XML triples.
  39. Architecture details Namespaces and imports http://www.xbrl.org/ http://www.ffiec.gov/xbrl/call/ http://finregont.com/fro/xbrl/ https://bankontology.com/br/ http://www.omg.org/spec/EDMC-FIBO/ owl:imports owl:imports reverse engineer reverse engineer xml namespace
  40. Finance key point Consolidated Life New York offices, 1960. (Jack Lemon in Billy Wilder’s “The Apartment”) Hundreds of people mapping between numerous heterogeneous systems, languages, components…
  41. Finance key point Standards Regulations FIBO Everything is a triple
  42. Program explanation XBRL Ontology Staging FIBO Lessons learned: FIBO has good support for compliance. We can load XBRL data into FIBO and generate the Call Report out of FIBO. FIBO is too assertive. Have more defined classes! XBRL import and transformations are slow.
  43. Architecture details References 1. Tutorial : http://finregont.com/financial-regulation-ontology-tutorial/ Chapter one has an into to OWL, FIBO, and the Legal reference ontology. There is also a getting started with Protégé section. Chapter two shows XML source import in depth for Code of Federal Regulations and United States Code. 2. XBRL Ontology: http://finregont.com/xbrl/ Includes links to the ontology files and documentation. 3. Bank ontology: https://bankontology.com/ontology-directory-files-prefixes/ 4. XBRL consortium, US website: https://xbrl.us/ 5. FFIEC taxonomy and data download: https://cdr.ffiec.gov/public/Default.aspx 6. Topbraid Composer website: http://www.topquadrant.com/tools/IDE-topbraid-composer-maestro-edition/