I am working on a file in which i have three columns starting with scenario [+100, +25, -25, -100, Implied, EBA up, EBA Down] then rate index[GL-AUD-AONIA-01D, GL-INR-01D] then rates with next 5 year dates in monthly format.

my concern is say if for a particular rate index the implied or base shock scenario rate is say 5.00 then in case of +100 shock scenario it should be implied rate + 100 bps means 5 + 1 = 6.00 but in case the file which i am loading to pandas dataframe has a different rate say 9.00 against +100 shock scenario then it should mark it as false in comparison file which is not happening.

import pandas as pd

# Function to extract currency from rate index
def extract_currency(rate_index):
    if 'GL-' in rate_index:  # Assuming rate index starts with 'GL-'
        parts = rate_index.split('_')
        if len(parts) > 1:
            currency = parts[1]  # Extract currency after 'GL-'
        else:
            currency = None  # Handle case where currency extraction fails
    else:
        currency = None  # If rate index doesn't start with 'GL-', handle accordingly
    
    return currency

# Function to apply scenario adjustments
def apply_scenario(rate, scenario, rate_index):
    # Define shock values for each currency
    shock_values = {
        'ARS': 400, 'AUD': 350, 'BRL': 400, 'CAD': 250, 'CHF': 150, 'CNY': 300,
        'EUR': 250, 'GBP': 300, 'HKD': 200, 'IDR': 400, 'INR': 350, 'JPY': 100,
        'KRW': 250, 'MXN': 400, 'RUB': 400, 'SAR': 300, 'SEK': 300, 'SGD': 150,
        'TRY': 400, 'USD': 200, 'ZAR': 350
    }
    
    currency = extract_currency(rate_index)
    
    if currency is None:
        return rate  # Handle case where currency extraction failed
    
    shock = shock_values.get(currency, 0) / 100  # Convert bps to percentage
    
    if scenario == '+100':
        return rate + 1.0
    elif scenario == '+25':
        return rate + 0.25
    elif scenario == '-25':
        return rate - 0.25
    elif scenario == '-100':
        return rate - 1.0
    elif scenario == 'EBA up':
        return rate + shock  # Applying the shock value
    elif scenario == 'EBA down':
        return max(rate - shock, -1.0)  # Applying the shock value with a floor at -1%
    else:  # For Implied and Static scenarios
        return rate

# Load data from Excel and process
def process_excel_data(file_path, sheet_name, output_file):
    # Load data from Excel
    df = pd.read_excel(file_path, sheet_name=sheet_name, header=5)
    df = df.rename(columns={df.columns[0]: 'Scenario', df.columns[1]: 'Rate Index'})
    df['Scenario'] = df['Scenario'].str.split('/').str[1]
    
 
    rate_indexes = df['Rate Index']
    t0_rates = df.iloc[:, 2:]  # Assuming rates start from the third column
    print(t0_rates.iloc[:,:7].head())    
  
    adjusted_rates = pd.DataFrame(columns=t0_rates.columns)
   
    
    # Apply scenario adjustments and compare with T0 rates
    for index, row in df.iterrows():
        scenario = row['Scenario']
        rate_index = row['Rate Index']
        
        # Extract T0 rates for the current row
        t0_row = df.iloc[index, 2:]  # Assuming rates start from the third column
        
        # Apply scenario adjustments to T0 rates
        adjusted_row = t0_row.apply(lambda rate: apply_scenario(rate, scenario, rate_index))
       
        # Add adjusted rates to the DataFrame
        adjusted_rates = adjusted_rates.append(adjusted_row, ignore_index=True)
        
    # Compare adjusted rates with T0 rates and store the results
    comparison_results = (abs(adjusted_rates - t0_rates) < 1e-14).astype(int)
   
    # Insert Scenario and Rate Index columns to the comparison results DataFrame
    comparison_results.insert(0, 'Scenario', df['Scenario'])
    comparison_results.insert(1, 'Rate Index', df['Rate Index'])
    
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        comparison_results.to_excel(writer, index=False, sheet_name='Comparison Results')
        
    
    return comparison_results

# Example usage
file_path = "C:\Users\peter\Desktop\Rate_index\MF.xlsx"
sheet_name = 'Market Forecast Rate Indexes'
output_file = 'C:\Users\peter\Desktop\Rate_index\Market Forecast_comparison.xlsx'


comparison_results = process_excel_data(file_path, sheet_name,output_file)
print(f"Comparison results exported to {output_file}")

This is how the file look like