Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Portfolio Risk Analysis in SQL

February 25, 2016

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.

Finding the beta of your investment can help you determine how volatile your investment is and adjust your risk exposure relative to the market.

Calculating Beta

Beta is calculated as

β=Cov(s,m)Var(m)

, 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

Cov(X,Y)=E[XY]−E[X]E[Y]

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

This is the beta of one stock, equivalent to a portfolio that consists of one stock. Your portfolio would mostly likely consist of several different assets. You can calculate the overall beta of your portfolio by

β=ωa⋅βa+ωb⋅βb+…

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

For a portfolio of three assets, we can generate the series by

with range_vals as 
    (select (row_number() over (order by true)) * 0.1 as
    from sp500 limit 11)
select distinct a.n as x, b.n as y, c.n as
from range_vals a 
cross join range_vals b 
cross join range_vals c 
where x + y + z = 1

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

Var(aX+bY)=a2Var(X)+b2Var(Y)+2abCov(X,Y).

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.


Want to discuss this article? Join the Periscope Data Community!
Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?

Subscribe to our Newsletter