Computing Excess Returns

I would like to compute the excess returns of dataframe of stock returns, where excess returns are defined as the difference between the stock returns and the market, where market

Solution 1:

The last line of code where you do set_index, you should assign the dataframe back to itself or do it inplace. Remaining you can do it as follows:

def func(row):
    date, asset = row.name
    return df.loc[(date, asset), 'returns'] - df.loc[(date, 'SPY'), 'returns']



dict0 = {'date': ['1/1/2020', '1/1/2020', '1/1/2020', '1/2/2020', '1/2/2020',
                  '1/2/2020', '1/3/2020', '1/3/2020', '1/3/2020'],
         'ticker': ['SPY', 'AAPL', 'MSFT', 'SPY', 'AAPL', 'MSFT', 'SPY', 'AAPL', 'MSFT'],
         'returns': [1, 2, 3, 4, 5, 6, 7, 8, 9]}
df = pd.DataFrame(dict0)  ###df = df.set_index(['date', 'ticker'])
df['excess_returns'] = df.apply(func, axis=1)

Solution 2:

Here is the vectorized solution:

One Liner

df['excess_returns'] = df.returns - df.groupby('date').returns.transform('first')

Complete Solution

dict0 = {'date': ['1/1/2020', '1/1/2020', '1/1/2020', '1/2/2020', '1/2/2020',
                  '1/2/2020', '1/3/2020', '1/3/2020', '1/3/2020'],
         'ticker': ['SPY', 'AAPL', 'MSFT', 'SPY', 'AAPL', 'MSFT', 'SPY', 'AAPL', 'MSFT'],
         'returns': [1, 2, 3, 4, 5, 6, 7, 8, 9]}
df = pd.DataFrame(dict0)

## Firstly I would convert date to datetime type, if required
df.date = pd.to_datetime(df.date, format='%d/%m/%Y')
df['excess_returns'] = df.returns - df.groupby('date').returns.transform('first')

## by any means if I require to set the index to date and tickerdf = df.set_index(['date', 'ticker']) ## Only if requireddf

Output

returnsexcess_returnsdateticker2020-01-01  SPY10AAPL21MSFT322020-02-01  SPY40AAPL51MSFT622020-03-01  SPY70AAPL81MSFT92