Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: Share Trading Database - Relationship

    Hi,

    I am learning Access and trying to build a database to keep track of share trading for my clients - from scratch. I've worked out the relationship. Any of you database gurus can give me an advice ?

    I have two questions:

    A. Overall, does the relationship looks right?
    B. There will be some trades that are partially executed, how can I track these ? (See step 4,5, below for explanation)

    Attached is the relationship image. And here is the current process (at the moment I am using Excel to keep track these trades but it's getting cumbersome).

    1. After my staff discuss with client over phone, they will agree with each other what shares to buy/sell. My staff would enter the Order (comprising of many Trades) into the system (current Excel - but I want to migrate to Access). At this stage, the Status for each trade is "Pending Client Approval"

    2. After client send in the signed form (or an email reply), the trade Status is change to "Pend Supervisor Approval". I will need to review the trade. If the timing is right, I will change the trade Status to "Awaiting Execution".

    3. Another staff will put the trades into the market, hence the status is changed to "In Market".

    4. The next day, I will receive a report from the broker, with details of which shares have been traded (bought or sold). Some trades are 100% traded. For example, if I want to buy 100 shares of ABC, i get 100 shares of ABC. If that is the case, trade status of this trade is changed to "Completed"

    5.Some trades, however, are partially completed only. For example, I might want to buy 100 shares of DEF but could only buy 56 of them. So, trade Status of this trade is changed to "Partially Completed".

    Thanks for your help. I will continue to ask questions along the way when i'm working in this project. Hope to have your support !

    Regards,
    TD
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    a screen capture of the relationship screen would not require us to open a word document. Perhaps that is why you have not received any views of your attached file.

    With respect to partially traded, it might be easier to have a column for amount requested and another for amount traded, so you can do a query for those that are not equal.
    John M Reynolds

  3. #3
    Join Date
    Jun 2011
    Posts
    8
    Thanks for your hint, jmrsudbury. I reattached it here as a image.

    Also regarding to the partial trade, I like your idea of separating the field "Amount Requested" and "Amount Traded". But what if I also want to keep track at a more detailed level of the partial trade, how can I do that? Should I have more fields (e.g. "Amount Traded Day 1", "Amount Traded Day 2") ??

    For example, today a client requested to buy 200 shares of ABC. I put that trade into the market.

    ++Three days later, I received a report from the broker that the following has been done:
    Day XX/XX/XX11: 20 shares of ABC bought
    Day XX/XX/XX11: 60 shares of ABC bought

    At this point of time, I need to know and tell clients how many have been bought in EACH day, and how many have not been bought

    ++Six days later, I received another report
    Day XX/XX/XX11: 120 shares of ABC bought

    At this point of time, I can change trade status to "Completed" but also like to keeps this details elsewhere.

    Cheers,
    TD
    Attached Thumbnails Attached Thumbnails Relationship.bmp  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id expect authorisation in order to be a foreign key to an new table called, say, staff
    Id expect staff in order to be a foreign key to staff
    that tabel staff would have a flag on it indicating if that member of staff could authorise trades.

    entity trade seems flaky
    don't understand original / completed / origianl amount / completed amount.doesnt' feel right

    Im guessing a trade is a specific transaction (it could be a buy / sell / rights issue/ share split / write off whatever. so you need some entity to defien what type of transaction it is, and how that transaction affects the stock (ie the buy increases, sell decreases)
    it happens at a specific point in time, although it may be completed at a later point in time. so there shoudl be no concept of original value or other balances in this entity when you need to get a current balance you run a query to work out the balance as and when required

    there should be no concept of original value, as those greedy grasping gits (AKA Tax authorities) will need to know what was paid for the stock and when (in case there is tapering tax relief). so you need to know the value when the stocks were accquired
    the revenue will probably demand a FIFO model. alternative if original amount means original amount of stocks (effectively an opening balance) the same argument applies
    a trade is an accquisition or disposal of stock for a specific amount of cash. it has no meaning
    a clients current stock portfolio is the sum of all trades grouped by shareID
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2011
    Posts
    8
    Hi healdem,

    Thanks for the response.

    Quote Originally Posted by healdem View Post
    entity trade seems flaky
    don't understand original / completed / origianl amount / completed amount.doesnt' feel right
    Im guessing a trade is a specific transaction (it could be a buy / sell / rights issue/ share split / write off whatever. so you need some entity to defien what type of transaction it is, and how that transaction affects the stock (ie the buy increases, sell decreases)
    it happens at a specific point in time, although it may be completed at a later point in time. so there shoudl be no concept of original value or other balances in this entity when you need to get a current balance you run a query to work out the balance as and when required

    there should be no concept of original value, as those greedy grasping gits (AKA Tax authorities) will need to know what was paid for the stock and when (in case there is tapering tax relief). so you need to know the value when the stocks were accquired
    the revenue will probably demand a FIFO model. alternative if original amount means original amount of stocks (effectively an opening balance) the same argument applies
    a trade is an accquisition or disposal of stock for a specific amount of cash. it has no meaning
    a clients current stock portfolio is the sum of all trades grouped by shareIDx
    Regarding the trade entity, what you are suggesting in right. It's very complicated in reality. I am not trying to achieve that as we have a third party provider to do all these tax reporting. This 3rd party system automatically capture all trades executed by the brokers. Sadly, there is a bug within their systems which is unsolved for years ....

    ... and what truly bugs me (and partly a reason why I am building this database) is that some partial trades do not go through to the system of 3rd party provider.

    Say, if my client want to buy $100,000 in value of stock ABC. In the first day, 2000 shares are bought, at $12 each, totalling $24,000. Second day, 5846 shares are bought , at $13 each, totalling $75,998.

    At the moment, the 3rd party provider fail to capture the second block of trade (5846 shares), which in turn underestimate my client's portfolio in their report.

    And the reason why I have "Original Amount", and "Original Number of Units" is:

    + For Buy transaction, I recommend how much to buy in $ value (i.e. you should buy $100,000 in value of shares ABC) --> "Original Amount"

    + For Sale transaction, I however recommend how many to sell (i.e. you should sell half of your holding in ABC, which is about 1000 shares) --> "Original Number of Units"

    Regarding staff entity, I like your recommendation, thanks for that. I will work on this as my knowledge goes. Any pointer of how to do that is much appreciated, healdem.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The original amount should be on the order. If an order can be for many stocks, then there would have to be an order items sub table to show the original amounts and from what stocks. Your trade table would then be related to the order items table and could have many records for each order item. Adding one extra column is okay, but as soon as you may need two or three, then add an extra table.
    John M Reynolds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •