Feature Request: Cached R objects output to Excel

[bmanngal]

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

[duncanwerner]

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)?

[bmanngal]

The cell shows a key string https://www.pyxll.com/docs/examples/objectcache.html

[duncanwerner]

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.

[bmanngal]

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

[asmquant]

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