Logical operation based on existing data VBA code

baljitaryan

New Member
Joined
Jun 18, 2016
Messages
2
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 18, 2016
Messages
2
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,298
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,644
Messages
5,524,046
Members
409,557
Latest member
lgambit

This Week's Hot Topics

Top