Insert data into excel, calculate+retrieve data using Python

[Hercuflea]

Hello,

I currently have a Google Sheets document that I want to run some analysis on.

The document is very formula- heavy (not just raw data). It takes a set of input numbers and does some computations and gives me an important output number at the bottom of the spreadsheet. These computations would be really hard to implement in a programming language, I need the cells of a spreadsheet (it is financial data).

What I want to do is use python to run through a range of numbers, input them into the spreadsheet, have the spreadsheet run its formulas on the input, and then have python retrieve the output, store in an array, and plot the results in Matplotlib. I need to do this about 100,000 times (preferable more even)

Is this possible?

 

[jim mcnamara]

Yes, but you will need to use python libraries made specifically for interfacing with Excel.

Try here: http://www.python-excel.org/

There are several good choices depending on what you want to do specifically.

 

[StoneTemplePython]

It's been a couple of years since I've done this -- I ultimately found each of the tools to be quirky in their own way. I think I liked 'xlwings'.

Honestly if you want something that's really low level and simple -- and you're on Windows -- I had some luck using win32com. This quite literally is used to 'drive' other programs via python commands. I in particular used it on excel files, though it works on other applications, as I recall.

E.g. here's a youtube video I found on it (and there are probably a lot more).



As always, have a backup / archive of your files before your run a script on them in case it obliterates things due to a bug.

 

[Hercuflea]

Hmmm...Interesting I'll give it a look. I figured out how to write a script in excel VBA for a single case, but I much prefer working with numpy and matplotlib.

All of the python excel packages I've looked at will let you update a "static" excel file, but apparently you can't dynamically update an excel workbook while letting the workbook compute its own functions on the data.

Right now its looking like I'm going to have about a 42 million x 2 single precision array in my VBA script. I would really like a way to just transfer that array directly to numpy from VBA in memory, rather than writing to file and reading in numpy. Do you think that'd possible? I have 16GB of memory.

 

[StoneTemplePython]

Originally your post said you were calling spreadsheet formulas, but now it seems there are VBA scripts running. If you want something simple, again wincom32 should allow you to do it. E.g.

https://stackoverflow.com/questions...-vba-functions-and-subs-using-python-win32com

If VBA uses C or fortran arrays (i.e. like an actual numeric programming language does), there should be a way to do it 'properly', but I don't know much more than that as I have no interest in VBA.

One common way of communicating across different languages is to use something like Popen and PIPE from the 'subprocess' module in python -- they can basically grab what has been 'printed' by that other program, normally something I'd do for an array with hundreds or thousands of entries -- not something I've done with millions of entries in it but I guess you could give it a shot.

 

[Hercuflea]

After playing with it some more, I decided win32com is too complicated to bother with.

I converted the file to ODS, and I converted my script from VBA to python. Got it running decently but it is ~200x slower running in python and LibreOffice than it was with VBA and Excel! Alas, there's always more work to be done.

I'm going to look into LibreOffice scripting some more. The lag seems to be coming from the calls to the LibreOffice API.