T-SQL bankers

Released on: 4/1/2004 | updated on: 4/1/2004

ForSomeSet-based solutions for Financial Problems

Itzik Ben-gan

Recently, when I was teaching a SQL Server programming course, several students gave me a difficult question. In their production systems, financial applications that use interest rates or indices that must be applied to currency values (such as deposits) are used. These students have a specific problem related to the system and want to know if there is a set-based T-SQL solution for this problem. Of course the answer is yes! Let's take a look at the general question raised by my students so that you can adjust the solution as needed.

Exponential currency value

Currency value changes over time. Financial applications seldom display currency values in the original form when they enter the database. Financial institutions need to apply the interest rate to savings account deposits, apply the index rate (such as the consumption price index-CPI) to the currency value in the profit and loss report, and apply the currency exchange rate to debts owed to foreign suppliers.

Generally, the currency value is input to the database of the financial application together with the effective date. The financial application will apply a series of adjustments to the principal and calculate the result value for a certain date (such as "today" or "December 31, 2002") after the deadline. For example, assume that you enter $ September 7, 2002 into the system on June 1, 1,300. You need to link this amount to CPI (CPI updated on the 15th day of each month) and display the value as of January 1, December 31, 2002. CPI includes monthly interest rates, which reflect changes in the currency value based on the average purchasing power of consumers. Indexes such as CPI can be expressed as growth percentage or proportional coefficient. Consumers usually care about the percentage of growth, while accounting usually uses the exponential rate for calculation.

In this example, the exponential rate increased by September, December, 0.3%, and 0.6% from 0.4% to 0.9%, respectively. The principal amount needs to be adjusted as follows:

$1,300.0000 * (1 + 0.3%) = $1,303.9000 $1,303.9000 * (1 + 0.6%) = $1,311.7234 $1,311.7234 * (1 + 0.4%) = $1,316.9703 $1,316.9703 * (1 + 0.9%) = $1,328.8230

When applying interest rates to savings account deposits, a similar calculation will be used (assuming that the non-fixed interest rate is specified in the account terms), but the interval between interest rate changes may be different.

Now let's take a look at this general question. Run the script in Listing 1 to create the amounts and indexrates tables, and then fill in the sample data. The amounts table contains the currency value and the Effective Date. The indexrates table contains the monthly growth percentage of CPI. Your task is to compile a query so that the query can reflect a given date in the future by adjusting the amount (provided in the @ givendate variable ). For example, table 1 on page 1 shows the results required when the specified date is January 1, 18th.

Imitation AggregationProduct ()Solution

First, we will write the formula that will be applied to each amount:

indexed_value =  amt_value * (1+1st idx_growth) * (1+2nd idx_growth) * ... * (1+last idx_growth)

Among them, the 1st and 2nd Ratios correspond to the effective date and several time periods of the amount respectively, and the last ratio is the ratio effective on this specific date.

Associating the corresponding exponential growth with each amount is a simple part of this problem. You can use the following join conditions to join the amounts Table and the indexrates table:

On IR. idx_date between amt_date and @ givendate

For each amount, you will obtain multiple exponential rows, which represent multiple exponential changes in the above formula. By using the left outer join, you can ensure that the amount of the modified date (for example, the row with the effective date of December 24, 2002 in amounts) is also displayed in the output after the effective date is later than the last exponential rate.

Here, the more difficult problem is to calculate the product of all the exponential changes returned according to the requirements of the formula. If the T-SQL supports the aggregate product () function (which calculates the product of a set of values, which is very similar to and computed by the sum () function, you can use the pseudo query shown in Listing 2. (However, you cannot run the query because the T-SQL does not support the aggregate product () function .) I use isnull () in the select list of the pseudo query, so that when the effective date is later than the last exponential rate change date, the principal amount will also be returned.

In the article "adding performance" (instantdoc ID May 2001) published in 20131, I discussed a mathematical method, which can be used to simulate aggregate product () by using the log10 function in the following formula () function:

PRODUCT() = POWER(10.,  SUM(LOG10()))

If you apply the formula above to the query for this problem, the following expression is obtained:

ISNULL(POWER(CAST(10 AS FLOAT),  SUM(LOG10(1+idx_growth))),  amt_value) * amt_value

The Return Value of the power () function is the same as the data type of the first parameter. Therefore, the Code explicitly converts the number 10 to float to force the float result to be output. Finally, the task can be completed by converting the above expression to the currency data type. You can run the last query shown in listing 3 to obtain the expected results displayed in table 1.

Solutions using their respective exponential Coefficients

Another way to solve this problem is to use different CPI representations. You can store the ratio coefficient, which indicates the ratio of the index rate of a month to the predefined base month index rate, instead of the change percentage of the storage index rate relative to the previous month. For example, you can determine December 2001 as the base month and set its ratio to 1. The index growth rate in January is 0.1%, so the index rate in January will be 1*(1 + 0.1%) = 1.001. The index growth rate in February is 0.2%, so the index rate in February will be 1.001*(1 + 0.2%), and so on. Remember that each exponential coefficient includes all the cumulative exponential changes since the base date. Therefore, to apply the exponential changes from from_month to to_month to amt_value, you can use the following formula:

amt_value * (to_index_rate / from_index_rate)

That's simple. You can store the index rate coefficient (rather than exponential growth), or add another column, so that you can use the coefficients most suitable for your needs in the various financial calculations you perform. You can even specify only one value in the insert statement and ask the trigger to calculate another value for you. An important benefit for you is that tables such as indexrates are not updated or deleted, and you only need to insert a row into the table for each period (for example, every month.

Run the code in Listing 4 on page 1. You can use another idx_rate column to recreate the indexrates table. This Code also creates an insert trigger, which calculates the index rate coefficient based on the exponential growth value stored in the idx_growth column and refills the table with exponential changes. This trigger runs an update statement to connect the indexrates table with the inserted Table to find the new row to be updated. The set clause uses a subquery to retrieve the exponent coefficient of the previous month and multiply it with (1 + current_month's _ index_growth. When you enter the first row in the table, the entire expression will generate null. Therefore, in this case, the Code uses isnull () to return 1.

Then, you need to write a query to find the effective date of the amount and the exponential coefficient of the specific date for each amount; you will use these dates in the simple formula provided earlier in this section. There is a problem when writing this query, that is, the amt_date and @ givendate values are not necessarily the 15th day of the current month. You can use the case expression to check whether the date value of this date is less than 15. If it is less than 15, the effective date is the 15th day of the previous month; otherwise, it is the 15th day of the current month. Therefore, the effective date of a specific date is calculated as follows:

DECLARE @givendate AS SMALLDATETIME SET @givendate = '20021231' DECLARE @effective_givendate AS SMALLDATETIME SET @effective_givendate =   CASE     WHEN DAY(@givendate) < 15       THEN CONVERT(CHAR(6), DATEADD(month, -1,        @givendate), 112)           + '15'     ELSE CONVERT(CHAR(6), @givendate, 112) + '15'   END

Now, you can use a similar technique to calculate the effective date of the amount, and then use a three-way join between the two instances of the amounts Table and the indexrates table. One instance retrieves the exponential coefficient of the effective date of the amount, and the other instance retrieves the exponential coefficient of the specific date. Listing 5 shows the complete query. This code looks a little longer than the code in the previous solution, but it executes faster, because it does not need to look for all exponential changes within the required period of each amount, instead, we only need to find the first and last exponential coefficient.

Application

In general, it looks complicated to provide solutions to financial problems with T-SQL. However, you can solve the problem when you have a toolbox that contains many T-SQL techniques, such as imitating aggregate product () functions. Try to propose several solutions for this problem, and do not meet the requirements when finding an effective solution. When you find multiple solutions, you can select a solution that best meets your needs at this time, and use the technology you have mastered to design a solution for the next task.