4

Background

I am managing a database containing my users' trading of financial products, stocks bonds, ETFs etc. The system offers simple bookkeeping functionality (create portfolio, create instrument, book a trade, book a cash flow ...) for my users, and some bulk features for the data admin user.

The system keeps track of trade / position performance ("pnl", profit-and-loss) and values sell-trades using the FIFO method, i.e. outgoing stock is valued at 'oldest' prices.

Problem statement

Thru so-called corporate actions, a company manipulates their quantity of outstanding stock. An N-for-1 stock split, for example, results in N times the number of stock outstanding (at a price of 1/N of the pre-Split price), and the same holds for the number of stock in a given portfolio of a user, e.g. their quantities double during at a 2-for-1-split. Thus, the meaning of quantity may change over time, complicating the FIFO method in my database. I want to reconcile the ability to value a sale trade using the FIFO method with the concept of stock splits.

Constraints

  • hard constraint: Historical entries must be recoverable 'as is': Users want to see their historical trades' units as of that date.
  • soft constraint: Data changes should be as local as possible, and be reversible: There should be no additional effect of adjusting for the 'same' stock split twice.

This is very a small project with <10 users and no interaction between the users' trades, concurrency is not an issue, I guess.

Status Quo

For simplicity, I have dropped some entities such as portfolio, instrument etc. below.

In order to be able to calculate the FIFO value for the position in a financial asset (think: stocks), my system contains a table called trade that holds the history of each trade with trade quantity qty, unit price prcand a field qty_allotable that tells me how many units of each buy trade have not yet been sold off (i.e. not yet allotted to a sale). The relevant fields for my problem are:

CREATE TABLE db.trade (
   ,valuedate       DATE                    NOT NULL
   ,qty             DECIMAL(16,6)           NOT NULL
   ,prc             DECIMAL(12,6) UNSIGNED  NOT NULL
  ,qty_allotable    DECIMAL(16,6) UNSIGNED  NOT NULL
);

For example, let's buy 6 units of some stock on one day and another 4 units the next day (via some stored procedure not depicted here)

 valuedate   qty    prc  qty_allotable    
2021-08-02     6  10.00              6
2021-08-03     4  11.00              4

On day 3, we sell 7 units. Via FIFO, we value the outgoing units at

6 x 10 + 1 x 11 = 71.00

where the FIFO algorithm is implemented in some function, and we deplete the allotable quantity after the sale:

 valuedate   qty    prc  qty_allotable    
2021-08-02     5  10.00              0
2021-08-03     5  11.00              3
2021-08-04    -7  12.00              0 -- a sale is not allotable itself

where the table update algorithm is performed by some stored procedure. At each point in time, the total invested volume is a simple SELECT(prc * qty_allotable) FROM ....

NB.: The setup requires trades to be entered (truly) in sequential order. If a user 'forgets' to enter a historical trade, I rebuild the whole trade table for the affected combination of user and instrument...

Increased complexity due to stock splits

My question now is how to properly treat events that change the significance of a stock's quantity, e.g. a stock split. To make it tangible, say there's a 2-for-1 stock split on 2021-08-05, i.e. my remaining three units become six units (and the trading price halves), i.e. my position now is

 qty    unit_prc
   6        5.50  -- doubling the total qty, halving the prices.

Perceived solutions

How should I incorporate this effect in the database? I think that I have identified a couple of options to follow,

1. Fire and forget WITH SYSTEM VERSIONING

Introduce versioning to the trade table and simply update the historical prc and qty_allotable fields by the split factor.

Although I can retrieve original trades with this setup, I cannot 'roll back' to an earlier state without risking loss of trade data entered in the mean time.

2. Introduce a table corporate_actions and adjusted business logic.

By introducing a table with an appropriate uniqueness index

CREATE TABLE db.corporate_action(
   ,valuedate       DATE                    NOT NULL
   ,split_factor    DECIMAL(12,6) UNSIGNED  NOT NULL
);
CREATE UNIQUE INDEX idx_ca_unq ON db.corporate_action(value_date);

In our example, the table could look like this:

 valuedate   split_factor
 1970-01-01  1.000000  -- auto entry per instrument
 2021-08-05  2.000000
 2099-12-31  1.000000  -- auto entry per instrument

This way, I make sure that a 'double entry' cannot take place (thru the uniqueness index). In order to apply the FIFO method, I must adjust the historical quantities and prices through some view on the corporate_actions table, e.g.

SELECT 
     c1.valuedate
    ,EXP(-SUM(LOG(c2.split_factor))) as compound_factor
FROM       corporate_action c1 
INNER JOIN corporate_action c2 
        ON c1.valuedate <= c2.valuedate 
GROUP BY   c1.valuedate;

resulting in

valuedate   compound_factor
1970-01-01  0.5
2021-08-05  0.5
2099-12-31  1

Then, when calculating the FIFO price, I'd calculate prc*compound_factor and qty/compound_factor in order to properly calculate the outgoing value in a sell trade.

Question

Do you think that either of the two perceived solutions is sensible, or can you propose another solution?

1
  • Hi, and welcome to dba.se! Ribbit... Your question appears quite involved and would require a lot of work - it may well be closed as a "tip of the iceberg" question - but not by me! :-) You might want to start by having a look at F/LOSS trading software?
    – Vérace
    Aug 28, 2021 at 21:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.