Skip to content
Snippets Groups Projects
README.Rmd 6.35 KiB
Newer Older
Bergant's avatar
Bergant committed
---
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
Bergant's avatar
Bergant committed
2. Validate statement calculations
3. Function to merge two reporting periods into single object
4. Calculations on statements data and lagged difference calculation
Bergant's avatar
Bergant committed

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
Bergant's avatar
Bergant committed
Use XBRL package to parse XBRL files. For example:
Bergant's avatar
Bergant committed
```{r xbrl_parse_min, eval=FALSE, echo=TRUE}
Bergant's avatar
Bergant committed
library(XBRL)
Bergant's avatar
Bergant committed
# 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"
Bergant's avatar
Bergant committed
xbrl_data_aapl2014 <- xbrlDoAll(xbrl_url2014)
xbrl_data_aapl2013 <- xbrlDoAll(xbrl_url2013)
Bergant's avatar
Bergant committed
```

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

Bergant's avatar
Bergant committed
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
Bergant's avatar
Bergant committed
income2013 <- st2013$StatementOfIncome
income2014 <- st2014$StatementOfIncome
Bergant's avatar
Bergant committed
balance_sheet2014
Bergant's avatar
Bergant committed
tail(income2014, 2)
Bergant's avatar
Bergant committed
```

Bergant's avatar
Bergant committed

Information about hierarchical structure of elements (concepts) is stored 
Bergant's avatar
Bergant committed
as an attribute to the statement object.

Bergant's avatar
Bergant committed
To get more data about the concepts used in the statement call `get_elements`: 
Bergant's avatar
Bergant committed
```{r relations}
Bergant's avatar
Bergant committed
bs_els <- get_elements(balance_sheet2014)
Bergant's avatar
Bergant committed
```

Bergant's avatar
Bergant committed
Elements store concept descriptions, balance attribute (debit/credit) and 
parent/child relationships between concepts.
Bergant's avatar
Bergant committed

Bergant's avatar
Bergant committed
## Validate statement calculation hierarchy
Recalculate higher order concepts from basic values and check for errors.

```{r check}
errors <- check_statement(balance_sheet2014)
errors
```


Bergant's avatar
Bergant committed
## 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 )
```

Bergant's avatar
Bergant committed
The structure of merged balance sheets may differ if XBRL
taxonomy changed. 
Function `merge` takes care of it by expanding the elements 
Bergant's avatar
Bergant committed
hierarchy to fit both statements. 
Bergant's avatar
Bergant committed
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 )
Bergant's avatar
Bergant committed
# check if blance sheets are merged:
Bergant's avatar
Bergant committed
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.
Bergant's avatar
Bergant committed
It is possible then to use statement as a data frame.
Bergant's avatar
Bergant committed

Lets calculate current ratio which is defined by

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

Bergant's avatar
Bergant committed
```{r dplyr}
Bergant's avatar
Bergant committed
library(dplyr)

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

```

Bergant's avatar
Bergant committed
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
    
)
```


Bergant's avatar
Bergant committed
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.

Bergant's avatar
Bergant committed
We will use the formula for yearly preiods:
Bergant's avatar
Bergant committed

$$ 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") %>%
Bergant's avatar
Bergant committed
  calculate(
    .AccountReceivableLast = lag(AccountsReceivableNetCurrent),
    .AccountReceivableAvg = (.AccountReceivableLast + AccountsReceivableNetCurrent)/2,
    DaysSalesOutstanding = .AccountReceivableAvg / SalesRevenueNet * 365 
  )
Bergant's avatar
Bergant committed

```
Bergant's avatar
Bergant committed

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

Bergant's avatar
Bergant committed
Use digits parameter to control rounding:

```{r income}


st_all$StatementOfIncome %>% calculate( digits = 2,

  Gross_Margin = 
    (SalesRevenueNet -  CostOfGoodsAndServicesSold) / SalesRevenueNet,

  Operating_Margin =
    OperatingIncomeLoss / SalesRevenueNet,

  Net_Margin = 
    NetIncomeLoss / SalesRevenueNet

  ) 

```

When running same calculation for different statements, store the
calculation with `calculation` and run with `do_calculation`:
```{r calculation}
# define calculation
profit_margins <- calculation(
  
  Gross_Margin = 
    (SalesRevenueNet -  CostOfGoodsAndServicesSold) / SalesRevenueNet,
  
  Operating_Margin =
    OperatingIncomeLoss / SalesRevenueNet,
  
  Net_Margin = 
    NetIncomeLoss / SalesRevenueNet,
  
  digits = 3
)

# run profit margins for two different statements
income2013 %>% do_calculation(profit_margins)
income2014 %>% do_calculation(profit_margins)

```


Bergant's avatar
Bergant committed
##Lagged difference
To calculate lagged difference for entire statement use `diff` function.
The result is statement of changes between successive years:

```{r diff}

balance_sheet %>% diff()

```