Convertion of Excel formulas to R script

#1
Hi all,

is there any tool to convert an Excel sheet consisting of formulas to an Rscript so that the formulas and not the values are kept?

For demonstration I constructed a small example: Starting point is an excel sheet contaning some calculations done with the (timeseries) data from another sheet. The following table shows the calclation sheet formulas' view:

=Sheet1!B1 =Sheet1!C1 =Sheet1!D1
=Sheet1!A2 =Sheet1!B2 =Sheet1!C2 =B2+C2 =B1+C2 =Sheet1!D2 =F2*D2 =G2-F1
=Sheet1!A3 =Sheet1!B3 =Sheet1!C3 =B3+C3 =B2+C3 =Sheet1!D3 =F3*D3 =G3-F2
=Sheet1!A4 =Sheet1!B4 =Sheet1!C4 =B4+C4 =B3+C4 =Sheet1!D4 =F4*D4 =G4-F3
=Sheet1!A5 =Sheet1!B5 =Sheet1!C5 =B5+C5 =B4+C5 =Sheet1!D5 =F5*D5 =G5-F4

The desired result should be an R script like this:

data <- read.csv(Sheet1,stringsasFactors=F) # or something like this
A <- data[,1]
rownames(data) <- A # dates column
B <- as.xts(data[,2])
C <- as.xts(data[,3])
F <- as.xts(data[,4])
D <- B+C
E <- Offset(B,1)+C
G <- F*D
H <- G-Offset(F,1)

where Offset is a predefined function like this:
Offset <- function(x,n) { # function for indices from other lines
if(n>0) {
r<-x[-((nrow(x)-n+1):nrow(x)),]
index(r)<-index(x)[(n+1):nrow(x)]
} else {r<-x}
as.xts(r)
}


This is a really small example, the file I have to "convert" or "translate" consists of more than 10 sheets, each of them containing thousands of columns.

My first attempt was selecting one row of the formula view, transposing it and performing some search-replace procedures. But Excel and R are too different so that this procedure would take rather years then months, and I do not have so much time. Internet research for a powerfull tool remained without success.

Thanks in advance
Petra
 
#2
R is a vector orientated programming language. Thus, as far your your dataframes would be named sheet1, sheet2, ... with columms a, b, c, ...

=Sheet1!A2
=Sheet1!A3
=Sheet1!A4
equivalent
sheet0$a= sheet1$a

=B2+C2
=B3+C3
=B4+C4
=B5+C5
equivalent
sheet0$d= sheet1$b + sheet1$c

=F2*D2
=F3*D3
=F4*D4
=F5*D5
equivalent
sheet0$g= sheet1$f * sheet1$d

However, you are not restricted to stupidly reference data excel like in A, B, C columns any more. In R you can give each data column by an intuitive name and reference the data by it. E.g. sheet0$sales for the whole column and sheet0$sales[1] for the first cell of the column.
 
#3
Thank you for your answer.

But do I really have to rewrite the whole Excel file in R manually?

Or are there functions in some R-package (e.g. readxl, tidyxl, XLConnect) that can help me to speed up the procedure? Perhaps somebody has allready experience with one or more of those packages.

Final goal is to do future calculations in R instead of Excel.
 
#4
But do I really have to rewrite the whole Excel file in R manually?
Or are there functions in some R-package (e.g. readxl, tidyxl, XLConnect) that can help me to speed up the procedure?
Maybe there is. But no one does so, as Excel is d*... f******* error generator. You would import the errors from your excel.

Anyway. Migrating your Excel to R is a very suitable beginners lesson.

Consuli
 
#5
It is not "my" Excel. An Excel sheet made by a former colleague provides the inputs for my R script. Until now I always saved the Excel results as csv and imported the csv to R. But now the Excel sheet has been abandoned by his owner and it has to be modified. So my idea was to transfer the calculations to R in order to get rid of f****** Excel. I really hoped there was some R package to accelerate this stupid task, but it seems there isn't any.

May be I have to look for a solution from the Excel side ... although I am not familiar at all with it.