import pandas as pd
from openpyxl import Workbook

def calculate_elo_rating(rating_a, rating_b, score_a, score_b, k_factor=32):
    expected_score_a = 1 / (1 + 10 ** ((rating_b - rating_a) / 400))
    expected_score_b = 1 / (1 + 10 ** ((rating_a - rating_b) / 400))

    new_rating_a = rating_a + k_factor * (score_a - expected_score_a)
    new_rating_b = rating_b + k_factor * (score_b - expected_score_b)

    return new_rating_a, new_rating_b

# Read the data from Excel into a pandas DataFrame
df = pd.read_excel('games.xlsx')

# Initialize new columns for the updated ratings
df['New Rating A'] = df['Rating A']
df['New Rating B'] = df['Rating B']

# Iterate over the rows and calculate the Elo ratings
for index, row in df.iterrows():
    rating_a = row['Rating A']
    rating_b = row['Rating B']
    score_a = row['Score A']
    score_b = row['Score B']

    new_rating_a, new_rating_b = calculate_elo_rating(rating_a, rating_b, score_a, score_b)

    df.at[index, 'New Rating A'] = new_rating_a
    df.at[index, 'New Rating B'] = new_rating_b

# Save the results to a new Excel file
df.to_excel('updated_ratings.xlsx', index=False)