Logical operation based on existing data VBA code

[baljitaryan]

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.

 

[Rijnsent]

Hi Baljitaryan,
welcome to the forum! You could do this without VBA:
-Have a sheet with your opening positions (by the way: your current example is confusion, as there are 3 different positions for 2413, I assume there is just 1 "aggregated" position?)
-After you entered "2210 Reliance 1 buy" you can with some formulas (e.g. VLOOKUP) add some columns to that line:
--position before (MF Securty Name Quantiy Long/Short)
--Action1 & action2
--position after

-Position before can initially be pulled from the Opening Position sheet, but afterwards, you'll have to look it up above the line of that order.

Hope that helps,

Koen

 

[baljitaryan]

Thanks koen, well this can be done manual. no issue...but every day i have hundreds of trades and this is logical expression what should be coming and going and inserting row. that's why i wanna do it in automated way..

 

[Rijnsent]

Hi Baljitaryan,
well, formulas are not "manual", they would calculate automatically... But anyhow, if you fancy VBA: please do post the code you created so far here (in [ CODE ] brackets), as that would help massively giving you feedback.
Thanks,
Koen