xlcalculator needs to be able to write Excel files.

[bradbase]

Currently xlcalculator cannot write an Excel file.

It's likely that someone will need to take the xlcalculator Model object and create an Excel file from it at some stage.

[strichter]

I would use openpyxl to write files. Simply load the file again, fill in the data you like and save it again. No need to burden xlcalculator with this task.

(In fact I would use openpyxl to read files too since it would avaoid many of the bugs I am seeing now.)

[pwalimbe]

Hello I was experimenting with this library and found it useful for doing quick formula re evaluation on large sets of data w/o the need of excel. I was wondering if you have used pandas dataframe in the background and is there any way to get the cells array as a df? that would be really useful in our quant project. There is really no need for me to get an excel output - but need to dump the array as such. the json format is not really suited for me as an output Thanks, Prasanna

[bradbase]

Hi @pwalimbe ,

Thanks for making contact.

I am grateful to hear you've found this library useful. And I'm happy to help you through this journey as I've been down through this rabbit warren.

There are a number of techniques used in the background of xlcalculator. Pandas is used. As is Numpy, Numpy-Financial and Python built-in operations. The use of Pandas and Numpy is the reason the mathematical operations are so rapid.

This library does not use a DataFrame as a base type. Reason being; A DataFrame is not an very good representation of an Excel worksheet let alone a workbook.

There are other tools which can be used for the operation you are asking about. Namely xlwings and, potentially, PyXll.

PyXll is a commercial product. I have not used it.

xlwings has both a community edition and a professional (commercial) edition. I am a great fan of this project. I have contributed to the project by introducing Anaconda support to it. Xlwings is a COM wrapper (two-way communication between Excel and Python) which does data conversion supporting Pandas (DataFrames) and NumPy data types. Xlwings is great as it allows you to call VBA modules from Python and introduce User Defined Functions (UDF) written in Python.

Inspired by my work in quantitative development and quantitative analysis I am currently writing a product which integrates xlcalculator with xlwings. In essence providing a real-time interactive use of xlcalculator while in Excel. In my opinion the best feature is being able to define a calculation in Excel but execute that definition in a UDF, allowing you to change the calculation definition dynamically. The intent is to bring domain specialists closer to data by using Excel as the user interface for defining xlcalculator models and subsequently applying them to data locally and on servers. There is a prototype I have released under the project FlyingKoala which is accompanied by a worked example. The version of FlyingKoala on GitHub still uses the Koala library (which has atrophied) but I have a version which I've re-written using xlcalculator which I expect to release as a commercial value-add. I would very much like to speak with you through another channel to see if I can partner with you and your quant's regarding potential use of FlyingKoala.