Logical operation based on existing data VBA code

  • [INDENT] Hi All

    Im am trying to write VBA code. I have a excel with 2 sheets named Current trades and opening position and third sheet i have added just for example of output. I want to create code based on following codition. Can anyone help me...It would be really kind. I generally can read, understand and manupulated the VBA code but lacks in logical coding.
    1. if there is new trade "buy" and open position is "Long" then action1 ="Buy" and action2= "Open. For example reliance (2210) -should check in open position and there we have 1 long posiition , so action1 ="Buy" and action2= "Open.
    2. if there is new trade "sell" trade and open Position is also "Short", then action1 ="sell" and action2= "Open , Example Wipro (2300") is having 1 "sell" trade and Open postiong is also short, so should be action1 ="sell" and action2= "Open ,
    3. if there is new security trade like Tesco 2410 (Sell), - and does not have any open positon it must be action1 ="sell" and action2= "Open and vice versa for APPLE 1564 - for buy security without open postion - action1 ="buy" and action2= "Open ,
    4. if there are several new sell trades, and opening position is long and each line of new trade must be subtracted from long position untill get ZERO - for example TCS (2413) is 'having differenct trades let's see first trade row is having 4 units the LONG opening positions are 7 (1+5+1). so mean before opening we gotta close these 7 position first. so we can sell these to close long positions. so this case first row for 4 units action1= "sell", and Action2 = "Close". and next row is having 5 trades and we are left only opening 3 (7-4) and this trade row should be splitted in 2 row something like 3 units with Action1 ="Sell" and Action2 ="Close" and 2 Units with action1 = "Sell" and "Open". Since we are not left with any open positon after this, Rest of the trades should be like Action1=sell and Action2=open
    5. if there is buy trade and open position is short, it should be booked on prorata basis, like for IBM ("25000") there are 2 buy trades and one 1 short position in opening position. In this case row must be spliited into 2 -with first row must be 1 unit with action1 =buy and action2=close and other row with 1 unit and action1 =buy and action2 open.

    Current Trade:-

    Security Codes Security Name Quantiy Buy/Sell
    2210 Reliance 1 buy
    2300 Wipro 1 sell
    2410 Tesco 1 sell
    2413 TCS 4 sell
    2413 TCS 5 sell
    2413 TCS 7 sell
    2413 TCS 1 sell
    2413 TCS 1 sell
    2413 TCS 1 sell
    25000 IBM 2 buy

    Opening Position:-
    MF Securty Name Quantiy Long/Short
    2210 Reliance 1 long
    2300 Wipro 1 Short
    2413 TCS 1 buy
    2413 TCS 5 buy
    2413 TCS 1 buy
    25000 IBM -1 Short

    OutPut should be like this
    Security Codes Security Name Quantiy Buy/Sell Action1 Action2
    2210 Reliance 1 buy buy open
    2300 Wipro 1 sell sell open
    2410 Tesco 1 sell sell open
    1564 Apple 1 buy buy open
    2413 TCS 4 sell sell close
    2413 TCS 3 sell sell close
    2413 TCS 2 sell sell open
    2413 TCS 7 sell sell open
    2413 TCS 1 sell sell open
    2413 TCS 1 sell sell open
    2413 TCS 1 sell sell open
    25000 IBM 1 buy buy close
    25000 IBM 1 buy buy open

    Thanks in advance and looking forward for some possible solution form this forum.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!