Personal Accounts - Handling share trading (revamped)

[essaytee]

Back tracking a little. Isn't that what the design stage is all about?

Upon researching how to handle currency exchange it became apparent that in effect currency exchange is similar to share trading and therefore the schema would be the same.

Share Trading concept : Value = share price * number of shares
Foreign Exchange concept: Value = exchange rate * amount

Generally then : Value = Unit Price * Quantity

As a result, I've decided to record everything (normal transactions, share trading etc.) by use of unit price and quantity within the Posting table.

From an application standpoint, when entering normal transactions there will be no reference to a quantity, it will default to 1. There should be no need to display it. On the other hand, if it's a share transaction or foreign currency exchange then quantity plays a role and depending upon whether it is a share trade or exchange rate scenario will dictate the labels used for quantity and unit price.

Here's my updated schema:

I've added further fields to the Accounts table but that doesn't mean they all have to be filled in. Not all accounts will be shares and therefore the Share ID (fk) will not be entered.

Some concepts in the early stages.

I've included a yes/no field "Account_Container". The idea is that a container merely holds other accounts within it but it itself cannot have transactions. A typical scenario is that of a broking account holding many shares (each share is a separate account).

The Account Container concept could also be considered a form of Account Type and therefore accounted for within the Account Type field.

"Account_Parent_ID" is a FK to the Account_ID within the same table. Two scenarios come to mind .

1. Create an Expense Account, Motor Car. Create other expense accounts, Petrol, Maintenance, Insurance etc. but for these accounts enter the Account_ID of Motor Car into the field, Account_Parent_ID. The user may elect to consider that the top level expense account of Motor Car can not be directed transacted, only it's sub-entries and this would be done by setting the Account_Container in the Motor Car account. I wouldn't but this is optional.

2. Create a Broking Account, "Etrade" and make it a Container only account. The purpose of this account is merely to hold actual share accounts. Create various share accounts making sure to enter the Account_ID of Etrade into the Account_Parent_ID fields.

The process of selling shares is still the same though I have removed the now redundant "tbl_Post_Shares_Traded" table as this information can be accounted for within the posting table. I have also removed reference to 'buy and sell' as this can be determined from the amount posted.

I've referenced currency exchange above but I haven't yet thoroughly run it through the schema. Also still to consider 'end of day' prices for shares.

This of course is still a work in progress.


Steve.

 

[MarkK]

In this design, one share can have many accounts. What does that mean? I still think a share will function best if it IS an account. A share can be the source or destination of a transfer--buy or sell--so it's an account, no? It certainly appears to function as one.

And, if a share is indeed an account, then I don't see a need to relate posts to each other in Lots. Your concept of a lot will join posts that are posted to the same account correct? So you don't need it then, since those posts are already joined by account.

IMO, hope this helps,

 

[essaytee]

In this design, one share can have many accounts. What does that mean? I still think a share will function best if it IS an account. A share can be the source or destination of a transfer--buy or sell--so it's an account, no? It certainly appears to function as one.

One stock/share is one account. It is possible to have two or more broker organisations and therefore the same stock/share can be purchased via any of the broker organisations. For all intents and purposes you can only purchase stocks/shares from a broker. That is, I may have purchased 5000 shares of ABC via broker 1 and also purchase 4000 shares of ABC via broker 2. Overall I have 9000 shares of ABC. In order to sell the 9000 shares it requires two transactions, 5000 via broker 1 and 4000 via broker 2 (which also involves two lots of commission expenses). I am treating the stock of ABC as two distinct entities, they have different Account_ID's as they have originated from different brokers.

And, if a share is indeed an account, then I don't see a need to relate posts to each other in Lots. Your concept of a lot will join posts that are posted to the same account correct? So you don't need it then, since those posts are already joined by account.

IMO, hope this helps,

I'll address this later.

 

[essaytee]

And, if a share is indeed an account, then I don't see a need to relate posts to each other in Lots. Your concept of a lot will join posts that are posted to the same account correct? So you don't need it then, since those posts are already joined by account.

IMO, hope this helps,

I'm not following.

Assume numerous purchases of stock, ABC, at different share prices and quantities and then a selling of the stock, ABC.

Following are the purchases:

Lot   Date          Price        Quantity

1      02 Jan 14      3.20       1,000
2      15 Jan 14      2.90       2,000
3      20 Feb 14      2.50       5,000
4      01 Jun 14      4.50       2,000
                                 ---------                         
                                 10,000
                                 ---------

During the above period there was no selling of stock, ABC. The total shares on hand for stock, ABC, is 10000.

Now assume I sell 2500 shares at a price of $4.00 per share on 7 Jul 14.

I have to associate the selling of the shares with the prior purchases (lots). In fact, I have the choice as to which purchases I associate the sale of shares. When assigning the sale of shares against the purchases a remaining balance has to be kept, per lot. In the case of selling 2500 shares there will be a lot with a partial balance remaining.

The following is after the sale of 2500 shares.

Lot   Date          Price        Quantity          Sell Date      Sell Quantity    Lot Balance     

1      02 Jan 14      3.20       1,000             07 Jul 14       1,000                  0
2      15 Jan 14      2.90       2,000             07 Jul 14       1,500                500
3      20 Feb 14      2.50       5,000                                                 5,000
4      01 Jun 14      4.50       2,000                                                 2,000 
                                                                                      ---------
                                                                                      7,500
                                                                                      ----------

From the above I could have just as easily and legally (for Aussie Tax purposes) associated the selling of shares against lots 3 & 4.

From my schema (post 21 above) I believe the table, "tbl_Lot_Sell", captures this information but if there is a simpler or more efficient method it will have to be spelt out.

Steve.

 

[MarkK]

I don't see how this concept of "Lot" is useful or important. You buy a quantity of shares. Fine. Then at some future date you sell some. Why does that sale need to be related to some discrete purchase? Is there a document that supports this concept of "Lot?" What is the real-world structure that we are keeping track of? Why can't you just do . . . .

Date           Price        Quantity
02 Jan 14      3.20       1,000
15 Jan 14      2.90       2,000
20 Feb 14      2.50       5,000
01 Jun 14      4.50       2,000
[COLOR="DarkRed"]07 Jul 14      4.25      -2,500[/COLOR]

Balance is super easy to calculate for any date. What else is there? How can it matter which of the purchased shares the sale is applied against?

 

[essaytee]

I don't see how this concept of "Lot" is useful or important. You buy a quantity of shares. Fine. Then at some future date you sell some. Why does that sale need to be related to some discrete purchase? Is there a document that supports this concept of "Lot?" What is the real-world structure that we are keeping track of? Why can't you just do . . . .

Date           Price        Quantity
02 Jan 14      3.20       1,000
15 Jan 14      2.90       2,000
20 Feb 14      2.50       5,000
01 Jun 14      4.50       2,000
[COLOR="DarkRed"]07 Jul 14      4.25      -2,500[/COLOR]

Balance is super easy to calculate for any date. What else is there? How can it matter which of the purchased shares the sale is applied against?

It's important as it relates back to profit taking and tax implications.

In my sample data I sold 2500 shares at $4.00 each which totals $10,000. In reality I would have assigned/associated those shares sold to Lot's #4 and #1 where the cost of 2500 shares overall was $10,600. If effect, I sold the shares at a loss of $600 and it's this loss (or profit) which I claim at tax time.

For the next sale I can only assign/associate the shares sold to the remaining lots, I can not double-dip.

 

[Steve R.]

I don't see how this concept of "Lot" is useful or important. You buy a quantity of shares. Fine. Then at some future date you sell some. Why does that sale need to be related to some discrete purchase? Is there a document that supports this concept of "Lot?" What is the real-world structure that we are keeping track of?

For Accounting and Tax purposes. Also for recording the holding period of a stock purchase. If you utilize a stock brokerage firm, look at your statements. You will see that they record your transactions by lot and holding period.

 

[essaytee]

My latest schema is attached (is getting too big as an embedded image) and I've modified and expanded it slightly to account for bank reconcilliation, numerous attachments per transaction, lookup table for account types and 'End of Day' prices for shares.

The schema is by no way complete but it's a start.

I will first be working on the Accounts table, adding and editing accounts. The Accounts table records all Assets, Liabilities, Expenses, Income and Trading. The Trading account will be specific to share trading and foreign exchange.

Though the schema doesn't show it I'll toy with the idea of 1-1 related tables pertinent to Assets, Liabilities, Expenses, Income and Trading. The idea being that more specific or pertinent field names will be utilised. As I said, I'll experiment.

As always any comments welcome.

Steve.

 

[alamnizamani]

Please attache file for practice

 

[essaytee]

Please attache file for practice

All the information is contained in the last image I posted in this thread. It's only the schema, there are no queries, forms or reports.

By saying, "it's only the schema" in no way do I diminish the value of the schema, in fact, it's the most important component of any project.

Steve.