Python Forum
How do I read in a Formula in Excel and convert it to do the computation in Python? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: How do I read in a Formula in Excel and convert it to do the computation in Python? (/thread-34206.html)

How do I read in a Formula in Excel and convert it to do the computation in Python? - JaneTan - Jul-07-2021

I have a table of time-series data downloaded from some system. Say it is the revenue data for different persons across time. Eg below

Table 1
| | Jan 1999| Feb 1999|Dec 1999|
|----- | --------| --------|--------|
| Peter| 9000 | 2000 |1000 |
| John | 6000 | 1000 |5000 |

The data is read into Python via pandas and converted into a dataframe.

Next, I need to read in from an Excel file, the formula I need to use for computation for Table 1.

Eg in the Excel file, the listed formula for Table 1 is "=sum(Jan 1999: Dec 1999)"

Is there a way for Python code to know how to interpret this formula and do the computation for each row in Table 1?

So the result for Table 1: Peter=12,000; John=12,000

Thank you

RE: How do I read in a Formula in Excel and convert it to do the computation in Python? - Larz60+ - Jul-07-2021

you can use the DejaVu fonts which allow for display of equations and other math formatting.
It's a common font available on most linux distributions

Windows has this: https://www.microsoft.com/en-us/download/details.aspx?id=56828 to offer.

RE: How do I read in a Formula in Excel and convert it to do the computation in Python? - Marbelous - Jul-07-2021

(Jul-07-2021, 11:20 AM)Larz60+ Wrote: you can use the DejaVu fonts which allow for display of equations and other math formatting.
It's a common font available on most linux distributions

Windows has this: https://www.microsoft.com/en-us/download/details.aspx?id=56828 to offer.

I think the OP is looking for a method to extract a formula from a cell in an Excel workbook and then convert that formula into python to perform the calculation.

There is a python module called formulas which does that but since Excel formulas allow all those tricky ways to reference absolute and/or relative ranges of cells I would suggest just translating the formulas into python manually.

I'm no expert in Excel though so if anyone knows that formula module please chime in...