How to export an R Random Forest model for use in Excel VBA without API calls

user_3023735 0
Viewed 3481

Problem:

I have a Random Forest model trained in R. I need to deploy this model in a standalone Excel tool that will be used by 350 people across a sales network to perform real-time predictions based on data entered into the spreadsheet by users.

How can I do this?

Constraints:

  • It is not an option to require users to install R on their local machines.

  • It is not an option to have a server (physical or cloud) providing a scoring API.

What have I done so far?

1. PMML

I can export the model in PMML (XML structure). From research I can see there are libraries for loading and executing PMML inputs in Python and Java. However I haven't found anything implemented in VBA / VB.

2. Zementis

I looked into a solution called Zementis which offers an Excel add-in to deploy PMML models. However from my understanding this requires web-service calls to a cloud server (e.g. AWS) where the actual model execution happens. My IT security department will not allow this.

3. Others

The most common recommendation seems to be to call R to load the model and run the predict function. As noted above, this is not a viable option.

Detailed Context:

The Random Forest model is trained in R, with c. 30 variables. The model is used to recommend "personalised" prices for products as part of a sales process.

The model needs to be distributed to the sales network, with about 350 users. The business's preference is to integrate the model into an existing spreadsheet tool that sales teams currently use to calculate deal profitability.

This means that I need to be able to export the model in a way that it can be implemented in Excel VBA.

Given timescales, the implementation needs to be self-contained with no IT infrastructure or additional application installs. We are working with the organisation's IT team on a server based solution, however their deployment timescales are 12 months+ which means we need a tactical solution in the short-term.

machine-learning random-forest r vba excel
2 Answers

Here's one approach to get the "rules" for the trees (example using the mtcars dataset)

install.packages("randomForest")
library(randomForest)

head(mtcars)
set.seed(1)
fit <- randomForest(mpg ~ ., data=mtcars, importance=TRUE, proximity=TRUE)
print(fit)

## Look at variable importance:
importance(fit)

# Print the rules for each tree in the forest
install.packages("rattle")
library(rattle)
printRandomForests(fit)

It is probably unrealistic to use the rules for 500 trees, but maybe you could implement 100 trees in your vba and then take an average of the results (for a continuous response) or predict the class with the most votes across the trees (for a categorical response).

user_85723540

Maybe you could recreate the model on a Worksheet. As far as I know, Excel can import XML structures (on the Development Tools ribbon).

Edit: 1) save pmml structure in plaintext editor as .xml file.

2) Open the file in Excel 2013 (maybe other versions also do it)

3) Click through the error message and open the file anyway. Trees open as a table, a bit funny, but recognizable.

4) Create prediction calculation (generic fn in VBA) to operate on the tree.

user_87008730
Related