How do Data Scientists use Black-Scholes model on ClickHouse?

Introduction

The Black-Scholes model is a mathematical formula used to estimate the price of European-style options, which are financial contracts that give the holder the right, but not the obligation, to buy or sell an underlying asset at a predetermined price within a specified time period. The formula was developed by Fischer Black and Myron Scholes in the 1970s and is widely used in the financial industry for pricing options.

Data scientists and statisticians in the banking industry use the Black-Scholes model to calculate the fair value of options and to develop trading strategies based on option prices. The model takes into account several variables, including the current price of the underlying asset, the option’s strike price, the time to expiration, the risk-free rate, and the volatility of the underlying asset. By estimating the fair value of an option, data scientists can determine whether an option is overpriced or underpriced, and develop strategies for buying or selling options based on their expected value.

Implementation of the Black-Scholes model using SQL in ClickHouse

Here’s an example implementation of the Black-Scholes model using SQL on ClickHouse, using a sample banking database with options data:

-- Calculate the Black-Scholes option price for a single option
SELECT
*,
(s * N(d1) - k * exp(-r * t) * N(d2)) AS option_price
FROM (
SELECT
'PUT' AS type,
50 AS s, -- underlying asset price
50 AS k, -- option strike price
0.25 AS t, -- time to expiration (in years)
0.05 AS r, -- risk-free interest rate
0.3 AS sigma -- volatility of the underlying asset
)
CROSS JOIN (
SELECT
SQRT(t) AS sqrt_t,
LN(s / k) AS d1_numerator,
LN(s / k) - (r + 0.5 * POW(sigma, 2)) * t AS d2_numerator,
sigma * SQRT(t) AS denominator
)
-- Calculate the cumulative normal distribution function
ARRAY JOIN (
sign, integer_part, fractional_part
) AS N
PREWHERE TRUE
ANY LEFT JOIN (
SELECT
number,
sign,
integer_part,
fractional_part
FROM
system.numbers
ARRAY JOIN (
1 AS sign,
number - integer_part - 1 AS fractional_part
)
WHERE
number <= 200
)
ON
1
WHERE
sign = 1
AND integer_part <= ROUND(100 * ABS(d1_numerator / denominator))
AND integer_part <= ROUND(100 * ABS(d2_numerator / denominator))
ORDER BY
1, 2;

In this example, we use a sample options data set to calculate the Black-Scholes option price for a single put option with a strike price of 50, an underlying asset price of 50, a time to expiration of 0.25 years, a risk-free interest rate of 0.05, and a volatility of 0.3.

We use SQL to calculate the cumulative normal distribution function, which is a key component of the Black-Scholes model, and then use the formula to calculate the option price. This implementation assumes that the option is a put option, but the formula can be adapted for call options as well.

Conclusion

In this article we have delved deeper into the implementation of the Black-Scholes model in ClickHouse with SQL, primarily for the benefit of data scientists in the banking sector. We hope this is useful as you explore ClickHouse for data science & analytics in BFSI.

To know more about Data Science in ClickHouse, do read the following articles:

• Data Science in ClickHouse: Skewed Q-Q Plots
• Data Science in ClickHouse: Implementing Chebyshev’s Inequality