Thank you sir, but unfortunately this does not solve the problem. The data is in the following format (example):
For purchases: Date Quantity Amount Rate 01/04/08 150/- 85050/- 567/- 05/04/08 100/- 61000/- 610/- 09/04/08 25/- 15125/- 605/- 10/04/09 200/- 130000/- 650/- For sales: Date Quantity Amount Rate 10/04/08 100/- 59000/- 590/- 07/04/09 175/- 117250/- 670/- 10/04/09 200/- 160000/- 800/- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Analysis of the data: the sale made of 10/04/08 is entirely short term. The cost of 100 shares sold are selected as follows: 100 out of purchases of 01/04/08. Profit is (100*590) - (100*567)= 2300/- >>>>>>>>>>>>>>>>>>>> The sale made on 02/04/09 is one year later, and the cost for the 175 shares sold is selected as follows: 50 out of purchases made on 01/04/08 and 100 out of purchases made on 05/04/08 and 25 out of purchases made on 06/04/08. Profit is (175*670) - ((50*567) + (100*610)+(25*605))= 12775/- out of which (150*670) - ((50*567)+(100*610))= 11150/- is long term and (25*670)-(25*605)=1625/- is short term. >>>>>>>>>>>>>>>>>>>>> The sale made on 10/04/09 is speculative because both the sale and purchased occurred on the same day for all the shares. (as all the earlier purchases were exhausted) >>>>>>>>>>>>>>>>>>>>>> so the program should not only calculate the profit by subtracting the cost of shares on FIFO basis (that is identify the earliest existing stock) but also tell if that was short term, long term or speculative. the second problem can easily be solved using the DATEIF function as explained by you. but since the data does not readily give me the correlation between the purchases and sales on FIFO basis, the first problem remains. Interesting problem, isnt it..?? <><><><><><><><><><><><><><><><> I have an idea of how to go about the problem, but i do not know how to implement it. See, the data is as follows: Date of each transaction, Quantity bought/sold in each transaction, total amount we convert that data into: Date of purchase/sale of each share, Quantity=1 (always), Rate per share. So we will end up with data spreading across many rows, as each transaction is split into several rows depending on the number of shares involved. We do this for both purchase data and sale data. then we place the data side by side, and voila.. just subtract the selling rate from purchase rate to get profit, then compare the dates to categorise the transaction. You think it may be possible in excel?? or would it be possible in any other manner??? --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
