CODEWITHSUNDEEP
rimport
user113156
user113156

Reputation: 7147

Importing excel balance sheet into R

I am trying to import into R an excel spreadsheet of a balance sheet, I am trying to import it so it would look more or less like the balance sheet does now.

Assets                2011,    2010,   2009
Non current assets    32.322   3.111   
intangible assets     12,222

something along those lines. I am also trying to import the second tab also which is a different balance sheet. The idea is that I will probably have 50 or more balance sheets. Would this be inefficient for analysis?

I am only interested in a few of the same variables from each of the balance sheet (think, current assets, non current assets for all the years etc.) is it possible to import just specific rows and columns from an excel spead sheet?

For instance just import;

A) Non current assets   32.322      3.111       322
B) Current assets   345     543     2.233

etc? - the row names do not change so could I use a function to do this?

Upvotes: 0

Views: 122

Answers (1)

ASH
ASH

Reputation: 20362

Look at quantmod!

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\\your_path_here\\Desktop\\bal_sheet.xlsx", row.names=FALSE)
transp <- read.xlsx("C:\\Users\\your_path_here\\Desktop\\bal_sheet.xlsx" , sheetName="Sheet1")
transp$year <- c("2015","2014","2013","2012")")

This is good too.

require(quantmod)

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

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

Also, check this out.

https://msperlin.github.io/pafdR/importingInternet.html

There are other ways to do very similar things.

Upvotes: 1

Related Questions