pythonpandasdataframestockyfinance

How to add price column to dataframe based on date column already present in yahooquery?

The following code is being used to create the output below it:

from yahooquery import Ticker

# Fetching data for AAPL
aapl = Ticker('AAPL')
types = ['asOfDate', 'TangibleBookValue', 'ShareIssued']
financial_data = aapl.get_financial_data(types, trailing=False)

# Dropping specific columns
columns_to_exclude = ['periodType', 'currencyCode']
financial_data.drop(columns=columns_to_exclude, inplace=True)
print(financial_data)

Output:

enter image description here

I would like to add an additional column from the history module that grabs the adjclose price based on the corresponding asOfDate. So the output should be:

enter image description here

Below is sample code that grabs price history data with the output below it:

tickers = Ticker('aapl', asynchronous=True)

# Default period = ytd, interval = 1d
df = tickers.history(start='2019-01-01', end='2023-12-31')
df.head()

enter image description here

Solution

  • Here you go:

    financial_data = financial_data.reset_index()
df = df.reset_index()
df['date'] = pd.to_datetime(df['date'], format='Y-%m-%d')
financial_data = pd.merge(left=financial_data, right=df[['date','adjclose']], left_on='asOfDate', right_on='date', how='outer')
financial_data

  symbol   asOfDate   ShareIssued  TangibleBookValue       date    adjclose
0   AAPL 2020-09-30  1.697676e+10       6.533900e+10 2020-09-30  113.604172
1   AAPL 2021-09-30  1.642679e+10       6.309000e+10 2021-09-30  139.697617
2   AAPL 2022-09-30  1.594342e+10       5.067200e+10 2022-09-30  137.204224
3   AAPL 2023-09-30  1.555006e+10       6.214600e+10        NaT         NaN