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?
