'retrieve stock price data from specific dates in pandas df
I have a pandas dataframe that has the earnings date of certain stocks, the eps actual and estimate as well as revenue estimate and actual. For my sample, I only have 10 tickers of all their earnings date but I to eventually incorporate all nasdaq tickers. Anyways, what is the fastest way to go through the pandas dataframe, retrieve the specific date and symbol and pull the stock price for that day (open, high, low, close). I know how to retrieve stock prices individually from the yahoo finance api. (i.e., downloading a specific ticker and retrieving stock prices from a start date and end date) But I'm unsure of how to connect the two. Thanks.
Below is my sample df and what I would like to see...
date symbol eps epsEstimated time revenue revenueEstimated
0 2022-01-27 CMCSA 0.77 0.73 bmo 3.033600e+10 3.046110e+10
1 2021-10-28 CMCSA 0.87 0.75 bmo 3.029800e+10 2.976570e+10
2 2021-07-29 CMCSA 0.84 0.67 bmo 2.854600e+10 2.717460e+10
3 2021-04-29 CMCSA 0.76 0.59 bmo 2.720500e+10 2.680920e+10
4 2021-01-28 CMCSA 0.56 0.48 bmo 2.770800e+10 2.309000e+10
.. ... ... ... ... ... ... ...
34 2013-07-24 FB 0.19 0.14 amc 1.813000e+09 1.335895e+09
35 2013-05-01 FB 0.12 0.13 amc 1.458000e+09 1.579500e+09
36 2013-01-30 FB 0.17 0.15 amc 1.585000e+09 1.398529e+09
37 2012-10-23 FB 0.12 0.11 amc 1.262000e+09 1.156833e+09
38 2012-07-26 FB 0.12 0.12 amc 1.184000e+09 1.184000e+09
My desired result (but with values under the new columns):
date symbol eps epsEstimated revenue revenueEstimated Open High Low Clos
0 2022-01-27 CMCSA 0.77 0.73 .033600e+10 3.046110e+10
1 2021-10-28 CMCSA 0.87 0.75 3.029800e+10 2.976570e+10
2 2021-07-29 CMCSA 0.84 0.67 2.854600e+10 2.717460e+10
3 2021-04-29 CMCSA 0.76 0.59 2.720500e+10 2.680920e+10
4 2021-01-28 CMCSA 0.56 0.48 2.770800e+10 2.309000e+10
.. ... ... ... ... ... ... ...
34 2013-07-24 FB 0.19 0.14 1.813000e+09 1.335895e+09
35 2013-05-01 FB 0.12 0.13 1.458000e+09 1.579500e+09
36 2013-01-30 FB 0.17 0.15 1.585000e+09 1.398529e+09
37 2012-10-23 FB 0.12 0.11 1.262000e+09 1.156833e+09
38 2012-07-26 FB 0.12 0.12 1.184000e+09 1.184000e+09
UPDATE EDIT::This is what I have so far...
the earnings df is called data1. I created three columns Day_0, Day_1 and Day_0_close. In the time column, the value is either amc or bmo. 'amc' means after market open and 'bmo' means before market open. In order for me to analyize earnings reaction on stock price. I need to possibly readjust the dates, which is why I created those new columns. For example bmo, since earnings are released before the market opens on the current day, i need to know yesterdays date and its closing price as Day_0. For amc, i need todays date's and closing price as Day_0_close. Eventually I need to get the next day prices but just keeping it to Day_0_close for now until I can resolve this issue.
date symbol eps epsEstimated time revenue revenueEstimated Day_0 Day_1 Day_0_Close
0 2022-01-27 CMCSA 0.770000 0.7300 bmo 3.033600e+10 3.046110e+10 0.0
1 2021-10-28 CMCSA 0.870000 0.7500 bmo 3.029800e+10 2.976570e+10 0.0
2 2021-07-29 CMCSA 0.840000 0.6700 bmo 2.854600e+10 2.717460e+10 0.0
3 2021-04-29 CMCSA 0.760000 0.5900 bmo 2.720500e+10 2.680920e+10 0.0
I have another df called price1 which has all the stocks price data.
Date Open High ... Adj Close Volume ticker
0 1980-03-17 0.000000 0.101881 ... 0.070243 138396 CMCSA
1 1980-03-18 0.000000 0.101881 ... 0.070243 530518 CMCSA
2 1980-03-19 0.000000 0.100798 ... 0.069462 738113 CMCSA
3 1980-03-20 0.000000 0.108385 ... 0.074925 1360895 CMCSA
4 1980-03-21 0.000000 0.111636 ... 0.077267 461320 CMCSA
... ... ... ... ... ... ... ...
71942 2022-02-18 209.389999 210.750000 ... 206.160004 37049400 FB
71943 2022-02-22 202.339996 207.479996 ... 202.080002 39852400 FB
I then created a for loop to go through each row in data1, to pull the stock ticker and date and get prices. But now I'm getting an error "IndexError: index 0 is out of bounds for axis 0 with size 0" It debugs out at
day_0_close = price1.loc[(price1.ticker == symbol) & (price1.Date == date_0), 'Adj Close'].values[0].
I don't know why it's erroring it out when sometimes the code works but stops several rows in.
See below
date symbol eps epsEstimated time revenue revenueEstimated \
0 2022-01-27 CMCSA 0.77 0.73 bmo 3.033600e+10 3.046110e+10
1 2021-10-28 CMCSA 0.87 0.75 bmo 3.029800e+10 2.976570e+10
2 2021-07-29 CMCSA 0.84 0.67 bmo 2.854600e+10 2.717460e+10
3 2021-04-29 CMCSA 0.76 0.59 bmo 2.720500e+10 2.680920e+10
4 2021-01-28 CMCSA 0.56 0.48 bmo 2.770800e+10 2.309000e+10
Day_0 Day_1 Day_0_Close
0 2022-01-26 2022-01-27 48.459999
1 2021-10-27 2021-10-28 0.000000
2 0.000000
Here is what i have so far on my for loop
for idx, row in data1.iterrows():
orig_day = pd.to_datetime(row['date'])
temp_day = orig_day + pd.tseries.offsets.CustomBusinessDay(1, holidays=nyse.holidays().holidays)
prev_temp_day = orig_day - pd.tseries.offsets.CustomBusinessDay(1, holidays=nyse.holidays().holidays)
if row['time'] == 'amc':
data1.at[idx, 'Day_0'] = orig_day.strftime("%Y-%m-%d")
data1.at[idx, 'Day_1'] = temp_day.strftime("%Y-%m-%d")
else:
data1.at[idx, 'Day_0'] = prev_temp_day.strftime("%Y-%m-%d")
data1.at[idx, 'Day_1'] = orig_day.strftime("%Y-%m-%d")
symbol = row['symbol']
date_0 = row['Day_0']
date_1 = row['Day_1']
day_0_close = price1.loc[(price1.ticker == symbol) & (price1.Date == date_0), 'Adj Close'].values[0]
print(day_0_close)
data1.at[idx, 'Day_0_Close'] = day_0_close
Thank you for any help you can give
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|