The purpose of finstr package is to create an environment for reproducible financial statement analysis. The package will not cover specific types of analysis (except in examples and package vignettes) but will provide a domain language to write them. With other functions in basic R and existing R packages users could store, share, reuse and reproduce the results of their analitic work.
For now it is offering:
1. Data structure for financial statements
2. Statement calculation validation
3. Merge statements
4. Calculate and reveal
To install finstr from github use install_github from devtools package:
library(devtools) install_github("bergant/finstr")
library(dplyr) library(tidyr) library(finstr) data(xbrl_data_aapl2013) data(xbrl_data_aapl2014)
Use XBRL package to parse XBRL files. For example:
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)
With xbrl_get_statements
convert XBRL data to statements object.
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).
To get a single statement use statements object as a regular R list:
balance_sheet2013 <- st2013$StatementOfFinancialPositionClassified balance_sheet2014 <- st2014$StatementOfFinancialPositionClassified income2013 <- st2013$StatementOfIncome income2014 <- st2014$StatementOfIncome balance_sheet2014 tail(income2014, 2)
Recalculate higher order concepts from basic values and check for errors.
check <- check_statement(balance_sheet2014) check
In case of error the numbers with errors will be presented along with elements:
check_statement( within(balance_sheet2014, InventoryNet <- InventoryNet * 2) )
Validation returns all calculation results in a readable data frame. Lets check only operating income from income statement:
check <- check_statement(income2014, element_id = "OperatingIncomeLoss") check check$expression[1] check$calculated / 10^6
Use merge
function to create single financial statement data from two
statements.
balance_sheet <- merge( balance_sheet2013, balance_sheet2014 )
The structure of merged balance sheets may differ if XBRL
taxonomy changes.
Function merge
takes care of it by expanding the elements
hierarchy to fit both statements.
The values of any missing elements in different periods is set to 0.
To merge all statements from statements object use merge on statements objects:
# merge all statements st_all <- merge( st2013, st2014 ) # check if balance sheets are merged: balance_sheet <- st_all$StatementOfFinancialPositionClassified balance_sheet$endDate
If there are no matching elements between the two statements
merge
joins statements by matching their periods.
For some financial ratio calculations the combined statement may be
a better starting point.
merge.statement( st_all$StatementOfFinancialPositionClassified, st_all$StatementOfIncome )
Statement object (in our case balance_sheet
) is also a data frame object
with statement elements 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} $$
With dplyr package we can use mutate
, select
or transmute
functions:
library(dplyr) balance_sheet %>% transmute( date = endDate, CurrentRatio = AssetsCurrent / LiabilitiesCurrent )
By using finstr::calculate
function we can achieve the same result but
don't have to handle the date field and there is a rounding parameter.
Lets calculate for example two ratios:
balance_sheet %>% calculate( digits = 2, 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:
merge(balance_sheet, st_all$StatementOfIncome ) %>% calculate( digits = 2, .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. Use digits
parameter to control rounding.
When running same calculation for different statements, define the
calculation with calculation
and call calculate
with argument
calculations
:
# define calculation profit_margins <- calculation( Gross_Margin = (SalesRevenueNet - CostOfGoodsAndServicesSold) / SalesRevenueNet, Operating_Margin = OperatingIncomeLoss / SalesRevenueNet, Net_Margin = NetIncomeLoss / SalesRevenueNet ) # run profit margins for two different statements income2013 %>% calculate(calculations = profit_margins, digits = 2) income2014 %>% calculate(calculations = profit_margins, digits = 2)
Calculations gives us freedom to use any formula with any data from financial statements. Most of the time this is not necessary as we can get useful information just by regrouping calculation hierarchy.
There are many additional reasons why is rearranging statements useful step before actual calculations:
To rearrange the statement to simple 2-level hierarchy use expose
function.
expose( balance_sheet, # Assets `Current Assets` = "AssetsCurrent", `Noncurrent Assets` = other("Assets"), # Liabilites and equity `Current Liabilities` = "LiabilitiesCurrent", `Noncurrent Liabilities` = other(c("Liabilities", "CommitmentsAndContingencies")), `Stockholders Equity` = "StockholdersEquity" )
Balance sheet stays divided by Assets and Liabilities and Equity. For the second level we are exposing current assets from noncurrent and similar is done for the liabilities. We choose to separate equity.
Function expose
expects a list of vectors with element names.
Function other
helps us identify elements without enumerating every single element.
Using other
reduces a lot of potential errors as the function "knows" which
elements are not specified and keeps the balance sheet complete.
Sometimes it is easier to define a complement than a list of elements.
In this case we can use the %without%
operator. Lets expose for example
tangible and then intangible assets:
expose( balance_sheet, # Assets `Tangible Assets` = "Assets" %without% c("Goodwill", "IntangibleAssetsNetExcludingGoodwill"), `Intangible Assets` = other("Assets"), # Liabilites and equity `Liabilities` = c("Liabilities", "CommitmentsAndContingencies"), `Stockholders Equity` = "StockholdersEquity" )
To calculate lagged difference for entire statement use diff
function.
The result is statement of changes between successive years:
diff(balance_sheet)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.