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.