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