Page 1 of 1

PHP - Need help with translation from Excel

Posted: Mon Jun 01, 2009 4:16 am
by suprsnipes
Need help with PHP translation from Excel to PHP.

I can classify almost 80% of the data using queries but where I am running into problems is as you can tell from the formula I am comparing the current row with the previous row of data and the previous rows needs to be updated correctly before moving to the next.

Here is the statement in Excel:

=IF(AND(E2="trd",F3<>"",H2<=F3),"Sell",IF(AND(E2="trd",I3="Buy",H2<H3),"Sell
",IF(AND(E2="trd",I3="Sell",H2<=H3),"Sell",IF(AND(E2="trd",I3="Sell",H2>H3),
"Buy",IF(AND(E2="trd",H2>=G3),"Buy",IF(AND(E2="trd",I3="Buy",H2>=H3),"Buy",I
F(AND(E2="trd",H2>3000,F3>3000,H2>F3),"Buy","")))))))&IF(AND(E2="trd",G3<>""
,H2<G3),"Sell","")

E2 = Trade
F3 = Previous BP
H2 = TP
H3 = Previous TP
I3 = Previous Class
G3 = Previous AP

Translating into business logic:

IF(AND(TRADE="trd",PREVIOUS_BP<>"",TP<=PREVIOUS_BP),"Sell",
IF(AND(TRADE="trd",PREVIOUS_CLASS="Buy",TP<PREVIOUS_TP),"Sell",

IF(AND(TRADE="trd",PREVIOUS_CLASS="Sell",TP<=PREVIOUS_TP),"Sell",

IF(AND(TRADE="trd",PREVIOUS_CLASS="Sell",TP>PREVIOUS_TP),"Buy",
IF(AND(TRADE="trd",TP>=PREVIOUS_AP),"Buy",

IF(AND(TRADE="trd",PREVIOUS_CLASS="Buy",TP>=PREVIOUS_TP),"Buy",

IF(AND(TRADE="trd",TP>3000,PREVIOUS_BP>3000,TP>PREVIOUS_BP),"Buy","")
)
)
)
)
)
)
&IF(AND(TRADE="trd",PREVIOUS_AP<>"",TP<PREVIOUS_AP),"Sell","")

If any member is willing to help me out here I would be very appreciative.

Re: PHP - Need help with translation from Excel

Posted: Mon Jun 01, 2009 5:29 am
by Mark Baker
You could always use the Excel logic "as is" using a PHP Excel library with a built-in calculation engine