sdllc
Feature Request: Cached R objects output to Excel
Hello, I have recently started using BERT and still learning my way around it but think its excellent. Im wondering if its possible to add a feature where a R. function call in excel can return a cached R object. For example, a calibrated spline, or a fitted lm() object. In that case we could use another function that references the object and performs calculations in R. I am thinking something along the lines of passing a dataframe from excel to fit linear regression and output the object to a cell on a sheet. Then use another function that takes the object as argument and another dataframe from the sheet and I can use predict method on the cached lm() object. This is a very useful feature in pyxll that integrates python in excel and I think would be very handy in BERT.
thank you
That shouldn't actually be possible, but if pyxll does it then I guess it is. Do they use some sort of look-up table?
Let me ask you this: what shows up in the Excel cell when you do this (reference a python object)?
The cell shows a key string https://www.pyxll.com/docs/examples/objectcache.html
Interesting. That is (basically) a lookup.
It does require some knowledge on the part of the functions (i.e. specifying argument type and return type), which is unfortunate -- meaning you can't use the same function on a data frame (in R) or a cached frame (from Excel).
It might be possible to short-circuit all that and do the lookup automatically based on a specially formatted cell value. That might actually be faster than calling xlfCaller, and it would allow for transparency, at least for the second function.
I'll poke at it.
Thanks Duncan, i would be interested to hear your thoughts. I think this would be a powerful feature. It can be used in many ways besides what I mentioned above:
- You can perform various analysis on data and return list of data frames as an object (for example trade blotter for a backtest and portfolio level statistics) and extract individual list components in different ranges via simple extractor functions.
- cached xts objects for data from yahoo or bloomberg that can be combined an manipulated with other functions.
- calibrated option models that can then be used to price different option structures defined in a spreadsheet.
etc
Hello Duncan, I am a new BERT user. I think the tool is fantastic. I also think a cached object would be extremely helpful. Is this something that is under consideration for the next version? thank you
