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

# BEGINNER EXAMPLE 1: Simple daily price export as CSV
tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA']
df = yf.download(tickers, period='1y', auto_adjust=True, progress=False)
df = df['Close'].reset_index()
df.columns.name = None
df.to_csv('prices_simple.csv', index=False)
print('Saved CSV: prices_simple.csv')

# BEGINNER EXAMPLE 2: Exporting a filtered ticker to CSV
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
aapl_df = ohlcv[ohlcv['Ticker'] == 'AAPL']
aapl_df.to_csv('aapl_ohlcv.csv', index=False)
print('Exported CSV: aapl_ohlcv.csv')

# BEGINNER EXAMPLE 3: Export portfolio with simulated shares as CSV
tickers = ['AAPL','MSFT','GOOGL','AMZN','TSLA','NVDA','META','NFLX','JPM','JNJ']
sector_map = {'AAPL':'Tech','MSFT':'Tech','GOOGL':'Tech','AMZN':'Consumer',
              'TSLA':'Auto','NVDA':'Tech','META':'Tech','NFLX':'Media',
              'JPM':'Finance','JNJ':'Health'}
np.random.seed(42)
data = yf.download(tickers, period='1y', auto_adjust=True, progress=False)['Close']
rows = []
for tk in tickers:
    buy = round(float(data[tk].iloc[0]), 2)
    cur = round(float(data[tk].iloc[-1]), 2)
    rows.append({'ticker': tk, 'shares': int(np.random.randint(5, 100)),
                 'buy_price': buy, 'cur_price': cur, 'sector': sector_map[tk]})
df = pd.DataFrame(rows)
df['gain_pct'] = np.round((df['cur_price'] - df['buy_price']) / df['buy_price'] * 100, 2)
df.to_csv('portfolio_positions.csv', index=False)
print('Exported simulated portfolio (real prices) to portfolio_positions.csv')

# BEGINNER EXAMPLE 4: Exporting summary statistics to CSV
returns = df['gain_pct']
summary = {
    'portfolio_gain_avg': returns.mean(),
    'portfolio_gain_min': returns.min(),
    'portfolio_gain_max': returns.max()
}
summary_df = pd.DataFrame([summary])
summary_df.to_csv('portfolio_summary.csv', index=False)
print('Exported summary statistics to portfolio_summary.csv')

# BEGINNER EXAMPLE 5: Exporting S&P 500 company data to Excel
url = 'https://raw.githubusercontent.com/datasets/s-and-p-500-companies/main/data/constituents.csv'
sp500_df = pd.read_csv(url)
sp500_df.to_excel('sp500_companies.xlsx', index=False)
print('Exported S&P500 list to sp500_companies.xlsx')

# INTERMEDIATE EXAMPLE 1: Exporting only selected columns
cols = ['ticker', 'shares', 'buy_price', 'cur_price', 'gain_pct']
df[cols].to_csv('portfolio_selected_cols.csv', index=False)
print('Exported selected portfolio columns to portfolio_selected_cols.csv')

# INTERMEDIATE EXAMPLE 2: Sorting by return before export
sorted_portfolio = df.sort_values('gain_pct', ascending=False)
sorted_portfolio.to_csv('portfolio_sorted.csv', index=False)
print('Exported sorted portfolio positions to portfolio_sorted.csv')

# INTERMEDIATE EXAMPLE 3: Exporting group summaries by sector
sector_summary = df.groupby('sector').agg({'gain_pct': ['mean','min','max'], 'shares': 'sum'})
sector_summary.columns = ['gain_avg','gain_min','gain_max','shares_total']
sector_summary.reset_index().to_csv('sector_summary.csv', index=False)
print('Exported sector-level summary report to sector_summary.csv')

# INTERMEDIATE EXAMPLE 4: Exporting top 5 and bottom 5 performers separately
top5 = df.nlargest(5, 'gain_pct')
bot5 = df.nsmallest(5, 'gain_pct')
top5.to_csv('portfolio_top5.csv', index=False)
bot5.to_csv('portfolio_bottom5.csv', index=False)
print('Exported top 5 to portfolio_top5.csv and bottom 5 to portfolio_bottom5.csv')

# INTERMEDIATE EXAMPLE 5: Exporting financial fundamentals to Excel
tickers_fund = ['AAPL','MSFT','GOOGL','AMZN','TSLA','NVDA','META','JPM','JNJ','XOM','WMT','V']
rows = []
for tk in tickers_fund:
    info = yf.Ticker(tk).info
    rows.append({
        'ticker':       tk,
        'sector':       info.get('sector', 'Unknown'),
        'market_cap':   info.get('marketCap'),
        'pe_ratio':     info.get('trailingPE'),
        'eps':          info.get('trailingEps'),
        'profit_margin':info.get('profitMargins'),
        'debt_equity':  info.get('debtToEquity')
    })
fund_df = pd.DataFrame(rows)
fund_df.to_excel('fundamentals_report.xlsx', index=False)
print('Exported financial fundamentals to fundamentals_report.xlsx')

# INTERMEDIATE EXAMPLE 6: Exporting returns time-series with custom rounding
data = yf.download('SPY', period='2y', auto_adjust=True, progress=False)
df_return = data[['Close','Volume']].reset_index()
df_return.columns = ['date','price','volume']
df_return['daily_return'] = (df_return['price'].pct_change()*100).round(4)
df_return = df_return.dropna().reset_index(drop=True)
df_return.to_csv('returns_time_series.csv', index=False, float_format='%.3f')
print('Exported SPY daily returns time-series to returns_time_series.csv')

# ADVANCED EXAMPLE 1: Exporting multiple dataframes each to a separate Excel sheet
with pd.ExcelWriter('portfolio_multi_sheet.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='Positions')
    sector_summary.reset_index().to_excel(writer, index=False, sheet_name='Sector')
    top5.to_excel(writer, index=False, sheet_name='Top5')
    bot5.to_excel(writer, index=False, sheet_name='Bottom5')
print('Exported to portfolio_multi_sheet.xlsx with multiple report tabs.')

# ADVANCED EXAMPLE 2: Applying Excel formatting - currency and percent columns
with pd.ExcelWriter('portfolio_excel_format.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False, sheet_name='Portfolio')
    workbook = writer.book
    worksheet = writer.sheets['Portfolio']
    fmt_dollar = workbook.add_format({'num_format': '$#,##0.00'})
    fmt_pct    = workbook.add_format({'num_format': '0.00%'})
    worksheet.set_column('C:D', 12, fmt_dollar)
    worksheet.set_column('F:F', 10, fmt_pct)
print('Portfolio report exported with Excel number formatting.')

# ADVANCED EXAMPLE 3: Exporting only high-performing trades, appending if file exists
import os
high_perf = df[df['gain_pct'] > 10]
file_path = 'high_performance_trades.csv'
header_flag = not os.path.exists(file_path)
high_perf.to_csv(file_path, mode='a', index=False, header=header_flag)
print(f'Appended high performers to {file_path}')

# ADVANCED EXAMPLE 4: Exporting trading signals for algorithmic backtesting
data = yf.download('AAPL', period='1y', auto_adjust=True, progress=False)
signal_df = data[['Close','Volume']].reset_index()
signal_df.columns = ['date','close','volume']
signal_df['sma20'] = signal_df['close'].rolling(20).mean().round(2)
signal_df['sma50'] = signal_df['close'].rolling(50).mean().round(2)
signal_df['signal'] = np.where(signal_df['sma20'] > signal_df['sma50'], 1, -1)
signal_df = signal_df.dropna().reset_index(drop=True)
signal_df.to_csv('aapl_signals.csv', index=False)
print('Exported AAPL trading signals to aapl_signals.csv')

# ERROR HANDLING AND DEBUGGING EXAMPLE 1: Handling export overwrite warnings
try:
    df.to_csv('portfolio_positions.csv', index=False)
    print('Portfolio exported.')
except PermissionError:
    print('Error: Could not write file. Is it open elsewhere?')

# ERROR HANDLING AND DEBUGGING EXAMPLE 2: Exporting after validating the DataFrame
if not df.empty:
    df.to_csv('portfolio_checked.csv', index=False)
    print('Portfolio checked and exported.')
else:
    print('Export failed: portfolio DataFrame is empty!')

# ADVANCED END-TO-END EXAMPLE: Full workflow from data to formatted Excel export
import datetime
today = datetime.datetime.today().strftime('%Y-%m-%d')
file_name = f'client_fin_report_{today}.xlsx'
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
summary_report = ohlcv.groupby('Ticker').agg({'Close': ['mean', 'max', 'min']})
summary_report.columns = ['mean_close','max_close','min_close']
with pd.ExcelWriter(file_name, engine='xlsxwriter') as writer:
    ohlcv.to_excel(writer, index=False, sheet_name='Data')
    summary_report.reset_index().to_excel(writer, index=False, sheet_name='Summary')
    workbook = writer.book
    ws_sum = writer.sheets['Summary']
    dollar_fmt = workbook.add_format({'num_format': '$#,##0.00'})
    ws_sum.set_column('B:D', 12, dollar_fmt)
print(f'Full client report exported to {file_name}')