Hi all,

LaRetta and Comment both tried to help me with this problem a fair while ago. Events overtook me and I have now started again.

I have included a sample based on what I have read on this forum.

Explanation.

This is a db to manage my Share portfolios and track profits and/or losses.

There are Buy contracts and Sell contracts. I cannot Sell unless I own the shares (ie Buy Contracts always precede Sell contracts).

I need to know in any one financial year what profit or loss I have made for each Sell contract in that year. (1st July to 30th June).

This is calculated when the shares are sold. I may have multiple buy contracts for the same company. I can decide which ones I sell (LIFO FIFO or any in the middle - there are tax benefits in doing so). There is a 50% discount, for tax purposes, if shares are held longer than 12 months.

I have two questions.

1. Is the structure in the sample the basic structure I should use?

2. Can you give me an idea how I can associate Sells with Buys bearing in mind (a) one Sell may cover a number of Buys or part thereof (e.g. ERH in Share Holdings) and (: One Buy may go over a number of Sells (e.g. HER) . I need a report that will list each Sell contract in a financial year and the profit or loss it has made.

I hope this time I have explained the problem so it is easy to understand.

Cheers and thanks for your past help,

Rob

RGLovettPM.fp7.zip

Hi all,

Am I on the right track or have I totally confused everyone... again?

Cheers

Rob

Hi all,

Does anyone have any suggestions or comments with regard to this post, pretty please?

Thanks

Rob

I suspect that nobody here has knowledge of the particular problem domain - share trading.

Is there a particular reason to keep the buy and sell contracts in separate tables? What would happen if there was just a "contracts" table and the buy or sell was indicated simply by a positive or negative number. That might make calculations and reporting much easier (could have other negative effects though).

Riddle me this, would you...

1,000 @ $1 (buy)

2,000 @ $1.50 (buy)

1,000 @ $1.25 (sell)

Which 1,000 did you sell? What is your current nett worth?

I guess this is where the FIFO and LIFO come into play -- I've never done an inventory system. I imagine one of the methods would have to be selected and stuck with.

So if it's FIFO then the $1 shares were sold.

I can see the need perhaps for a table that stores the share prices so that a nett worth calculation could be made at any point. This table would hold the share price for each contract, plus prices at any other time. To get your nett worth for any particular time just enter in the share prices and it will calculate. Having the buys and sells in one table means that it could even calculate for long or short positions.

I need to know in any one financial year what profit or loss I have made for each Sell contract in that year. (1st July to 30th June). This is calculated when the shares are sold. I may have multiple buy contracts for the same company. I can decide which ones I sell (LIFO FIFO or any in the middle - there are tax benefits in doing so). There is a 50% discount, for tax purposes, if shares are held longer than 12 months.

In that case, the table would just hold buy contracts. Each contract (record) would have a "bought amount" field and a "sold amount" field. A sale would entail working through the buy contracts and setting the "sold amount" fields in each contract until the bought=sold. This could be scripted, plus it could also allow a lot of manual entry to be done (though that could get boring if there are lots of buy contracts).

Where in Australia are you, I'm in Sydney. This is getting complicated. :)

Hi Vaughan,

Thanks for your response.

No there is no particular reason for the two tables except that in everything I read concerning these sorts of databases it seemed to be recommended.

In your example it would be better to be able to choose based on what is best for you, taxwise.

I agree I think a better way to do it would be to have the main table based on purchases and then attribute sales to each one but I always come unstuck it trying to organise a way to do this.

Using your example you could have the following scenarios.

Scenario One

3 separate sales of 1000 shares i.e. the 2nd purchase of 2000 shares would have 2 Sell contracts attached to it.

Scenario Two

One sale of 3000 shares

So the 3 purchases all have the one (3000 share) Sell contract attached to them.

Thanks again. I'm in Brisbane.

Thanks again Rob