I am trying to create a python function which would allow me to pass on SP500 tickers and calculate their upside capture ratio over time. However, I get thos keyError 'Date' and Cannot figure out why, I even tried to write the whole function from the beginning, step by step. When I run the funtion with couple tickers, it runs just fine. Yet it starts to give me the error once I try to run with many tickers. I am new here, and new to programming. I appreciate your help and time!
table = pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies')
tickers = table[0]['Symbol'].values
start_date= '2020-12-31'
end_date = dt.datetime.now()
benchmark = 'SPY'
def upside_capture_ratio(tickers,start='start date', end='end_date',benchmark='SPY'):
"""
Takes a list of tickers and returns the upside capture ratio overtime
"""
upside_results=[]
for ticker in tickers:
#Pulling benchmark data from yahoo finance as well as positive returns and corresponding periods
benchmark_daily_prices= pdr.DataReader(benchmark, 'yahoo', start_date, end_date)
benchmark_daily_returns = benchmark_daily_prices.pct_change().dropna()
up_benchmark_periods = benchmark_daily_returns[benchmark_daily_returns['Adj Close']>0].reset_index()['Date']
positive_benchmark_returns = benchmark_daily_returns.loc[up_benchmark_periods][['Adj Close']].rename(columns={'Adj Close':'Returns'})
#Pulling ticker information from yahoo finance as well as positive returns and corresponding positive benchmark periods
ticker_prices_daily = pdr.DataReader(ticker, 'yahoo', start_date, end_date)
ticker_daily_returns = ticker_prices_daily.pct_change().dropna()
ticker_returns_when_benchmark_up = ticker_daily_returns.loc[up_benchmark_periods][['Adj Close']].rename(columns={'Adj Close':'Returns'})
compounded_benchmark_positive_returns = (positive_benchmark_returns+1).cumprod()
compounded_ticker_positive_returns = (ticker_returns_when_benchmark_up+1).cumprod()
upside_capture_ratio = (compounded_ticker_positive_returns)/(compounded_benchmark_positive_returns)
upside_capture_ratio.rename(columns={'Returns':f'Upside_Capture_Overtime for {ticker}'}, inplace=True)
upside_results.append(upside_capture_ratio)
return pd.concat(upside_results, axis=1)`
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
3079 try:
-> 3080 return self._engine.get_loc(casted_key)
3081 except KeyError as err:
pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()
KeyError: 'Date'
The above exception was the direct cause of the following exception:
KeyError Traceback (most recent call last)
<ipython-input-37-251dcb7c65dd> in <module>
----> 1 upside_capture_ratio(tickers=tickers,start=start_date, end=end_date, benchmark='SPY')
<ipython-input-35-b9f22bff5751> in upside_capture_ratio(tickers, start, end, benchmark)
24 #Pulling ticker information from yahoo finance as well as positive returns and corresponding positive benchmark periods
25
---> 26 ticker_prices_daily = pdr.DataReader(ticker, 'yahoo', start_date, end_date)
27 ticker_daily_returns = ticker_prices_daily.pct_change().dropna()
28 ticker_returns_when_benchmark_up = ticker_daily_returns.loc[up_benchmark_periods][['Adj Close']].rename(columns={'Adj Close':'Returns'})
> ~\Anaconda3\lib\site-packages\pandas\util\_decorators.py in
> wrapper(*args, **kwargs)
> 197 else:
> 198 kwargs[new_arg_name] = new_arg_value
> --> 199 return func(*args, **kwargs)
> 200
> 201 return cast(F, wrapper)
>
> ~\Anaconda3\lib\site-packages\pandas_datareader\data.py in
> DataReader(name, data_source, start, end, retry_count, pause, session,
> api_key)
> 368
> 369 if data_source == "yahoo":
> --> 370 return YahooDailyReader(
> 371 symbols=name,
> 372 start=start,
>
> ~\Anaconda3\lib\site-packages\pandas_datareader\base.py in read(self)
> 251 # If a single symbol, (e.g., 'GOOG')
> 252 if isinstance(self.symbols, (string_types, int)):
> --> 253 df = self._read_one_data(self.url, params=self._get_params(self.symbols))
> 254 # Or multiple symbols, (e.g., ['GOOG', 'AAPL', 'MSFT'])
> 255 elif isinstance(self.symbols, DataFrame):
>
> ~\Anaconda3\lib\site-packages\pandas_datareader\yahoo\daily.py in
> _read_one_data(self, url, params)
> 159 prices = DataFrame(data["prices"])
> 160 prices.columns = [col.capitalize() for col in prices.columns]
> --> 161 prices["Date"] = to_datetime(to_datetime(prices["Date"], unit="s").dt.date)
> 162
> 163 if "Data" in prices.columns:
>
> ~\Anaconda3\lib\site-packages\pandas\core\frame.py in
> __getitem__(self, key) 3022 if self.columns.nlevels > 1: 3023 return self._getitem_multilevel(key)
> -> 3024 indexer = self.columns.get_loc(key) 3025 if is_integer(indexer): 3026 indexer = [indexer]
>
> ~\Anaconda3\lib\site-packages\pandas\core\indexes\base.py in
> get_loc(self, key, method, tolerance) 3080 return
> self._engine.get_loc(casted_key) 3081 except KeyError
> as err:
> -> 3082 raise KeyError(key) from err 3083 3084 if tolerance is not None:
>
> KeyError: 'Date'