1

01 - BACKGROUND:

I'm a typical non-programmer R user for 6+ years. Which means:
i) I`m comfortable with the language most of the time and have written R packages for myself, but
ii) writing C++ code (Rcpp) is not something I've done
iii) R is fast enough for my purposes and data size

02 - PROBLEM STATEMENT:
Few months ago I started at a new company where my peers do not use R/Python, only MS-Excel. I want to share my statistical analyses/workflow with them.

03 - WHAT I TRIED, AND POTENTIAL SOLUTIONS:

  • First option that came to mind was to write a simple UDF library in pure VBA (.xlam). It's easy to distribute and update, since everyone has Excel/VBA installed in their machines. I've started to work down this road BUT found myself reinventing the wheel for very basic tasks (fit a few distributions to data, eg.), and limited by the VBA capabilities on data analysis. Something similar to what I would like to accomplish would be: https://www.real-statistics.com/ + our own analysis workflow.
    Conclusion: I understand it will be (very) hard to re-write all the routines I used to do in R in a vba-xlam.

  • Second option : PyXLL. Nope; peers won't install Python on their machines.

  • Third option: ask peers to install two Excel addins: MyLibrary.xlam and QuantLibXL to harness the power of Quantlib on statistics and use some VBA to put everything together. I haven't tried this since it seems that QuantLib is really focused on pricing derivatives and I will still have to write huge VBA code to fit a Log-Logistic distribution to data on a spreadsheet, for example. Something that is a one-liner in R.

  • Fourth option: bite the bullet and learn C++. Read a book on XLL development something like this and build the XLL add-in using statistical libraries made on/for C++. Easy distribution, also free to build, BUT dive in a whole new universe that I can find hard to navigate (real programming, not R scripting).

  • Fifth Option: Use Excel-DNA to write my library in VB.Net (so I can use things like Accord.NET). Easier than (fourth) because VB.Net easier than learning C++ ?

04 - MY QUESTIONS

  • 1/ Am I missing any obvious alternative here ?
  • 2/ Which of the options above would you suggest and why ?

05 - SUPPORTING MATERIAL

  • This is a great thorough answer from 2014 and somewhat related to the above. Yes, I've done my homework and read about all of the options before asking.
  • I am aware of Deriscope. Seems overkill compared to what I want to accomplish (a library of ~50 functions that will make analyses faster in our department). Plus I'll be dependent on a third-party solution.
11
  • If someone could please add the appropriate tags, I'd appreciate it. New user with limited privileges here.
    – Dan
    Dec 16 '20 at 17:40
  • please don't cross-post: stackoverflow.com/questions/65328608/… "Cross-posting is frowned upon as it leads to fragmented answers splattered all over the network..."
    – gnat
    Dec 16 '20 at 18:04
  • 1
    ... however, the C++ road would definitely be overkill.
    – Doc Brown
    Dec 16 '20 at 20:46
  • 1
    @Dan: as I said, I would use Excel.DNA. If you use VB.Net or C# with Excel.DNA is more or less a matter of taste. If you know VBA already, the learning curve for VB.Net may be less steep. For statistical analysis you will definitely need an additional component, probably one of those listed at the Q&A from 2014 you already linked to.
    – Doc Brown
    Dec 16 '20 at 22:00
  • 1
    You should add BERT, the Basic Excel R Toolkit to your list of options: bert-toolkit.com and github.com/sdllc/Basic-Excel-R-Toolkit/issues.
    – Govert
    Dec 20 '20 at 20:25
3
+50

You can install BERT as an add-in in Excel. Then in Excel you can enter the R console for basic coding and debugging. And for the clients, you wrap your R function into a new file saved to the startup folder, so BERT can load all the functions from there, and clients can choose whatever they want in Excel directly.

customized_function <- function(data){
  ## Your original R code
}

Other than that, your second option on PyXLL should work.

peers won't install Python on their machines.

According to their doc, to deploy the Python environment people don't have to install Python locally, instead, you can take a Python environment and copy it to a network drive and have PyXLL reference it from there.

Another option is to use their pyxll-installer as the Python runtime is bundled with PyXLL into a single standalone installer instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.