SONJOE JOSEPH
Member
Good Moring Excel Experts,
I have some data in an excel sheet which contains all the buy and sell transactions done by an investor. I would love to do the following things
1. First i need to sort the SELL transactions as per the BUY transactions my idea is down mentioned
Date Security Qty Price Total
22-Sept-20 Heidelberg 10 179.19 1791.91 since u can see the Buy was happened on 3rd April 20
22-Sept-20 Marico 5 341.18 1706.41 since u can see the Buy was happened on 3rd April 20
04-Aug-20 VIPIND 10 263.86 2638.55 since u can see the Buy was happened on 20th April 20
31-Aug-20 LAOPALA 10 207.74 2077.40 since u can see the Buy was happened on 21st April 20
03-Jul-20 JYOTHYLAB 20 121.17 2423.32 since u can see the Buy was happened on 30th April 20
Like this it should go I hope u guys got it.
2. Some of the shares the investor is not selling the full qty just selling half of the quantity's. So need to record how much quantity the investor was holding the same share and how much he has sold off.
3 Also need to find out the holding days the investor is holding a particular stock. Holding Days = Selling Date - Buying Date. This is useful to know whether its a long term or short term gain which will be useful in tax calculation. Here also if the investor is selling shares bit by bit or half quantity how will be the holding days calculation done.
4 Finally i need to find out the profit and loss of each security Sold and Bought. This will help to know the %Gain / Loss
This are the ideas in my mind which i need to put it in Excel in a need data.
I'm attaching the working file along with this message.
Thanks & Regards,
I have some data in an excel sheet which contains all the buy and sell transactions done by an investor. I would love to do the following things
1. First i need to sort the SELL transactions as per the BUY transactions my idea is down mentioned
Date Security Qty Price Total
22-Sept-20 Heidelberg 10 179.19 1791.91 since u can see the Buy was happened on 3rd April 20
22-Sept-20 Marico 5 341.18 1706.41 since u can see the Buy was happened on 3rd April 20
04-Aug-20 VIPIND 10 263.86 2638.55 since u can see the Buy was happened on 20th April 20
31-Aug-20 LAOPALA 10 207.74 2077.40 since u can see the Buy was happened on 21st April 20
03-Jul-20 JYOTHYLAB 20 121.17 2423.32 since u can see the Buy was happened on 30th April 20
Like this it should go I hope u guys got it.
2. Some of the shares the investor is not selling the full qty just selling half of the quantity's. So need to record how much quantity the investor was holding the same share and how much he has sold off.
3 Also need to find out the holding days the investor is holding a particular stock. Holding Days = Selling Date - Buying Date. This is useful to know whether its a long term or short term gain which will be useful in tax calculation. Here also if the investor is selling shares bit by bit or half quantity how will be the holding days calculation done.
4 Finally i need to find out the profit and loss of each security Sold and Bought. This will help to know the %Gain / Loss
This are the ideas in my mind which i need to put it in Excel in a need data.
I'm attaching the working file along with this message.
Thanks & Regards,
