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

CodePudding user response：

Using dataframe from pandas can be an option for you.

import pandas as pd

df = pd.read_excel('excel.xlsx')
df['total_sales'] = df['europe_sales']   df['us_sales']

CodePudding user response：

There is no tool to my knowledge that does exactly what you're asking. That said, I think you have a couple options.

1. If what you are doing is not too complex, write it in excel.
2. You can use VBA to write macros, however, if your supervisors can understand your VBA code, more than likely they will understand your python script.
3. If your boss really wants to check your work, try and replicate a previous example (if one exists) and confirm the output, or create a couple edge/test cases to confirm your script works, or just have them confirm the results of the actual data for the first time and trust it going forward.

• Create a flow chart to help explain what you're doing

Excel is very powerful but there is a lot that ends up being a pain. If you're working on a task that is not straight forward to do in excel, chances are it will be difficult for your boss to confirm/check your excel logic anyway. Best advice is to talk to your boss and explain you can give them the result in excel but the functions/logic is not easily implemented.

best of luck