+ Reply to Thread
Results 1 to 6 of 6

Thread: Convertion of Excel formulas to R script

  1. #1
    Points: 84, Level: 1
    Level completed: 68%, Points required for next Level: 16

    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convertion of Excel formulas to R script




    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. #2
    Points: 8,120, Level: 60
    Level completed: 85%, Points required for next Level: 30

    Posts
    169
    Thanks
    1
    Thanked 7 Times in 7 Posts

    Re: Convertion of Excel formulas to R script

    R is a vector orientated programming language. Thus, as far your your dataframes would be named sheet1, sheet2, ... with columms a, b, c, ...

    Quote Originally Posted by Cynclida View Post
    =Sheet1!A2
    =Sheet1!A3
    =Sheet1!A4
    equivalent
    sheet0$a= sheet1$a

    Quote Originally Posted by Cynclida View Post
    =B2+C2
    =B3+C3
    =B4+C4
    =B5+C5
    equivalent
    sheet0$d= sheet1$b + sheet1$c

    Quote Originally Posted by Cynclida View Post
    =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.
    Prediction is very difficult, especially about the future. (Niels Bohr)

  3. #3
    Points: 84, Level: 1
    Level completed: 68%, Points required for next Level: 16

    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convertion of Excel formulas to R script

    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. #4
    Points: 8,120, Level: 60
    Level completed: 85%, Points required for next Level: 30

    Posts
    169
    Thanks
    1
    Thanked 7 Times in 7 Posts

    Re: Convertion of Excel formulas to R script

    Quote Originally Posted by Cynclida View Post
    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
    Prediction is very difficult, especially about the future. (Niels Bohr)

  5. #5
    Points: 84, Level: 1
    Level completed: 68%, Points required for next Level: 16

    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convertion of Excel formulas to R script

    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.

  6. #6
    Human
    Points: 12,908, Level: 74
    Level completed: 15%, Points required for next Level: 342
    Awards:
    Master Tagger
    GretaGarbo's Avatar
    Posts
    1,394
    Thanks
    458
    Thanked 471 Times in 411 Posts

    Re: Convertion of Excel formulas to R script


    Make a reproducible example!

+ Reply to Thread

           




Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats