###### Common Statistical Operations

# Portfolio Risk Analysis in SQL

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

, where ** Cov(s,m)** is the covariance between the asset

**and the market**

*s***and**

*m***, the variance of the market.**

*Var(m)*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)asbeta

fromstock_x s

joinsp500 m

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

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:

withrange_valsas

(select

((row_number() over(orderbytrue))-1)*0.1asn

fromsp500

limit11)

selectdistincta.nasx,1-a.nasy

fromrange_vals a

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

withrange_valsas

(select(row_number() over (orderbytrue))*0.1asn

fromsp500limit11)

selectdistincta.nasx, b.nasy,c.nasz

fromrange_vals a

crossjoinrange_vals b

crossjoinrange_valsc

wherex+y+z=1

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

with

stock_x_valsas(

select

variance(m.price)asmarket_variance,

variance(s.price)asvariance,

(sum(s.price*m.price)

-sum(s.price)*sum(m.price)/count(s.price))

/count(s.price)ascovariance

fromstock_x s

joinsp500 m

ons.date=m.date),

stock_y_valsas(...),

stock_a_betaas(

select

covariance/market_varianceasbeta

fromstock_a),

stock_b_betaas(

select

covariance/market_varianceasbeta

fromstock_b),

range_valsas(...),

percsas(...)

select

percs.x,

percs.y,

stock_a_beta.beta*percs.x

+stock_b_beta.beta*percs.yasbeta

fromstock_a_beta

crossjoinstock_b_beta

crossjoinpercs

### 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

**by**

*Y***and**

*a***, respectively. The variance of our portfolio is then**

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

with

stock_x_valsas(...),

stock_y_valsas(...),

stock_abas(

select

(sum(s.price*m.price)

-sum(s.price)*sum(m.price)/count(s.price))

/count(s.price)ascovariance

from

stock_x_vals s

joinstock_y_vals m

ons.date=m.date),

range_valsas(...),

percsas(...)

select

p.x,

p.y,

p.x*p.x*a.variance+p.y*p.y*b.variance

+2*p.x*p.y*ab.covarianceasvariance

frompercs p

crossjoinstock_a a

crossjoinstock_b b

crossjoinstock_ab ab

Putting all these together in one view, we get

Stock ** Y** has a higher

**, so owning more of stock**

*β***gives you higher beta, but the variance is minimum when the portfolio is more evenly split between stock**

*Y***and stock**

*X***.**

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