The Package: finreportr

The key tool to facilitate the financial analysis of companies that file regular SEC reports of certain forms is finreportr. To make use of it, we must first have R install it and dependencies. To install it, install.packages("finreportr", dependencies=TRUE).

require("finreportr")
## Loading required package: finreportr

The Commands

The first command is CompanyInfo().

library(finreportr)
CompanyInfo("JPM")
##               company        CIK  SIC state state.inc FY.end
## 1 JPMORGAN CHASE & CO 0000019617 6021    NY        DE   1231
##    street.address        city.state
## 1 270 PARK AVENUE NEW YORK NY 10017

CompanyInfo() gives the basics on the company from the SEC including addresses, incorporation details, and numeric codes.

The next command is AnnualReports(). It returns a list of the reports with the type and the accession number of the actual document.

AnnualReports("JPM")
##    filing.name filing.date         accession.no
## 1         10-K  2016-02-23 0000019617-16-000902
## 2         10-K  2015-02-24 0000019617-15-000272
## 3         10-K  2014-02-20 0000019617-14-000289
## 4         10-K  2013-02-28 0000019617-13-000221
## 5         10-K  2012-02-29 0000019617-12-000163
## 6         10-K  2011-02-28 0000950123-11-019773
## 7         10-K  2010-02-24 0000950123-10-016029
## 8         10-K  2009-03-02 0000950123-09-003840
## 9         10-K  2008-02-29 0001193125-08-043536
## 10        10-K  2007-03-01 0000950123-07-003015
## 11      10-K/A  2006-08-03 0000950123-06-009854
## 12      10-K/A  2006-06-22 0000950123-06-008545
## 13        10-K  2006-03-09 0000950123-06-002875
## 14      10-K/A  2005-06-28 9999999997-05-030472
## 15        10-K  2005-03-02 0000950123-05-002539
## 16      10-K/A  2004-06-28 9999999997-04-028305
## 17        10-K  2004-02-18 0000950123-04-002022
## 18      10-K/A  2003-06-30 9999999997-03-028080
## 19        10-K  2003-03-19 0000950123-03-002985
## 20      10-K/A  2002-06-28 9999999997-02-037872
## 21     10-K405  2002-03-22 0000950123-02-002823
## 22     10-K405  2001-03-22 0000950123-01-002499
## 23      10-K/A  2000-06-28 0000019617-00-000125
## 24        10-K  2000-03-13 0000950123-00-002204
## 25      10-K/A  1999-06-29 0000950123-99-006055
## 26     10-K405  1999-03-11 0000950123-99-002057
## 27     10-K405  1998-03-30 0000950123-98-003043
## 28        10-K  1997-03-25 0000950123-97-002412
## 29     10-K405  1996-03-20 0000950123-96-001197
## 30      10-K/A  1995-06-26 0000019617-95-000080
## 31     10-K405  1995-03-27 0000950123-95-000706
## 32      10-K/A  1994-05-09 0000019617-94-000048
## 33        10-K  1994-03-25 0000950123-94-000607

The analysis works on three financial reports: (1) GetIncome(), (2) GetCashFlow(), and (3) GetBalanceSheet(). Each takes as argument the ticker and the year. First, a look at the disclaimer.

The General Layout

Help File

Help File

Income Statements

JPM.IS <- GetIncome("JPM", 2015)

Balance Sheets

JPM.BS <- GetBalanceSheet("JPM", 2015)

Statement of Cash Flows

JPM.SCF <- GetCashFlow("JPM", 2015)

Rendering a Useful Tool for Excel

Reshape: Rendering the Data in Wide Format

I want to develop a tool to export the data that we have grabbed. How might we do that? This will involve a reshape that will require the reshape2 package. This can be done relatively systematically. The reshape command will take the Metric and Units as rows and place the Amount in columns defined by the endDate. The command is dcast and it takes an input file and restructures data according to a fixed rule. Here, I will have a Metric and Units for each of multiple entries defined by endDate with distinct Amount for each endDate. First, I will generate a separate version of each statement.

library(reshape2) # load reshape2 to use dcast to reformat the data for Excel
JPM.IS.W <- dcast(JPM.IS, Metric + Units ~ endDate, value.var="Amount")
JPM.SCF.W <- dcast(JPM.SCF, Metric + Units ~ endDate, value.var="Amount")
## Aggregation function missing: defaulting to length
JPM.BS.W <- dcast(JPM.BS, Metric + Units ~ endDate, value.var="Amount")

Exporting the Data

Now I want to write the file to Excel and combine them. I will use xlsx.

library(xlsx) #load the package
## Loading required package: rJava
## Loading required package: xlsxjars
write.xlsx(JPM.IS.W, file = "JPM-2015.xlsx", sheetName = "IS", row.names = FALSE)
write.xlsx(JPM.BS.W, file = "JPM-2015.xlsx", sheetName = "BS", row.names = FALSE, append=TRUE)
write.xlsx(JPM.SCF.W, file = "JPM-2015.xlsx", sheetName = "SCF", row.names = FALSE, append=TRUE)

The Coup de Grace for Excel Users

The last set of code can automate this. I will create a little function to do it all, taking as input, the ticker and year.

getSEC <- function(ticker, year) {
options(scipen=12)
require(finreportr)
require(reshape2)
require(xlsx)
SEC.IS <- GetIncome(ticker, 2015)
SEC.IS$Amount <- as.numeric(SEC.IS$Amount)
SEC.BS <- GetBalanceSheet(ticker, 2015)
SEC.BS$Amount <- as.numeric(SEC.BS$Amount)
SEC.SCF <- GetCashFlow(ticker, 2015)
SEC.SCF$Amount <- as.numeric(SEC.SCF$Amount)
SEC.IS.W <- dcast(SEC.IS, Metric + Units ~ endDate, value.var="Amount", fun.aggregate=mean)
SEC.SCF.W <- dcast(SEC.SCF, Metric + Units ~ endDate, value.var="Amount", fun.aggregate=mean)
SEC.BS.W <- dcast(SEC.BS, Metric + Units ~ endDate, value.var="Amount", fun.aggregate=mean)
write.xlsx(SEC.IS.W, file = paste(ticker,"-",year,".xlsx", sep=""), sheetName = "IS", row.names = FALSE)
write.xlsx(SEC.BS.W, file = paste(ticker,"-",year,".xlsx", sep=""), sheetName = "BS", row.names = FALSE, append=TRUE)
write.xlsx(SEC.SCF.W, file = paste(ticker,"-",year,".xlsx", sep=""), sheetName = "SCF", row.names = FALSE, append=TRUE)
return(list(BS=SEC.BS,IS=SEC.IS,SCF=SEC.SCF))
}

Does the function work?

# Try the function on Target
TGT <- getSEC("TGT", 2015)
# Try the function on Mondelez
MDLZ <- getSEC("MDLZ", 2015)
# Try the function on Goldman Sachs
# GS <- getSEC("GS", 2015)  does not work, a statement is missing.
# Try the function on Mondelez
XOM <- getSEC("XOM", 2016)