XBRLfiles.Rmd

---
title: "Exploring XBRL files with R"
author: "Darko Bergant"
date: "Saturday, January 17, 2015"
output:
  html_document:
    keep_md: yes
    toc: yes
---

#What is XBRL?
Extensible Business Reporting Language ([XBRL](https://www.xbrl.org/the-standard/what/)) is the open international standard for [digital business reporting](http://xbrl.squarespace.com), managed by a global not for profit consortium, [XBRL International](http://xbrl.org). 


#XBRL Parser for R
Parsing XBRL is [not](https://www.xbrl.org/the-standard/how/getting-started-for-developers/) something you could do with your eyes closed.
Fortunately the [XBRL](http://cran.r-project.org/web/packages/XBRL) package by Roberto Bertolusso and Marek Kimel takes all the pain away.

To parse complete XBRL, use `xbrlDoAll` function. 
It extracts xbrl instance and related schema files to a list of data frames.

```{r parse, message=FALSE, cache=TRUE}
library(XBRL)

inst <- "http://edgar.sec.gov/Archives/edgar/data/21344/000002134414000008/ko-20131231.xml"
options(stringsAsFactors = FALSE)
xbrl.vars <- xbrlDoAll(inst, cache.dir = "XBRLcache", prefix.out = NULL)

str(xbrl.vars, max.level = 1)
```

# XBRL Data Frames Structure
The data structure of the data frames is shown in the image below 

![XBRL tables](img/xbrl_files.png)

All values are kept in the `fact` table (in the `fact` field, precisely).
The `element` table defines _what_ are these values (the XBRL _concepts_, e.g. “assets”, “liabilities”, “net income” etc.).
The `context` table defines the periods and other dimensions for which the values are reported.

With [dplyr](http://cran.r-project.org/web/packages/dplyr)'s `join` and `filter` it is quite easy to explore the data in interrelated tables. 
For example, to extract revenue from the sale of goods we have to join the 
*facts* (the numbers) with the *context* (periods, dimensions):


```{r sales, message=FALSE}
library(dplyr)

xbrl.vars$fact %>%
  filter(elementId == "us-gaap_SalesRevenueGoodsNet") %>%
  left_join(xbrl.vars$context, by = "contextId") %>%
  filter(is.na(dimension1)) %>%
  select(startDate, endDate, fact, unitId, elementId) %>% 
  (knitr::kable)(format = "markdown")

```

# Balance Sheet Example
## Select Statement
XBRL encapsulates several reports of different types:

```{r roleid, message=FALSE}
table(xbrl.vars$role$type)
```

To find all _statements_, filter roles by `type`:
```{r all_statements, message=FALSE, results='asis'}
htmlTable::htmlTable(data.frame(Statements=
  with(
    xbrl.vars$role[xbrl.vars$role$type=="Statement", ],
    paste(roleId, "\n<br/>", definition, "\n<p/>")
  )),
  align = "l",
  rnames = FALSE
)
```


## Presentation hierarchy
To find out which concepts are reported on specific financial statement component, we have to search the presentation tree from the top element.

```{r balance_sheet, message=FALSE}

library(tidyr)
library(dplyr)

# let's get the balace sheet
role_id <- "http://www.thecocacolacompany.com/role/ConsolidatedBalanceSheets"

# prepare presentation linkbase : 
# filter by role_id an convert order to numeric
pres <- 
  xbrl.vars$presentation %>%
  filter(roleId %in% role_id) %>%
  mutate(order = as.numeric(order))

# start with top element of the presentation tree
pres_df <- 
  pres %>%
  anti_join(pres, by = c("fromElementId" = "toElementId")) %>%
  select(elementId = fromElementId)

# breadth-first search
while({
  df1 <- pres_df %>%
    na.omit() %>%
    left_join( pres, by = c("elementId" = "fromElementId")) %>%
    arrange(elementId, order) %>%
    select(elementId, child = toElementId);
  nrow(df1) > 0
}) 
{
  # add each new level to data frame
  pres_df <- pres_df %>% left_join(df1, by = "elementId")
  names(pres_df) <-  c(sprintf("level%d", 1:(ncol(pres_df)-1)), "elementId")
}
# add last level as special column (the hierarchy may not be uniformly deep)
pres_df["elementId"] <- 
  apply( t(pres_df), 2, function(x){tail( x[!is.na(x)], 1)})
pres_df["elOrder"] <- 1:nrow(pres_df) 

# the final data frame structure is
str(pres_df, vec.len = 1 )


```

## Amounts and Contexts
Elements (or _concepts_ in XBRL terminology) of the balance sheet are now gathered in data frame with presentation hierarchy levels. To see the numbers we have to join the elements with numbers from `fact` table and periods from `context` table:

```{r results='asis'}
# join concepts with context, facts
pres_df_num <-
  pres_df %>%
  left_join(xbrl.vars$fact, by = "elementId") %>%
  left_join(xbrl.vars$context, by = "contextId") %>%
  filter(is.na(dimension1)) %>%
  filter(!is.na(endDate)) %>%
  select(elOrder, contains("level"), elementId, fact, decimals, endDate) %>%
  mutate( fact = as.numeric(fact) * 10^as.numeric(decimals)) %>%
  spread(endDate, fact ) %>%
  arrange(elOrder)

library(pander)
pres_df_num %>% 
  select(elementId, contains("2013"), contains("2012")) %>%
  pandoc.table(
    style = "rmarkdown",
    split.table = 200,
    justify = c("left", "right", "right")
  )

```

## Labels

Every concept in XBRL may have several labels (short name, description, documentation, etc.) perhaps in several languages. In presentation linkbase there is a hint (`preferredLabel`) which label should be used preferrably. Additionally the computed rows are emphasized. 

```{r results='asis'}
# labels for our financial statement (role_id) in "en-US" language:
x_labels <-
  xbrl.vars$presentation %>%
  filter(roleId == role_id) %>%
  select(elementId = toElementId, labelRole = preferredLabel) %>%
  semi_join(pres_df_num, by = "elementId") %>%
  left_join(xbrl.vars$label, by = c("elementId", "labelRole")) %>%
  filter(lang == "en-US") %>%
  select(elementId, labelString)

# calculated elements in this statement component
x_calc <- xbrl.vars$calculation %>%
  filter(roleId == role_id) %>%
  select(elementId = fromElementId, calcRoleId = arcrole) %>%
  unique()

# join concepts and numbers with labels
balance_sheet_pretty <- pres_df_num %>%
  left_join(x_labels, by = "elementId") %>%
  left_join(x_calc, by = "elementId") %>%
  select(labelString, contains("2013"), contains("2012"), calcRoleId)


names(balance_sheet_pretty)[1] <- 
  "CONDENSED CONSOLIDATED BALANCE SHEETS (mio USD $)"

names(balance_sheet_pretty)[2:3] <-
 format(as.Date(names(balance_sheet_pretty)[2:3]), "%Y")
# rendering balance sheet

pandoc.table(
  balance_sheet_pretty[,1:3],
  style = "rmarkdown",
  justify = c("left", "right", "right"),
  split.table = 300,
  big.mark = ",",
  emphasize.strong.rows = which(!is.na(balance_sheet_pretty$calcRoleId))
)

```

## Calculation Hierarchy
XBRL includes three hierarchies of concepts: definition, presentation and
calculation. Hierarchies are stored as links in `definition`,
`presentation` and `calculation` tables. Columns `fromElementId` and 
`toElementId` represent parent and child. 

Sometimes it is easier to use calculation hierarchy 
when it is reshaped into elements table with
explicit hierarchy position:

```{r results='asis'}
role_id <- "http://www.thecocacolacompany.com/role/ConsolidatedBalanceSheets"

relations <- 
  xbrl.vars$calculation %>% 
  filter(roleId == role_id) %>% 
  select(fromElementId, toElementId, order)

elements <-
  data.frame( 
    elementId = with(relations, unique(c(fromElementId, toElementId))),
    stringsAsFactors = FALSE
  )  %>%
  left_join(xbrl.vars$element, by = c("elementId")) %>%
  left_join(relations, by = c("elementId" = "toElementId")) %>%
  left_join(xbrl.vars$label, by = c("elementId")) %>%
  filter(labelRole == "http://www.xbrl.org/2003/role/label") %>% 
  transmute(elementId, parentId = fromElementId, order, balance, labelString)

# get top element(s) in hierarchy  
level <- 1
df1 <- elements %>%
  filter(is.na(parentId)) %>%
  mutate(id = "") %>% 
  arrange(desc(balance))

# search the tree
while({
  level_str <- 
    unname(unlist(lapply(split(df1$id, df1$id), function(x) {
      sprintf("%s%02d", x, 1:length(x))
    })))
  
  elements[elements$elementId %in% df1$elementId, "level"] <- level
  to_update <- elements[elements$elementId %in% df1$elementId, "elementId"]
  elements[ 
    #order(match(elements$elementId, to_update))[1:length(level_str)], 
    order(match(elements$elementId, df1$elementId))[1:length(level_str)], 
    "id"] <- level_str
  
  df1 <- elements %>%
    filter(parentId %in% df1$elementId) %>%
    arrange(order) %>%
    select(elementId, parentId) %>%
    left_join(elements, by=c("parentId"="elementId")) %>%
    arrange(id)
  nrow(df1) > 0})
{
  level <- level + 1
}

# order by hierarchy ID and mark terminal nodes 
elements <- 
  elements %>%  
  dplyr::arrange_(~id) %>% 
  dplyr::mutate( 
    terminal = !elementId %in% parentId,
    Element = paste(
      substring(paste(rep("&nbsp;",10), collapse = ""), 1, (level-1)*2*6),
      gsub("us-gaap_", "",elementId)
    )
  )

pandoc.table(
  elements[, c("Element", "balance", "id")],
  style = "rmarkdown",
  justify = c("left", "left", "left"),
  split.table = 300,
  emphasize.strong.rows = which(elements$level == 1)
)
```

_Notice that TreasuryStockValue element has different balance side than its parent
element StockholdersEquity. In this case the element value should be deducted instead
of added to the total sum, when calculating (or validating) the value of its parent
concept._

# Related

## [finstr](https://github.com/bergant/finstr) package: financial statements in R

[finstr](https://github.com/bergant/finstr) package includes the
"data wrangling" functions needed to use the XBRL data.
It allows user to focus on financial statement analysis.

## [xbrlus](https://github.com/bergant/xbrlus) package: R interface to XBRL US API

XBRL US (http://xbrl.us/) provides
free access to their database via 
[XBRL US API](https://github.com/xbrlus/data_analysis_toolkit/).
Package [xbrlus](https://github.com/bergant/xbrlus) is an R interface to this API.