Any investment involves some amount of risk. Different people have different amounts of risk that they’re willing to take on when investing. We will do risk analysis of an investment portfolio in SQL in this post.

A key component of portfolio risk analysis is the beta ββ of an investment. It indicates volatility of the investment, i.e., whether the investment changes in value along with the market and by how much.

For example, an investment of ββ 1.4 will increase by a larger portion than the market when the market goes up in value, but it will also decrease by a larger portion when the market goes down in value. ββ of less than 1 means the portfolio swings less than the market, and a negative beta means the portfolio moves in the opposite direction of the market.

Calculating Beta

Beta is calculated as:

, where Cov(s,m) is the covariance between the asset s and the market m and Var(m), the variance of the market.

In this post, we are using the Redshift flavor of SQL, which does not have a covariance function, so we can explicitly calculate it, using the formula:

In our case, expected value EE is equivalent to average. Some variants of SQL, such as Postgres and oracle, offer covar_pop.

We have downloaded the historical prices of a stock X and the S&P 500 (date, price) from online and calculated the beta on SQL:

select (sum(s.price*m.price)
- sum(s.price)*sum(m.price)/count(s.price))
/count(s.price)/variance(s.price) as beta
from stock_x s
join sp500 m
on s.date = m.date

Simulating Beta of Portfolio

where ωs are proportions of the respective assets in the portfolio.

We can generate a series to see how the beta changes as we allocate different amounts of stocks in the portfolio. For more information on generating series, refer to this post. We have generated the series that increment by 0.1 as follows:

with range_vals as
(select
((row_number() over(order by true)) - 1) * 0.1 as n
from sp500
limit 11)
select distinct a.n as x, 1 - a.n as y
from range_vals a

We will use a portfolio of two assets in this example.

with
stock_x_vals as (
select
variance(m.price) as market_variance,
variance(s.price) as variance,
(sum(s.price * m.price)
- sum(s.price) * sum(m.price) / count(s.price))
/ count(s.price) as covariance
from stock_x s
join sp500 m
on s.date = m.date),
stock_y_vals as (...),
stock_a_beta as (
select
covariance / market_variance as beta
from stock_a),
stock_b_beta as (
select
covariance / market_variance as beta
from stock_b),
range_vals as (...),
percs as (...)
select
percs.x,
percs.y,
stock_a_beta.beta * percs.x
+ stock_b_beta.beta * percs.y as beta
from stock_a_beta
cross join stock_b_beta
cross join percs

Variance of Portfolio

Beta measures the risk of an investment that cannot be reduced by diversification, but diversification also helps control the variance of your portfolio. So in order to fully analyze the risk profile of your portfolio, you will want to calculate the variance as well. Here we will simulate how these values change depending on your diversification.

Assume our portfolio consists of two assets, X and Y by a and b, respectively.

The variance of our portfolio is then:

We can see how variance changes as we change the portfolio composition with the series we generated earlier.

with
stock_x_vals as (...),
stock_y_vals as (...),
stock_ab as (
select
(sum(s.price * m.price)
- sum(s.price) * sum(m.price) / count(s.price))
/ count(s.price) as covariance
from
stock_x_vals s
join stock_y_vals m
on s.date = m.date),
range_vals as (...),
percs as (...)
select
p.x,
p.y,
p.x * p.x * a.variance + p.y * p.y * b.variance
+ 2 * p.x * p.y * ab.covariance as variance
from percs p
cross join stock_a a
cross join stock_b b
cross join stock_ab ab

Putting all these together in one view, we get:

Stock Y has a higher β, so owning more of stock Y gives you higher beta, but the variance is minimum when the portfolio is more evenly split between stock X and stock Y.

While the capital asset pricing model (CAPM) implicates that higher beta gives you higher returns since you are taking more risk, there are studies that indicate a portfolio of lower β  provides higher returns, in the long run, founding the basis of value investment. So here’s your data and now you get to make informed decisions on your portfolio to your own preferences.

Tags: