library not working as expected

[anmol1ratn]

I came across this library while searching to evaluate circular references in excel and decided to give it a try. I tried with a simple excel with circular reference that requires iterative solution and the result was underwhelming. The library generates the correct graph but the calculate function does not work as expected. Here is the example I tried.

Loaded the excel in xl_model

import formulas
fpath = 'test.xlsx'
xl_model = formulas.ExcelModel().loads(fpath).finish(circular=True)
xl_model.to_dict()

{"'[test.xlsx]SHEET'!A1": 400,
 "'[test.xlsx]SHEET'!A2": 312,
 "'[test.xlsx]SHEET'!A3": "=ROUND(('[test.xlsx]SHEET'!A4 * 0.1), 0)",
 "'[test.xlsx]SHEET'!A4": "=(('[test.xlsx]SHEET'!A1 + '[test.xlsx]SHEET'!A2) - '[test.xlsx]SHEET'!A3)"}

The above output show cell A3 and A4 formulas.

Plotting the graph

xl_model.dsp.plot(view=True)

The above graph correctly shows a circular dependency

But when I run calculate function

xl_model.calculate()

The output is:

Solution([("'[test.xlsx]SHEET'!A1", <Ranges>('[test.xlsx]SHEET'!A1)=[[400]]),
          ("'[test.xlsx]SHEET'!A2", <Ranges>('[test.xlsx]SHEET'!A2)=[[312]]),
          ("'[test.xlsx]SHEET'!A3", <Ranges>('[test.xlsx]SHEET'!A3)=[[0]]),
          ("'[test.xlsx]SHEET'!A4", <Ranges>('[test.xlsx]SHEET'!A4)=[[0]])])

Why A3 and A4 not calculated iteratively as MS Excel does? MS Excel calculates and shows A3=65 and A4=647

Can't get around this. Am I doing something wrong? Some parameter in a function that I am missing to pass?

Thanks in advance for the help and please excuse if I missed something simple.

[dberardo-com]

could you show the rest of the code where you initialize the xl_model object and call the calculate function ?

[vinci1it2000]

The behavior you are experiencing is due to Excel's settings for handling circular references, which by default can show 0 if iterative calculation is not enabled. When iteratively calculated, the results depend on the settings and initial values when you start the calculation.

I can show the Excel issue and the unpredictable results. The problem is more evident when setting the maximum iterations to 1, as seen in the attached video.

https://github.com/vinci1it2000/formulas/assets/11716821/47553b17-ca09-4ea3-bfb0-0ad383b88380

We have four different results, as shown in the figure.

In conclusion, formulas identify the circular reference and do not iterate the calculation. Therefore, the results show zero because we want to replicate the wired behavior. However, if you check the result type, it is not zero; it is an XlCircular error.