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.
PHP - Need help with translation from Excel
Moderator: General Moderators
-
suprsnipes
- Forum Newbie
- Posts: 3
- Joined: Mon May 25, 2009 11:26 pm
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: PHP - Need help with translation from Excel
You could always use the Excel logic "as is" using a PHP Excel library with a built-in calculation engine