Hi all,

This is a great forum and though I have lurked here often I have posted very little (if ever.. maybe years ago).

I've built many databases over many years but although I'm not a novice, I'm not very accomplished either.

I'm grappling with relationship post FM7 and figuring out all I can regarding tables and TOs but I'm not quite there yet with this problem. I think if I get to understand this I'll have made a quantum leap.

I've attached an Excel Spreadsheet for clarity.

The object is to manage a Share portfolio and report holdings and profits/Losses on the capital gain/loss.

The first problem is multiple sales for the one buy. I can solve that but it's probably not very elegant.This is relatively easy because the shares were all bought at the same price so profit calculation and numbers held are straight forward.

The real problem arises where more than parcel of shares were bought at varying prices on varying days. This means that different parcels will have different profits losses etc.

I'm fairly confident I've expressed the problem fairly clearly in the spreadsheet.

Any help would be appreciated.

Cheers

Rob

SharesRelat.xls.zip

Am I approaching this from the wrong angle? Should I have two tables bought_trans, sold _trans and when a stock is sold populate the sold_contract_number field in the bought_trans table?

I was trying to have one trans table with bought and sold contract_numbers in each record i.e. populate the the sold_contract_number in the relavant bought record from the sold record is created.

Any help would be appreciated.

Cheers

Rob

Edited January 23, 200718 yr by Guest

I'm starting to feel terribly unloved, is anyone able to help me with this question?

Cheers

Rob

Lol unloved, the question's too long winded. But i'll try take a look tonight if no one else has answered you yet. In the mean time, dumb it down more.

I think the question was the right length and well presented. As you've seen, Rob, you've had 10 downloads. But sometimes more complex solutions take a bit of thought. Don't give up on us - I've been pulling all-nighters and I'm just now getting my brain back. Hang in there!

And no, I haven't looked at the downloaded file yet - it's on my desktop. But if you haven't received answers within first few hours it usually indicates that it takes a bit of thinking to provide a good answer.

UPDATE: See there - it's up to 15 since I downloaded earlier today.

LaRetta

Thanks Genx,

Dumb it down, I feel dumb enough now. But I'll try.

It's a database to manage a number of share portfolios.

I need to record share purchases (identified by the stock market code, downunder ours usually have 3 letters). I need to record how many of them are sold and at what price.

Each stockmarket trans is identified by contract notes.

A sales contract note may cover more than one purchase e.g. Buy shares on four different ocassions and sell them all at once.

On the other hand a bought contract may be covered by more than one sale e.g. Buy a parcel of shares on one ocassion and sell them off a bit at a time.

For each share code I need to record what was bought and when and for how much so I can calculate the profit for each stock. The buy price can vary so I'm really trying to link each purchase event with the relevant sale event.

Now I don't know if I've dumbed it down or whether I've made it more confusing.

Fingers crossed.

Rob

No, that's much less confusing. Remember that we may or may not know much about stocks, nor what you're doing with them. It seems as if a simple self-relationship on the Stock Code would tie them together. Then you could do a Sum (self-relationship::Amount). But why is Amount positive for the 1st Bought, then negative for all the rest?

Also, if you have only a self-relationship on the Stock Code, then a while from now you could not differentiate between earlier transactions and later. Or does that matter?

It is still a bit confusing to me. I believe I would have a separate table for contracts, and record the transactions in a portal to a transactions table - or perhaps even two portals (to the same table), one for purchases, one for sales.

A lot depends on how exactly do you want to calculate profit/loss. If the calculation is done on a contract level, then it should be easy - although it's not clear what the result should be when not all the stock has been sold yet.

If each sale needs to calculate its own profit/loss, then it could get more complex - for example, what is the purchase price when there were multiple purchases?

Hi Fenton,

It was positive for the purchase to show the value of the portfolio. When you first buy the shares like inventory you have say 10,000 and then the number reduces as you sell them.

Yes Comment that is the problem.

I could buy the same share at different prices on different days but sell them all at one price. The profits on the sales will be different. And of course vice versa. I might buy them all at one or varying prices and sell them in stages at different prices vis a vis again different profits.

The price on the stock m,arket varies each day.

Thanks for your help guys, I'm starting to feel a little more loved.

Cheers

Rob

Then here's more love ...

When I worked with these charts (10 or so years ago), we also tracked the value yearly (sorta like Inventory's COGS), increasing/decreasing the value [color:green](based upon market price) and those funds (profit/loss) also need to be pulled in (and displayed apart) or moved to income/loss. Check with your Financial Planner on how to accrue them. I don't know how the rules might have changed. But I think Michael's on the right track. Actually, your display might control the design in this instance, ie, required formats are pretty standard and I envision portals within portals.

I'll see what I can dig up. But that won't help with the FM structure. I understand how it works but I'm not that great at relationships. Ask the people that know me.

LaRetta

Edited January 23, 200718 yr by Guest
Added green

Aah..a quick clarification.

I may have made it sound more complex than it is.

I don't care what happens to the price each day, I only mention that to highlight that the price changes.

I am only concerned with shares bought and then their subsequent sale for each stock code.

If we said that a stock is open when we buy it and the transaction is finally closed when we sell all of them.

Then I would need to have a report on a list of all open stocks at a given date i.e. my current portfolio (list of shares currently held)

I also need to report for a financial year that would show the profit for all sales in that year.

I hope that helps.

Cheers

Rob

that is the problem.

But we need to know the solution before proceeding. This is not a Filemaker problem: the question is what is the correct way to perform this calculation, using pencil and paper.

Yes, Shares (your Portfolio, one record per Stock Name) is one-to-many to Holdings which is one-to-many Transactions. It is identical to Invoices to LineItems (a General Journal). You'll never sell a stock to yourself. It's akin to standard invoice structure (with a middle table) where LineItems will also hold receipts and adjustments. Is this level necessary? Yes, because adjustments and stock transfers can only effect part of each holding. Buy and sell in Holdings.

One Share to many Holdings. Each Holding has many Transactions (pieces sold/purchased). When Sum(Transactions::NumberShares) = 0, it closes. Portfolio activity (your requested display) could probably be a grouped report which would eliminate the complexity of the structure.

Do you understand a one-to-many relationship?

Edited January 23, 200718 yr by Guest
Added some words

Thanks Comment

The penny drops and I wished I'd checked my spreadsheet earlier. Apologies all round. Please ignore the $balance column it is just a running total that eventually shows you the profit.

Stock Code ABC

1/1/2007 Bought 10,000 for $10,100

1/2/2007 Sold 5,000 for $6200 profit=$6200-Price Bought for 5,000 {10100/1000*5000}

which is $6200-$5050=$1150

1/3/2007 Sold 2,000 for $4000

Profit is $4000-(10100/100*2000)=$1980.

So the cost of these shares was constant and the sale price varied.

The other example would work in much the same way only it is the Buy price that varies.

Again apologies for the error in the spreadsheet and thanks again for your patience.

Cheers

Rob

Edited January 23, 200718 yr by Guest

one record per Stock Name

Is that so? So if I bought Stock ABC in January, sold all of it February, and bought again in September - would all three transactions have the same parent (Holding? Contract?) record?

Dividends and such are an interesting problem. Perhaps it could be solved by bypassing the details and recording only the final result, e.g.

Stock Dividend:

Bought x shares at price 0.

Cash dividend:

Sold 0 shares, amount received y.

So if I bought Stock ABC in January, sold all of it February, and bought again in September - would all three transactions have the same parent (Holding? Contract?) record?

Well. That's why I haven't jumped forward with a demo file. At American Express, we worked them in different formats as needed in their own program. I only knew the screen views. But to answer your question, all three would have the same Share (Contract?) but NOT the same Holding because each different [color:green][color:red]Holding/Price/Date is the unique identifier. These Holdings are still different records because the Transactions against the share group (Holding) must be tracked independently.

Dividends, Stock Dividends, Share transfers are Transactions (like a Credit Adjustment in LineItems) and are pro-rated back against each Holding at the % of the whole [color:green]at year end. As we talk it out, I'm getting more clarity. Again my question ... the views required. Because from the ones I've seen, it varies greatly from simple report to drill-down functionality.

I hope I'm not muddying this tread; it's a subject I *used* to know quite well but I feel the rust oozing out and about. Truly, we don't know who will be using this solution. If it's an individual, very little activity takes place and their needs are simple; if for an investment group, it would be a different story.

UPDATE: The red was green but it's now a correction.

LaRetta

Edited January 23, 200718 yr by Guest
Ooops! Added green & corrected red

Hi Comment,

maybe the terminology is different up above but down under each and every Buy or Sale has it's own contract number (you contract to buy (or Sell) so many shares @ such and such a price. So it would be handy to match the sale contract number with the relevant buy contract number. And, as mentioned before, this can be one Buy for many Sales or One sale for many Buys.

LaRetta,

I'm not that interested in the dividends, I track them through the Cash Book file.

It's really just a matter of tracking holdings and capital gain/loss on the sale of the shares.

Cheers

Rob

Edited January 23, 200718 yr by Guest

This is what was throwing me - Contract numbers each for every buy/sale is your Holdings table and not your Portfolio. It would also help us if you answered my questions. But Michael has enough to go on so I'll bow out. :wink2:

I am still unclear on a couple of things, the main being this:

Client says: buy me 10,000 shares of ABC (contract 001). Later on, client asks to sell 5,000 shares (contract 002?). Still later, client wants to buy 2,000 shares. Finally, client sells 6,000 shares, remaining with 1,000 shares.

How do you want to handle this? Can we calculate an aggregate profit for all 4 contracts together, or do we need to calculate the profit for each sale separately (which is going to be complex, AND we would need to set rules for what is the purchase price in contract 004).

I'll bow out.

Don't even think about it!

Sorry Laretta,

I appreciate your help. I think the only question I failed to answer was regarding one to many relationships. If you mean do I understand in this case one Stock code is related to many transactions. Yes I understand that. And I can set that up to show the transactions for each stock in a portal. But what I'm really missing is understanding how to set it up both ways.

It seems to me that the first trancaction will always be a Buy. I'm not into short selling (for those who aren't familiar, that's when you Sell stocks you have not yet bought hoping the price will go down). So if I could display the list of Buys in a report with their relevant Sale details then I'd be home and hosed.

BTW, I've gone from unloved to "I haven't had a chance to try anything yet" because I was at work when all the suggestions came in.

I'll get stuck into it after dinner and see what Michael suggests

Thanks LaRetta.

Sorry I've made such a mess of this explanation.

I thought that was the easy part!

Sorry Comment,

I only just saw your post with Qs. It went to page two for me which is when you asked LaRetta not to bow out.

The correct way (according to the Tax Dept your Inland revenue) is to work the profit out for each Buy contract not to average it.

I'm not sure of what you mean about setting the rules for contract 4?

Cheers

Rob

I can't speak for Comment but I'm working on getting clarity (inside) on structure so displaying the summaries will be dynamic. You presented quite well. Since it is date/year tied, then the data (by its nature) will be static which you need to maintain as history. But I now must turn to network issues. I'll check back later on (as I can) and I'm still working on ideas inside. However, I have no doubt Michael will beat me to it - in speed and quality of solution.

An aside, this presents exactly like an Invoices to LineItems to Inventory concept even as far as costing per share. When inventory is gone, you can't sell more but your LineItems tracks the history of it.

Ahhhhh,

I'd love to contribute, but i'm still confused .

Rob could you perhaps draw a diagram of what you THINK you need, or of how it works in the real world e.g...

Customer > Has Many Invoices > Has Many Line Items

Line Items Draw Info From Products...

Or Contracts > Have Many Stocks > Have Many Sale Parties

(That's an example only)

Or Stocks > Have Many Prices At Different Times

...Stocks > Also Have Many Sales and Purchases at different prices

I am still in the dark:

The correct way (according to the Tax Dept your Inland revenue) is to work the profit out for each Buy contract not to average it.

Surely, you mean the profit for each [color:red]Sell? But that still doesn't explain HOW to work out the profit. That was my question regarding Contract 004. I am no accountant, but I think you need to choose between LIFO, FIFO and average. If you don't understand the question, I can elaborate, but I am afraid that would also mean you don't have the answer.

I think the structure (not the tables, but the relationships, calculations and reports) depends on it.

An aside, this presents exactly like an Invoices to LineItems to Inventory concept even as far as costing per share. When inventory is gone, you can't sell more but your LineItems tracks the history of it.

I agree, but if I might add a twist and I don't know if this will help or hinder but instead of it being FIFO (First in First Out) it should be the users choice.i.e. it may be more beneficial to sell the last shares first ( LIFO) to allow the older shares to mature and attract less tax.

I didn't want to even think about that till I got my head around the first bit.

Thanks again

Rob

Does this help?

Attempt_for_sanity.xls.zip

"The profit on the shares sold in contract 125 would depend on whether we "chose" to sell contract 118 or 120"

... Why? How are they dependant on each other? Contract 118 is a purchase of 10,000 GHI shares, on contract 120 is likewise a purchase of 10,000 GHI shares... You can resell anywhere between 1-20000 shares that you have as your current balance at the market value -- Is there something missing here?

Lol you buy GHI high, and sell low, why's that?

Michael,

"The correct way (according to the Tax Dept your Inland revenue) is to work the profit out for each Buy contract not to average it."

It's a Buy contract to the shareholder. It is the Holdings table. Purchase and Stock Transfers in only (usually). No sales will happen from Holdings - a Holding will close when value is 0 (sum of Transactions).

Profit:

Purchase 100 shares (1 record in Holdings) of ABC (Portfolio; the stock) for $5 per share. Portfolio worth $500.

Sell 10 shares (1 record in Transactions) from THIS Holding for $10/share. The 'cost' is looked up from this Holding record.

Holdings::ShareValue = $400 or Original value less sum of related Transactions

Another purchase of same stock ABC. New Holding. Date & price make it unique to other ABC Holdings. This is the Contract. Purchased 50 shares (1 new record in Holdings) for $8 per share.

Sell 5 shares (1 record in Transactions) for $10/share = $50. Cost is looked up from this Holding. $8 * 5 = 45. Profit $5.

Purchases (Holdings) are kept separate from Transactions only because they are 1:n and the two tables can independently balance and display more easily. IIRC, screens for viewing all current Holdings had more fields, such as grabbing current NSE rates to determine current worth, history, Sellee details etc. Structurally, they shouldn't need to be separate but my mind (the part still awake) thinks they should. Otherwise we're turning it into flat-file (like moving Invoices to LineItems).

LaRetta

Genx,

yours and my tax system discounts CGT (Capital Gains Tax) once the shares are held more than a year.

If you wanted to sell close to anniversary to lock in profits, and since you will pay the higher tax anyway you may wish to sell the shares whose anniversary is further away to give yourself a better chance of paying the discount rate on the next sale.

This is the reason on why you want the choice but has no other bearing on the matter.

"

Lol you buy GHI high, and sell low, why's that?

I wish my shares always went up, but they don't and sometimes you have to cut and run.

Cheers

Rob

Can you sell a portion of a contract amount of shares, or do you only ever sell entire contract amounts?

Yes you can sell a portion or as many portions as you like as per the first example in the spreadsheet.

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?

... 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, 200718 yr by Guest

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

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, 200718 yr by Guest

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.

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.

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, 200718 yr by Guest

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

What is EA price?

Forgive the unclarity ... each.

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, 200718 yr by Guest

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

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