Deploying a Simple R Function in Excel using XLSTAT-R

 

Background

We use the rfit function from the {Rfit} package to estimate rank-based linear model coefficients, which are more robust to outliers than classical methods.

rfit requires a quantitative dependent variable and qualitative or quantitative independent variables.

We will also display a barchart of the regression coefficients using the XLSTAT chart engine.

 

Step 1: Open an XML Template and Fill out the Method Structure

XLSTAT-R features are written in XML code. A detailed documentation on the XLSTAT-R XML syntax is available here . The complete XML code linked to this tutorial can be found on the top-right of this page. To start developing our feature, we need to open an XLSTAT-R XML template. Click on R / Open an XML template .

The template opens in your default XML editor (set in XLSTAT options, XLSTAT-R tab), but editing with Sublime (Windows/Mac) or Notepad++ (Windows) is recommended.

You can start your project from an existing feature with similar input/output, found in %AppData%\ADDINSOFT\XLSTAT\XLSTAT-R\groups.

Next, fill in the Method structure attributes—the root of your XLSTAT-R feature project—by replacing the placeholder texts. Define key details like the feature name, input data structure, and required R libraries.

Fill the attributes as follows:

• text="Rank-Based Regression" — feature name

• datastructure="model" — data merged into an R formula (dependent and independent variables), typical for rfit

• function="rfit" — core R function name

• group="Rfit" — submenu name in XLSTAT-R and project folder name

• packages="Rfit" — required R packages (add more separated by commas, e.g., packages="Rfit,plotrix")

• family="Robust Linear Modeling" (optional)

• question="How can I obtain robust linear model estimates?" (optional)

• synonyms="Robust regression" (optional)

Fill method subnodes as:

<AuthorXML>John Smith</AuthorXML>  
<AuthorRFunction>John Kloke</AuthorRFunction>  
<CompatibilityXLSTAT>19.5</CompatibilityXLSTAT>  
<CompatibilityR>3.4.2</CompatibilityR>  
<DateCreated>2017.09.29</DateCreated>  
<DateModified>2017.09.29</DateModified>  
<PackageVersion>rfit 0.23.0</PackageVersion>  
<RDescription>Minimizes Jaeckel's dispersion function to obtain a rank-based solution for linear models.</RDescription>  

Update the dates and package versions as needed.

Use ?rfit in R for author info and sessionInfo() to get package versions.

Step 2: Save your XML template

Locate your XLSTAT-R\groups folder (usually at %AppData%\ADDINSOFT\XLSTAT\XLSTAT-R\groups).
Create an Rfit folder and save your XML template as rfit.xml. The folder name must match the method’s group attribute, and the XML file name must match the function attribute.

During development, regularly save your template, then open XLSTAT, go to the R menu, and click Refresh to update your project in XLSTAT-R.

 

 
3. Open your feature within the R menu.​ 

Step 3: Data Setup

 

Deciding on the Inputs

The first step is deciding which inputs to include in the XLSTAT-R interface. For this simple project, we use the R function’s arguments as inputs.

Load the Rfit package and check the rfit function help in R:

library(Rfit)
?rfit

The Usage section shows key arguments:
rfit(formula, data, subset, yhat0 = NULL, scores = Rfit::wscores, symmetric = FALSE, TAU = "F0", ...)

In this example, data will be merged into an R formula via the model data structure in XLSTAT-R. The user will control the symmetric Boolean and the TAU argument.

Deciding the Tab Structure

The user interface (i.e. the dialog box) is configured under the <!-- START USER INTERFACE --> section. The user interface is organized in tabs . Here we decide to include the classic XLSTAT dialog box tab structure: General , Options , Missing , Outputs and Charts .

 

Step 4: Configuring the General Tab

We begin by configuring the General tab to include:

• Quantitative dependent variable input

• Quantitative explanatory variables inputs

• Qualitative explanatory variables inputs

• Optional observation labels

Instead of generic DataField nodes, we use XLSTAT-R special nodes that merge inputs into an R formula and allow point-and-click configuration of terms and interactions.

The tab starts with:

<Tab text="#General" >

The # lets XLSTAT translate "General" based on language settings.

Then add:

<YQuantiField text="Dependent variable" controltip="Select your dependent variable" rname="data" />
<Spacer height="2" />
<XQuantiField rname="data" optional="true" optiondefault="true" />
<XQualiField rname="data" optional="true" optiondefault="false" />

• YQuantiField: for quantitative dependent variable; text sets label, controltip shows tooltip, rname links to R data frame.

• Spacer: adds vertical space (2 pixels here).

• XQuantiField and XQualiField: for quantitative and qualitative explanatory variables; optional adds a checkbox, optiondefault sets its default state.

Here’s how the user interface looks like at this point:

Configure the second part of the General tab with:

<NextColumn />
<VarHeaders text="Variable labels" />
<Spacer height="10" />
<ObsLabelsField rname="data" />
<Spacer height="6" />
</Tab>

• <NextColumn /> moves to the right side of the dialog box.

• <VarHeaders> is a checkbox to include variable names during data selection.

• <ObsLabelsField> lets the user select row/observation labels, which are passed to R.

• </Tab> closes the General tab configuration.

This completes the General tab setup.

 

Time to configure the Options tab now.

Step 5: Configuring the Options Tab

Include controls for interactions, symmetric, and TAU arguments in the Options tab:

<Tab text="#Options" >
  <Interactions reportgroup="1" />
  <CheckBox text="Symmetric" name="SymmetricAnalysis" rname="symmetric" default="false" reportgroup="2" />
  <Spacer height="6" />
  <ComboBox text="Routine" name="Routine" rname="TAU" default="0" left="55" width="100" reportgroup="3" >
    <Item text="Fortran" rname="F0" />
    <Item text="R" rname="R" />
    <Item text="None" rname="N" />
  </ComboBox>
  <Spacer height="6" />
</Tab>

• <Interactions> lets users select interaction levels in the model.

• <CheckBox> for the Boolean symmetric argument (default false).

• <ComboBox> for TAU selection from limited values, defaulting to "F0".

• Attributes like reportgroup include inputs in the results summary.

• left and width adjust ComboBox positioning and size.

Step 6: Configuring the Missing Data Tab

We keep the default missing tab configuration within the XML template. Those are special XLSTAT options for handling missing data. For more information, please refer to the

Step 7: Setting up the Outputs and Charts Tabs

Keep the Outputs and Charts tabs unchanged to auto-generate options based on the results specification:

<!-- OUTPUTS TAB OF THE DIALOG BOX -->
<Tab text="#Outputs" >
  <AutoOutputs stats="true" />
</Tab>

<!-- CHARTS TAB OF THE DIALOG BOX -->
<Tab text="#Charts" >
  <AutoCharts />
</Tab>

• AutoOutputs with stats="true" enables descriptive statistics display.

• These tabs dynamically adapt to what is specified later in the results section.

Step 8: Writing the R Code

Choosing the Analysis Results to Display in the Outputs

The easiest XLSTAT-R result format is a data frame. For an rfit model, the coefficients table (estimates and p-values) is useful but not a data frame by default.

Example R commands:

library(Rfit)
data(baseball)
fit <- rfit(weight ~ height, data = baseball)
sum_fit <- summary(fit)

names(sum_fit)
# [1] "coefficients" "waldstat" "waldpval" "overall.test" "call"

sum_fit$coefficients
# matrix with estimates, std. error, t-value, p-value

is.data.frame(sum_fit$coefficients)
# FALSE

# Convert to data frame for XLSTAT-R output:
coef_df <- as.data.frame(sum_fit$coefficients)

So, you must convert summary(fit)$coefficients to a data frame before returning it as a result.

Writing up the Code: 

R code is written in the XML template’s RScript node using ScriptLine subnodes.

Two methods exist, controlled by replacebyvalue in RScript:

• replacebyvalue="true": XLSTAT-R replaces XML variables (rnames) with their values directly in the script lines; simpler but limited for complex R object manipulation.

• replacebyvalue="false": Inputs are stored as R objects for more flexible coding.

This example uses the simpler replacebyvalue="true" method.

Writing the Code Using replacebyvalue="true"

Here is the concise R code section for your XML template:

<RScript replacebyvalue="true">
  <ScriptLine replace="true" code="results=rfit(formula,data,symmetric,TAU)" />
  <ScriptLine replace="true" code="results$CoefficientsDataFrame=as.data.frame(summary(results)$coefficients)" />
</RScript>

Explanation:

• Line 1 runs the rfit() function using dialog box inputs (formula, data, symmetric, TAU) automatically replaced with user values.

• Line 2 extracts and converts the coefficients summary to a data frame and stores it in results$CoefficientsDataFrame.

Result:
This code is automatically translated and saved as an .R script in XLSTAT’s working directory when the feature runs. Values are substituted in real time based on the user’s dialog box selections.

Step 9: Setting up the Outputs display in Excel

Return to the result report configuration using the <RResults> node. Each <Result> defines a result table or chart to display in Excel. Use this code to show the coefficients and an optional bar chart:

<RResults>
  <Result 
    text="Coefficients" 
    rname="CoefficientsDataFrame" 
    type="double" 
    rowlabels="rdesc" 
    collabels="rdesc" 
    chartname="Coefficients chart, R style" 
    charttype="r" 
    rplotformat="emf" 
    rplotcode="barplot(results$CoefficientsDataFrame[,1], names.arg=row.names(results$CoefficientsDataFrame), ylab='Estimates', xlab='Coefficients', main='Coefficients Bar Chart')" 
  />
</RResults>

This creates a table from CoefficientsDataFrame and a bar chart using R’s barplot() function. Check the Outputs and Charts tabs — corresponding checkboxes will now appear automatically.

At the bottom of the XML file, do not forget to close the method structure using the code below:
</Method>

Step 10 Using your New Feature in XLSTAT

Here are some screenshots of the feature executed on the XLSTAT. Download the dataset on the top right of the page. Open R / Rfit / Rank-Based Regression

Fill in the General tab as follows:

Activate the Interactions option in the Options tab.

Click OK .
In the dialog box that appears, select the terms you want to include in the model:

Select the terms you want to keep and click OK .
Interpret the results.