RPGM 2.0 Docs

Tutorial: Excel

In this tutorial, using a small program, we will learn how to use the Excel functions to read and write data or results in an xlsx file.

Begin with a new project with File -> New -> Project and name it Excel.
Open Excel.ppro which is the main file for your project and set Company name to the one you want. We will use a logo to display at the top of the Client, you can download it here and place it in the project folder. The easiest way is to click on the folder icon at the bottom right of the File explorer which displays the folder of the project in your operating system. Once added, drag and drop it in Top logo which will be set with logo_excel.png:

Project

Download the file Data.xlsx which contains example data of deaths of a full population. nx represents the number of survivors at age x and dx the number of deaths between x and x+1.

We will create an User Interface which will ask for this kind of xlsx file with, by default, this one.

Right click on the File explorer and choose Create GUI… and call it Inputs. Add this file as a step in main.pseq. Remember that you can drag and drop it from the File explorer to main.pseq. Do not forget to link it with an arrow using the mouse:

Sequence

Add the following widgets:

  • Type: Label, Value: Inputs, Font size: 24.
  • Type: File chooser, ID: data_path, Value: rpgm.pgmFilePath(“Data.xlsx”), R expr.: Checked, Required: Checked.
  • Type: Label, Value: On a Worksheet “data”, contains a column nx and a column dx, Margin top: 11.

GUI

Press F5 to see the current program. Now, we are ready to read the data. Create a new script.R file and add it to the sequencer. In the R file, write:

xlsx.open(data_path)
xlsx.selectWorksheet("Data")
data <- xlsx.getCells("A2:B120")
data <- data[rowSums(is.na(data)) == 0,]

The first line opens the xlsx file. The second one selects, for RPGM, the worksheet on which we will get the data. The third line reads the table from range A2:B120 in the worksheet Data of the file in data_path. Note that the table in the xlsx file ends before the row 120, then it is filled with NA values. The fourth line removes these lines.

Continue the code with:

m <- nrow(data)
nx <- data[, 1]
dx <- data[, 2]
qx <- dx/nx

The variable qx represents the death rate. The aim of qx is to estimate the death rate of the people according to the deaths observed. However, for the high ages, the number of survivor to observe is small and the estimator has a high variance. To reduce the variance, with a cost of bias, we will use the Whittaker-Henderson technique in which the purpose it to smooth the function x -> qx. The goal of this tutorial is not about this method but to manage the Excel functions, so we will not detail how it works.

Therefore, the full code is:

xlsx.open(data_path)
xlsx.selectWorksheet("Data")
data <- xlsx.getCells("A2:B120")
data <- data[rowSums(is.na(data)) == 0,]

m <- nrow(data)
nx <- data[, 1]
dx <- data[, 2]
qx <- dx/nx

F <- function(qx, w)
    return(sum(w*(qx-cx)^2))

getKz <- function(m, z)
{
    if(m < z)
        stop("Not enough years to compute Kz")
    kz <- choose(z,0:z)*(-1)^(0:z)
    Kz <- matrix(0, m-z, m)
    for(i in 1:(m-z))
        Kz[i, i:(z+i)] <- kz
    return(Kz)
}

getqliss <- function(qx, h, W, z, m)
{
    Kz <- getKz(m, z)
    return(solve(W + h*t(Kz)%*%Kz)%*%W%*%qx)
}

W <- diag(nx)

graph <- function(qx, h, W, z, m)
{
    png("qx_smooth.png")
    qxl <- getqliss(qx, h, W, z, m)
    plot(0:(m-1), qx, type = "l", xlab = "x", main = "qx estimation with Whittaker-Henderson")
    lines(0:(m-1), qxl, col = "green")
    legend("topleft", legend = c("qx","smooth qx"), lty = 1, col = c("black", "green"))
    dev.off()
    return(qxl)
}

h <- 2500
qxl <- graph(qx, h, W, 1, m)

The last function, graph, creates a graph which compares the original qx to qxl which is the one smooth. The variable h tell how smooth it is. h = 0 corresponds to qx and the higher h is, the smoother qxl will be. We want to choose h using a graphic with qx and qxl generated with the function graph. To do that, we will create a GUI which will display the graphic, add a Slider which will contain the value h and update dynamically the graphic with the slider in order to select h and the qxl:

Script

Press F5 to look at the graphic in the output folder. Now right click on the File explorer and choose Create GUI…, name the file Smooth and add it to the sequencer.

Add the following widgets:

  • Type: Label, Value: Smooth qx, Font size: 24.
  • Type: Image, ID: img_qxl, Value: qx_smooth.png.
  • Type: Slider, ID: h, Label: Smooth coefficient, Value: 2500, Minimum value: 0, Maximum value: 5000.

GUI

Press F5, you can see the graphic displayed, however, it is not yet dynamic, moving the slider has no effect. To make it dynamic, add the following code in R on change in the widget Slider:

qxl <- graph(qx, h, W, 1, m);gui.setValue("this", "img_qxl", "qx_smooth.png");

When the slider will be moved, its value, h (the ID) will be changed and the line of code in R on change of the Slider will be executed. Thus, the function graph will update the graph in the output folder with the new h. The function gui.setValue will then change the value of the widget with the ID img_qxl, which is the Image with the path of the graph. Here it is the same as before but the call of the function will update the graphic of img_qxl. The value "this" tells that the widget is in the current executing GUI. For more details, refer to the Tutorial based on the R on change.

Now press F5 and see how the graphic is updating as you move the slider:

Client

Choose one value of h. Now it is time to create an xlsx file with the value of qxl and add the graphic in the spreadsheet. Create a new R script, name it Excel and add the following code:

xlsx.open(data_path)

xlsx.copyWorksheet("Data", "qx")
xlsx.addColumns("A", 1)
xlsx.setCell("A1", "age")
xlsx.setCell("A2",0:(m-1))
xlsx.setCell("D1", "qx")
xlsx.setCell("D2", qx)

xlsx.addWorksheet("Table")
xlsx.setCell("A1", "age")
xlsx.setCell("A2", 0:(m-1))
xlsx.setCell("B1", "qxl")
xlsx.setCell("B2", qxl)
xlsx.insertImage("qx_smooth.png", "G4")

xlsx.saveAs("Table.xlsx")
  • The first line opens the Excel file with the data on which we will base our output
  • xlsx.copyWorksheet("Data", "qx") creates a new worksheet qx which is the copy of Data.
  • xlsx.addColumns("A", 1) adds a column in front
  • xlsx.setCell("A1", "age") and xlsx.setCell("A2",0:(m-1)) fill this column with the age and the two next line fill the fourth column with qx.
  • xlsx.addWorksheet("Table") creates a new worksheet named Table. The fourth following lines fill it with the age and qxl with the h chosen.
  • xlsx.insertImage("qx_smooth.png", "G4") adds the graphic created with the h chosen.
  • xlsx.saveAs("Table.xlsx") save the new file created in the output folder.

You can download the program of this tutorial as a pgm file for the Client here and as a zip file containing all the project for the Editor here.