|
|
|
6.9 Online Account Balancing6.9.1 ProblemYou need to create an account-balancing solution, where accounts can be debited or credited with different products. Each credit or debit will be recorded in a history table. Your system then needs to generate the current position, or balance, of an account based on its debit/credit history. 6.9.2 SolutionThe code shown in this recipe is actually a part of an online portfolio tracking system for a brokerage application. The idea is to use the accounting principle of credits and debits to record each event affecting an account. These credits and debits can then be summarized to list the portfolio. The portfolio is the list of products in an account. The following table, named StockRecord, can be used to store events in the system. Each account can have multiple products in its portfolio. An important column in this table is the ValidDate column. Each event has a validity date that states the date from which the position is valid. For example, if a trade is made with a settlement period of five days, it means that even if we bought the product today, it will not be available and shown in the portfolio until five days from now. All such events should be taken into consideration when building an account balance for a particular date. CREATE TABLE StockRecord (
RecordId INTEGER IDENTITY,
AccountId INTEGER NOT NULL,
ProductId VARCHAR(40),
Qty NUMERIC(20,4) NULL,
Type CHAR(1) NOT NULL
CHECK(Type in ('C','D','O','L','B')),
ValidDate DATETIME,
PRIMARY KEY(RecordID)
)
The type column contains a type code indicating the type of transaction that occurred. Table 6-1 describes the meaning of the various type codes used in this application.
The following is a representative example of the data that you would see in the StockRecord table: RecordId AccountId ProductId Qty Type ValidDate --------- ---------- ---------- --------- ----- --------------------------- 1 3 IBM 20.0000 O 2000-07-02 07:30:09.000 2 3 CFT 40.0000 O 2000-07-03 07:30:21.000 3 3 MSFT 10.0000 C 2000-07-02 10:30:39.000 4 3 MSFT 10.0000 D 2000-07-02 10:45:54.000 5 3 MSFT 25.0000 C 2000-07-02 12:11:15.000 6 3 MSFT 15.0000 C 2000-07-02 12:41:22.000 7 3 MSFT 10.0000 B 2000-07-03 09:00:00.000 8 3 USD 1000.0000 L 2000-07-03 09:00:00.000 ... These results could be interpreted as follows: at 7:30 the account was opened, and 20 shares of IBM and 40 shares of CFT were put in as the opening balance. The client then bought, and later sold, MSFT stock at 10:30 and 10:45, respectively. At 12:11, the client bought 25 shares of MSFT. After half an hour, the client bought additional shares of MSFT. During the day, the client arranged a line of credit, which will be available on the following day. 10 shares of MSFT were used as a collateral. With the StockRecord table created, and with the codes in Table 6-1 being used to record transactions, you can use the following query to generate the current portfolio for the accounts in the system: SELECT
AccountID,
ProductID,
(SUM(CASE WHEN Type='C' OR Type='L' OR Type='O'
THEN Qty ELSE 0 END)-
SUM(CASE WHEN Type='D' OR Type='B'
THEN Qty ELSE 0 END)) Total,
SUM(CASE WHEN Type='B'
THEN Qty ELSE 0 END) Blocked,
MAX(s.ValidDate) ValidDate
FROM StockRecord s
WHERE s.ValidDate BETWEEN
ISNULL((SELECT MAX(st.ValidDate)
FROM StockRecord st
WHERE st.AccountID=s.AccountID AND
st.ProductID=s.ProductID AND
st.Type='O'),'1900-1-1')
AND CURRENT_TIMESTAMP
GROUP BY s.AccountID, s.ProductID
Given the StockRecord rows displayed in the example, you would obtain the following position report when you executed the current position query: AccountID ProductID Total Blocked ValidDate ---------- ---------- ---------- -------- --------------------------- 3 CFT 40.0000 .0000 2000-07-02 07:30:21.000 3 IBM 20.0000 .0000 2000-07-03 07:30:09.000 3 MSFT 40.0000 10.0000 2000-07-02 09:00:00.000 3 USD 1000.0000 .0000 2000-07-03 09:00:00.000 ... This position report includes all changes to the account up until the time of the query. It excludes all changes that become valid subsequent to the time at which the query was executed. Since the line of credit doesn't become valid until tomorrow, it is not reflected in the current position. Likewise, the blockage of 10 shares of Microsoft stock to be used as collateral is not reflected in the current position. 6.9.3 DiscussionThis recipe illustrates one way to extend the basic concepts of an auditing mechanism to give your systems some additional flexibility. It allows users to have access to their history of transactions, and it also allows them to generate their current portfolios based on that history. The biggest advantage of such a system is that you have a direct relationship between the event-based views and the cumulative views of an account's portfolio. Every change in the stock record is immediately reflected in the portfolio view of an account. The logic behind this solution is actually derived from audit-logging mechanisms explained earlier in this chapter. The query groups the current portfolio's events by the ProductID and AccountID columns. This is logical since we want to report on each product in each account. The following GROUP BY clause accomplishes this: GROUP BY s.accountID, s.productID The query also limits the scope of the event records that it looks at to those that fall between the date of a product's opening position and the current date. If there is no opening position available for a product, the query defaults the opening-position date to January 1, 1900.
The WHERE clause highlighted in the following example shows the logic that restricts event records to those that fall between the opening position date and the current date: WHERE s.ValidDate BETWEEN
ISNULL((SELECT MAX(st.ValidDate)
FROM StockRecord st
WHERE st.AccountID=s.AccountID AND
st.ProductID=s.ProductID AND
st.Type='O'),'1900-1-1')
AND CURRENT_TIMESTAMP
Once the date limits are set and the correct records have been returned, the query sums all events that add quantities to the balance for a product and deducts from that the sum of all events that remove quantities from a product's balance. The result is the amount of each product in an account that is currently available for trading. The following code performs these computations: (SUM(CASE WHEN Type='C' OR Type='L' OR Type='O'
THEN Qty ELSE 0 END)-
SUM(CASE WHEN Type='D' OR Type='B'
THEN Qty ELSE 0 END)) Total,
SUM(CASE WHEN Type='B'
THEN Qty ELSE 0 END) Blocked
As you can see, blocking entries (Type='B') remove product from an account. Line of credit entries (Type='L'), on the other hand, as well as opening position entries (Type='O'), add product to an account. Just for fun now, consider the following query. Unlike the previous query to display the current account positions, the following query returns the transaction history together with running totals for each of the products involved. This is a great tool for a trader to investigate scenarios and to anticipate when an account will have the funds or product to close a trade. SELECT
AccountID,
ProductID,
(SELECT SUM(CASE WHEN Type='C' OR Type='L' OR
Type='O' THEN Qty ELSE 0 END)-
SUM(CASE WHEN Type='D' OR Type='M' OR Type='B'
THEN Qty ELSE 0 END)
FROM StockRecord s1
WHERE s1.ValidDate >=
isnull((SELECT MAX(st.ValidDate)
FROM StockRecord st
WHERE st.AccountID=s.AccountID AND
st.ProductID=s.ProductID AND
st.Type='O'),'1900-1-1') AND
s1.ValidDate<=MAX(s.ValidDate)AND
s1.AccountID=s.AccountID AND
s1.ProductID=s.ProductID) Available,
CONVERT(CHAR(10),s.ValidDate,102) ValidDate
FROM StockRecord s
GROUP BY s.accountID, s.productID,
CONVERT(CHAR(10),s.ValidDate,102)
HAVING MAX(s.validDate) >=
ISNULL((SELECT MAX(st.validDate)
FROM StockRecord st WHERE
st.accountID=s.accountID AND
st.productID=s.productID AND
st.type='O'),'1900-1-1')
Although at first glance this query looks a bit scary, it works very similarly to the current portfolio query given earlier in the recipe. You could obtain a similar result by running the current portfolio query against the StockRecord table for each date on which a position changed. With this query, however, you can obtain a complete historical record in one step. Finally, you could report the transaction history with a running total for each product using the second query shown in this recipe. The output from that query would look like this: AccountID ProductID Available ValidDate ---------- ---------- ---------- ---------- 3 CFT 40 2000.07.02 3 IBM 20 2000.07.02 3 MSFT 40 2000.07.02 3 MSFT 30 2000.07.03 3 USD 1000 2000.07.03 From this result, you can see that on the July 2, there was a change in the balance of shares for CFT, IBM, and MSFT. Changes planned for the next day affect MSFT and the line of credit. The amount of available MSFT stock drops to 30 because 10 shares are being used as collateral and the line of credit increases to $1,000. |
|
|
|
