README.Rmd

---
title: finstr - Financial Statements in R 
output:
  html_document:
    keep_md: yes
---


```{r, echo=FALSE, results='hide', message=FALSE }
library(dplyr)
library(tidyr)
library(finstr)
data(xbrl_data_aapl2013)
data(xbrl_data_aapl2014)
```

**Warning: finstr package is in development. 
Please use with caution.**

The purpose of finstr package is to use financial statements 
data in more structured form and process.
For now it is offering:

1. Data structure for financial statements in tidy and usable format
2. Function to merge two reporting periods in single object
3. Some helper functions to help explore and manipulate the data in the 
structure

The idea in long term is to create an environment for reproducible financial 
statement analysis. With existing packages like XBRL for XBRL parsing, 
dplyr for data manipulation and knitr for reproducible research, this 
shouldn't be a long journey.


## Get data
Use XBRL package to parse XBRL files. For example:
```{r xbrl_parse_min, eval=FALSE, echo=TRUE}
library(XBRL)
# parse XBRL (Apple 10-K report)
xbrl_url2014 <- 
  "http://edgar.sec.gov/Archives/edgar/data/320193/000119312514383437/aapl-20140927.xml"
xbrl_url2013 <- 
  "http://edgar.sec.gov/Archives/edgar/data/320193/000119312513416534/aapl-20130928.xml"
xbrl_data_aapl2014 <- xbrlDoAll(xbrl_url2014)
xbrl_data_aapl2013 <- xbrlDoAll(xbrl_url2013)
```

## Prepare statements
With `xbrl_get_statements` convert XBRL data to *statements* object. 
```{r xbrl_get_statements}
library(finstr)

st2013 <- xbrl_get_statements(xbrl_data_aapl2013)
st2014 <- xbrl_get_statements(xbrl_data_aapl2014)
st2014
```

Statements object is a list of 
several statement objects (ballance sheets, income and cash 
flow statements) which are data frames with elements as columns and periods
as rows. 
To get a single *statement* use *statements* object as a regular R list:
```{r statement}
balance_sheet2013 <- st2013$StatementOfFinancialPositionClassified
balance_sheet2014 <- st2014$StatementOfFinancialPositionClassified
balance_sheet2014
```


Information about hierarchical structure of elements (concepts) is stored 
as an attribute to the statement object.

To see the calculation hierarchy of elements use `get_elements`: 
```{r relations}
get_elements(balance_sheet2014)
```

To query the fundamental elements from higher order elements use
`get_elements`:
```{r elements}
get_elements(balance_sheet2014, parent_id = "LiabilitiesCurrent")
```

## Merge statements from different periods
Use `merge` function to create single financial statement data from two 
statements. 
```{r merge_statement}
balance_sheet <- merge( balance_sheet2013, balance_sheet2014 )
```

The structure of merged balance sheets may differ because of changes in XBRL
taxonomy. 
Function `merge` takes care of it by expanding the element 
hierarchy to fit both statements. 
The values of any missing elements is set to 0.

To merge all statements from *statements* object use merge on statements objects:
```{r merge_statements}
# merge all statements
st_all <- merge( st2013, st2014 )
# check if balance sheets are merged:
balance_sheet <- st_all$StatementOfFinancialPositionClassified
```

## Calculate new values and ratios
Statement object (in our case `balance_sheet`) is also a data frame object.
With elements (or concepts) as columns and time periods as rows.
It is possible then to use statement as a data frame.

Lets calculate current ratio which is defined by

$$ Current Ratio = \frac{Current Assets}{Current Liabilities} $$

```{r dplyr}
library(dplyr)

balance_sheet %>%
  mutate(CurrentRatio = AssetsCurrent / LiabilitiesCurrent) %>%
  select(endDate, CurrentRatio)

```

By using `calculate` function we can achieve the same result with
less verbose language. Lets calculate now two ratios:

```{r calculate}
library(dplyr)

balance_sheet %>% calculate(
  
    Current_Ratio = AssetsCurrent / LiabilitiesCurrent,
    
    Quick_Ratio =  
      ( CashAndCashEquivalentsAtCarryingValue + 
          AvailableForSaleSecuritiesCurrent +
          AccountsReceivableNetCurrent
        ) / LiabilitiesCurrent
    
)
```


If we need a period average value we can use a `lag` function.
For example, to calculate *DSO* (days sales outstanding) over longer periods
the average of account receivable is compared to net sales.

We will use the formula for yearly preiods:

$$ DSO = \frac{Average Accounts Receivable}{Sales Revenue} \times 365 $$

In this case we need to connect two type of statements: balance sheets and
income statements. With matching reporting periods it can be accomplished 
with joining two data frames:

```{r DaysSalesOutstanding}

balance_sheet %>%
  inner_join( st_all$StatementOfIncome, by = "endDate") %>%
  calculate(
    .AccountReceivableLast = lag(AccountsReceivableNetCurrent),
    .AccountReceivableAvg = (.AccountReceivableLast + AccountsReceivableNetCurrent)/2,
    DaysSalesOutstanding = .AccountReceivableAvg / SalesRevenueNet * 365 
  )

```

The leading dot instructs the calculate function to hide the value. In our case
only DaysSalesOutstanding is selected in final result.