how to replace stock-prices symbols in a dataframe

Question

I would like to get the S&P 500 ['Adj Close'] column and replace the column with the corresponding stock symbol, however, I am not able to replace the dataframe columns because it gives me an error: KeyError: '5'

What I would like to achieve is to loop through all the available stocks from the list and replace the Adj Close with the stock symbol.

This is what I did:

First I have scraped the stock symbols from Wikipedia and added them to a list.

data = pd.read_html('https://en.wikipedia.org/wiki/List_of_S%26P_500_companies') 
symbols = data[0] # get first column 
symbols.head()

stock = symbols['Symbol'].to_list() 
print(stock[0:5])

this gives me a list of stock symbols as below:

['MMM', 'ABT', 'ABBV', 'ABMD', 'ACN']

then I scraped Yahoo finance to get the daily financial data as below

stock_url = 'https://query1.finance.yahoo.com/v7/finance/download/{}?'

params = {
    'range' : '1y',
    'interval' : '1d',
    'events' : 'history'   
}

response = requests.get(stock_url.format(stock[0]), params=params)

file = StringIO(response.text)
reader = csv.reader(file)
data = list(reader)

df = pd.DataFrame(data)
stock_data = df['5']

Can you kindly include the outputs of the dataframe from yahoo, so I/others don't have to use requests? — David Erickson

sharathnatraj sharathnatraj · Accepted Answer · 2020-12-11T00:32:15

Fix for key error

You are calling the the url using the list 'stock' and it gives a 404 response when I tried.

Call the URL with individual stock like below,

requests.get(stock_url.format(stock[0]), params=params)

Also do below, The column 5 is stored as integer instead of character. That is the reason you got 'key error'

stock_data = df[5]

I tried for stock 'MMM' - stock[0] and it prints below:

              0           1           2           3           4           5  \
0          Date        Open        High         Low       Close   Adj Close   
1    2019-12-11  168.380005  168.839996  167.330002  168.740005  162.682480   
2    2019-12-12  166.729996  170.850006  166.330002  168.559998  162.508926   
3    2019-12-13  169.619995  171.119995  168.080002  168.789993  162.730667   
4    2019-12-16  168.940002  170.830002  168.190002  170.750000  164.620316   
..          ...         ...         ...         ...         ...         ...   
249  2020-12-04  172.130005  173.160004  171.539993  172.460007  172.460007   
250  2020-12-07  171.720001  172.500000  169.179993  170.149994  170.149994   
251  2020-12-08  169.740005  172.830002  169.699997  172.460007  172.460007   
252  2020-12-09  172.669998  175.639999  171.929993  175.289993  175.289993   
253  2020-12-10  174.869995  175.399994  172.690002  173.490005  173.490005   
[254 rows x 7 columns]

Loop through stocks and replace Adj Close (Edited as per requirements from comments)

Code for looping through stocks and replacing Adj close with Stock symbol.

stock_url = 'https://query1.finance.yahoo.com/v7/finance/download/{}?'

params = {
    'range' : '1y',
    'interval' : '1d',
    'events' : 'history'   
}
df = pd.DataFrame()
for i in stock:
    response = requests.get(stock_url.format(i), params=params)
    file = io.StringIO(response.text)
    reader = csv.reader(file)
    data = list(reader)
    df1 = pd.DataFrame(data)
    df1.loc[df1[5] == 'Adj Close',5] = i
    df = df.append(df1)

Tried the code for first 3 stocks and here it is:

how to replace stock-prices symbols in a dataframe

1 Answers