Newer
Older
---
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. Validate statement calculations
3. Function to merge two reporting periods into single object
4. Calculations on statements data and lagged difference calculation
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
# 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)
With `xbrl_get_statements` convert XBRL data to *statements* object.
```{r xbrl_get_statements}
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
income2013 <- st2013$StatementOfIncome
income2014 <- st2014$StatementOfIncome
Information about hierarchical structure of elements (concepts) is stored
To get more data about the concepts used in the statement call `get_elements`:
Elements store concept descriptions, balance attribute (debit/credit) and
parent/child relationships between concepts.
## Validate statement calculation hierarchy
Recalculate higher order concepts from basic values and check for errors.
```{r check}
errors <- check_statement(balance_sheet2014)
errors
```
## 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 if XBRL
taxonomy changed.
Function `merge` takes care of it by expanding the elements
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 )
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.
Lets calculate current ratio which is defined by
$$ Current Ratio = \frac{Current Assets}{Current Liabilities} $$
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.
$$ 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.
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
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)
```