Koala is getting increasingly difficult to manage -- re-write?

[bradbase]

Hi Everyone,

I have a vested interest in increasing Koala's maintainability. My project FlyingKoala relies on Koala and I am trying to start a business off the back of it. Some of changes to Koala in the last 12 months, which have helped move Kola forward, have removed features I rely upon and have made it very difficult to re-instate the features I need.

I am in a situation where I have been putting effort into re-writing Koala so we (/I) can have a modernized code base to work from while supporting the features we all want and making the code maintainable.

The original intent was to just remove all the things that are in there for no reason, and keep as much as possible. This was quickly proven not realistically possible. So I've used the "good" bits -- the stuff that does the AST and evaluation -- and have been slowly re-writing the surrounding code.

I have a version now which supports:

• parsing cells, formulas and defined names into a dataclass representing a spreadsheet ("model")
• persisting and loading the model dataclass
• evaluation of cell values (eg; no formulas), and formulas which use cells

I am about 50% through supporting ranges. Ranges are being recognized in defined names and formulas and are being resolved to collections of cell addresses. I am today beginning work on having those collections of cell addresses packaged and passed to the formula functions.

Once Ranges are supported, the basics of Koala's features will have been successfully re-written. I have ignored OFFSET and INDEX for now. I am yet to test (or consciously support) unicode.

The ignore, input/output are not yet supported but need to be for feature parity (and are unbelievably useful).

And I'm yet to implement a method to set value.

Besides re-using a small amount of code for parsing formulas and executing the subsequent Python code, this is essentially a completely new codebase.

Is there an appetite to try and use a re-write for Koala moving forward or is it best this becomes a new project?

I'm happy to make the code available for critique.

[bradbase]

The current example of use:

import logging
from new_koala import ModelCompiler
from new_koala import Model
from new_koala import Evaluator

logging.basicConfig(level=logging.DEBUG)

filename = 'reader.xlsm'
compiler = ModelCompiler()
new_model = compiler.parse_excel_file(filename)

new_model.persist_to_json_file('brad_graph.json.gzip')

reconstituted_model = Model()
reconstituted_model.construct_from_json_file('brad_graph.json.gzip')
# reconstituted_model.draw_graph()
reconstituted_model.build_code()

evaluator = Evaluator(reconstituted_model)
val1 = evaluator.evaluate('First!A2')
print("value 'evaluated' for First!A2 without a formula:", val1)
val2 = evaluator.evaluate('Seventh!C1')
print("value 'evaluated' for Seventh!C1 with a formula:", val2)
val3 = evaluator.evaluate('Ninth!B1')
print("value 'evaluated' for Ninth!B1 with a defined name:", val3)
val4 = evaluator.evaluate('Hundred')
print("value 'evaluated' for Hundred with a defined name:", val4)
val5 = evaluator.evaluate('Tenth!C1')
print("value 'evaluated' for Tenth!C1 with a defined name:", val5)
val6 = evaluator.evaluate('Tenth!C2')
print("value 'evaluated' for Tenth!C2 with a defined name:", val6)
val7 = evaluator.evaluate('Tenth!C3')
print("value 'evaluated' for Tenth!C3 with a defined name:", val7)

Current output (defined name "Hundred" is cell Eighth!B1):

Cell First!A2 has no formula
but its value is 0.1

value 'evaluated' for First!A2 without a formula: 0.1
Evaluator.apply("add",self.eval_ref("Seventh!A1", ref = None),self.eval_ref("Seventh!B1", ref = None),None)
DEBUG:root:
Cell Seventh!A1 has no formula
but its value is 1

DEBUG:root:
Cell Seventh!B1 has no formula
but its value is 2

func add first 1 second 2 ref None
DEBUG:root:
Cell Seventh!C1 has a formula, A1+B1
which has been translated to Python as Evaluator.apply("add",self.eval_ref("Seventh!A1", ref = None),self.eval_ref("Seventh!B1", ref = None),None) which evaluates to 3

value 'evaluated' for Seventh!C1 with a formula: 3
Evaluator.apply("add",self.eval_ref("Ninth!A1", ref = None),self.eval_ref("Eighth!B1", ref = None),None)
DEBUG:root:
Cell Ninth!A1 has no formula
but its value is 1

DEBUG:root:
Cell Eighth!B1 has no formula
but its value is 100

func add first 1 second 100 ref None
DEBUG:root:
Cell Ninth!B1 has a formula, A1+Hundred
which has been translated to Python as Evaluator.apply("add",self.eval_ref("Ninth!A1", ref = None),self.eval_ref("Eighth!B1", ref = None),None) which evaluates to 101

value 'evaluated' for Ninth!B1 with a defined name: 101
DEBUG:root:
Cell Eighth!B1 has no formula
but its value is 100

value 'evaluated' for Hundred with a defined name: 100
xSum.xsum(self.eval_ref("Tenth!A1:B1", ref = None))
ERROR:root:Empty cell at Tenth!A1:B1
ERROR:root:ExcelError, #NULL, Cell Tenth!A1:B1 is empty
DEBUG:root:
Cell Tenth!C1 has a formula, SUM(A1:B1)
which has been translated to Python as xSum.xsum(self.eval_ref("Tenth!A1:B1", ref = None)) which evaluates to 0

value 'evaluated' for Tenth!C1 with a defined name: 0
xSum.xsum(self.eval_ref("Tenth!A2:B2", ref = None))
ERROR:root:Empty cell at Tenth!A2:B2
ERROR:root:ExcelError, #NULL, Cell Tenth!A2:B2 is empty
DEBUG:root:
Cell Tenth!C2 has a formula, SUM(A2:B2)
which has been translated to Python as xSum.xsum(self.eval_ref("Tenth!A2:B2", ref = None)) which evaluates to 0

value 'evaluated' for Tenth!C2 with a defined name: 0
xSum.xsum(self.eval_ref("Tenth!A3", ref = None),self.eval_ref("Eighth!B1", ref = None))
DEBUG:root:
Cell Tenth!A3 has no formula
but its value is 2

DEBUG:root:
Cell Eighth!B1 has no formula
but its value is 100

DEBUG:root:
Cell Tenth!C3 has a formula, SUM(A3,Hundred)
which has been translated to Python as xSum.xsum(self.eval_ref("Tenth!A3", ref = None),self.eval_ref("Eighth!B1", ref = None)) which evaluates to 100

value 'evaluated' for Tenth!C3 with a defined name: 100

[bradbase]

Features I can see a pathway to support in the new approach:

• persisting different formats from the model (compressed/not compressed, GraphML, MS Excel, etc...)
• being able to evaluate ad-hoc formulas, so they don't have to be in Excel at all... eg: evaluator.evaluate_formula("=AVERAGE(Sheet1!A1:A5)")
• More simplified pathways to adding Excel function support

[brianmay]

Bradley van Ree <notifications@github.com> writes:

Is there an appetite to try and use a re-write for Koala moving forward or is it best this becomes a new project?

I guess it depends on what you think should happen with existing installs. 1. If you prefer people upgrade, then release this as a new major version, increment the major version, and add instructions on what changes are required to upgrade. Consider releasing alpha versions and beta versions first as appropriate. 2. If you prefer people stay, then create a new project. My suspicion is option 1 is probably going to be the preferred one. Also I am curious, if this new rewrite is any faster then before? -- Brian May <brian@linuxpenguins.xyz> https://linuxpenguins.xyz/brian/

[bradbase]

Thanks for the input Brian.

In short, I'd be leaning toward something like "option 1" as you've described. But my needs and intentions for a Koala style solution appear to be different from others here which is why I'm asking the question.

It depends on how committed people are to the current use cases and what Koala is expected to become.

I think I have been observing a movement toward viewing Koala as "replacing a spreadsheet" eg; openpyxl with a "calculate" button. But this is not the direction I've taken. I have been viewing Excel workbooks as definitions of a calculation (a model or part thereof). Where the model is an abstraction that just so happens to be expressed in the form of an Excel spreadsheet. So, in that sense, (as I see it) Koala isn't simply replacing the Excel calculation engine (eg; openpyxl with a "calculate" button) but is more a toolkit which uses Excel as an interface for defining parts or whole mathematical systems which can use Python as a gateway to evaluation. If used in one way it would achieve the purposes of openpyxl with a "go" button. But would offer significantly more for those who are inextricably bound to Excel but need more from their math. Now that I've written that, it really sounds like I've got an independent project... (again, depending on what Koala is understood to be and which direction it is heading)

To be 100% honest, I've not been measuring for speed. My sole measure has been to get a solution which satisfies the features of roughly Koala 0.0.31 which can actually be worked on. My assumption has been that increasing speed (and other code progression) will be easier to achieve if;

• there isn't code with comments like "does anyone know what this does?", and
• where methods are not duplicated multiple times across the solution,
• where we aren't using lru caches in place of hashmaps /dictionaries (because we can't figure out WTF a method does or are scared/unable to implement a purpose built cache)

The question of speed is interesting, though. Which element of the solution needs to be faster Depends on what you need to be faster, and what is meant by fast.

I have broken the process down into steps.

1. load the Excel file into a Python compatible state, I call a model. (ModelCompiler and parse_excel_file())
2. interpret the model into Python executable code (Model and build_code())
3. evaluation. executing the Python code (Evaluator and evaluate())

After either step 1 and/or 2, the Model can be persisted to/from JSON files. So these steps only really need to be done once (unless the spreadsheet changes). And if we can implement Evaluator.evaluate_function("=Average(Sheet1!A1:A5)"), it may only ever need to be done once because this function could "freestyle"/ad-hoc/"interactively" calculate against the pre-existing model.

Step 3, I'm guessing, is actually where you're asking for "speed".

Step 1: Reading and translating Excel into Python

I am now using xml.etree.ElementTree to read the interesting parts of the Excel file rather than using custom regex and string processing. Also, providing you don't change the spreadsheet, translating the Excel workbook into a Python compatible state only has to happen once. You can persist the Python compatible state as JSON (I'm using JSON and jsonpickle). And, equally (obvjously?), load the Python compatible state from the JSON. So if you've got a very complex spreadsheet that took ages to translate, I expect it'll be easier/quicker to load from JSON. I'm hoping (for my needs), once the input and output concepts are implemented, the library will be able to load everything associated with one formula (ergo, one specified cell). This will optimize the Python compatible state. I can't see an easy way to optimize reading the Excel file in that situation, but the resulting Python compatible state would be optimized to contain only the cells, defined names, ranges and formulas associated in calculating that specified cell. Maybe this has to happen in tandem with "step 2"..?

Step 2: Converting the formulas into Python

This is reasonably quick because Step 1 has already parsed cells and recorded all the discovered formulas. I'm not convinced the process to tokenize and convert to Python code is a particularly bad bottleneck.

Step 3: Evaluating the results

I have broken the Excellib functions out into their own objects so it's possible to have multiple ways to define a function. There is a trap, Excel is occasionally incorrect in its calculations and people now count on those bugs so we may want strict Excel compatible modes along side mathematically correct modes. Many of the existing Excellib definitions ignore the faults in Excel and so, pedantically, may produce results different to Excel. We may also want implementations in vanilla Python, numpy/pandas, wrapped C++ libraries, and Jython. Currently this element of the process is going to rely on dictionary lookups to extract the values from cells and then package them in a way which the Excellib function can handle (this makes set_value() really easy to implement). As far as I'm aware, dictionaries in Python are optimized. I'm happy if someone wants to alter this paradigm to optimize for speed. But my current focus is on getting a maintainable project running so dicts are well good enough for me ATM. I'm expecting to use dataframes as the structures representing ranges to pass to the Excellib functions.

Which aspect of speed are you interested in?

[bradbase]

Equally... There's no reason why the AST interpreting the formulas needs to write Python code.

It can write any code you like. BUT the code it writes does need align with a Model object in the target language, the "Excellib" function library in the target language, and have a Evaluator object written in the target language.

[vallettea]

hello @bradbase,

nice recap.
I see your point when you say "Excel workbooks as definitions of a calculation" and it is in fact a bit different of what koala was coded for. Koala was coded because we needed to do enormous amount of calculation from gigantic excel workbooks and we had no time to translate the models of these workbooks into a well written model.
From my point of view (of someone who hates excel), defining a model through excel is not the way to go if you know maths and code because you always have to deal with this tabular structure that creates a bias in the way you have to lay out things. However, for people who don't code (and they are may) your approach may be interesting.

As you can noticed, i'm not really active on koala and I'm really open to how it should be re-thought and maintained.

Would your project be open-source ?

[bradbase]

Hi @vallettea,

Thank you for your input. It is most appreciated. Hopefully my discussion here will answer questions from others as well.

My understanding of koala has come from the doco. The presentation, the abstract found on the anthill site and the other writing in the docs directory along side reading the code. Knowing the history, and having faced the same challenges, explains everything. And as I've formed an understanding I've seen great inspiration.

I absolutely agree with you about Excel being sub-optimal for defining a mathematical model. The limitations are excruciating. I hate it too. Finding ways around Excel has lead me to become an "expert"(/?) in it.

Over the last 10 years I have been working as an analyst is the energy sector, programmer and a consulting to a demographics company with some time spent in telephone billing (accounts receivable - read big data). The skills these companies have valued the most has been in the space of mathematical modelling and communicating mathematical modelling.

What I have observed is; each case the domain expert is usually not skilled in programming. This causes some challenges;

• intra-company communications surrounding calculations. Especially between engineer(/s), data analyist/programmer(/s) and manager(/s),
• inter-company communications surrounding calculations as company A may need to check the workings of company B which requires both sides have comparable expertise in a specific language (which is never trusted),
• Excel is ubiquitous and is the first line to express a mathematical model as other tools have traditionally been expensive and require specialist knowledge to install, support and/or use. This causes networks of spreadsheets with 100+ tabs each and endless copy+pasted poorly understood VBA which, often enough, cannot be understood thoroughly enough by anyone,
• Key person risk on these mega spreadsheets causes a multitude of severe problems.

These challenges are compounded by the domain expert's time being too valuable for them to sufficiently learn programming. So companies turn to hiring analysts, programmers and contractors to get what they need from the investment they've made in the math expressed in Excel. When they learn it'll take 3 years to translate Excel to code which will tie them to a new world of technical debt and still not assure them of correctness and accuracy, it's deemed unworthy.

Microsoft are trying to alleviate this problem taking Excel to the cloud. This may (or may not) eventuate in helping. Faster processing of Excel is only part of the answer. What is really useful is a common language to express math which can be understood by engineers, managers and programmers both within and across companies. The most important of all these positions, however, are auditors. The question of proof that the audited and approved Excel model has been precisely translated into another language so often comes down to "trust me" which is obscene. Accepting the significant deficiencies, currently Excel is the one language of expression which "everyone" knows.

So, I have seen that using Koala can resolve the above challenges. They keys are:

• keeping the domain expert working in a familiar environment,
• removing the need for people (programmers) in language translation,
• facilitating the writing of Python math "objects" (can't think of a better name) without the use of a programmer or analyst. If you do have a programmer handy the Python math "objects" could then be run in places like PLPython. Good use of defined names helps automate the koala "object" to database field name resolution. I have committed code (Tokenizer.py:620) code which helps software outside koala "autodetect"/query the input names to an equation system,
• If you couple koala with other tools (xlwings), you can define User Defined Functions (UDF) that use the Python math "objects" from koala directly in Excel formulas which helps leverage the domain expert's time. In the case of xlwings, UDF definition can be easy enough for non-programmers to write. Again, the code I committed (Tokenizer.py:620) helps software like xlwings "autodetect"/query the inputs to an equation system. See FlyingKoala for examples.

The advantage of above has already been exampled in my project FlyingKoala.

I can accept koala has been written to essentially replace the Excel calculation engine to gain speed and remove the translation problem. Eg; feature compatible with Excel, just faster -- openpyxl with a "go" button. But the features koala had (around 0.0.31 and before) were properly genius. Where you could;

• save an equation and its state,
• load an equation and its state,
• "focus" koala on only certain outputs (eg; ignore everything except a given equation system)

These are the things that turn koala from "Excel but faster" into a really powerful tool to leverage domain experts and ease communication between all stakeholders. Sadly much of this was broken post version 0.0.31.

My interests are such:

• an open source tool which services the "keys", as mentioned above (I'd like it to be Koala, but I need it in any case),
• my project (FlyingKoala) is expected to remain open source (hopefully leveraging knowledge domains other people are in),
• Flyingkoala already has an Excel add-in, but I am writing a commercial "one-click installer" which will appear in the Excel in-app store and is likely to have some features which support a value add on top of simply installing an open source toolkit.

The best worked example of the principles I have documented: https://flyingkoala.readthedocs.io/en/latest/worked_example_horticulture.html

Thanks again for your questions and input.

[bradbase]

Once the one-click installer is up, I'm will develop commercial solutions for companies to leverage FlyingKoala with PLPython and Python within MS products (eg; MSSQL Server and PowerBI).

[bradbase]

For those following:

First order approximation of Range support using Dataframes as the unit for calculation in the "Excel_lib" functions (currently only SUM).

Cell First!A2 has no formula
but its value is 0.1
value 'evaluated' for First!A2 without a formula: 0.1
ERROR:root:ExcelError, #N/A, 'DataFrame' object has no attribute 'value'
DEBUG:root:
Cell Seventh!C1 has a formula, A1+B1
which has been translated to Python as Evaluator.apply("add",self.eval_ref("Seventh!A1"),self.eval_ref("Seventh!B1"),None) which evaluates to #N/A

value 'evaluated' for Seventh!C1 with a formula: #N/A
ERROR:root:ExcelError, #N/A, 'DataFrame' object has no attribute 'value'
DEBUG:root:
Cell Ninth!B1 has a formula, A1+Hundred
which has been translated to Python as Evaluator.apply("add",self.eval_ref("Ninth!A1"),self.eval_ref("Eighth!B1"),None) which evaluates to #N/A

value 'evaluated' for Ninth!B1 with a defined name: #N/A
DEBUG:root:
Cell Eighth!B1 has no formula
but its value is 100
value 'evaluated' for Hundred with a defined name: 100
DEBUG:root:
Cell Tenth!C1 has a formula, SUM(A1:B1)
which has been translated to Python as xSum.xsum(self.eval_ref("Tenth!A1:B1")) which evaluates to 102

value 'evaluated' for Tenth!C1 with a defined name: 102
DEBUG:root:
Cell Tenth!C2 has a formula, SUM(A2:B2)
which has been translated to Python as xSum.xsum(self.eval_ref("Tenth!A2:B2")) which evaluates to 102

value 'evaluated' for Tenth!C2 with a defined name: 102
DEBUG:root:
Cell Tenth!C3 has a formula, SUM(A3,Hundred)
which has been translated to Python as xSum.xsum(self.eval_ref("Tenth!A3"),self.eval_ref("Eighth!B1")) which evaluates to 102.0

value 'evaluated' for Tenth!C3 with a defined name: 102.0

[bradbase]

@vallettea and @brianmay and anyone lurking, the code I've written so far;

https://github.com/bradbase/koala_xlcalculator

It's not yet feature compatible with koala, but will need to be for it to be useful to anyone.

EDIT: koala_xlcalculator is now known as xlcalculator and can be found here:
https://github.com/bradbase/xlcalculator

[vallettea]

Hello @bradbase
sorry i take years to answer but I running another company and I have no time for koala as people may have noticed ;)

I'm quite impressed by your work and I really think that there is an opportunity in this kind of product especially with someone like you who knows the internal needs of the sector.

I haven't had time to benchmark flyingkoala but i think that if is does what does koala2 with pretty good performances, we should consider telling people to use flyingkoala instead.

[bradbase]

Hello @vallettea

No problem on timing. The world is in considerable turmoil at the moment and these projects are the least of anyone's worries. The time you've taken to respond is especially appreciated. (for posterity COVID-19 era)

Your feedback means a great deal. Thank you.

In case our timing doesn't align and to be a pedant: FlyingKoala 0.0.3b is currently associated with Koala2 0.0.31.

The code I've put forward as potential inspiration for "Koala2 re-write" is currently found as koala_xlcalcualtor and intended for eventual use with FlyingKoala . It is currently a long way from being feature complete against Koala2. I am working on that as a priority.

For those who tread here: It's sounding like koala_xlcalcualtor may be on collision course to be re-named Koala3 once I (or the royal "we"?) reach feature parity.

[bradbase]

For those lurking: I have just release FlyingKoala 0.0.4b0. It's got a bunch of bugfixes and changes to make the add-in optional and actually compatible with Koala2 0.0.31.

[LFoglietti]

Hi Guys,
my start up wants to build an easy UI to make algotrading, not HF, easy to be implemented by investor with no coding skills.

We will start with Cryptomarkets but the idea could be easily translated for traditional markets.
Let s assume the all infrastructure is in place. We just need the rule from the investor:

Ex. Buy Bitcoin with USD if BTC price is Up 2% within 45 Min...then...

which MUST be written in a language compatible with the rest of the infrastucture.
If we want to give to the final user a good product, he has to ba able to build his own rule with a great deal of flexibility :

Ex. Do the previus buy only if the price is above the Max price of last 3 days. Do not execute the rule on friday...

We end up with the only option: The final user needs to know at least basic excel logic.
If he does he can use the UI to build an excel file without coding.
Excel is definetly the solution. we all know it.
Just think about the amount of strategy already implemented in excel by the financial industry.

Problem is... can excel be wrapped in our infrustructure as it is ???
As you can undesrtand, it would be difficoult and extremly expensive to implement such a solution with thousand of excel instances opend toghether. ( not impossible, Microsoft is working on it..)
This is why we need to translate it in a -- Python math "objects" -- and connect our strategy to the market for real time action.
I 100 % agree with bradbase.
Your project is a powerfoul tool and it opens up to lots of intersting commercial use.

Ciao