hi Chinmay here i attached a file hope that its help u. im working on it to make it more simple.
On Fri, Oct 9, 2009 at 5:13 AM, RolfJ <r...@pacificsound.us> wrote: > > You are describing exactly what I had proposed. Should be > straightforward to implement. > > Have fun, > Rolf > > On Oct 8, 7:45 am, chinmay kamat <kamatchin...@gmail.com> wrote: > > 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??? > > > > -- Warm Regard Prashant Bhawar ( CA Final ) --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- 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 -~----------~----~----~----~------~----~------~--~---
CG.xls
Description: MS-Excel spreadsheet
