Financial Analysis of SEC Reports in R

The Package: finreport

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).

The Commands

The first command is CompanyInfo().

library(finreportr)
CompanyInfo("JPM")

##               company        CIK  SIC state state.inc FY.end     street.address
## 1 JPMORGAN CHASE & CO 0000019617 6021    NY        DE   1231 383 MADISON AVENUE
##          city.state
## 1 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.

JP Morgan [JPM]

AnnualReports("JPM")

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

Disclaimer

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

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

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

Rendering a Useful Tool for Excel

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)
JPM.IS.W <- dcast(JPM.IS, Metric + Units ~ endDate, value.var="Amount")
JPM.SCF.W <- dcast(JPM.SCF, Metric + Units ~ endDate, value.var="Amount")
JPM.BS.W <- dcast(JPM.BS, Metric + Units ~ endDate, value.var="Amount")

Writing a file to Excel

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

library(openxlsx) #load the package
write.xlsx(list(JPM.IS.W,JPM.BS.W,JPM.SCF.W), file = "JPM-2015.xlsx")

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)
library(finreportr)
library(reshape2)
library(openxlsx)
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(list(SEC.IS.W,SEC.BS.W,SEC.SCF.W), file = paste(ticker,"-",year,".xlsx", sep=""))
return(list(BS=SEC.BS,IS=SEC.IS,SCF=SEC.SCF))
}

Does it work?

# Try the function on Target
TGT <- getSEC("TGT", 2015)
# Try the function on Mondelez
MDLZ <- getSEC("MDLZ", 2015)

It does not always work. Some statements are incomplete and the file fail. Then we have to go back and figure it out.