import pandas as pd
import yfinance as yf
import numpy as np
import warnings
warnings.filterwarnings('ignore')

# Load multi-ticker OHLCV data for 5 major US tech companies (1 year)
tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA']
ohlcv = yf.download(tickers, period='1y', auto_adjust=True, progress=False)
ohlcv = ohlcv.stack(future_stack=True).rename_axis(['Date','Ticker']).reset_index()
ohlcv.columns.name = None
print(ohlcv.shape)
print(ohlcv.head(3))

# Select only Apple (AAPL) data from the long OHLCV DataFrame
aapl_ohlcv = ohlcv[ohlcv['Ticker'] == 'AAPL']
print(aapl_ohlcv.head(5))

# Select TSLA data for the first trading week
first_week = ohlcv[(ohlcv['Ticker'] == 'TSLA') & (ohlcv['Date'] <= ohlcv[ohlcv['Ticker']=='TSLA']['Date'].iloc[4])]
print(first_week)

# Find top 3 highest volume days for Google (GOOGL)
googl = ohlcv[ohlcv['Ticker'] == 'GOOGL']
top_vol = googl.sort_values('Volume', ascending=False).head(3)
print(top_vol[['Date','Volume','Close']])

# Pivot to create a wide Close price table (dates as rows, tickers as columns)
close_wide = ohlcv.pivot(index='Date', columns='Ticker', values='Close')
print(close_wide.head())

# Calculate daily returns for each ticker
ohlcv['Return'] = ohlcv.groupby('Ticker')['Close'].pct_change() * 100
print(ohlcv[['Date','Ticker','Close','Return']].head(8))

# Find all AMZN days where High-Low > 6% of Open price
amzn = ohlcv[ohlcv['Ticker']=='AMZN'].copy()
amzn['RangePct'] = (amzn['High'] - amzn['Low']) / amzn['Open'] * 100
volatile = amzn[amzn['RangePct'] > 6]
print(volatile[['Date','Open','High','Low','RangePct']].head(4))

# Top 5 TSLA days with both huge Volume and negative returns
tsla = ohlcv[ohlcv['Ticker'] == 'TSLA']
threshold = tsla['Volume'].quantile(0.95)
big_down = tsla[(tsla['Volume'] > threshold) & (tsla['Return'] < -4)]
print(big_down[['Date','Volume','Return','Close']].head())

import matplotlib.pyplot as plt
# Plot AAPL closing price and volume on two axes
aapl = ohlcv[ohlcv['Ticker']=='AAPL']
fig, ax1 = plt.subplots(figsize=(9,4))
ax1.plot(aapl['Date'], aapl['Close'], color='b', label='Close Price')
ax1.set_ylabel('Price (USD)', color='b')
ax2 = ax1.twinx()
ax2.bar(aapl['Date'], aapl['Volume'], alpha=0.2, color='r', label='Volume')
ax2.set_ylabel('Volume', color='r')
plt.title('AAPL: Close Price and Volume (1 year)')
plt.show()

# Compute 20-day rolling standard deviation of returns for MSFT
msft = ohlcv[ohlcv['Ticker'] == 'MSFT'].copy()
msft['RollStd'] = msft['Return'].rolling(20).std()
print(msft[['Date','Return','RollStd']].tail(5))

# Count NaNs in each column after all calculations so far
na_counts = ohlcv.isna().sum()
print('Missing values per column:')
print(na_counts)

# Drop rows with any missing data (if desired)
clean_ohlcv = ohlcv.dropna()
print('OHLCV after dropping missing:', clean_ohlcv.shape)

# Check for duplicate Date/Ticker pairs
dupes = ohlcv.duplicated(subset=['Date','Ticker']).sum()
print(f'Total duplicate date/ticker rows: {dupes}')

# Step 1: Clean OHLCV DataFrame
df = ohlcv.dropna()
# Step 2: Pivot to wide Close price table
wide = df.pivot(index='Date', columns='Ticker', values='Close')
# Step 3: Calculate total return for each ticker (percent change from start to end)
total_ret = 100.0 * (wide.iloc[-1] / wide.iloc[0] - 1)
best = total_ret.idxmax()
print('Total return by ticker (%):')
print(total_ret.round(2))
print(f'Best performing stock: {best}')

filename = 'best_stock_report.txt'
with open(filename, 'w') as f:
    f.write('Total return by ticker (%)\n')
    for ticker, val in total_ret.round(2).items():
        f.write(f'{ticker}: {val}%\n')
    f.write(f'\nBest performing stock: {best}\n')
print(f'Results saved to {filename}')