Planet Web Developer

My main goal for doing a SPARQL query against XBRL data was to be able to pull out the same bit of information from multiple companies' reports at once, and it turned out to be much less work than I thought it would be. Here is the result of my query for interest expense figures across several companies:

------------------------------------------------------------------------------
| companyName                    | periodStart  | periodEnd    | interestExp |
==============================================================================
| "GENERAL MILLS INC"            | "2005-05-30" | "2006-05-28" | "399600000" |
| "GENERAL MILLS INC"            | "2006-05-29" | "2007-05-27" | "426500000" |
| "GENERAL MILLS INC"            | "2007-05-28" | "2008-05-25" | "421700000" |
| "PAPA JOHNS INTERNATIONAL INC" | "2007-01-01" | "2007-07-01" | "3232000"   |
| "PAPA JOHNS INTERNATIONAL INC" | "2007-04-02" | "2007-07-01" | "1706000"   |
| "PAPA JOHNS INTERNATIONAL INC" | "2007-12-31" | "2008-06-29" | "3694000"   |
| "PAPA JOHNS INTERNATIONAL INC" | "2008-03-31" | "2008-06-29" | "1802000"   |
| "PEPSICO INC"                  | "2006-12-31" | "2007-06-16" | "96000000"  |
| "PEPSICO INC"                  | "2007-03-25" | "2007-06-16" | "54000000"  |
| "PEPSICO INC"                  | "2007-12-30" | "2008-06-14" | "132000000" |
| "PEPSICO INC"                  | "2008-03-23" | "2008-06-14" | "74000000"  |
------------------------------------------------------------------------------

Being able to compare specific financial figures from different companies will be great for people doing financial research.

A given company's XBRL SEC filing is typically an instance file full of facts plus additional files with taxonomies about the terms used and XLink linkbases about the relationships between the facts. The instance files, on their own, looked like the low hanging fruit to me.

After kicking around some of my ideas for modeling XBRL in RDF with Dave Raggett (who's doing some very interesting, more ambitious work modeling the whole deal in RDF—in related news, Kingsley Idehen said that OpenLink has an XBRL ontology almost ready, and TopQuadrant's Ralph Hodgson pointed out the BRONTO project at TIFbrewery), I wrote an instance2rdf.xsl XSLT stylesheet to convert an XBRL instance to RDF/XML. After running it on the instance documents for several companies that I downloaded from the SEC website and manually creating a file that I called colist.rdf to map company identifiers in the XBRL instances to company names, I ran the following query with ARQ 1.4 to ask about all Interest Expense figures in my collection of reports:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xi:  <http://www.xbrl.org/2003/instance>

SELECT DISTINCT ?companyName ?periodStart ?periodEnd ?interestExp

FROM <RRDonnelley.rdf>
FROM <pepsico.rdf>
FROM <nobleenergy.rdf>
FROM <generalmills.rdf>
FROM <papajohns.rdf>
FROM <dow.rdf>
FROM <ge.rdf>
FROM <cocacola.rdf>
FROM <colist.rdf>

WHERE {
  ?s rdf:type http://xbrl.us/us-gaap/2008-03-31#InterestExpense>;
     rdf:value     ?interestExp;
     xi:identifier ?identifier;
     xi:startDate  ?periodStart;
     xi:endDate    ?periodEnd.

  ?identifier rdfs:label ?companyName.

}
ORDER BY ?companyName ?periodStart

Not all of the named companies have InterestExpense figures in that namespace; the query just asks for the figure from the companies that do.

I originally planned to merge all the RDF files into one before running the query, but I decided to let SPARQL do it, which is why there are nine FROM clauses above. In a more realistic scenario, the RDF versions of the companies' XBRL data would be loaded into a single triplestore and you would run the query against that.

As Dave suggested, I could add data typing to the RDF created from the XBRL instances. Before I add anything else to the RDF, though, I want to make sure that it enables a new kind of useful SPARQL query against the data that I couldn't do before the addition. I'm open to suggestions!

What does this prove? We know that RDF is great for aggregating data, especially resources that may have different data structures but certain data in common. XBRL gets more interesting when you start aggregating data from multiple companies, and I haven't seen much of that, although my research was limited to free software. Being able to compare specific financial figures from different companies will be great for people doing financial research, and this new combination of standards and free software makes it pretty easy.

In an August 19th interview with Jon Udell, David Huynh of Freebase (and formerly of MIT's Project Simile) introduced his Freebase demo by describing a hypothetical query to a database asking for presidents' ages when they are inaugurated and whether there's a trend that we're getting younger presidents. Jon replies:

If it were possible to issue a database query over Wikipedia, then you could ask a question like that; you could say give me—well first of all, it would presume that you could identify US presidents, and it would further presume that you could find a field within those documents that would say the ages of those people, and that's not really part of the structure of Wikipedia. This information can be explicitly made available in Freebase. It hasn't in all cases, and that's part of the social process. So it ultimately relies on people to refine this raw information that came from Wikipedia and elsewhere so that it is more fielded and structured.

DBPedia + SPARQL is my new favorite toy.

They then go on to do such a query with Freebase... but they could have done it with Wikipedia, with a little help from SPARQL and DBpedia.

Wikipedia has plenty of fielded information in infoboxes. DBpedia lets you access this collection of data via a SPARQL endpoint. While Wikipedia (and hence DBpedia) have no field for a president's age at inauguration, it does store their birthdate and the year they began their first term, so calculating their ages when they each became president is pretty easy.

You could see a list of US Presidents by going to Wikipedia's List of Presidents of the United States page, but let's do it programatically with this SPARQL query so that we can build from there to get their ages at inauguration. We ask for the things in the database that have a subject of "Presidents of the United States":

PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
SELECT ?presName WHERE {
  ?presName skos:subject <http://dbpedia.org/resource/Category:Presidents_of_the_United_States>.
}

To see the query in action, click this executable URL version.

A slightly more complex query lists the name, birth date, and beginning of the first term of each one:

PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX dbpedia2: <http://dbpedia.org/property/>
SELECT ?presName,?birthday, ?startDate WHERE {
  ?presName skos:subject <http://dbpedia.org/resource/Category:Presidents_of_the_United_States>;
            dbpedia2:birth ?birthday;
            dbpedia2:presidentStart ?startDate.
}

Click here to see it in action. Because the fielded information for the various presidents is not consistent, only 19 of them have dbpedia2:birth and dbpedia2:presidentStart fields, so you'll only see those presidents returned for this query. Wikipedia pages for all US presidents do have this information, but it's not always named the same way—compare the dbpedia pages for Zachary Taylor and Lyndon Johnson, who doesn't show up on the list return by that last query, for some examples. As Jon said, filling out that data is part of the social process.

The real promise of Linked Data is the ability to write a program or script that grabs the data and does something with it, so I wrote a two-line batch file that:

1. uses curl to send that URL to DBpedia and store the results in an XML file
2. runs a short XSLT script to calculate the presidents' ages at inauguration

It doesn't look much like a two-line batch file here, so before running it, replace the first six carriage returns with spaces to turn the first seven lines into one:

curl -o presidentAges.xml -F "query=PREFIX dbpedia2: 
  <http://dbpedia.org/property/> PREFIX skos: 
  <http://www.w3.org/2004/02/skos/core#> SELECT ?presName,?birthday, 
  ?startDate WHERE { ?presName skos:subject 
  <http://dbpedia.org/resource/Category:Presidents_of_the_United_States>; 
  dbpedia2:birth ?birthday; dbpedia2:presidentStart ?startDate.}" 
  http://dbpedia.org/sparql 
xsltproc presidentAges.xsl presidentAges.xml

Here is the XSLT stylesheet, presidentAges.xsl:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:s="http://www.w3.org/2005/sparql-results#"
                version="1.0">

  <xsl:strip-space elements="*"/>
  <xsl:output method="text"/>


  <xsl:template match="s:result">

    <xsl:variable name="birthYear"
                  select="substring(
                          s:binding[@name='birthday']/s:literal,1,4)"/>
    <xsl:variable name="presidentName"
                  select="substring(s:binding[@name='presName']/s:uri,29)"/>

    <xsl:value-of select="translate($presidentName,'_',' ')"/>
    <xsl:text> </xsl:text>
    <xsl:value-of select="s:binding[@name='startDate']/
                          s:literal - $birthYear - 1"/>
<xsl:text>
</xsl:text>
  </xsl:template>
</xsl:stylesheet>

I subtracted the birth year and then another 1 from the startDate because with inaugurations being in January (at least in modern times) I assumed that each president hadn't reached his birthday yet. Here is the result:

Abraham Lincoln 51
Andrew Johnson 56
Bill Clinton 46
Chester A. Arthur 50
Franklin Pierce 48
George H. W. Bush 64
George Washington 56
Harry S. Truman 60
James K. Polk 49
James Monroe 58
John Adams 61
John Quincy Adams 57
Martin Van Buren 54
Millard Fillmore 49
Richard Nixon 55
Rutherford B. Hayes 54
Thomas Jefferson 57
Ulysses S. Grant 46
Zachary Taylor 64

I never realized that Grant was the same age as Clinton when he started—a year younger than Obama is now—but having led the army that won the US Civil War, I guess he had reasons to look a bit older at the start of his term.

DBPedia + SPARQL is my new favorite toy, and I'm getting more and more ideas lately about useful (or at least fun) things to do with the combination.