Serving the Quantitative Finance Community

 
User avatar
tags
Topic Author
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Modeling a spreadsheet with a programming language

July 9th, 2021, 3:17 pm

What are recommendations to model an Excel spreadsheet with a programming language, e.g. python (but not restricted to it)?
A "spreadsheet" is a workbook, that contains worksheets, that contains ranges/cells. Workbooks and worksheets have countless properties. There are also countless methods to set range/cells values, formats, etc...   (apologies for the wording "properties", "methods" that may note be so Excel-like).
E.g. I would like my code opens a workbook, gets into a workbooks, reads and set cells values, also controls calculation mode of the workbook, etc ...
What is the right design to "code a spreadsheet", please?  What shall be a property or method of my class?  etc... ?
I mean it is not crazy use case. Someone else must have already done it.
Any thought, source, snippet code-ready-to-use much welcome.
Merci.
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: Modeling a spreadsheet with a programming language

July 9th, 2021, 8:52 pm

Mikael (@MikeJuniperhill) is the #1 expert here

http://mikejuniperhill.blogspot.com/201 ... -swap.html
 
User avatar
bearish
Posts: 5186
Joined: February 3rd, 2011, 2:19 pm

Re: Modeling a spreadsheet with a programming language

July 10th, 2021, 2:43 am

My only two cents would be to point out that at least the basic Excel structure is inherently functional rather than object oriented. The content of a cell is either a constant or a function of one or more other cells. The fact that a function has some cells as an argument does not allow said function to modify their value. These functional relationships can be nested (in principle) arbitrarily deep, and it is the job of the Excel dependency graph to keep track of what needs to be updated when something changes. You surely don’t want to replicate all that in your code. Experience suggests that it is usually not all that hard to replicate the functionality of a particular spreadsheet/workbook in code, or to write a program that generates a “live” spreadsheet with certain key ingredients (like calling Bloomberg). Writing a generic Excel replacement in whatever language? Good luck with that!
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

Re: Modeling a spreadsheet with a programming language

July 10th, 2021, 6:26 pm

Most(?) programming interfaces use the Excel Object Model, e.g. C++, C#, VBA. I wrote for interfacing with MC, FDM etc. for visualisation and add ins. Very useful.

https://docs.microsoft.com/en-us/previo ... dfrom=MSDN

It may be functional but OO is easier.

Excel dependency graph to keep track of what needs to be updated when something changes. You surely don’t want to replicate all that in your code
Do you have an example? I can't remember if I ever implemented change notification in the object model. Most was input, click on button, run MC or FDM dll and produce output in Excel (no fancy chain reaction),

// this is all making me feel old.
 
User avatar
tags
Topic Author
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Modeling a spreadsheet with a programming language

July 10th, 2021, 7:25 pm

thank you @Cuch @bearish for your suggestions/comments.
I ended up writing a wrapper around the python library xlwings.
It is fairly basic, as it consists on is a single class "spreadsheet" in 50 lines. The python-Excel connection (connection to the Excel workbook object)  is set when the class is instantiated , Worksheet, ranges, values, etc ...  are accessed and set by properties of the class/changes in the properties.
I'll see if it does the job well in the coming weeks, then maybe refactor/improve the code and share it, if it is useful to someone.
thank you!
 
User avatar
Hernan
Posts: 5
Joined: April 2nd, 2002, 7:46 pm
Location: London
Contact:

Re: Modeling a spreadsheet with a programming language

October 28th, 2022, 2:55 pm

Hi @tagoma
How when your test with your wrapper and xlwings?
I'm looking to use xlwings with quantlib, and some info from previous tests would be great.
Tks
 
User avatar
tags
Topic Author
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Modeling a spreadsheet with a programming language

October 29th, 2022, 2:14 pm

Hi @Hernan,
I haven’t run any specific performance testing.
On daily basis though, the general feel is you slow down to Excel standard use. Not better, not worse.
 
User avatar
Hernan
Posts: 5
Joined: April 2nd, 2002, 7:46 pm
Location: London
Contact:

Re: Modeling a spreadsheet with a programming language

October 31st, 2022, 2:23 pm

Tks @tagoma.
By any chance, is your wrapper open-source, or would you mind sharing it?
 
User avatar
tags
Topic Author
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Modeling a spreadsheet with a programming language

October 31st, 2022, 3:33 pm

This is stuff I did for work, hence I cannot share.
To be honest it is not rocket-science. It is mere wrapper around xlwings. Basically (as touched in a previous post) it is something like a class Workbook that takes the filename of a wokbook as input argument. Filename is used to instantiate a xw.Book() object in __init__ and then I wrote as many pyhon properties and functions I needed to navigate across worksheets, rename, open, read, write, compute, save, close the workbook, etc...
 
User avatar
Hernan
Posts: 5
Joined: April 2nd, 2002, 7:46 pm
Location: London
Contact:

Re: Modeling a spreadsheet with a programming language

October 31st, 2022, 5:04 pm

Tks Tagoma
 
User avatar
tags
Topic Author
Posts: 3162
Joined: February 21st, 2010, 12:58 pm

Re: Modeling a spreadsheet with a programming language

October 31st, 2022, 5:54 pm

We are looking forward to hearing again about your project!