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
  {SOLVED]Help getting formula value in Excel to a csv Pedroski55 1 90 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 369 Jul-12-2021, 11:09 AM
Last Post: jefsummers
  Read and write active Excel file euras 4 517 Jun-29-2021, 11:16 PM
Last Post: Pedroski55
  Filter Excel and Convert an Excel File giddyhead 0 544 May-13-2021, 06:31 PM
Last Post: giddyhead
  Matplotlib: How do I convert Dates from Excel to use in Matplotlib JaneTan 1 745 Mar-11-2021, 10:52 AM
Last Post: buran
  code to read files in folders and transfer the file name, type, date created to excel Divya577 0 558 Dec-06-2020, 04:14 PM
Last Post: Divya577
  Reading from Excel: value not formula! faryad13 0 826 Oct-28-2020, 09:52 PM
Last Post: faryad13
  Openpyxl: Excel formula & condition formatting removed JaneTan 0 903 Sep-25-2020, 07:02 AM
Last Post: JaneTan
  Convert Excel to CSV and retain formatting slider09 5 1,150 Jul-24-2020, 10:36 AM
Last Post: Gribouillis
  Pd read excel()_ errors fat_fingers_squared 2 1,131 Jul-18-2020, 02:09 AM
Last Post: fat_fingers_squared

Forum Jump:

User Panel Messages

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