SPY Portfolio Hedging Excel Sheet

C

caementarius

The attached sheet is submitted for feedback purposes and carries no warranty :D

I calculated a cross-hedge beta with the SPY using closing prices from 3/3 to 10/3 of 2008. In some cases, the number is close to what I find on Yahoo!'s 'Key Statistics' page for the stocks and in other cases it seems pretty different. Insight into what I should be using for intra-day hedges would be appreciated. I realize that I should probably expand this into using sector ETFs to be more precise.

Sometimes I feel like when trading a stock I might as well be trading the SP500 itself, they often move so closely together.

The idea for using this sheet is this:
- You have one or more positions reflecting views about the specific stock(s)
- You want to remove effects from changes in the overall market
- You put in stocks, position sizes, prices (yellow cells) into the sheet
- You get back the position in the SPY you should take in order to make your portfolio "market neutral" (green cell)

Please take a look and give me some feedback if possible. Thank you.
 

Attachments

To be more clear -

The number in the green cell is the amount to sell (go short) for a hedge. If it is a negative number, then you'd go long that amount. That's a bit confusing but it's a work in progress.
 
its just confusing where you're getting the betas from. if they're static estimates from linear regression then your hedge will get messed up over time.

linear regression averages your parameter over time, and are thus bad estimates for factor sensitivities for hedging.

you will need to use some state-space form model. these allow factor sensitivities to vary over time in response to new information.

it has long term memory, but old previous data is weighted less than recent data. this is similar to weighted least squares but is maximized beyond WLS.

other than your betas, its a good exercise. you should import data from esignal so you can run it live.:D
 
Quote from tomtuttletacoma:

its just confusing where you're getting the betas from. if they're static estimates from linear regression then your hedge will get messed up over time.

linear regression averages your parameter over time, and are thus bad estimates for factor sensitivities for hedging.

you will need to use some state-space form model. these allow factor sensitivities to vary over time in response to new information.

it has long term memory, but old previous data is weighted less than recent data. this is similar to weighted least squares but is maximized beyond WLS.

other than your betas, its a good exercise. you should import data from esignal so you can run it live.:D
More...

The betas are Covariance(Stock, SPY) / Var(SPY) which use closing prices (% returns) from 3/3 to 10/3 (a block of data I had, not in this sheet).

What stood out to me is how different those betas can be from the commonly accepted Beta found in, for example, Yahoo!'s Key Statistics. They are close enough that I think I'm doing the calculation right but off in some cases - I guess it shows how much Beta can change based on what time period you use.

So, I should maybe calculate Beta for 1-month periods and do a weighted average? I mainly want to hedge intraday positions, so perhaps I should weight the last couple of weeks most heavily.

Yeah the goal is to incorporate this hedging into an ATS under development. I'm a little new to this stuff but have some quant finance classes under my belt.
 
Great spreadsheet, caementarius. I've been going down a similar path trying to hedge naked positions overnight with ETFs & correlated stocks. My biggest problem has been position sizing for the relative volatility.

I've been doing things in a very simple manner (possibly too simple). For instance, if I wanted to hedge 1000 shares long C I would short either XLF, KBE, or BAC. Since BAC and XLF are closest in price, they would be the most likely candidates.

The 65 day ATR for each instrument is:

C = 1.35
XLF = 1.09
BAC = 2.06

The price for each instrument is:

C = $6.57
XLF = $11.65
BAC = $12.95

Because BAC is in a merger with MER the price action has a different engine so XLF is probably the best hedge.

To capital balance the two we divide 11.65 by 6.57 = 1.7732

Therefore we need approx. 1.8 shares of C for every 1 share of XLF. When you take into account the volatility we have 1.35 divided by 1.09 = 1.2385 so we have to have more shares of XLF by a volatility factor of 1.2385 to account for the average price move.

We should have 687 shares of XLF to hedge off a 1000 share position of C.

555 shares XLF * 1.8 = 1000 C

555 * 11.65 = $6465.75

$6465.75 * 1.2385 (volatility factor) = 8007.83

8007.83 / 11.65 = 687.375 = 687 shares

I know there is probably some flawed logic in there that a more quant-minded person can point out to me, but this is how I have been calculating my hedge ratio.

All the best!
 
You must log in or register to reply here.
Back
Top