Relationships not quite there yet

[Rob Lovett]

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

[Rob Lovett]

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, 2007 by Guest

[Rob Lovett]

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

Cheers

Rob

[Genx]

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.

[LaRetta]

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

[Rob Lovett]

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

[Fenton]

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?

[comment]

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?

[Rob Lovett]

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.

[Rob Lovett]

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

[LaRetta]

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, 2007 by Guest
Added green

[Rob Lovett]

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

[comment]

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.

[LaRetta]

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, 2007 by Guest
Added some words

[Rob Lovett]

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, 2007 by Guest

[comment]

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.

[LaRetta]

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, 2007 by Guest
Ooops! Added green & corrected red

[Rob Lovett]

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, 2007 by Guest

[LaRetta]

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:

[comment]

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).

[comment]

I'll bow out.

Don't even think about it!

[Rob Lovett]

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.

[Rob Lovett]

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

I thought that was the easy part!

[Rob Lovett]

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

[LaRetta]

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.

[Genx]

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

[comment]

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.

[Rob Lovett]

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

[Rob Lovett]

Does this help?

Attempt_for_sanity.xls.zip

[Genx]

"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?

[LaRetta]

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

[Rob Lovett]

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.

[Rob Lovett]

"

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

[Genx]

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

[Rob Lovett]

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