I am using data from quandl and would like to calculate the log-return as shown below using a dataframe:
Date YAHOO/ACN - Close YAHOO/AAPL - Close YAHOO/ACN - Log-Return YAHOO/AAPL - Log-Return
02.01.2002 26.209999 23.299999
03.01.2002 25.389999 23.580001 -0.031785623 0.01194562
04.01.2002 27.700001 23.69 0.087077093 0.004654081
07.01.2002 26.450001 22.9 -0.046176253 -0.033916108
08.01.2002 27.280001 22.61 0.030897674 -0.012744624
09.01.2002 27.57 21.65 0.010574355 -0.043386832
10.01.2002 27.82 21.23 0.009026961 -0.019590179
The main problem is, that the headers for the closprices is using the reference together with the ticker. How can I calculate the log return? My code is shown below:
import quandl
token = quandl.ApiConfig.api_key = 'xxx'
ticker = ['YAHOO/ACN.4', 'YAHOO/AAPL.4']
DataLevels = quandl.get(ticker,start_date='2002-1-1', end_date='2002-2-11',authtoken=token, collapse='dayly', returns='pandas')
DataLevels['log_return'] = np.log(DataLevels / DataLevels.shift(1))
print(DataLevels)
It seems you can use map for replace column names with concat:
ticker = ['YAHOO/ACN.4', 'YAHOO/AAPL.4']
#simplier df
DataLevels = quandl.get(ticker, collapse='dayly', returns='pandas')
print (DataLevels.head())
YAHOO/ACN - Close YAHOO/AAPL - Close
Date
1980-12-12 NaN 28.750
1980-12-15 NaN 27.250
1980-12-16 NaN 25.250
1980-12-17 NaN 25.875
1980-12-18 NaN 26.625
df1 = np.log(DataLevels / DataLevels.shift(1))
df1.columns = df1.columns.map(lambda x: x.replace('Close','Log-Return'))
df = pd.concat([DataLevels, df1], axis=1)
print (df.head())
YAHOO/ACN - Close YAHOO/AAPL - Close YAHOO/ACN - Log-Return \
Date
1980-12-12 NaN 28.750 NaN
1980-12-15 NaN 27.250 NaN
1980-12-16 NaN 25.250 NaN
1980-12-17 NaN 25.875 NaN
1980-12-18 NaN 26.625 NaN
YAHOO/AAPL - Log-Return
Date
1980-12-12 NaN
1980-12-15 -0.053584
1980-12-16 -0.076227
1980-12-17 0.024451
1980-12-18 0.028573