I'm a newbie an am trying to develop a small system to keep track of my share portfolio. At the moment I have 3 tables - tblStockCodes, tblHoldings and tblSellOrder with the following structure:
When a buy order for a number of shares in a company is executed I can enter the details into my tblHoldings table and set the SoldQuantity to zero. As I could buy more than one parcel of shares for the same company on the same date or on different dates I established a one-to-many relationship between tblStockCodes and tblHoldings using the field StockCodeID. Similarly I established a one-to-many relationaship between tblSockCodes and tblSellOrder using the field StockCodeID.
Where my confusion creeps in is not understanding how to relate the tables tblHoldings and tblSellOrder. Depending on how I want to handle the sell order for capital gains purposes, etc I could have a tblSellOrder record for a certain company reduce the quantity of shares held by a number of the tblHoldings records for that same company ie a one-to-many relationship bewteen tblSellOrder and tblHoldings. In reality, I can have multiple sell orders reducing the quantity of shares for some of those same tblHoldings records as before until the share holdings for a particular tblHoldings rceord is reduced to zero. So I seem to have a many-to-many relationship bewteen tblHoldings and tblSellOrder. I've probably confused you with the above so let me give you a small example, although unlikely to happen.
Today I buy 3 parcels of shares in XYZ company. Let's say I buy 500 in first parcel, 350 in second parcel and 400 in third parcel. Tomorrow I sell 2 parcels of shares in XYZ company. Let's say I sell 600 in first parcel and want to allocate 200 against the first bought parcel of 500 (leaving 300), 100 against the second bought parcel of 350 (leaving 250) and 300 against the third bought parcel of 300 (leaving 100). The second parcel sold is for 400 shares and i wish to allocate 200 against the first bought parcel leaving 100, 100 against the second bought parcel leaving 150 and 100 against the third bought parcel leaving 0.
How do I set up my tables to be able to produce a holdings statement at any point in time and keeping track of the buys and sells that may be made from time to time?
Maybe I'm being too ambitious in trying to develop a system to cater for such a situation and should revert to an excel based system where all entries, calculations are done with a lot of manual and duplicated effort.
Any suggestions would be most appreciated.