[Solved] Python tool that shows calculations in Excel

[Lawrence594]

Hi
Is there a good tool where I can do a complex calculation in Python but have it show the results as Excel formulas? I'm thinking of a use case where I want to do complex financial projections with more business logic than is comfortable to write directly in Excel. However, the end-users are familiar with Excel and want to verify my work by checking a spreadsheet.

To be more concrete, I would like to write things like

total_sales = europe_sales + us_sales

and have that translate to Excel formulas like

A3 = A2 + A1

Obviously, this would be for generating more complex spreadsheets with dozens of columns across an arbitrary number of rows

[Villeroy]

This can be done easily without any Python code in Excel, Calc, Gnumeric, Numbers and any other spreadsheet application supporting named ranges.

[Lupp]

total_sales = europe_sales + us_sales

Is this supposed to be a line of code in any script language, or a spreadsheet formula using already named ranges? (Are single named cells useful? Intersection mode using named rangs shouldn't be recommendable anyway.)
The first variant doesn't seem to be reasonable, and can't be solved without ingenious AI tools, imo. In fact it can't be solved at all.
In the second case ...

...and have that translate to Excel formulas like
A3 = A2 + A1

it should be basically feasible, but I don't know a feature of Calc doing it.
Doing it by user code (with what programming tool ever) you would need to replace the named ranges in formulas using the .NamedRanges properties of the document for one kind and the respective sheets for the other kind to get the absolute names". It shouldn't be exactly difficult: The .Tokens parsed from the original formulas are referencing named ranges with the opcode 4 and with the index into the respective .NamedRange Property which is identified by the sheet index for ranges with sheet scope and by -1 for ranges with doc scope. You see, it's simple - regarding that I never tied it, and my statements concerning the tokens may be erroneous or incomplete..

Calc formulas look like formulas in Excel in most cases. However, Calc isn't Excel. We should appreciate the little diifferences.
Anyway: To convert a formula using named ranges to an equivalent one using absolute addresses in their place may sometimes help to verify sheets. Names may be chosen misleading or used wrongly, after all - or the original ideas needed to be modified, and the chosen set of names is no loger optimal. I personally don't like named ranges too much therefore.

@Villeroy: Can you actually do the conversion easily based on a dedicated feature?

[Villeroy]

@Villeroy: Can you actually do the conversion easily based on a dedicated feature?

Nope. IMHO, the user should use a spreadsheet or something else. Turning a spreadsheet into something else is not my business.

[Lupp]

@Villeroy: Can you actually do the conversion easily based on a dedicated feature?

Nope. IMHO, the user should use a spreadsheet or something else. Turning a spreadsheet into something else is not my business.

Sorry. I had reduced the question to my second interpretation:
"How can formulas using named ranges be converted to formulas using the respective addresses?"
(Both representations, of course, aren't handled as strings, but eventually deignate refrences.)

My BTW-question to you was about this interpretation, which surely is a spreadsheet-only-question. As already mentioned I rarely use named (ordinary) ranges. One reason to avoid them was that I got in trouble with debugging formulas now and then. The range name pretended the correct semantics, but I had to look it up via the dialog when a check was needed (or to select the range using the name box).

[Villeroy]

"How can formulas using named ranges be converted to formulas using the respective addresses?"
(Both representations, of course, aren't handled as strings, but eventually deignate refrences.)

FIrst of all, you need a formula parser to identify all named references. Too much ado for nothing.

I recommend this with OpenOffice:
menu:Insert>Names>Insert... [Insert All]
which inserts a 2-column table with all named references.

P.S. Instead of a formula parser:
queryContentCells(nFormula)
NamedRanges.ElementNames
Then find all unquoted element names and replace them with the Content property of the respective name. This becomes a lot more complex when the named reference is a relative or mixed one. With R1C1 notation it would be a non-issue, though.

[Lawrence594]

Hi
Thanks for your suggestions. It helped me alot.