importing financial statements from getFin() to data.frame or data.table?

Question

the getFin() function returns an object of type "financials". which contains a list of lists.

getFin("AAPL")

structure of resulting object

i need to create tables for each of the following:

Balance Sheet
Income Statement
Cash Flow

End goal is to display these tables on a dashboard.
Here's what I tried, but it doesn't seem right:

df <- data.frame(AAPL.f[[2]][2])
df2 <- data.frame(viewFin(AAPL.f,"BS", "A"))

How can I get the above statements into Data frames?

score 1 · Accepted Answer · answered Mar 08 '16 at 04:20

This should give you what you want.

require(quantmod)
setwd("C:/Users/your_path_here/downloads")
stocks <- c("AXP","BA","CAT","CSCO","CVX","DD","DIS","GE","GS","HD","IBM","INTC","JNJ","JPM","KO","MCD","MMM","MRK","MSFT","NKE","PFE","PG","T","TRV","UNH","UTX","V","VZ","WMT","XOM")

# equityList <- read.csv("EquityList.csv", header = FALSE, stringsAsFactors = FALSE)
# names(equityList) <- c ("Ticker")

for (i in 1 : length(stocks)) {   
        temp<-getFinancials(stocks[i],src="google",auto.assign=FALSE)
        write.csv(temp$IS$A,paste(stocks[i],"_Income_Statement(Annual).csv",sep=""))
        write.csv(temp$BS$A,paste(stocks[i],"_Balance_Sheet(Annual).csv",sep=""))
        write.csv(temp$CF$A,paste(stocks[i],"_Cash_Flow(Annual).csv",sep=""))
        write.csv(temp$IS$A,paste(stocks[i],"_Income_Statement(Quarterly).csv",sep=""))
        write.csv(temp$BS$A,paste(stocks[i],"_Balance_Sheet(Quaterly).csv",sep=""))
        write.csv(temp$CF$A,paste(stocks[i],"_Cash_Flow(Quaterly).csv",sep=""))
}

this is not the exact solution I was looking for. but it is very helpful. Thanks! — pranav, Mar 08 '16 at 06:38

score 1 · Answer 2 · answered Mar 28 '16 at 17:37

Here's what I was looking for.... I'm sure there are better ways to do this.

library(quantmod)
library(xlsx)
getFin("GS")
gs_BS <- GS.f$BS$A
str(gs_BS)

 #num [1:42, 1:4] 106533 NA 113003 71883 NA ...
 #- attr(*, "dimnames")=List of 2
 # ..$ : chr [1:42] "Cash & Equivalents" "Short Term Investments" "Cash and Short Term Investments" "Accounts Receivable - Trade, Net" ...
 # ..$ : chr [1:4] "2015-12-31" "2014-12-31" "2013-12-31" "2012-12-31"
 #- attr(*, "col_desc")= chr [1:4] "As of 2015-12-31" "As of 2014-12-31" "As of 2013-12-31" "As of 2012-12-31"

transposed <- t(gs_BS)
write.xlsx(transposed, "C:\\Users\\abc\\Desktop\\bal_sheet.xlsx", row.names=FALSE)
transp <- read.xlsx("C:\\Users\\Tatter\\Desktop\\bal_sheet.xlsx" , sheetName="Sheet1")
transp$year <- c("2015","2014","2013","2012")

#> str(transp)
#'data.frame':   4 obs. of  43 variables:
#$ Cash...Equivalents                          : num  106533 90406 94224 65919
#$ Short.Term.Investments                      : logi  NA NA NA NA
#$ Cash.and.Short.Term.Investments             : num  113003 96196 98364 72669
#$ Accounts.Receivable...Trade..Net            : num  71883 94479 97880 91354
#$ Receivables...Other                         : logi  NA NA NA NA
#$ Total.Receivables..Net                      : num  71883 94479 97880 91354
#$ Total.Inventory                             : logi  NA NA NA NA
#$ Prepaid.Expenses                            : logi  NA NA NA NA
#$ Other.Current.Assets..Total                 : logi  NA NA NA NA
#$ Total.Current.Assets                        : logi  NA NA NA NA
#$ Property.Plant.Equipment..Total...Gross     : num  17726 18324 18236 17267
#$ Accumulated.Depreciation..Total             : num  -7770 -8980 -9040 -9050
#$ Goodwill..Net                               : num  3657 3645 3705 3702
#$ Intangibles..Net                            : num  491 515 671 1397
#$ Long.Term.Investments                       : num  548317 547272 615841 602819
#$ Other.Long.Term.Assets..Total               : num  5548 5181 5241 55291
#$ Total.Assets                                : num  861395 855842 911507 938555
#$ Accounts.Payable                            : num  210362 213572 204765 194485
#$ Accrued.Expenses                            : num  8149 8368 7874 8292
#$ Notes.Payable.Short.Term.Debt               : num  196752 186133 250283 241931
#$ Current.Port..of.LT.Debt.Capital.Leases     : num  29623 29501 47288 67349
#$ Other.Current.liabilities..Total            : num  1280 1533 1974 2724
#$ Total.Current.Liabilities                   : logi  NA NA NA NA
#$ Long.Term.Debt                              : num  268652 257954 245227 176270
#$ Capital.Lease.Obligations                   : logi  NA NA NA NA
#$ Total.Long.Term.Debt                        : num  268652 257954 245227 176270
#$ Total.Debt                                  : num  495027 473588 542798 485550
#$ Deferred.Income.Tax                         : logi  NA NA NA NA
#$ Minority.Interest                           : num  459 404 326 508
#$ Other.Liabilities..Total                    : num  51035 70829 70120 152289
#$ Total.Liabilities                           : num  774667 773045 833040 862839
#$ Redeemable.Preferred.Stock..Total           : logi  NA NA NA NA
#$ Preferred.Stock...Non.Redeemable..Net       : num  11200 9200 7200 6200
#$ Common.Stock..Total                         : num  9 9 8 8
#$ Additional.Paid.In.Capital                  : num  51340 50049 48998 48030
#$ Retained.Earnings..Accumulated.Deficit.     : num  83386 78984 71961 65223
#$ Treasury.Stock...Common                     : num  -62640 -58468 -53015 -46850
#$ Other.Equity..Total                         : num  -718 -743 -524 -520
#$ Total.Equity                                : num  86728 82797 78467 75716
#$ Total.Liabilities...Shareholders..39..Equity: num  861395 855842 911507 938555
#$ Shares.Outs...Common.Stock.Primary.Issue    : logi  NA NA NA NA
#$ Total.Common.Shares.Outstanding             : num  419 430 467 465
#$ year                                        : chr  "2015" "2014" "2013" "2012"

so, the financial statement object has been transposed so that each item on the statement (Balance Sheet in this case) becomes a column and can be written to a database table

score 0 · Answer 3 · answered Mar 05 '16 at 01:16

0

ticker = "AAPL"
period = 'A'
statements = getFin(ticker, auto.assign=FALSE)
bs = viewFin(statements, type="BS", period=period)
is = viewFin(statements, type="IS", period=period)
cf = viewFin(statements, type="CF", period=period)
bs.df = data.frame(bs)
is.df = data.frame(is)
cf.df = data.frame(cf)

You can then check to make sure they're all of data.frame class like so.

> is.data.frame(bs.df)
[1] TRUE

> is.data.frame(is.df)
[1] TRUE

> is.data.frame(cf.df)
[1] TRUE

answered Mar 05 '16 at 01:16

Jacob Amos

996
11
18

thanks for the answer. the second statement in my snippet does the same thing. Need each item from those statements (list total assets, total income...) as a column on my data frame / data table... if i were to do this let's say, for S&P 500, it'd be easier to lookup any item like "total assets" for any company during any year. – pranav Mar 05 '16 at 19:26
Is there a python equivalent for this? – D.prd Jul 18 '17 at 03:47

score 0 · Answer 4 · answered Mar 08 '16 at 04:19

This should give you what you want.

require(quantmod)
setwd("C:/Users/rshuell001/Desktop/downloads")
stocks <- c("AXP","BA","CAT","CSCO","CVX","DD","DIS","GE","GS","HD","IBM","INTC","JNJ","JPM","KO","MCD","MMM","MRK","MSFT","NKE","PFE","PG","T","TRV","UNH","UTX","V","VZ","WMT","XOM")

# equityList <- read.csv("EquityList.csv", header = FALSE, stringsAsFactors = FALSE)
# names(equityList) <- c ("Ticker")

for (i in 1 : length(stocks)) {   
        temp<-getFinancials(stocks[i],src="google",auto.assign=FALSE)
        write.csv(temp$IS$A,paste(stocks[i],"_Income_Statement(Annual).csv",sep=""))
        write.csv(temp$BS$A,paste(stocks[i],"_Balance_Sheet(Annual).csv",sep=""))
        write.csv(temp$CF$A,paste(stocks[i],"_Cash_Flow(Annual).csv",sep=""))
        write.csv(temp$IS$A,paste(stocks[i],"_Income_Statement(Quarterly).csv",sep=""))
        write.csv(temp$BS$A,paste(stocks[i],"_Balance_Sheet(Quaterly).csv",sep=""))
        write.csv(temp$CF$A,paste(stocks[i],"_Cash_Flow(Quaterly).csv",sep=""))
}

importing financial statements from getFin() to data.frame or data.table?

4 Answers4