Anyone willing to offer advice on how I could translate an Excel formula to PHP

[suprsnipes]

Anyone willing to offer advice on how I could translate an Excel formula to PHP.

 

I want to achieve the same outcome the formula below would produce but on data that is stored in a MySQL database with auto_increment ID.

 

The Excel formula in the classification column is designed to do the following;

 

When there is a trade(N) event it classifies the trade according to several conditions and will either use the previous bid(b) or ask(a) quote in conjunction with the current trade price in relation to the previous bid or ask price.

 

OR/

 

If the previous trade has already been classified as a "Buy" or "Sell" (in the case of consecutive trades(N)), whereas there will be no bid or ask price to refer to. In this case it will use the previous Buy or previous Sell classification in relation to the current trade price v's the previous trade price.

 

=IF(AND(D2="N",D1="a",E2>=E1),"Buy",IF(AND(D2="N",D1="b",E2<=E1),"Sell",IF(AND(D2="N",D1="a",E2<E1),"Sell",IF(AND(D2="N",D1="b",E2>E1),"Buy",IF(AND(D2="N",G1="Buy",E2>=E1),"Buy",IF(AND(D2="N",G1="Sell",E2<=E1),"Sell",IF(AND(D2="N",G1="Buy",E2<E1),"Sell","")))))))&IF(AND(D2="N",G1="Sell",E2>E1),"Buy","")

 

I can post a data sample if someone wishes.

 

A friend of mine come up with this, I know that it still refers to 'previous' but that is the problem. I don't know how to do that besides using an SQL INNER JOIN but that only solves part of the problem, for example somehow I need to use the results from the initial INNER JOIN, update the trades(N) to a "buy" or "sell" and then run the query again and again, which is something I don't know how to do...

 

if (type == "N") {
>
>   If (previous_classification != null) {
>
>     if (previous_classification == "buy") {
>   
>       if (price >= previous_price) {
>         action == "buy";
>       }
>       else {
>         action == "sell";
>       }
>     }
>     else if (previous_classification == "sell") {
>   
>       if (price <= previous_price) {
>         action == "sell";
>       }
>       else {
>         action == "buy";
>       }
>     }
>   }
>   else {
>
>     if (previous_type == "a") {
>
>       if (price >= previous_price) {
>         action == "buy";
>       }
>       else {
>         action == "sell";
>       }
>     }
>     if (previous_type == "b") {
>
>       if (price > previous_price) {
>         action == "buy";
>       }
>       else {
>         action == "sell";
>       }
>     }
>   }
> }
>

 

[Daniel0]

=IF(AND(D2="N",D1="a",E2>=E1),"Buy",IF(AND(D2="N",D1="b",E2<=E1),"Sell",IF(AND(D2="N",D1="a",E2<E1),"Sell",IF(AND(D2="N",D1="b",E2>E1),"Buy",IF(AND(D2="N",G1="Buy",E2>=E1),"Buy",IF(AND(D2="N",G1="Sell",E2<=E1),"Sell",IF(AND(D2="N",G1="Buy",E2<E1),"Sell","")))))))&IF(AND(D2="N",G1="Sell",E2>E1),"Buy","")

 

^^ THIS is one of the reasons why I think Excel sucks. In my opinion, it has a poorly implemented DSL.

 

I don't have any solution to your problem. I just came in here to say that.

[Mark Baker]

I've suggested elsewhere that you use a calculation engine that can handle Excel formulae, so there would be no need to do any translation

[suprsnipes]

Yes Mark you have.

 

So are you saying that I can do what I want to do by using PHPExcel?

 

If you think I can I will look into it now. Please respond so I know...

[Mark Baker]

So are you saying that I can do what I want to do by using PHPExcel?

 

If you think I can I will look into it now. Please respond so I know...

I don't see why not.

I have applications where I read a set of rows and columns using a database select and feed values from that directly into the PHPExcel calculation engine according to an Excel formula, using it to calculate simple values like MEDIAN, STDEV, etc.

For more complex formulae, I can write the database rows/columns into 'cells' within PHPExcel, set named ranges if I want, and then just set another cell to a formula value (e.g. '=IF(SUM(DATASET),500,"Big List", "Small List"))

I've not tested it with anything that's as complex as your formula, but there's no reason why it shouldn't work.... and I've nearly finished a complete rewrite of the calculation engine that would have no problem with your formula, execute it faster and with less memory overhead than the current production version, and not require a prepopulated PHPExcel object, but where you would be able to call the calculation engine with a straight formula with values rather than cell or named range references