import os
import pandas as pd

#defining the folder path
folder_path = r'C:\Users\My PC\Desktop\python\index fund'

#get the list of files in the folder
files = os.listdir(folder_path)

# Filter the list to include only CSV files
csv_files = [file for file in files if file.endswith('.csv')]

# Define the list of file names
files = ['DIA.csv', 'FNILX.csv', 'IVV.csv', 'QQQ.csv', 'SPY.csv', 'SWPPX.csv', 'TRIWX.csv', 'VOO.csv', 'VTI.csv', 'VTWO.csv']

# Create an empty DataFrame to hold the combined data
combined_data = pd.DataFrame()

def calculate_moving_averages(data):

    # Calculate SMA50 and SMA200
    data['SMA50'] = data['Close'].rolling(window=50).mean()
    data['SMA200'] = data['Close'].rolling(window=200).mean()

    return data

# Calculate moving averages
combined_data = calculate_moving_averages(combined_data)

# Extract day of the week from 'Date' column
combined_data['DayOfWeek'] = pd.to_datetime(combined_data['Date']).dt.day_name()

# Shift the 'Close' column by 1 to get the previous day's closing price
combined_data['PrevDayClose'] = combined_data['Close'].shift(1)

# Calculate the change in price (Close - PrevDayClose)
combined_data['ChangePriceClose'] = combined_data['Close'] - combined_data['PrevDayClose']

# Calculate the percent change
combined_data['PercentChangeClose'] = ((combined_data['ChangePriceClose']) / 
                                  combined_data['PrevDayClose']) * 100

# Shift the 'Volume' column by 1 to get the previous day's Volume price
combined_data['PrevDayVolume'] = combined_data['Volume'].shift(1)

# Calculate the change in price (Volume - PrevDayVolume)
combined_data['ChangePriceVolume'] = combined_data['Volume'] - combined_data['PrevDayVolume']

# Calculate the percent change Volume
combined_data['PercentChangeVolume'] = ((combined_data['ChangePriceVolume']) / 
                                  combined_data['PrevDayVolume']) * 100

# Calculate the percent change
combined_data['PercentChangeVolume'] = ((combined_data['ChangePriceVolume']) / 
                                  combined_data['PrevDayVolume']) * 100

# Merge the best day of the week with the original DataFrame
df_merged = pd.merge(combined_data, best_day_df, on='stock', how='left')

df_merged = df_merged.rename(columns={"DayOfWeek_y": "BestDayTrade"})

# Save the modified data back to the new file
df_merged.to_csv('top 10 index funds historical data.csv', index=False)

# Define a function to generate the paragraph based on the stock ticker symbol
def generate_paragraph(stock):
    if stock == 'VTWO':
        return 'VTWO is the ticker symbol for the Vanguard Russell 2000 ETF, which is an exchange-traded fund that tracks the performance of the Russell 2000 index. This index includes 2000 small-cap US companies, providing investors with exposure to the small-cap equity market. As of 2021, VTWO had over $20 billion in assets under management.'
    elif stock == 'VTI':
        return 'VTI is the ticker symbol for the Vanguard Total Stock Market ETF, which is an exchange-traded fund that tracks the performance of the CRSP US Total Market Index. It provides investors with exposure to the entire US stock market, including large, mid, and small-cap companies across various sectors. As of 2021, VTI had over $200 billion in assets under management.'
    elif stock == 'VOO':
        return 'VOO is the ticker symbol for the Vanguard S&P 500 ETF, which is an exchange-traded fund that tracks the performance of the S&P 500 index. It provides investors with exposure to 500 of the largest companies in the US, spanning various sectors. As of 2021, VOO was one of the largest ETFs in the world, with assets under management of over $700 billion.'
    elif stock == 'TRIWX':
        return 'TRIWX is the ticker symbol for the T. Rowe Price Institutional Large Cap Growth Fund, which is a mutual fund that invests in large-cap US growth stocks. It aims to provide long-term capital growth through investments in companies with strong growth potential. The fund has a minimum investment of $1 million and is targeted at institutional investors.'
    elif stock == 'SWPPX':
        return 'SWPPX is the ticker symbol for the Schwab S&P 500 Index Fund, which is an index fund that tracks the performance of the S&P 500 index. It provides investors with exposure to 500 of the largest companies in the US, spanning various sectors. The fund has a low expense ratio and no minimum investment, making it an attractive option for investors seeking broad market exposure.'
    elif stock == 'SPY':
        return 'SPY is the ticker symbol for the SPDR S&P 500 ETF Trust, which is an exchange-traded fund that tracks the performance of the S&P 500 index. It provides investors with exposure to 500 of the largest companies in the US, spanning various sectors. As of 2021, SPY was one of the largest ETFs in the world, with assets under management of over $350 billion.'
    elif stock == 'QQQ':
        return 'QQQ is the ticker symbol for the Invesco QQQ Trust, which is an exchange-traded fund that tracks the performance of the NASDAQ-100 index. This index includes the largest non-financial companies listed on the NASDAQ stock exchange, with a focus on technology and growth stocks. As of 2021, QQQ had over $150 billion in assets under management.'
    elif stock == 'IVV':
        return 'IVV is the ticker symbol for the iShares Core S&P 500 ETF, which is an exchange-traded fund that tracks the performance of the S&P 500 index. It provides investors with exposure to the largest 500 companies in the US, spanning various sectors. As of 2021, IVV had over $300 billion in assets under management, making it one of the largest ETFs in the world.'
    elif stock == 'FNILX':
        return 'FNILX is the ticker symbol for the Fidelity ZERO Large Cap Index Fund, which is an index fund that tracks the performance of large-cap US stocks. It aims to provide investors with broad exposure to the US large-cap equity market while maintaining a low expense ratio. The fund has no minimum investment and no annual fees, making it an attractive option for cost-conscious investors.'
    elif stock == 'DIA':
        return 'DIA is the stock symbol for an ETF that tracks the performance of the Dow Jones Industrial Average, a stock market index of 30 large-cap companies in the US. It provides investors with exposure to a diverse range of industries. As of 2021, DIA had over $25 billion in assets under management.'

df_merged['paragraph'] = df_merged['stock'].apply(generate_paragraph)

# Save the modified data back to the new file
df_merged.to_csv('top 10 index funds historical data.csv', index=False)

# Define a function to generate the paragraph based on the stock ticker symbol
def generate_links(stock):
    if stock == 'VTWO':
        return 'https://finance.yahoo.com/quote/VTWO'
    elif stock == 'FNILX':
        return 'https://finance.yahoo.com/quote/FNILX' 
    elif stock == 'IVV':
        return 'https://finance.yahoo.com/quote/IVV'
    elif stock == 'QQQ':
        return 'https://finance.yahoo.com/quote/QQQ'
    elif stock == 'SPY':
        return 'https://finance.yahoo.com/quote/SPY' 
    elif stock == 'SWPPX':
        return 'https://finance.yahoo.com/quote/SWPPX' 
    elif stock == 'VOO':
        return 'https://finance.yahoo.com/quote/VOO' 
    elif stock == 'VTI':
        return 'https://finance.yahoo.com/quote/VOO' 
    elif stock == 'TRIWX':
        return 'https://finance.yahoo.com/quote/TRIWX' 
    elif stock == 'DIA':
        return 'https://finance.yahoo.com/quote/DIA' 

df_merged['link'] = df_merged['stock'].apply(generate_links)

# Save the modified data back to the new file
df_merged.to_csv('top 10 index funds historical data.csv', index=False)