G
Gregg
I'm not sure if this is even possible in Excel, so bear
with me. I am looking to construct a "hedged" portfolio
of stocks, whereby I purchase 10 stocks in equal amounts
to 10 other stocks that are sold, with the goal of
minimizing the cumulative daily volatility. For example,
if Pepsi and Coke are highly correlated over time, I would
buy 100 shares of Pepsi, and sell 100 shares of Coke, with
the full year goal of no net change in the portfolio (or
as small a change as possible). I am choosing these 20
stocks from a universe of 50, for which I have the daily
stock price changes for the past year in each column (50
columns). The goal is to choose the 20 (10 buys and 10
sells of equal # of shares) that would have resulted in
the least net change (or possibly the highest average
correlation) at the end of the time period. Seems like
the statistical functions or Solver should be able to
provide the appropriate mix. Any thoughts are appreciated.
with me. I am looking to construct a "hedged" portfolio
of stocks, whereby I purchase 10 stocks in equal amounts
to 10 other stocks that are sold, with the goal of
minimizing the cumulative daily volatility. For example,
if Pepsi and Coke are highly correlated over time, I would
buy 100 shares of Pepsi, and sell 100 shares of Coke, with
the full year goal of no net change in the portfolio (or
as small a change as possible). I am choosing these 20
stocks from a universe of 50, for which I have the daily
stock price changes for the past year in each column (50
columns). The goal is to choose the 20 (10 buys and 10
sells of equal # of shares) that would have resulted in
the least net change (or possibly the highest average
correlation) at the end of the time period. Seems like
the statistical functions or Solver should be able to
provide the appropriate mix. Any thoughts are appreciated.