Relationships not quite there yet

[Genx]

So lets say in contract 125 you wanted to sell 12,000 shares from GHI... could you select that you wanted to sell 10,000 from Contract 118, 2000 from contract 120 -- And if so, wouldn't you want to detail how much were remaining from each contract not just the entirety of the balance?

[Genx]

... See the attached. I think I MAY have gotten my head around it.

For each contract either enter a Purchase OR multiple sales.

Also, Profit / Loss, should really just be profit.

share_Contracts.zip

Edited January 23, 2007 by Guest

[Rob Lovett]

Genx,

I think that's making it more complex than it needs to be.

It's a very unlikely scenario. You would either want to sell all or part or more than one of the parcels of shares you bought, but not bits and pieces of each - not in the one sale at least.

Thanks again for your help.

Cheers

Rob

[Rob Lovett]

Genx,

The last response was in response to your previous post.

I'm still checking out what you did. Although... contracts are singular as in one transaction. It's either a purchase or a sale. A salemight relate to more than one purchase, all a purchase, or even portion of a purchase.

I need to play more but I can see a need to exclude contracts from the list because they've previously been sold. I know I saw some topics on that.

I'll check it out further tomorrow, I have a meeting I have to prepare for.

Thanks for what you've done so far.

Cheers

Rob

Edited January 23, 2007 by Guest

[Genx]

Yeh, you also have to check for purchases that have no remaining shares, etc. etc. I was just trying to throw together what could be an appropriate structure.

Technically, if you'll NEVER have multiple sales or purchases though, you could probably get away with no Sales or Purchases tables and just use the contracts table.

[comment]

I think it's time to summarize a few things, in the hope of getting some clarity:

First, I'd like to put the 'contract' issue in its correct place. I think these contracts are a red herring. We have spent a lot of time on them, while in fact they are not at all important.

A sales contract note may cover more than one purchase

Unless I am very much mistaken, what matters are the actual purchases. The contract itself is a non-event. Without the purchases, it is a mere declaration of intent.

Yes, the buys (and the sells) are created as "children" of a contract. But that is the extent of it. Once the transactions are made, they have an existence of their own. The gains/losses are calculated based on the dates and prices of transactions - not those of the contracts.

So I think the core relationship here is:

Client - Transactions

The other relationship:

Client - Contract - Transactions(2)

is merely an auxiliary relationship useful for creating transactions, and nothing else.

Next, I'd like to address the question that I think IS the important question here:

Should I have two tables bought_trans, sold _trans

I think LaRetta really moved us forward on this in her last post. I tend to agree that splitting the transactions into two separate tables is more a question of convenience than of essence. The important point is that for every sale, the origin/s of the sold shares must be identified.

So the question becomes: when I sell shares, how do I link the sale to the original purchase? Or rather purchases (plural) - because one sale can have several original purchases. This would suggest that a join table is required here - and the creation of the join records would have to be scripted.

I'll pause here to hear if we are all agreed so far.

[Genx]

Did you happen to see the sample file i posted comment?

Though i used three tables actually - Contracts, Purchases and Sales -- Seeing as only One Sale or One Purchase could ever happen per contract, you could probably just get away with using just the contracts table with a simple number field 1 for sale, 2 for purchase.

But yes, that's my understanding of this problem.

Edited January 23, 2007 by Guest

[LaRetta]

So the question becomes: when I sell shares, how do I link the sale to the original purchase? Or rather purchases (plural) - because one sale can have several original purchases. This would suggest that a join table is required here - and the creation of the join records would have to be scripted.

I think you have it, Michael but only Rob can confirm. One Sale can be comprised of different stock - each of which can originate from several different purchases (of the same stock from different contracts, purchased at different times at different prices). BTW, shares are bought/sold at an EA price. It seems obvious but it is easily forgotten (even by shareholders) who tend to look only at total stock value (note Rob's spreadsheet). It was something which needed constant reinforcement. This prorate must be applied to all shares within the single Purchase transaction and THAT share price populated through all distributions (Sales or transfers) of that Share. This is common omission which can complicate sale calculations 5 years later. I agree that the Contract is informational (one Contract can purchase several shares and it ties the purchase together). But that is no different than one check paying several invoices; ie, it is reference only.

So, are we looking at n:n here? I again question the views required. Rob's spreadsheets show flatfile and it will not be so. I question avg. share prices and extension at current value. I suppose I'm ahead of the structure here but if browse-mode of summaries are required then it might make a difference in structural approach (?).

Forgive me if I'm getting everyone off track but these concerns are sticking in my mind. I don't need answers; I just want them considered as Michael (smile) pulls this together.

LaRetta

[comment]

What is EA price?

[LaRetta]

Forgive the unclarity ... each.

[Rob Lovett]

One Sale can be comprised of different stock - ..... (one Contract can purchase several shares and it ties the purchase together). But that is no different than one check paying several invoices; ie, it is reference only.

LaRetta

Sorry for my absence I've been in meetings all day and tonight I have a Rotary meeting.

Just a clarification of what LaRetta said above. Down here I assume it's the same up there a contract is a contract to buy a number of shares in one stock only. e.g. a contract could be to by 10,000 ABC; another contract may be to buy 4,000 DEF will not include multiple purchases.

What LaRetta says about allocating a check against many invoices is a good analogy.

Thinking of that and applying it to GenX's sample. If, in the bottom portal in the contract layout, when you are applying the sale you were able to check a field that relates to the purchase until the total number Sold are exhausted - similar to what you do in MYOB to pay invoices - then we'd be on the money (excuse the terrible pun).

BTW, I was not concerned about the price per share because in the end it is total cost, which includes brokerage stamp duty etc, which determines profit, and I didn't want to complicate things further.

Thanks again for your help.

I'll be around for a little while and then back after Rotary.

Cheers

Rob

Edited January 24, 2007 by Guest

[Rob Lovett]

Hi all,

Just a short post to thank everyone for their help to date and to let you know I'm still working on this.

I've gone down the track of one table for contracts (transactions) self joined. I'm going to have a new table (Line items type) that will specifically deal with Buy contracts. The key will be a calculation based on whether the contract is a Buy or Sell. Since I only need to record the Sell contracts against the initial Buy, I'll have a global field as the key "Buy" to create a new record each time a "Buy" contract is created. When I sell I will refence this table to record the Sell contract against th Buy in the new table.

If this confuses you further don't worry. I'll post again when it's almost complete and if I get stuck. If I solve it I'll post the finished file FYI.

Cheers

Rob

[dorthy]

This is surely one of the best forums to help newbies learn cool stuff.

[color:white]SOCIETY RELATIONSHIPS ONLINE DATING

[color:white]Internet Online Dating Services Agency for Singles

SOCIETY RELATIONSHIPS ONLINE DATING