LIFO with stock splits

## 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

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:

lang-sql
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

lang-sql
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.

lang-sql
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?