1

I am attempting to recreate an excel spreadsheet through python, there are a lot of formulas that I need to translate. I have the following formula

=IFERROR(MIN(1,0.05+(1-0.05)*EXP((SUMIFS(O:O,AB:AB,AB2)-SUMIFS(AF:AF,AB:AB,AB2))/(2*(1-0.05)*SUMIFS(AT:AT,AB:AB,AB2)))),1)

I have changed this so that there is a loop for one of the variables, so this filters out all of the others. I believe I am now looping in the AB column so I don't have to use the sumif any longer. I should just be able to sum the columns required. However I'm still unsure how to replicate if error and how to take the exponential of certain functions.

I now have the following code:

amount1 = df_Temp[df_Temp["IMED_PARNT_NAM"] == ns].sum()["MTM_AMT"]

amount2 = df_Temp[df_Temp["IMED_PARNT_NAM"] == ns].sum()['Collateral/# Trades in Netting Set']

amount3 = df_Temp[df_Temp["IMED_PARNT_NAM"] == ns].sum()['Add-On']

amount4 = amount1 - amount2 / 2 * (1 - 0.05) * amount3

df_Temp['Multiplier'] = np.where(0.05 + (1 - 0.05) * np.exp(amount4) > 1,0.05 + (1 - 0.05) * np.exp(amount4), 1)

However, I do not think the above is giving me the desired output that I require from the Excel formula in the first place. Any help is appreciated!

Thanks,

Kieran

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KieranC97
  • 47
  • 5
  • Which of the excel functions are you having trouble duplicating in python? – wwii Jan 17 '20 at 16:58
  • 2
    I think you need to break down the formula into it's individual steps, then identify python functions that could accomplish each step, then combine them. If you could provide these steps we would be more inclined to provide a solution. – Derek Eden Jan 17 '20 at 16:58
  • Does this answer your question? [sumifs function in python](https://stackoverflow.com/questions/11035767/sumifs-function-in-python) - `IFERROR` -> [`try/except`](https://docs.python.org/3/tutorial/errors.html#handling-exceptions) – wwii Jan 17 '20 at 17:05
  • I agree with @DerekEden. Have you done any research? What is the issue, exactly? – AMC Jan 17 '20 at 18:54

1 Answers1

2

Check out the formulas package on PyPi. It contains several useful tools for everything related to excel formulas.

The following example might be useful:

>>> import formulas
>>> func = formulas.Parser().ast('=(1 + 1) + B3 / A2')[1].compile()
diegog
  • 101
  • 6