Python Forum
How do I read in a Formula in Excel and convert it to do the computation in Python?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How do I read in a Formula in Excel and convert it to do the computation in Python?
#1
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
Reply
#2
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...x?id=56828 to offer.
Reply
#3
(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...x?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...
"So, brave knights, if you do doubt your courage or your strength, come no further, for death awaits you all with nasty, big, pointy teeth!" - Tim the Enchanter
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Help using a dynamic array excel formula with XLWings FXMonkey 2 1,208 Jun-06-2023, 09:46 PM
Last Post: FXMonkey
  how to read txt file, and write into excel with multiply sheet jacklee26 14 9,515 Jan-21-2023, 06:57 AM
Last Post: jacklee26
  Openpyxl manipulate excel write formula SamLiu 0 1,002 Nov-04-2022, 03:00 PM
Last Post: SamLiu
  Convert Excel file into csv with Pipe symbol.. mg24 4 1,288 Oct-18-2022, 02:59 PM
Last Post: Larz60+
  Element wise computation divon 2 1,502 Jun-01-2022, 02:36 AM
Last Post: divon
  numpy.dot() result different with classic computation for large-size arrays geekgeek 5 1,830 Jan-25-2022, 09:45 PM
Last Post: Gribouillis
  {SOLVED]Help getting formula value in Excel to a csv Pedroski55 1 1,971 Sep-20-2021, 12:19 AM
Last Post: Pedroski55
  Python “Formula” Package: How do I parse Excel formula with a range of cells? JaneTan 1 2,640 Jul-12-2021, 11:09 AM
Last Post: jefsummers
  Read and write active Excel file euras 4 3,378 Jun-29-2021, 11:16 PM
Last Post: Pedroski55
  Filter Excel and Convert an Excel File giddyhead 0 2,195 May-13-2021, 06:31 PM
Last Post: giddyhead

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020