is there any tool to convert an Excel sheet consisting of formulas to a program script so that the formulas and not the values are kept? My favorite target would be an R script, but convertion to any programming language or even Excel macro would be very helpfull for me.

For demonstration I constructed a small example: Starting point is an excel file consisting of 2 sheets where the first one contains the basic data and the second one some calculations done with the data from the first sheet. The following tables show the data sheet an the calclation sheet in values (which would be easy to import to R) and in formulas view:

Excel-Sheet1(Sh1)

#N/A #N/A #N/A

10/1/17 1 6 5

10/2/17 5 3 2

10/3/17 2 1 9

10/4/17 4 7 8

Excel-Sheet2 values

#N/A #N/A #N/A

10/1/17 1 6 7 # 5 35 #N/A

10/2/17 5 3 8 4 2 16 11

10/3/17 2 1 3 6 9 27 25

10/4/17 4 5 9 7 8 72 63

Excel-Sheet 2 formulas

=Sh1!B1 =Sh1!C1 =Sh1!D1

=Sh1!A2 =Sh1!B2 =Sh1!C2 =B2+C2 =B1+C2 =Sh1!D2 =F2*D2 =G2-F1

=Sh1!A3 =Sh1!B3 =Sh1!C3 =B3+C3 =B2+C3 =Sh1!D3 =F3*D3 =G3-F2

=Sh1!A4 =Sh1!B4 =Sh1!C4 =B4+C4 =B3+C4 =Sh1!D4 =F4*D4 =G4-F3

=Sh1!A5 =Sh1!B5 =Sh1!C5 =B5+C5 =B4+C5 =Sh1!D5 =F5*D5 =G5-F4

The desired result should be an R script 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)

}

data <- read.csv(Sh1,stringsasFactors=F) # or something like this

A <- data[,1]

rownames(data) <- A

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)

This is a really small example, the file I have to "convert" or to "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