Skills for XBRL: Creating Financial Reports with XML

1. Skills for XBRL: Creating Financial Reports with XML John Stamey, Gregory Krippel Joseph Russell, Dylan Houston Coastal Carolina University Numbers & Bytes, October 6, 2010, Conway, SC Southeast InfORMS, October 7, 2010, Myrtle Beach, SC Jwstamey | krippel | jsrussel | dbhousto @ coastal.edu

2. We live in a world built on XML (and most people don't know it). • All your MS Office applications now have an X at the end (such as .docx, etc.) • RSS feeds are XML • Data delivered via SOAP are most likely in XML • Web 2.0 (AJAX Applications) usually return XML to be parsed • The standard resides at: www.w3.org/XML It’s All About XML

3. In the early 1970’s, three IBM researchers • Charles Goldfarb • Ed Mosher • Ray Lorie invented GML, a way of marking up technical documents with structural tags. • Goldfarb invented the term MARK-UP LANGUAGE • XML was adopted by the ISO in 1986. A Little History of XML

4. XML is both human-readable and machine-readable. • Have you ever tried to read a Microsoft Word document with a text editor? • If not available, an XML document can be easily read and parsed (data elements determined and used. • XML data is not trapped within the constraints of one software application. Advantages of XML

5. <?xml version="1.0" encoding="iso-8859-1" ?> <personnelInformation> <name> <first>John</first> <last>Stamey</last> </name> <work extension="2552"> <jobTitle>Associate Professor</jobTitle> <employer>Coastal Carolina University</employer> <department>Computer Science and Information Systems</department> </work> </personnelInformation> A Simple XML Example

6. The descriptive names of information (we could also call categories) are enclosed in angle brackets. These are called tags. • Tags usually appear in pairs. <name> is the OPENING NAME TAG </name> is the CLOSING NAME TAG • Additional descriptive information is found in the attributes. The phone extension is included as part of the WORK tag. <work extension="2552"> Some Things to Note

7. I. Personnel Information A. Name 1. First: John 2. Last: Stamey B. Work (Phone 2552) 1. Job Title: Associate Professor 2. Employer: Coastal Carolina University 3. Department: Computer Science and Information Systems The Hierarchical Nature of Information Stored in XML

8. 1996 - Charles Hoffman, CPA • The idea that accounting information can be stored as XML. • July 2009 - SEC began requirements of electronic filings in XML. • "All companies, foreign and domestic, are required to file registration statements, periodic reports, and other forms electronically through EDGAR-online.com." XML Moves to Accounting

9. XBRL is eXtensible Business Reporting Language • www.XBRL.com • www.XBRL.us • Financial information can be selected, analyzed, stored and exchanged with other systems. • XBRL greatly increases the speed of handling of financial data, reduces the chance of error and permits automatic checking of information. The XBRL Advantage

10. Phase 1: Top 500 US-GAPP filers (the Fortune 500) • Phase 2: • The remainder of the large filers (about 1400 companies) and IFRS (Int. Fin. Rep. Stds.) • Mutual Fund Risk/Return filings in January 2011 • Phase 3: • Begins June 2011, with an additional 10,000 smaller publically traded companies • Nationally Recognized Statistical Rating Organizations (NRSROs) in November 2010 XBRL Adoption in the US

11. XBRL mandates in other countries • China, Israel, Japan, Korea, Spain, others • Italy (2009) • All corporations were required to file corporate returns in XBRL. • The experience was reported at the Bryant University Conference last weekend. • UK (2011) • All corporations in will be required to file corporate returns in XBRL format. Global XBRL Initiatives

12. Edgar Online (NSDQ: EDGR) • Will have end-to-end electronic financial reporting with the purchase of XBRL software firm UBmatrix. • The merger will strengthen the position of both companies, creating the leading provider of (SEC) public company XBRL filings and data. • http://www.marketwatch.com/investing/stock/EDGR Recent Developments at Edgar

13. Revenue Sales Revenue $ 5,557,241,000.00 Revenue Total $ 5,557,241,000.00 Expense Cost Of Goods Sold $ 2,665,566,000.00 Selling, General And Admin. Expense $ 1,479,041,000.00 Interest Expense $ 60,093,000.00 Income Tax Expense $ 136,385,000.00 Net Income (Loss) $ 216,156,000.00 Expense Total $ 4,557,241,000.00 Net Income $ 1,000,000,000.00 Example: An Income Statement

14. <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> <xbrli:xbrl xmlns:xbrli="http://www.xbrl.org/2003/instance" xmlns:link="http://www.xbrl.org/2003/linkbase" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:iso4217="http://www.xbrl.org/2003/iso4217" xmlns:us-gaap="http://xbrl.us/us-gaap/2009-01-31"> <link:schemaRef xlink:type="simple" xlink:href="core" /> The XBRL Equivalent (1 of 3)

15. <xbrli:context id="Q3_2009"> <xbrli:entity> <xbrli:identifier schema="www.NYSE.com">HSY</xbrli:identifier> </xbrli:entity> <xbrli:period> <xbrli:instant>2009-09-03</xbrli:instant> </xbrli:period> </xbrli:context> <xbrli:unit id="USD"> <xbrli:measure>iso4217:USD</xbrli:measure> </xbrli:unit> The XBRL Equivalent (2/3)

16. <us-gaap:RevenueRecognitionSalesOfGoods ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 5557241000</us-gaap:RevenueRecognitionSalesOfGoods> <us-gaap:CostOfGoodsSold ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 2665566000</us-gaap:CostOfGoodsSold> <us-gaap:SellingGeneralAndAdministrativeExpense ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 1479041000</us-gaap:SellingGeneralAndAdministrativeExpense> <us-gaap:InterestExpense ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 60093000</us-gaap:InterestExpense> <us-gaap:IncomeTaxExpenseBenefit ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 136385000</us-gaap:IncomeTaxExpenseBenefit> <us-gaap:NetIncomeLoss ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 216156000</us-gaap:NetIncomeLoss> </xbrli:xbrl> The XBRL Equivalent (3/3)

17. There are three particularly important technologies involved in an XBRL Filing • XML NAMESPACE - A collection of logically related fields contained in an XML Schema. <xhtml:br /> • XML SCHEMA - A document that formally defines an XML document. Extension is .xsd XML Technologies (1/2)

18. XLINK – a link from one XML document to another location, not necessarily within a URL. Additional instructions and semantics are included. <presentationArc xlink:type="arc" xlink:arcrole="http://www.xbrl.org/arcrole/ parent-child" xlink:from="loc_IncomeStatementAbstract“ xlink:to="loc_EarningsPerShareDilutedAbstract" order="10" use="optional" preferredLabel="http://www.xbrl.org/2003/role/ verboseLabel" /> XML Technologies (2/2)

19. Filing documents include: • Instance Document • Schema Document • Calculation Linkbase Document • Lables Linkbase Document • Presentation Linkbase Document • Definition Linkbase Document Example of an XBRL Filing

20. XBRL Instance Document – Values and parameters of the filing • Linkbase Documents – Define relationships and “a whole lot more.” They are not required but highly useful. • Calculation Linkbase - defines how values of concepts should, for example, sum up from one to another. • Label Linkbase - allows the user to attach English-readable labels to tags. What is an XBRL Filing? (1/2)

21. Presentation Linkbase - defines how concepts are nested and ordered. • Definition Linkbase - allows the user to define additional (custom) semantics and relationships. • Reference Linkbase - allows the user to attach external information (authoritative sources) to concepts. • Footnote Linkbase - allows the user to relate a footnote description to a concept. What is an XBRL Filing? (2/2)

22. From IFRS – A “Visual”

23. Fields used in multiple calculations – ConAgra "Earnings Per Share Diluted" is used in two different calculations, • IncomeLossFromDiscontinuedOperations NetOfTaxPerDilutedShare • IncomeLossFromContinuingOperationsPer DilutedShare • Where is the human-readable text we would actually use in the financial statement? • How can we determine formatting? • LINKBASES PROVIDE ANSWERS Decoding Information from Linkbases – Sometimes Difficult!

24. <calculationArc xlink:type="arc" xlink:arcrole="http://www.xbrl.org/2003/ arcrole/summation-item" xlink:from="loc_EarningsPerShareDiluted" xlink:to="loc_IncomeLossFromContinuing OperationsPerDilutedShare" order="40" use="optional" weight="1" /> Computation Linkbase: Sum (+1), Precedence, and Name of tag (for Printable Label, loc_)

25. <loc xlink:type="locator" xlink:href="http://taxonomies.xbrl.us/us- gaap/2009/elts/us-gaap-2009-01- 31.xsd# us-gaap_IncomeLossFromContinuing OperationsPerDilutedShare" xlink:label="loc_IncomeLossFromContinuingOperationsPerDilutedShare" /> Computation Linkbase: Name of Tag in Label Linkbase (loc_ from previous slide)

26. <loc xlink:type="locator" xlink:href="http://taxonomies.xbrl.us/us-gaap/2009/elts/us-gaap-2009-01-31.xsd# us-gaap_IncomeLossFromContinuingOperations PerDilutedShare" xlink:label="us- gaap_IncomeLossFromContinuingOperations PerDilutedShare"/> Label Linkbase: From hyperlink to us-gaap_ scope

27. <labelArc xlink:type="arc" xlink:arcrole="http://www.xbrl.org/2003/ arcrole/concept-label" xlink:from="us- gaap_IncomeLossFromContinuing OperationsPerDilutedShare" xlink:to="us- gaap_IncomeLossFromContinuing OperationsPerDilutedShare_lbl"/> Label Linkbase: From us-gaap_ to us-gapp_/_lbl in prep for Printable English

28. <label xlink:type="resource" xlink:role="http://www.xbrl.org/2003/role/label" xlink:label="us-gaap_IncomeLossFromContinuingOperationsPerDilutedShare_lbl" xml:lang="en-US"> Income from continuing operations, diluted </label> Label Linkbase: Link to Printable Label (us-gaap_/_lbl from previous slide to Printable English)

29. Creating the Trial Balance • Defining the tags – not an easy task! • Creating XBRL • XML instance document with information • Up to six LINKBASE documents with further description • Validate all files • XBRL Reports and Data Analysis The XBRL Lifecycle

30. Using XBRL: Formatting options to create human-readable reports • XSLT – eXtensible Stylesheet Language for some tasks • XSLT-FO is used to create PDFs • Teaching examples from a CCU class • http://www.softwareengineeringonline.com/csci409fall2010/seinforms.cfm • Python more robust XML Processing • SAX • DOM – we are using DOM in our work due to its similarity to JavaScript • Element Tree XBRL Reporting and Technologies

31. XHTML report from XML

32. The Base XML File

33. Viewing Source Code from the Browser

34. XSLT Code – For Each Loop

35. XSLT Code – Messy Recursive Computation

36. How do we know to use the specific tag name CostOfGoodsSold? <us-gaap:CostOfGoodsSold ContextRef="Q3_2009" unitRef="USD" decimals="-6"> 2665566000 </us-gaap:CostOfGoodsSold> Determining Tag Names

37. Find the current US-GAAP Taxonomy at http://www.xbrl.org/2003/xbrl-instance-2003-12-31.xsd • Use the Yeti Viewer at http://bigfoot.corefiling.com/yeti/resources/yeti-gwt/Yeti.jsp This will allow us to view tags and definitions (the XBRL Taxonomy) Determining Correct Tags (from the over 10,000 in US-GAAP)

38. Using the Yeti Viewer (1/5)

39. Yeti: Select US-GAAP (2/5)

40. Yeti: Search for COG (3/5)

41. Yeti: View Search Results (4/5)

42. Yeti: Success! (5/5)

43. XML Pilot – Joe Russell • Online system written in ColdFusion • Originally written in PHP • Will be used to train Accounting Information Systems students • Takes a simple income statement and produces an XBRL document that will validate • This example does NOT create Linkbase documents. Creating an XBRL Instance Document: XML Pilot

44. XML Pilot – Input Screen

45. XML Pilot – Output XML File

46. Beginning with an XBRL Document • Create a human-readable report using Python 3.1 • Discussion of general XML handling in Python • Solution presented by Dylan Houston • Had we used linkbases, we would have created the Dragon Tag (Rivet Software) or UB Matrix products XBRL Reports in Python 3.1

47. Python has three ways to process XML • SAX • DOM – Based on the Document Object Model, very reminiscent of JavaScript XML handling • Element Tree – by Frank Lundh • We present 6 Object-Oriented Programming Methods from Python to process XML based on the Document Object Model. Processing XML with Python 3.1

48. from xml.dom import minidom • IMPORT the Minidom library that lets us manipulate XML with Python. This statement is placed at the top of the program. • dom = minidom.parse("document.xml") • This statement INSTANTIATES a variable dom that holds the easily accessible (parsed) XML found in document.xml. 1. Invoking DOM parser

49. x = dom.getElementsByTagName(‘tag’) print (x) Example: <name>John Doe</name> This method would return “John Doe” if the tag were NAME 2. getElementsByTagName

50. NAMESPACE= ‘http://xbrl.us/us-gaap/2009-01-31’ x = dom.getElementsByTagNameNS(NAMESPACE, ‘tag’) print x Most tags are accompanied by a namespace. Namespaces are sets of logically connected tags. Example: <us-gaap:interestExpense ContextRef="Q3_2009" unitRef="USD" decimals=“-6">2665566000</us-gaap:interestExpense> 2665566000 would be printed (exclusive of any additional formatting) 3. getElementsByTagNameNS