Personal Accounts - Handling share trading (revamped) (1 Viewer)

essaytee · Aug 9, 2014

Personal Accounts - Handle share trading (revamped)

Okay, after much research scouring the Internet for examples on how to incorporate share trading within a personal accounts model, I've decided on the following. I think it's almost there. I've not included miscellaneous attributes or simple lookup tables simply to focus on the model itself.

I do have a reservation regarding the highlighted fields above. The share price and quantity are recorded within the 'tbl_Shares_Traded' table. This can be calculated to arrive at a total figure. It will be this figure that resides in the "tbl_Transaction_Line_Items" table. Is there a way around this? Can this be better designed?

Any suggestions welcome.

Steve.

MarkK · Aug 9, 2014

I don't know this material as well as the accounting stuff we worked on in a different thread. That said, I don't see why you need both tables TransactionLineItems AND SharesTraded. Your LineItems table, which should not contain the Amount in yellow (always calculate that!) only has a notes field. The rest are keys. That's not enough data for a whole table.

Also, if the Transaction is a child of an account, does each line item need to link to an account too? Could a transaction link to an account and still have line-item children that link to a different account? Then maybe the transaction shouldn't link to an account?

What kind of transaction will there be? You need to buy, sell, and record price changes right? What else?

Thinking out loud a little, hope this helps,

essaytee · Aug 10, 2014

MarkK said:
I don't know this material as well as the accounting stuff we worked on in a different thread. That said, I don't see why you need both tables TransactionLineItems AND SharesTraded. Your LineItems table, which should not contain the Amount in yellow (always calculate that!) only has a notes field. The rest are keys. That's not enough data for a whole table.

Also, if the Transaction is a child of an account, does each line item need to link to an account too? Could a transaction link to an account and still have line-item children that link to a different account? Then maybe the transaction shouldn't link to an account?

What kind of transaction will there be? You need to buy, sell, and record price changes right? What else?

Thinking out loud a little, hope this helps,

Thanks Mark, I like the 'thinking out loud' bit, that's what I need.

Forget the share trading aspect for a moment, that leaves me with three tables.

Scenario:

I purchase groceries from the local supermarket for say $150 and at the same time I withdraw $100 in cash. For this one transaction, my savings account (because I used my debit card) will reflect $250 when I come to reconcile a month later upon receiving my statement. I want to record the fact that one entry is $150 for groceries and another entry is $100 cash.

For the one transaction I need the ability to separate items to their respecitive accounts (category types - income, expense or transfer) hence the inclusion of the third table solely for the separate items.

In the table "..... Line_Items" there is an Account_ID field which in effect is the corresponding or reverse account, to where the funds are being attributed and this Account_ID must exist in the table, "tbl_Accounts".

After entry of the initial transaction, spending $250, I will have the application automatically create the reverse entries. In fact, the reverse entries actually create two entries in the table "tbl_Transactions" referring to the Accounts "groceries" and "cash" and each of these "tbl_Transactions" entries will have only one table "....Line_Item" entry and each will refer to the Savings Account.

Now including the Share Trading aspect:

On trading shares, it's common for the individual share price to be recorded to three decimal digits, obviously multiplied by the quantity to arrive at a total amount for the block of shares traded.

Following on from my scenario above, an entry is made in the table "tbl_Transactions" and the originating account will be my share trading account. Two entries are made in the table, "......line_items", one for the block of shares traded and another for the 'Commission' expense in relation to the trade of shares. There could be other expenses associated to the trade of shares.

I've loosely modeled the share trading aspect on an inventory model. I need to know, at any given point in time, how many shares I own (not that many by the way).

I'm not sure if I should combine the tables ".... Line_Items" and ".....Shares_Traded" as share trading is only a minor component of my overall transactions. I see that the "....Shares_Traded" table is more 'product quantity - on hand' transaction type table and not a costing/pricing type table.

Market value of shares:

I've not included a table relating to the 'End of Day' data for shares. This information will basically contain, date, share, high price, low price, open price, close price. It will be the 'close price' of most concern. When I get my underlying basic schema in order I can then hook this table into the schema.

Some other thoughts (single amount field):

The actual amount entered will be recorded as either negative or positive. Upon creating the reverse entry the sign will be reversed. The data entry screen will have purpose controls for debits/credits, spending/receiving etc and this will dictate the sign of the amount to be entered into the tables.

Lastly:

I'm coming from a background of using Quicken and years ago, Microsoft Money, for handling my personal accounts. I hope the above clears things a little and provides a bit more information to advise on making improvements. I'm all ears.

Steve.

MarkK · Aug 10, 2014

Here's the bit I think you might still not get, and it comes from Quicken. Quicken makes a model of a transaction like there is always a current "Account." Every transaction in Quicken lives in an account, and money moves from the account to one or more categories, or one or more other accounts, but it's like there's this concept that the transaction is owned by an account. As a result, your Transaction has an AccountID as a foreign key, and seems to have a "Parent" account. Try to shift your thinking as follows . . .

tTransaction
TransactionID
Date
Payee
Memo

tPost
PostID
TransactionID
AccountID
Memo
Amount

tAccount
AccountID
Name
Type

In this model, the transaction is not "owned" by an account, and every transaction is a "Split Transaction", to use Quicken jargon. There are as many posts to and from accounts as there need to be to show where the money moved inside the transaction. The amounts of all posts in a transaction must balance to zero. That's it. All the money that came from somewhere, went somewhere. Zero

In your scenario, above: that is one transaction, a unique row in the transaction table, dated with a payee of "Local Supermarket". There are three posts to three accounts,
1) Savings -250,
2) Groceries +150,
3) Cash +100.

It balances to zero, and records all the movement of funds from source to destination accounts completely.
1) You'll have a reconcilable post in Savings.
2) You've increased your Groceries expense.
3) You've added 100 bucks to Cash.
It's all there.

To represent that you need a main form on the tTransaction table, and a datasheet subform on tPost. Here's a screen-shot of my Access home accounting system. . .

essaytee · Aug 10, 2014

MarkK said:
Here's the bit I think you might still not get, and it comes from Quicken. Quicken makes a model of a transaction like there is always a current "Account." Every transaction in Quicken lives in an account, and money moves from the account to one or more categories, or one or more other accounts, but it's like there's this concept that the transaction is owned by an account. As a result, your Transaction has an AccountID as a foreign key, and seems to have a "Parent" account. Try to shift your thinking as follows . . .

In this model, the transaction is not "owned" by an account, and every transaction is a "Split Transaction", to use Quicken jargon. There are as many posts to and from accounts as there need to be to show where the money moved inside the transaction. The amounts of all posts in a transaction must balance to zero. That's it. All the money that came from somewhere, went somewhere. Zero

In your scenario, above: that is one transaction, a unique row in the transaction table, dated with a payee of "Local Supermarket". There are three posts to three accounts,
1) Savings -250,
2) Groceries +150,
3) Cash +100.

It balances to zero, and records all the movement of funds from source to destination accounts completely.
1) You'll have a reconcilable post in Savings.
2) You've increased your Groceries expense.
3) You've added 100 bucks to Cash.
It's all there.

To represent that you need a main form on the tTransaction table, and a datasheet subform on tPost. Here's a screen-shot of my Access home accounting system. . .

Thanks Mark, yes, this is a different way of looking at it.

So, from a double-entry point of view, under your method, there is no need for any behind the scenes creation of 'reverse' entries as they are all handled within the one transaction. In order to achieve this when entering a transaction, the grocery/cash scenario, three direct entries are made at the same time, in your sub-form, which is the post table, making sure that it balances to zero. So you have to purposely use a negative sign for at least one or more amounts but as long as it all balances back to zero.

If I'm re-iterating it's only because I'm trying to come to grips with it. I must head out now and can't address the share trading component at the moment.

Much appreciated,

Steve.

essaytee · Aug 10, 2014

I've revamped my design yet again, taken on board advice and subsequently modified my 'Shares_Traded' table by removing the individual share price. As regards the purchase or sale of shares I was looking at it from the reverse in that I was wanting to calculate the total. The calculated value is the share price itself. I hope I'm on the right track here.

The posting table will therefore correctly (I think) show purchase/selling of shares and commision expenses.

Anyway, here's another schema.

Steve.

MarkK · Aug 10, 2014

That makes more sense to me in terms of the accounting structure, but now the shares. I haven't implemented this in my system, but you still need a dated transaction if you buy/sell shares. Also, I would represent a share as if it were an account. It is a place you can move money to, or move money out of, so it's an account, essentially. Then, a share trade--and you can see where this is going--is a specialized post. So it still fits into your existing tables, just add a quantity and price/unit field to tPost, and for share transactions use those fields instead. Maybe add an IsShare field to tAccount, or actually, you already have a type field, so that's enough.

Also, you'll break your whole system if you have many accounts in different currencies with the post amount in the same field. Each currency's value will vary on a day-to-day basis in respect to other currencies, and when that happens the balance of your system is gone. But I'll leave currencies alone for now. There's enough going on.

essaytee · Aug 11, 2014

MarkK said:
That makes more sense to me in terms of the accounting structure, but now the shares. I haven't implemented this in my system, but you still need a dated transaction if you buy/sell shares. Also, I would represent a share as if it were an account. It is a place you can move money to, or move money out of, so it's an account, essentially. Then, a share trade--and you can see where this is going--is a specialized post. So it still fits into your existing tables, just add a quantity and price/unit field to tPost, and for share transactions use those fields instead. Maybe add an IsShare field to tAccount, or actually, you already have a type field, so that's enough.

I've revamped my design yet again. It sort of fits with what you say above. I've effectively extended the Post table by linking it to the Shares Traded table (1 - 1 relationship). This extended Post entry will only be used for share trading and will form a part of the specialised post otherwise a normal post will occur.

As per your suggestion I envisage utilising a specialised transaction/posting for Share Trading by utilising the Price and Quantity fields. Also to consider is that for every share trade there is an associated commission expense. It therefore follows that the posting of the commission, the fields used would be the share price and quantity where the quantity is one.

Assuming the above is sound would it be wise to create another field in the Transaction table, IsTrade, so from a querying and reporting sense the commission expense will easily be retrieved (knowing to calculate the price x quantity)?

Anyway, here's my latest schema:

MarkK said:
Also, you'll break your whole system if you have many accounts in different currencies with the post amount in the same field. Each currency's value will vary on a day-to-day basis in respect to other currencies, and when that happens the balance of your system is gone. But I'll leave currencies alone for now. There's enough going on.

I'll come back to this when I've got the share trading nutted out.

All the help and advice so far is very much appreciated.

Steve.

MarkK · Aug 11, 2014

Well, if it was me, and as I mentioned, I'd try to make a "share" a specialized account. I'd make a "share trade" a specialized post. Then I could keep it all working with three tables. Maybe I'd need to add a couple of fields to my Account and Post tables. But you're obviously free to do whatever you think.

In my accounting system I have one post table, but I use it for Invoicing and Billing too, so in an invoice post I have Quantity, PricePerUnit, and Discount fields, which, when edited automatically adjust the Amount field. Those fields are not used in a regular post, they are used in Invoice/Bill transactions.

I would not move ahead with a one-to-one relationship between tables. How do you maintain that? How do you enforce that? How do make a user interface for that? One-to-one might as well be the same table.

essaytee · Aug 12, 2014

MarkK said:
Well, if it was me, and as I mentioned, I'd try to make a "share" a specialized account. I'd make a "share trade" a specialized post. Then I could keep it all working with three tables. Maybe I'd need to add a couple of fields to my Account and Post tables. But you're obviously free to do whatever you think.

In my accounting system I have one post table, but I use it for Invoicing and Billing too, so in an invoice post I have Quantity, PricePerUnit, and Discount fields, which, when edited automatically adjust the Amount field. Those fields are not used in a regular post, they are used in Invoice/Bill transactions.

I would not move ahead with a one-to-one relationship between tables. How do you maintain that? How do you enforce that? How do make a user interface for that? One-to-one might as well be the same table.

When you speak of 'specialised' account I'm unsure of what you actually mean.

Are you suggesting that the 'specialised' account is in fact a separate table? If a separate table then that requires another corresponding field in the post table, only used when necessary?

I believe under my latest schema that the shares traded can be traced back to an account. The actual shares themselves are in a 'lookup' table containing pertinent code, name & exchange information. I'm unsure what additional fields I could incorporate into a separate 'specialised' account (if in fact it is a separate table).

In relation to the 1 - 1 related table approach, I'm not yet committed and probably will revert back to a single table with additional fields but only used when required. I suppose the same could be said for the 1 - 1 approach, then additional table only used when needed.

Steve.

MarkK · Aug 12, 2014

No, by "specialized account" I mean a type of account; a record in the Account table.

You have bank accounts and expense accounts and credit accounts in the account table right? They are very different things, but they are functionally equivalent in that you can transfer (post) money freely between them. I don't see why you can't treat shares as if they were accounts. It would simplify your structure there quite considerably.

gotta run . . .

Steve R. · Aug 12, 2014

You are taking on a project that I have contemplated in a more limited fashion. That is keeping track of stock transactions. You may be after a more inclusive financial database. My comments below are a quick reflection.

I would suggest greater differentiation between your primary key and your foreign key which will also make them unique. Otherwise it gets to be a bit confusing. In the "tbl_Shares" you have "Share_ID". A duplicate name appears in "tbl_Post_Shares_Traded". I would suggest changing the name of the field in "tbl_Post_Shares_Traded" to something like "Share_IDFK" to identify it as a foreign key and make the names unique.

I did not get a sense that the database was designed to track "lots". The table "tbl_Post_Shares_Traded" could be adapted to do that. Essentially, a stock account is the sum of all lot transactions.

Since, I have not yet thought through how to track stock lots, I don't have much further advise. You may want to take a look at
Using Tax Lots: A Way To Minimize Taxes. Ignore the tax aspect and focus on how to track the buying and selling of stock by lot. I didn't get too many hits with "how to track stock lots", but further research may turn something up.

essaytee · Aug 13, 2014

Steve R. said:
You are taking on a project that I have contemplated in a more limited fashion. That is keeping track of stock transactions. You may be after a more inclusive financial database. My comments below are a quick reflection.

I would suggest greater differentiation between your primary key and your foreign key which will also make them unique. Otherwise it gets to be a bit confusing. In the "tbl_Shares" you have "Share_ID". A duplicate name appears in "tbl_Post_Shares_Traded". I would suggest changing the name of the field in "tbl_Post_Shares_Traded" to something like "Share_IDFK" to identify it as a foreign key and make the names unique.

Yes, I agree with you in this regard and is how I normally handle field names so as I can easily determine which tables they originate from. For my examples above I merely shortened field names somewhat (or lengthened) for clarity.

Steve R. said:
I did not get a sense that the database was designed to track "lots". The table "tbl_Post_Shares_Traded" could be adapted to do that. Essentially, a stock account is the sum of all lot transactions.

Since, I have not yet thought through how to track stock lots, I don't have much further advise. You may want to take a look at
Using Tax Lots: A Way To Minimize Taxes. Ignore the tax aspect and focus on how to track the buying and selling of stock by lot. I didn't get too many hits with "how to track stock lots", but further research may turn something up.

Yes, "lots" are on my radar. I've been researching FIFO, LIFO and average methods, I believe here in Australia we are forced to use the FIFO method (still to confirm) but having said that I'd prefer a system to be configured any which way or a default system to be overridden when required.

If you have any design ideas after thinking it through I'd appreciate your input. Like you, I haven't thought this through fully yet but I'll post something when I think I've got something that works (or close to working).

Steve.

essaytee · Aug 13, 2014

Personal Accounts - Handle share trading (Lots) (associate sell qty to buy qty)

Following on from my previous threads regarding the table structure for personal accounts I've arrived at " LOTS " regarding the selling of shares/stocks/securities and associating a quantity of shares to a particular purchase or purchases of shares.

It is possible to purchase the one stock many times at varying quantities and prices. At the time of selling there is a requirement to associate the quantity of shares to one or more LOTS of purchases of the same share in order to calculate overall costs involved. It will also be possible to sell partial lots, therefore a particular purchase will have a balance remaining.

Sample data best explains:

Code:

[FONT="Courier New"]
Lot #  Purchase Date  Stock  Quantity   Sell Tran #  Sell Date   Quantity    Balance
1        15/03/14         ABC     5000       1        05/06/14   4000         1000
1        15/03/14         ABC     5000       2        10/06/14   1000         0
2        25/03/14         ABC     3000                                       3000
3        16/04/14         ABC     6000       3        30/06/14   6000         0
4        20/05/14         ABC     3000       3        30/06/14   1000         2000
4        20/05/14         ABC     3000       4        15/07/14   2000         0
[/FONT]

(It's the best I could format, hopefully you get the idea.)

The following is the schema. The tables in question are:

tbl_Post_Shares_Traded, and
tbl_Lot_Sell.

The way I see it is that the simple act of purchasing shares creates a LOT within the Post_Shares_Traded table.

The Lot_Sell table is only used for selling shares and records the current "Share_Traded_ID" from "tbl_Post_Shares_Traded" (the actual sale) and "Share_Traded_ID" (named, Share_Traded_Buy_ID). There could be multiple entries.

I believe I will be able to track partial balances for future trades of particular stocks.

Rule to maintain:

Can not have a sold quantity greater than the balance of particular LOT (purchase).

I welcome any suggestions, improvements etc.

Steve.

essaytee · Aug 13, 2014

essaytee said:
Yes, "lots" are on my radar. I've been researching FIFO, LIFO and average methods, I believe here in Australia we are forced to use the FIFO method (still to confirm) but having said that I'd prefer a system to be configured any which way or a default system to be overridden when required.

If you have any design ideas after thinking it through I'd appreciate your input. Like you, I haven't thought this through fully yet but I'll post something when I think I've got something that works (or close to working).

Steve.

I've creatred a new thread purposely for LOTS, can be found here.

EDIT: New thread created merged back into this thread.

Steve R. · Aug 13, 2014

To achieve "lot" identification, may I suggest the (draft) outline below. It is not meant to be a complete solution.

Your table "tbl_Post_Shares_Traded" can be modified to record purchases only. Furthermore, add a unique auto-increment field to identify the lot number for each stock purchase.

Create a separate table to track each stock sale. This table will need to have a field identifying the lot from which the stock is being sold. As stock is sold, you will need VBA code or SQL to compute how much stock remains in that lot and that lot's remaining cost-basis.

The stock sale table will need to have a transaction number, but this (same) number may be used multiple times. See below.

For example, you buy 1,000 share of XYZ Corporation at $20.00. That makes the cost-basis for your stock $20,000. (I am leaving out the effect of brokerage commissions or other fees that may affect the cost basis.)

A while later, you sell 100 shares which is recorded in your stock sale table. You then use VBA or SQL to compute that your remaining balance is 900 shares and that your cost-base is now $18,000. The difference between your purchase price ($20) and the selling price is assigned to "realized gain/loss".

As shares of XYZ are progressively sold, there will come a time when a lot is sold out. Consequently, the program needs to recognize this situation. So let's assume that you sell 200 shares of XYZ but only 100 shares remain in lot#1. Given that situation, the stock sale table will record the sale of 200 shares as the sale of two 100 share lots, once to lot#1 and once to lot#2. This will mean using the same sale transaction number twice.

I hope that outline above helps.

essaytee · Aug 13, 2014

Steve R. said:
To achieve "lot" identification, may I suggest the (draft) outline below. It is not meant to be a complete solution.

Your table "tbl_Post_Shares_Traded" can be modified to record purchases only. Furthermore, add a unique auto-increment field to identify the lot number for each stock purchase.

Create a separate table to track each stock sale. This table will need to have a field identifying the lot from which the stock is being sold. As stock is sold, you will need VBA code or SQL to compute how much stock remains in that lot and that lot's remaining cost-basis.

The stock sale table will need to have a transaction number, but this (same) number may be used multiple times. See below.

For example, you buy 1,000 share of XYZ Corporation at $20.00. That makes the cost-basis for your stock $20,000. (I am leaving out the effect of brokerage commissions or other fees that may affect the cost basis.)

A while later, you sell 100 shares which is recorded in your stock sale table. You then use VBA or SQL to compute that your remaining balance is 900 shares and that your cost-base is now $18,000. The difference between your purchase price ($20) and the selling price is assigned to "realized gain/loss".

As shares of XYZ are progressively sold, there will come a time when a lot is sold out. Consequently, the program needs to recognize this situation. So let's assume that you sell 200 shares of XYZ but only 100 shares remain in lot#1. Given that situation, the stock sale table will record the sale of 200 shares as the sale of two 100 share lots, once to lot#1 and once to lot#2. This will mean using the same sale transaction number twice.

I hope that outline above helps.

Thanks Steve. We must have posted about the same time as I created a new thread here which I think is more or less along the same lines as your outline.

Steve.

MarkK · Aug 13, 2014

How does a "Lot" different from a transaction?

essaytee · Aug 14, 2014

Re: Personal Accounts - Handle share trading (Lots) (associate sell qty to buy qty)

MarkK said:
How does a "Lot" different from a transaction?

Essentially they are the same though the LOT process is extended to account for the fact that all sell transactions must be related back to the purchase transactions and keep track of balances on hand.

When purchase transaction balances are zero as a result of various sell transactions then it is not possible to sell against a zero balance purchase transaction.

Steve.

essaytee · Aug 14, 2014

essaytee said:
I've creatred a new thread purposely for LOTS, can be found here.

A new thread was created but it has subsequently been merged back into this thread. That is why the above quote probably doesn't make any sense and it links back to this thread.

Personal Accounts - Handling share trading (revamped) (1 Viewer)

Need a good one-liner.

bit cruncher

Need a good one-liner.

bit cruncher

Attachments

Need a good one-liner.

Need a good one-liner.

bit cruncher

Need a good one-liner.

bit cruncher

Need a good one-liner.

bit cruncher

Retired

Need a good one-liner.

Need a good one-liner.

Need a good one-liner.

Retired

Need a good one-liner.

bit cruncher

Need a good one-liner.

Need a good one-liner.

Similar threads

Users who are viewing this thread