There is a common misconception about the costs associated with structuring data for financial filings to regulators. Companies assume that converting financial reports to XBRL a high effort / low value expense. But the lack of perceived value is due to their misunderstanding of the value of data automation and standardization, not XBRL.

In other words, accountants are manually creating reports then converting them to XBRL.

The true benefits of XBRL and other data standardization tools are seen when they are automated. And automation must begin before the reporting process – namely, by tagging data from the source.

Unfortunately, one article alone could never cover all the steps involved in full XBRL data automation. However,  while the tutorial below doesn’t delve into the intricacies of tagging XBRL data, it does explain how to automate XBRL for financial analysis, after the tagging has occurred. Hopefully, this will help show the value of automated reporting – moving from compiling data to considering insights – as the end goal of a full automated data standardization pipeline.

This project is for the automation of XBRL financial analysis, it acts as an example of how easy automation can be just by learning a few key concepts.

We will use the XBRL API to grab financial statements, then use publically available libraries to QA and analyze the statements.


If you do not have dev tools already installed, do so, then install the remaining git packages



#devtools::install_github("bergant/xbrlus") #https://github.com/bergant/xbrlus
#devtools::install_github("bergant/finstr") #https://github.com/bergant/finstr


Getting Data

Initally we are going to use hard coded values, then create a micro service to get them online.

But first, will will need an API key from XBRL

You can get it from here: https://xbrl.us/home/use/data-analysis-toolkit/

Then either create a system environment variable XBRLUS_API_KEY=APIValueHere or add a new R system variable to .Renviron in your R home (find by using normalizePath(“~”) in R Console)

Or use the code below each time you run the script, but putting an API key in code is never a good practice

Additional info here: https://xbrlus.github.io/data_analysis_toolkit/ on the api

##Uncomment below if seting API key in code
#api_key = "xxxxx"

balanceSheetCompare <- function(c1 = "aapl",c2 = "goog",c3 = "fb", year =2016, return_data = FALSE){

  #heavily lifted from example on https://github.com/bergant/xbrlus
  cik <- xbrlCIKLookup(c(c1,c2,c3))
  elements <- xbrlBaseElement(c(
  values <- xbrlValues( 
    CIK = cik$cik, 
    Element = elements$elementName, 
    DimReqd = FALSE, 
    Period = "Y",
    Year = year,
    NoYears = 1,
    Ultimus = TRUE,
    Small = TRUE,
    as_data_frame = TRUE
  balance_sheet <- 
    elements %>% 
    left_join(values, by = "elementName") %>% 
    select(entity, standard.text, amount) %>% 
    mutate(amount = round(amount / 10e6,0)) %>%  
    spread(entity, amount)
  if(ncol(balance_sheet == 5)) {balance_sheet[,5] <- NULL}

  balance_sheet <- balance_sheet[
  row.names(balance_sheet) <- NULL
    caption = "Balance Sheet Comparison",
    big.mark = ",",
    split.table = 200,
    style = "rmarkdown",
    justify = c("left", rep("right", 3)))


This function allows me to compare three ticker symbols for any year I want, pretty cool and it was lifted right from the github of the author so it’s basically cut and paste!

It also returns the balance sheet value if I want to get more data


Balance Sheet Comparison

standard.textAlphabet Inc.APPLE INCFACEBOOK INC
Assets, Current10,54110,6873,440
Assets, Noncurrent6,209NANA
Liabilities and Equity16,75032,1696,496
Liabilities, Current1,6767,901288
Liabilities, NoncurrentNANANA
Stockholders’ Equity Attributable to Noncontrolling InterestNANANA
Stockholders’ Equity Attributable to Parent13,90412,8255,919
Stockholders’ Equity, Including Portion Attributable to Noncontrolling InterestNANANA

Getting XBRL Ourselves

Next we want to look at parsing entire xbrl files we get from EDGAR: https://www.sec.gov/edgar/searchedgar/companysearch.html

Again, this is taken right from the github for the library:

getXbrlStatement <- function(url) {
old_o <- options(stringsAsFactors = FALSE)
fullStatementXbrl <- xbrlDoAll(url, verbose = TRUE)

bullStatementDF <- xbrl_get_statements(fullStatementXbrl)

getBalanceSheet <- function(parsedXBRL){
    balanceSheet <- parsedXBRL$StatementOfFinancialPositionClassified
    balanceSheet <- parsedXBRL$ConsolidatedBalanceSheets

getIncomeStatement <- function(parsedXBRL){
  if(!is.null( parsedXBRL$StatementOfIncome)){
    incomeStatement <- parsedXBRL$StatementOfIncome
  if(!is.null( parsedXBRL$ConsolidatedStatementsOfComprehensiveIncome)){
    incomeStatement <- parsedXBRL$ConsolidatedStatementsOfComprehensiveIncome

Financial Analysis

Here’s where it gets incredible, using built in calculation functions in the finstr library, we can take multiple years of data and completely automate the analysis!

applXBRL2016 <- "https://www.sec.gov/Archives/edgar/data/320193/000162828016020309/aapl-20160924.xml"
applXBRL2015 <- "https://www.sec.gov/Archives/edgar/data/320193/000119312515356351/aapl-20150926.xml"

appl201610K <- invisible(getXbrlStatement(applXBRL2016))
appl201510k <- invisible(getXbrlStatement(applXBRL2015))

appl2016Balance <- getBalanceSheet(appl201610K)
appl2015Balance <- getBalanceSheet(appl201510k)

Sweet, we have our statements, now remember in introductory accounting classes how much it work it was to close the books and verify there weren’t any errors?

…checking for errors:

check <- check_statement(appl2016Balance)

Number of errors: 0 Number of elements in errors: 0

… looking at specific calcuations:


[1] “AssetsCurrent”


[1] “+ CashAndCashEquivalentsAtCarryingValue + AvailableForSaleSecuritiesCurrent + AccountsReceivableNetCurrent + InventoryNet + NontradeReceivablesCurrent + OtherAssetsCurrent”


…and verification those calcuations are correct:

[1] 8.9378e+10


[1] 8.9378e+10


Well, there goes a few jobs…

Let’s get rid of some more shall we!

We can merge the statements together and do whatever analysis we like, even custom calculations

balance_sheet <- merge( appl2015Balance, appl2016Balance )

ratios <- balance_sheet %>% calculate( digits = 2,
    current_ratio = AssetsCurrent / LiabilitiesCurrent,
    quick_ratio =  
      ( CashAndCashEquivalentsAtCarryingValue + 
          AvailableForSaleSecuritiesCurrent +
        ) / LiabilitiesCurrent

Year over year ratios:

        caption = "Year Over Year Ratios",
        big.mark = ",",
        split.table = 200,
        style = "rmarkdown",
        justify = c("left", rep("right", 2)))

5. Making a Plot

And last but not least a quick visual to show some things aren’t as easy in R 🙂


First we have to make our data ‘long’ for it to work appropriately in ggplot:

long_df <- invisible(melt(ratios)) #invisible to hide log output
## Using date as id variables
  caption = "Ratios in Long Format",
  big.mark = ",",
  split.table = 200,
  style = "rmarkdown",
  justify = c("left", rep("right", 2)))

Then we have to use the unusual language of ggplot to make a chart, it looks cool, but the syntax is… less than obvious…

ratio_plot <-   ggplot(data = long_df, aes(x=date, y=value, fill = variable)) 

ratio_plot <- ratio_plot + geom_bar(stat="identity", position=position_dodge())


XBRL Automation For Financial Analysis

The complete (end to end) automation that XBRL provides can help organizations with data governance and maintaining a level of standardization that saves time and resources, as well as significantly reduce errors from manual touchpoints.

Need help with complex financial analysis? Ask a Syntelli expert for help!

Request Demo for Financial Analysis


Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts


How To Export Google Analytics Data into a Data Lake: Case Study

One of our clients recently presented a unique and advanced analytics challenge. Through an innovative approach, the Syntelli team successfully implemented a simple, yet effective solution, to satisfy several other business requirements. Ultimately, the team Read more…


How To Unlock Analytics Maturity in Your Organization: 3 Skills For Success

This is our third and concluding post in the series of achieving analytics maturity for finance and accounting professionals. Read the first part here, and the second part here. As seen in our Data Science Read more…


How To Become Analytically Mature in Accounting and Finance – Part Two

Refer back to part one for intro to Reporting and Business Intelligence for professionals in Accounting and Finance. How to Become Analytically Mature: Data Science Examples of Tools Considered: SAS, RapidMiner, Knime, SPSS, Rattle



Register | Lost your password?