Section 6.9. Online Account Balancing

   

6.9 Online Account Balancing

6.9.1 Problem

You 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 Solution

The 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.

Table 6-1. Transaction type codes

Code

Type

Description

C

Credit

Signifies that a quantity of product was added to an account.

D

Debit

Signifies that a quantity of product was removed from the account.

O

Opening position

Records an account's opening position for a specific product. All events for the product recorded prior to this event are no longer valid for the purpose of calculating the current position.

L

Credit-line limit

Records the amount of a product that can be borrowed and is available for trading.

B

Blocked

Records the quantity of a product that is blocked and not available for trading.

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 Discussion

This 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 default opening-position date needs to be set early enough to ensure that all transactions for a product are captured. If you expect to enter transactions for dates prior to January 1, 1900, then use an earlier date as your default.

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.

   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net