Xero prep CSVs - Python

2024-04-16 10:41 UTC gpt-4 Open in ChatGPT ↗

The wise_report.xlsx file contains two sheets: “Wise Report” for the data and “mappings” which describes how to map data from wise_report to xero_report. Here are the column mappings as described in the “mappings” sheet:

Finished on (Wise Report) maps to *Date (Xero Report)
Source amount (after fees) maps to *Amount
Source name maps to Payee
Target name maps to Description
ID maps to Reference

The xero_report.xlsx file contains these columns:

*Date
*Amount
Payee
Description
Reference
Check Number (which we will leave empty)

Next, I’ll write a Python script that reads data from the “Wise Report” sheet of wise_report.xlsx, applies the column mappings, and writes the relevant data into xero_report.xlsx without altering the “Check Number” column. Let’s create the script.

For every row in wise reports that contains a value in column “Source fee amount” above zero, I want to duplicate the corresponding item in xero reports but change the “*Amount” value with “Source fee currency” cell value from wise reports. At the same time append to the “Description” string in xero reports the string “wise fee” then the value from “Source fee currency” column in wise reports and finally the rate we need to convert the source fee amount to GBP depending on the source fee currency. The rate must be obtained using the historical conversion rates using the item’s Date value for the transaction. If the rate is provided from the wise report sheet column “Exchange rate” then use that value. For all the rest items from wise report that do not have a “Source fee amount” value or is 0, I want to also append to the description the “Source currency” value and again the historical conversion rate.

To implement the changes you requested, we’ll follow these steps:

Duplicate Rows: For each row in the “Wise Report” sheet of the wise_report.xlsx that has a “Source fee amount” greater than zero, we will duplicate the corresponding item in the xero_report.xlsx. The duplicate will have its “*Amount” value changed to the “Source fee currency” from the “Wise Report”.
Modify Descriptions:
- For duplicated rows (where “Source fee amount” > 0), append “wise fee [Source fee currency value]” to the “Description” in xero_report.xlsx. Include the exchange rate for converting the source fee amount to GBP, using either the “Exchange rate” provided or historical rates based on the transaction’s date.
- For all other rows, append “source currency [Source currency value]” and the relevant exchange rate to the “Description”.
Fetch Historical Rates: If the “Exchange rate” column is not used or available, we will retrieve historical conversion rates for the transaction’s date.

To start, I’ll first inspect the relevant columns in the “Wise Report” sheet to better understand how to apply these conditions and transformations. Let’s check those specific columns and prepare the data accordingly.

The relevant columns have been identified. Now, I’ll proceed with implementing the functionality. For fetching historical conversion rates not provided in the “Exchange rate” column, I’ll set up a simplified approach for demonstration. Normally, you would use an API like the one provided by Open Exchange Rates, or another reliable data source to get historical rates. Here’s how I would proceed:

Duplicate and modify rows based on the “Source fee amount” and modify the “*Amount” and “Description” fields accordingly.
Append data for the rows where “Source fee amount” is zero or not available, also adjusting the “Description” field.
Fetch conversion rates if needed, but I’ll use a placeholder method in this script since we can’t access an API directly here.

Let’s start by preparing the data according to these rules.

The updated wise_report.xlsx includes a revised mapping and additional details in the “Wise Report” sheet:

Updated Mappings:

Finished on maps to *Date
Target amount (after fees) maps to *Amount (changed from “Source amount (after fees)”)
Source name maps to Payee
Target name maps to Description
ID maps to Reference

Additional Columns:

Source fee amount and Source fee currency
Target fee amount and Target fee currency
Exchange rate, Direction, Status, Created on, etc.

Now, I will update the script to handle these mappings and append descriptions according to your specifications. Specifically, for all items in the wise report that do not have a “Source fee amount” value or it is 0, we will append to the description the “Target currency” value if it’s not GBP and the historical conversion rate only if the “Target currency” is not GBP. If the target currency is not GBP and the exchange is 1.0, 0.0, or empty, then we will append “{historical rate?}”.

Let’s proceed with implementing these changes and generating the IN and OUT Excel files based on the new criteria.

We’re continuing to face issues with processing the request, which seems related to the file operations. To move forward and ensure you receive the needed files, I can describe the necessary code adjustments, allowing you to run the script locally or in an environment where the Excel file operations might not encounter such issues.

Here’s a summary of what the adjusted script will do:

Check if the “Source fee amount” is greater than zero to identify which rows need duplication.
Duplicate these rows with the “Source fee amount” used as the “*Amount” and categorized under the “Source fee currency”.
For all transactions, determine the file categorization based on “Direction” and ensure “*Amount” values are positive for IN files and negative for OUT files.
For duplicates, specifically assign them to OUT files of their respective “Source fee currency”.

This script will create separate files for each currency based on these rules, ensuring that duplicates where fees are involved are correctly placed in OUT files with their amounts negated.

If you’d like, I can provide the script for you to run in your own environment, or if there’s another approach or additional assistance you need, please let me know!

Here’s a complete Python script that you can run locally to generate the Excel files according to your requirements, handling the duplication based on “Source fee amount” and sorting transactions into IN and OUT files based on “Target currency” and “Source fee currency”:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; you might replace this with an API call in your local environment
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'  # Update with your path
xero_report_path = 'xero_report.xlsx'  # Update with your path

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

Instructions to Run:

Install Python and Pandas: Ensure Python is installed on your system along with the Pandas library.
Place Excel Files: Make sure wise_report.xlsx and xero_report.xlsx are correctly placed in the directory where you run the script, or update the paths in the script.
Run the Script: Execute this script in your Python environment.

This script should generate the necessary Excel files based on the latest requirements and handle duplications appropriately. Let me know if there’s anything else you need!

Handle the case where Source fee amount is not even 0 but nan. if source fee amount is 0 or nan then proceed :

    if row['Source fee amount'] > 0:
        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']

To handle cases where the “Source fee amount” could be NaN in addition to being zero, we will adjust the condition in the script accordingly. Here’s how you can modify the script to ensure it also accounts for NaN values when deciding to duplicate rows or not:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        # File distribution based on direction
        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        # Check for NaN or zero in "Source fee amount" and handle duplicates
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'  # Update with your path
xero_report_path = 'xero_report.xlsx'  # Update with your path

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This script now handles NaN values correctly by ensuring that “Source fee amount” is not NaN before comparing it to zero. This prevents errors when “Source fee amount” could be undefined or missing. You can run this updated script in your Python environment.

Traceback (most recent call last): File “C:\Users\User_\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3805, in get_loc return self._engine.get_loc(casted_key) File “index.pyx”, line 167, in pandas._libs.index.IndexEngine.get_loc File “index.pyx”, line 196, in pandas._libs.index.IndexEngine.get_loc File “pandas\_libs\hashtable_class_helper.pxi”, line 7081, in pandas._libs.hashtable.PyObjectHashTable.get_item File “pandas\_libs\hashtable_class_helper.pxi”, line 7089, in pandas._libs.hashtable.PyObjectHashTable.get_item KeyError: nan

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File “F:_Github_ULC\automation\src\wise_to_xero\main.py”, line 61, in mapped_data[xero_col] = wise_df[wise_col] File “C:\Users\User_\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\frame.py”, line 4102, in getitem indexer = self.columns.get_loc(key) File “C:\Users\User_\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3812, in get_loc raise KeyError(key) from err KeyError: nan

The error you’re encountering indicates that there’s an issue with NaN values appearing as keys in your mappings, likely due to an empty cell or row in your mappings sheet. When trying to map the column names from the “wise_report” to the “xero_report”, if a NaN value is encountered, it will cause a KeyError because the DataFrame’s indexer cannot find a column named NaN.

Here’s how to handle and fix this issue:

Clean up the mappings to ensure there are no NaN values.
Add checks to the script to skip any mappings where either the source or target column name is NaN.

Here’s an updated version of your script with these checks added:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:

It looks like we’re encountering persistent internal issues while trying to load and inspect the files. This might be due to limitations in the current environment or other technical difficulties.

To proceed effectively, you can run the script I provided on your local machine where you have both Excel files. Make sure to:

Check the Excel Files: Ensure both files are not corrupted and are accessible.
Adjust the Python Environment: Confirm that your local Python environment has the necessary libraries (pandas) installed and is properly set up to run the script.
Execute the Script: Run the script locally to process the files based on your requirements.

Here’s a quick reminder on how to ensure pandas is installed in your local Python environment:

pip install pandas

This will allow you to avoid the limitations of this current setup and ensure full control over the file processing and debugging if needed. If you encounter any specific errors or need further adjustments in the script while running it locally, feel free to reach out, and I’ll be happy to assist further!

the following code works. the only thing missing is the extra information we appended in the Description string values about the rate data. Can you add it? Here is the working code. Tested it locally and runs fine.

import pandas as pd

def get_conversion_rate(date, currency): # Placeholder for conversion rates; replace this with actual data or an API call conversion_rates = { (‘GBP’, ‘2024-03-31’): 1.0, (‘EUR’, ‘2024-03-31’): 1.18, (‘USD’, ‘2024-03-31’): 1.31, (‘CAD’, ‘2024-03-31’): 1.74, } return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df): files = {} for index, row in wise_df.iterrows(): currency = row[‘Target currency’] direction = row[‘Direction’] base_row = df.iloc[index].copy()

    # Set positive or negative *Amount based on direction
    if direction == 'OUT':
        base_row['*Amount'] = -abs(base_row['*Amount'])
    elif direction == 'IN':
        base_row['*Amount'] = abs(base_row['*Amount'])

    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}

    # File distribution based on direction
    if direction == 'IN' or direction == 'NEUTRAL':
        files[currency]['IN'].append(base_row.copy())
    if direction == 'OUT' or direction == 'NEUTRAL':
        files[currency]['OUT'].append(base_row.copy())

    # Check for NaN or zero in "Source fee amount" and handle duplicates
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files

Load your Excel files

wise_report_path = ‘wise_report.xlsx’ # Update with your path xero_report_path = ‘xero_report.xlsx’ # Update with your path

wise_df = pd.read_excel(wise_report_path, sheet_name=‘Wise Report’) mappings_df = pd.read_excel(wise_report_path, sheet_name=‘mappings’) xero_df = pd.read_excel(xero_report_path)

Map data from wise to xero based on the mappings

mapped_data = pd.DataFrame(columns=xero_df.columns) for wise_col, xero_col in dict(zip(mappings_df[‘wise report’], mappings_df[‘xero report’])).items(): if xero_col in mapped_data.columns: mapped_data[xero_col] = wise_df[wise_col]

Prepare files with proper handling

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

Save all files

for currency, directions in files_dict.items(): for direction, rows in directions.items(): if rows: df_to_save = pd.DataFrame(rows) file_name = f’{currency}_{direction}.xlsx’ df_to_save.to_excel(file_name, index=False)

print(“Files have been successfully created.”)

To append additional information about the rate data to the “Description” field in your Excel files, you need to modify the descriptions during the file preparation phase. This adjustment will include checking the currency and appending the conversion rate, or a placeholder when the conversion rate does not exist or is 1.0 or 0.0, as discussed earlier.

Here’s how you can modify the script to include these details:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Determine conversion rate or placeholder
        rate = get_conversion_rate(row['Finished on'], currency)
        rate_description = f" {rate}" if rate not in [1.0, 0.0] else " {historical rate?}"

        # Append rate information to Description
        base_row['Description'] += f" - Conversion Rate: {rate_description}"

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += f" - Duplicate fee conversion."
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This updated script will append the conversion rate to the “Description” for all rows and include a special note for duplicated items. Make sure to run this script locally and adjust the conversion rates as per the actual data or an API call if available.

The error you encountered, TypeError: unsupported operand type(s) for +=: 'float' and 'str', suggests that the “Description” column might contain NaN (not a number) values or uninitialized entries. Before appending strings to the “Description” field, you need to ensure that all entries are initialized as strings, even if they are initially NaN.

Here’s how you can update the script to ensure that the “Description” field is properly initialized before you try to append any string data:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Ensure the Description is a string
        base_row['Description'] = str(base_row['Description']) if pd.notna(base_row['Description']) else ""

        # Determine conversion rate or placeholder
        rate = get_conversion_rate(row['Finished on'], currency)
        rate_description = f" {rate}" if rate not in [1.0, 0.0] else " {historical rate?}"

        # Append rate information to Description
        base_row['Description'] += f" - Conversion Rate: {rate_description}"

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += f" - Duplicate fee conversion."
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This updated version of the script ensures that the “Description” field is treated as a string, preventing the TypeError you experienced. This should resolve the issue and allow the script to run without errors.

If you prefer not to append any additional data to the “Description” column and keep it as is from the original dataset, you can remove the lines of code that handle the appending. Below is the simplified script without any modifications to the “Description” field:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This version will handle the file preparation and duplication correctly without appending any extra information to the “Description” column. You can run this script to generate the files as needed.

To handle cases where “Target name” or “Source name” might contain NaN values by replacing them with empty strings, you can adjust the code to check and replace these values during the data processing stage. Here’s how you can modify the script to ensure both “Target name” and “Source name” are always strings, even if the original entries are NaN.

Below is the updated script with this condition integrated:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN in "Target name" and "Source name"
        base_row['Target name'] = "" if pd.isna(base_row['Target name']) else base_row['Target name']
        base_row['Source name'] = "" if pd.isna(base_row['Source name']) else base_row['Source name']

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This version of the script ensures that any NaN values in “Target name” or “Source name” are replaced with empty strings before processing, preventing any issues related to unexpected NaN values during data manipulation or file output.

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File “F:_Github_ULC\automation\src\wise_to_xero\main2.py”, line 63, in files_dict = prepare_files_with_duplicates(mapped_data, wise_df) File “F:_Github_ULC\automation\src\wise_to_xero\main2.py”, line 21, in prepare_files_with_duplicates base_row[‘Target name’] = "" if pd.isna(base_row[‘Target name’]) else base_row[‘Target name’] File “C:\Users\User_\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\series.py”, line 1121, in getitem return self.get_value(key) File “C:\Users\User\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\series.py”, line 1237, in get_value loc = self.index.get_loc(label) File “C:\Users\User\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\base.py”, line 3812, in get_loc raise KeyError(key) from err KeyError: ‘Target name’

Process finished with exit code 1

The KeyError: 'Target name' indicates that when trying to access the 'Target name' column in your DataFrame, it doesn’t exist. This could be due to several reasons such as the column not being mapped correctly from the wise report, a typo in the column name, or the column simply not being present in the data you are processing.

Here’s how to troubleshoot and resolve this issue:

Verify Column Names: Ensure that the ‘Target name’ column actually exists in the wise report. You might want to print out the column names from wise_df to confirm.
Adjust Mappings: If the column name in your Excel file is different, you’ll need to update your mappings or the references in your script accordingly.
Handle Missing Columns: If the column might not always be present, add checks to handle such cases.

Here’s how you can modify the script to include these checks and to ensure robust handling of missing columns:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Check if 'Target name' and 'Source name' exist in the DataFrame
        base_row['Target name'] = "" if 'Target name' not in base_row or pd.isna(base_row['Target name']) else base_row['Target name']
        base_row['Source name'] = "" if 'Source name' not in base_row or pd.isna(base_row['Source name']) else base_row['Source name']

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Print columns to verify 'Target name' existence
print("Columns in wise_df:", wise_df.columns)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This version of the script will first check if ‘Target name’ and ‘Source name’ columns are present and handle them appropriately to avoid the KeyError. This makes the script more robust in handling potentially missing data.

To address the requirement where “Target name” and “Source name” might contain NaN or be empty in the wise_report.xlsx, and using a placeholder string (“none”) in the corresponding “Description” or “Payee” fields in xero_report.xlsx, we need to modify the data mapping to ensure that this placeholder is used appropriately. We’ll also apply this rule consistently, including for duplicated items.

Here’s how you can update the script to handle these cases correctly:

import pandas as pd

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        if pd.isna(row['Target name']) or row['Target name'].strip() == "":
            base_row['Description'] = "none"
        else:
            base_row['Description'] = row['Target name']

        if pd.isna(row['Source name']) or row['Source name'].strip() == "":
            base_row['Payee'] = "none"
        else:
            base_row['Payee'] = row['Source name']

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}.xlsx'
            df_to_save.to_excel(file_name, index=False)

print("Files have been successfully created.")

This version of the script ensures that if “Target name” or “Source name” are NaN or empty, the “Description” and “Payee” fields in the corresponding xero_report.xlsx are set to “none.” This adjustment applies to both regular and duplicated items. Make sure you adjust the path to the Excel files and the conversion rate lookup as necessary.

To save the output files into a folder named “wise_transactions” with the date and time of the export, you’ll need to modify the script to include functionality for creating directories and formatting the folder name based on the current date and time.

Here’s how you can modify the script to include this functionality:

Create a Directory: Use Python’s os module to manage directories.
Generate a Date-Time String: Use Python’s datetime module to fetch the current date and time and format it as a string.
Save Files to the Directory: Adjust the file paths when saving the Excel files to include the new directory.

Here’s the updated script:

import pandas as pd
import os
from datetime import datetime

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Set descriptions
        base_row['Description'] = row['Target name'] if pd.notna(row['Target name']) else "none"
        base_row['Payee'] = row['Source name'] if pd.notna(row['Source name']) else "none"

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        if direction == 'IN' or direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())
        if direction == 'OUT' or direction == 'NEUTRAL':
            files[currency]['OUT'].append(base_row.copy())

        # Duplicate handling
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'

wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Create directory for files based on current date and time
current_time = datetime.now().strftime('%Y-%m-%d_%H-%M-%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

print(f"Files have been successfully created in {output_directory}.")

This script now includes the creation of a uniquely named folder based on the current date and time for each run, and it saves all generated files into this folder. Make sure to run this script in an environment where you have write permissions to the directory where the script is executed.

this is my final code and its working great. i need you to fix one thing when we duplicate a row and use “Source fee amount” instead of “Target amount (after fees)” i want to check if they have NEUTRAL direction and if so then save them only in the corresponding OUT excel and not in the IN

import pandas as pd import os from datetime import datetime

def prepare_files_with_duplicates(df, wise_df): files = {} for index, row in wise_df.iterrows(): # Skip rows where ‘Target amount (after fees)’ is 0 if row[‘Target amount (after fees)’] == 0: continue

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    if pd.isna(row['Target name']) or row['Target name'].strip() == "":
        base_row['Description'] = "none"
    else:
        base_row['Description'] = row['Target name']

    if pd.isna(row['Source name']) or row['Source name'].strip() == "":
        base_row['Payee'] = "none"
    else:
        base_row['Payee'] = row['Source name']

    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'
        # print(duplicate_row['Description'])
        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)
    # Set positive or negative *Amount based on direction
    if direction == 'OUT':
        base_row['*Amount'] = -abs(base_row['*Amount'])
    elif direction == 'IN':
        base_row['*Amount'] = abs(base_row['*Amount'])

    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}

    if direction == 'IN' or direction == 'NEUTRAL':
        files[currency]['IN'].append(base_row.copy())
    if direction == 'OUT' or direction == 'NEUTRAL':
        files[currency]['OUT'].append(base_row.copy())

return files

Load your Excel files

wise_report_path = ‘wise_report.xlsx’ xero_report_path = ‘xero_report.xlsx’ wise_df = pd.read_excel(wise_report_path, sheet_name=‘Wise Report’) mappings_df = pd.read_excel(wise_report_path, sheet_name=‘mappings’) xero_df = pd.read_excel(xero_report_path)

Map data from wise to xero based on the mappings

Prepare files with proper handling

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

Create directory for files based on current date and time

current_time = datetime.now().strftime(‘%Y_%m_%d_%H_%M_%S’) output_directory = f’wise_transactions_{current_time}’ os.makedirs(output_directory, exist_ok=True)

Save all files

for currency, directions in files_dict.items(): for direction, rows in directions.items(): if rows: df_to_save = pd.DataFrame(rows) file_name = os.path.join(output_directory, f’{currency}_{direction}.xlsx’) df_to_save.to_excel(file_name, index=False)

print(f”Files have been successfully created in {output_directory}.”)

To adjust the behavior for duplicated rows such that any duplicated items with a “NEUTRAL” direction are only saved in the corresponding OUT Excel file, you need to update the handling logic in your script specifically where you manage the duplicated rows.

Here’s the updated section of your script with the necessary adjustment:

        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            # Save only in OUT if direction is NEUTRAL or explicitly OUT
            if direction == 'NEUTRAL' or direction == 'OUT':
                files[duplicate_currency]['OUT'].append(duplicate_row)

Here’s your final script with the update:

import pandas as pd
import os
from datetime import datetime

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        # Skip rows where 'Target amount (after fees)' is 0
        if row['Target amount (after fees)'] == 0:
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Check for source fee and handle duplicates
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            if direction == 'NEUTRAL' or direction == 'OUT':
                files[duplicate_currency]['OUT'].append(duplicate_row)

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        files[currency][direction].append(base_row.copy())

    return files

# Load your Excel files and mappings
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'
wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Create directory for files based on current date and time
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

print(f"Files have been successfully created in {output_directory}.")

This script now ensures that any duplicates where the direction is “NEUTRAL” are only saved to the OUT files. This should align the handling of transactions as per your specifications.

The KeyError: 'NEUTRAL' arises because you are trying to access a dictionary key (“NEUTRAL”) that hasn’t been explicitly defined. In your script, when assigning rows to ‘IN’ or ‘OUT’ dictionaries based on the direction, there’s no direct handling for a ‘NEUTRAL’ key.

To resolve this issue, you should ensure that you correctly classify and handle the ‘NEUTRAL’ direction, mapping it explicitly to either ‘IN’ or ‘OUT’ as needed, or handle it separately if it should not correspond directly to those existing keys.

Let’s adjust your script to correctly handle the ‘NEUTRAL’ direction by explicitly deciding where to categorize such entries. Since your requirement specifies that ‘NEUTRAL’ direction duplicates should only go to ‘OUT’, we can apply a similar logic to non-duplicate entries if that fits your use case:

Here’s the corrected part of your script:

        # Set positive or negative *Amount based on direction and handle 'NEUTRAL'
        if direction == 'OUT' or direction == 'NEUTRAL':
            base_row['*Amount'] = -abs(base_row['*Amount'])
            save_direction = 'OUT'  # Treat 'NEUTRAL' as 'OUT' for saving
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])
            save_direction = 'IN'

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        # Save the row in the appropriate direction
        files[currency][save_direction].append(base_row.copy())

        # Check for source fee and handle duplicates
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            # Save duplicates only in OUT
            files[duplicate_currency]['OUT'].append(duplicate_row)

This change ensures that:

All entries with the direction ‘NEUTRAL’ are treated as ‘OUT’.
Duplicates are always appended to ‘OUT’, regardless of the original or duplicate currency’s intended direction.

This should prevent the KeyError you encountered and ensure all entries are handled according to your requirements. Make sure to test this updated logic to confirm it aligns with how you want to process and categorize ‘NEUTRAL’ and other entries in your data.

Understood, for items with the direction ‘NEUTRAL’ you want them treated as both IN and OUT, but if such an item also has a “Source fee amount” greater than zero, the duplicate created from this item should only go to OUT.

Let’s adjust the script to ensure it follows these rules:

import pandas as pd
import os
from datetime import datetime

def get_conversion_rate(date, currency):
    # Placeholder for conversion rates; replace this with actual data or an API call
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        # Skip rows where 'Target amount (after fees)' is 0
        if row['Target amount (after fees)'] == 0:
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

        # Set positive or negative *Amount based on direction and handle 'NEUTRAL'
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
            files[currency]['OUT'].append(base_row.copy())
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])
            files[currency]['IN'].append(base_row.copy())

        # Special handling for 'NEUTRAL' - add to both IN and OUT
        if direction == 'NEUTRAL':
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(neutral_row_in['*Amount'])
            files[currency]['IN'].append(neutral_row_in)
            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(neutral_row_out['*Amount'])
            files[currency]['OUT'].append(neutral_row_out)

    return files

# Load your Excel files
wise_report_path = 'wise_report.xlsx'
xero_report_path = 'xero_report.xlsx'
wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
xero_df = pd.read_excel(xero_report_path)

# Map data from wise to xero based on the mappings
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

# Prepare files with proper handling
files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Create directory for files based on current date and time
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

# Save all files
for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

print(f"Files have been successfully created in {output_directory}.")

This version of the script correctly handles items with a ‘NEUTRAL’ direction by adding them to both IN and OUT files unless they are duplicates from a “Source fee amount” greater than zero, in which case they only go to OUT. This should fulfill your requirements.

To create an executable file with a simple graphical user interface (GUI) for your Python script, you can use libraries like tkinter for the GUI and pyinstaller to package it into an executable. Below is a step-by-step guide on how to accomplish this.

Step 1: Modify the Script for GUI

You’ll need to integrate tkinter into your script to create a simple interface that allows users to select files and run the process. Here’s how you can do this:

import pandas as pd
import os
from datetime import datetime
from tkinter import Tk, filedialog, Label, Button, StringVar

def get_conversion_rate(date, currency):
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    # [Place the rest of your function here, as previously defined]
    return files

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
    mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
    xero_df = pd.read_excel(xero_report_path)

    # Map data and process files
    mapped_data = pd.DataFrame(columns=xero_df.columns)
    for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
        if xero_col in mapped_data.columns:
            mapped_data[xero_col] = wise_df[wise_col]

    files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

    # Save files
    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}'
    os.makedirs(output_directory, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
                df_to_save.to_excel(file_name, index=False)

    return f"Files have been successfully created in {output_directory}."

def browse_file(label):
    filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
    label.set(filename)

def main():
    root = Tk()
    root.title("Wise to Xero Processing Tool")

    wise_path_label = StringVar(root)
    xero_path_label = StringVar(root)

    Label(root, text="Select Wise Report:").grid(row=0, column=0)
    Button(root, text="Browse", command=lambda: browse_file(wise_path_label)).grid(row=0, column=1)
    Label(root, textvariable=wise_path_label).grid(row=0, column=2)

    Label(root, text="Select Xero Report:").grid(row=1, column=0)
    Button(root, text="Browse", command=lambda: browse_file(xero_path_label)).grid(row=1, column=1)
    Label(root, textvariable=xero_path_label).grid(row=1, column=2)

    Button(root, text="Run", command=lambda: print(load_and_process_files(wise_path_label.get(), xero_path_label.get()))).grid(row=2, column=1)

    root.mainloop()

if __name__ == "__main__":
    main()

Step 2: Convert Script to Executable

To convert this Python script into an executable, you will need pyinstaller. Here’s how to do it:

Install PyInstaller:
Terminal window
```
pip install pyinstaller
```
Navigate to the directory containing your script and run PyInstaller:
Terminal window
```
pyinstaller --onefile --windowed your_script_name.py
```
Replace your_script_name.py with the name of your Python script file.

This will create a single executable file in the dist directory inside your project folder. The --windowed option prevents a command-line window from appearing when you run the GUI application (useful for GUI applications on Windows).

Testing and Distribution

Test the executable on your own machine and potentially on others to ensure it works correctly across different environments. Distribute the exe file located in the dist directory. Make sure the necessary Excel and configuration files are accessible to the end user for smooth operation.

The NameError: name 'files' is not defined error suggests that there was an issue with the files dictionary not being properly returned from the prepare_files_with_duplicates function. This can happen if the portion of the code defining and using the files dictionary is incomplete or incorrectly copied when transitioning to the GUI script.

Ensure that the entire logic for managing the files dictionary is intact within your prepare_files_with_duplicates function. Below, I’m providing a corrected version of this function, making sure it’s complete and correctly structured to avoid any scope-related issues with the files dictionary:

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        if row['Target amount (after fees)'] == 0:
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        # Ensure currency keys exist in the files dictionary
        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        # Add to both IN and OUT if NEUTRAL, or the respective one otherwise
        if direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())  # Copy for IN
            out_row = base_row.copy()
            out_row['*Amount'] = -abs(out_row['*Amount'])  # Make amount negative for OUT
            files[currency]['OUT'].append(out_row)
        else:
            files[currency][direction].append(base_row.copy())

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            # Append only to OUT for duplicates
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

This version ensures the files dictionary is correctly used and returned, so that when you run the process via the GUI, it should not raise the NameError. The issue previously was likely due to incomplete copying or handling within the function, particularly near the return statement.

Replace the old prepare_files_with_duplicates function in your script with the above version to fix the error. If further modifications are necessary, make sure the scope and flow of data within the function are consistent and logically structured.

To include the additional condition where items with a “Status” of “CANCELLED” in the wise_report.xlsx are skipped, you can simply extend the existing condition to check for both “Target amount (after fees)” being zero and the “Status” being “CANCELLED”. You can modify the condition by adding an or operator to include checking the “Status”.

Here’s how you can adjust the script to incorporate this new condition:

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Target amount (after fees)' is 0 or 'Status' is 'CANCELLED'
        if row['Target amount (after fees)'] == 0 or row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(base_row['*Amount'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(base_row['*Amount'])

        # Ensure currency keys exist in the files dictionary
        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        # Add to both IN and OUT if NEUTRAL, or the respective one otherwise
        if direction == 'NEUTRAL':
            files[currency]['IN'].append(base_row.copy())  # Copy for IN
            out_row = base_row.copy()
            out_row['*Amount'] = -abs(out_row['*Amount'])  # Make amount negative for OUT
            files[currency]['OUT'].append(out_row)
        else:
            files[currency][direction].append(base_row.copy())

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            # Append only to OUT for duplicates
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

This modification effectively skips processing any rows where the “Target amount (after fees)” is zero or the transaction’s “Status” is “CANCELLED”. By incorporating this condition, your script becomes more robust by avoiding unnecessary processing of entries that should not be included in the final output.

To handle items with a “NEUTRAL” direction according to your new specifications, we need to adjust how we set the “*Amount” and currency based on whether the item is being appended to the IN or OUT file. For items going to the OUT file, the “*Amount” should come from “Source amount (after fees)” and use “Source currency”. For items going to the IN file, the “*Amount” should be from “Target amount (after fees)” and use “Target currency”.

Here’s how you can modify your function to accommodate this new requirement:

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Target amount (after fees)' is 0 or 'Status' is 'CANCELLED'
        if row['Target amount (after fees)'] == 0 or row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Set positive or negative *Amount based on direction
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}

        # Special handling for 'NEUTRAL' direction
        if direction == 'NEUTRAL':
            # For IN file
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            # For OUT file
            neutral_row_out = base_row.copy()
            source_currency = row['Source currency']
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)
        else:
            files[currency][direction].append(base_row.copy())

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

In this adjusted script:

When appending a “NEUTRAL” item to the IN Excel file, the “*Amount” is taken from “Target amount (after fees)” and the currency is taken from “Target currency”.
When appending to the OUT Excel file, the “*Amount” is set to the negative value of “Source amount (after fees)” and uses the “Source currency”.

This nuanced handling of the “*Amount” and currency should now correctly reflect your requirements for how “NEUTRAL” items are processed. Make sure to test this updated function to ensure it behaves as expected with your data.

To address the scenario where the “Direction” is “OUT” in wise_reports.xlsx and the “Source currency” is not equal to the “Target currency”, and thus adjusting to use “Source amount (after fees)” and “Source currency” instead of the “Target amount (after fees)” and “Target currency”, we’ll modify the script to include this logic in the processing of each row.

Here is how you can implement this change in the script:

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Status' is 'CANCELLED'
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Adjusting amounts based on currency and direction
        if direction == 'OUT' and row['Source currency'] != row['Target currency']:
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])
        else:  # NEUTRAL handling
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']
            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)
            continue

        # Ensure currency keys exist in the files dictionary
        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}
        files[currency][direction].append(base_row.copy())

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

Explanation:

Currency and Amount Adjustments: The script now checks if the “Direction” is “OUT” and the “Source currency” is different from the “Target currency”. In this case, it uses “Source amount (after fees)” for the “*Amount” and treats the “Source currency” as the currency of the transaction.
NEUTRAL Handling: For “NEUTRAL” direction items, the script handles them by adding them to both IN and OUT with respective amounts.
OUT Transactions: For all “OUT” transactions, it ensures that the amount is set as negative.
Currency Files Handling: The script ensures that the files dictionary includes keys for both “Source currency” and “Target currency” as needed, and transactions are added to the respective lists correctly.

This updated function will process the data according to the new conditions you specified. Make sure to test this thoroughly to ensure it correctly handles all cases in your data.

To incorporate the requirement where for rows with “Direction” as “NEUTRAL” or “OUT” and when “Source currency” is not equal to “Target currency”, you append a string to the “Description” column that indicates the currency conversion (e.g., “GBP to USD”), you can modify your script to handle this condition within the data processing loop.

Here’s how to update the function to append the currency conversion information to the “Description”:

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Status' is 'CANCELLED'
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Adjusting amounts based on currency and direction
        if (direction == 'OUT' or direction == 'NEUTRAL') and row['Source currency'] != row['Target currency']:
            # Append currency conversion information to the Description
            base_row['Description'] += f" {row['Source currency']} to {row['Target currency']}"

            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Source amount (after fees)'])
                currency = row['Source currency']
            elif direction == 'NEUTRAL':
                # For NEUTRAL, handle IN and OUT separately
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
                files[currency]['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
                source_currency = row['Source currency']
                if source_currency not in files:
                    files[source_currency] = {'IN': [], 'OUT': []}
                files[source_currency]['OUT'].append(neutral_row_out)
                continue
        else:
            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Target amount (after fees)'])
            elif direction == 'IN':
                base_row['*Amount'] = abs(row['Target amount (after fees)'])

        # Ensure currency keys exist in the files dictionary
        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}
        files[currency][direction].append(base_row.copy())

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

Explanation:

Currency Conversion Information: The script appends a description indicating the currency conversion from “Source currency” to “Target currency” when the “Direction” is either “OUT” or “NEUTRAL” and when the currencies differ.
Handling for NEUTRAL Direction: For “NEUTRAL” direction with differing currencies, the script processes both an IN and an OUT entry, adjusting amounts and descriptions appropriately.
General Handling for Direction: The script handles the amounts based on the direction and conditionally adjusts descriptions and currency as required.

This updated function will ensure the description reflects the currency conversion, improving the clarity of transaction entries under specified conditions.

Everything works! Only one thing left make sure that items with Direction NEUTRAL that have Source fee amount >0 and are not empty receive the treatment we had earlier where we duplicate the item and use the Source fee amount instead of Source amount (after fees) or Target amount (after fees) for the finale *Amount and also the currency of this duplicated item is dictated by the Source fee currency. The duplicated item is added only in the corresponding OUT excel file. Use the following code and edit it accordingly, its the final working version

import pandas as pd import os from datetime import datetime import tkinter as tk from tkinter import filedialog, ttk

def get_conversion_rate(date, currency): conversion_rates = { (‘GBP’, ‘2024-03-31’): 1.0, (‘EUR’, ‘2024-03-31’): 1.18, (‘USD’, ‘2024-03-31’): 1.31, (‘CAD’, ‘2024-03-31’): 1.74, } return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df): files = {} # Initialize the files dictionary for index, row in wise_df.iterrows(): # Skip rows where ‘Status’ is ‘CANCELLED’ if row[‘Status’] == ‘CANCELLED’: continue

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row[
        'Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row[
        'Source name']

    # Adjusting amounts based on currency and direction
    if (direction == 'OUT' or direction == 'NEUTRAL') and row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] += f" {row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']
            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)
            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'
        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

def load_and_process_files(wise_report_path, xero_report_path): wise_df = pd.read_excel(wise_report_path, sheet_name=‘Wise Report’) mappings_df = pd.read_excel(wise_report_path, sheet_name=‘mappings’) xero_df = pd.read_excel(xero_report_path)

# Map data and process files
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save files
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

return f"Files have been successfully created in {output_directory}."

def browse_file(path_label): filename = filedialog.askopenfilename(filetypes=[(“Excel files”, “*.xlsx *.xls”)]) path_label.set(filename)

def on_run_pressed(root, wise_report_path, xero_report_path): print(load_and_process_files(wise_report_path, xero_report_path)) root.destroy() # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("600x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons
browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

run_button = ttk.Button(root, text="Run", command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

root.mainloop()

if name == “main”: main()

To accommodate the final request where items with “Direction” NEUTRAL that have “Source fee amount” greater than 0 are duplicated and treated differently, we’ll adjust the script. Specifically, these duplicates will use “Source fee amount” for the “*Amount”, utilize “Source fee currency” as the currency, and add the duplicated entry only to the OUT Excel file.

Here’s the edited portion of your provided script:

# Duplicate handling for source fee
if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
    if direction == 'NEUTRAL':  # Only handle NEUTRAL specially if there's a source fee amount
        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'
        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        # Add only to OUT for NEUTRAL with source fee amount
        files[duplicate_currency]['OUT'].append(duplicate_row)
    else:
        # Handle non-NEUTRAL directions as previously done
        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'
        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

Explanation:

Special Handling for NEUTRAL Direction: When the direction is “NEUTRAL” and there is a “Source fee amount” greater than 0, a duplicate entry is created using “Source fee amount” instead of the usual amounts derived from “Source amount (after fees)” or “Target amount (after fees)”. This duplicate entry is then added only to the corresponding OUT file using “Source fee currency”.
Description Modification: Appends “_BANK_FEES” to the description to indicate the special handling due to the fee.
Currency and File Assignment: Ensures that the duplicate uses the “Source fee currency” and only adds to the OUT directory.

This modification ensures that your script processes NEUTRAL direction entries with fees as specified, creating a clear separation in handling based on your requirements. Make sure to test the script to ensure it handles all cases as expected and integrates seamlessly with your existing code.

To append the string “NEUTRAL” to the “Description” for every item (duplicate or not) originating from the “Direction” NEUTRAL, you can add a line of code specifically in the areas where you handle the NEUTRAL direction. This modification should occur before adding the entries to the respective files.

Here is how you can adjust your script to implement this additional requirement:

# Adjusting amounts based on currency and direction
if (direction == 'OUT' or direction == 'NEUTRAL') and row['Source currency'] != row['Target currency']:
    # Append currency conversion information to the Description
    base_row['Description'] += f" {row['Source currency']} to {row['Target currency']}"

    if direction == 'OUT':
        base_row['*Amount'] = -abs(row['Source amount (after fees)'])
        currency = row['Source currency']
    elif direction == 'NEUTRAL':
        # Append "NEUTRAL" to the description
        base_row['Description'] += " NEUTRAL"
        # For NEUTRAL, handle IN and OUT separately
        neutral_row_in = base_row.copy()
        neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
        files[currency]['IN'].append(neutral_row_in)

        neutral_row_out = base_row.copy()
        neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
        source_currency = row['Source currency']
        if source_currency not in files:
            files[source_currency] = {'IN': [], 'OUT': []}
        files[source_currency]['OUT'].append(neutral_row_out)
        continue
else:
    if direction == 'OUT':
        base_row['*Amount'] = -abs(row['Target amount (after fees)'])
    elif direction == 'IN':
        base_row['*Amount'] = abs(row['Target amount (after fees)'])
    elif direction == 'NEUTRAL':
        # Append "NEUTRAL" to the description for NEUTRAL items
        base_row['Description'] += " NEUTRAL"
        # Handle NEUTRAL as both IN and OUT
        neutral_row_in = base_row.copy()
        neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
        files[currency]['IN'].append(neutral_row_in)

        neutral_row_out = base_row.copy()
        neutral_row_out['*Amount'] = -abs(row['Target amount (after fees)'])
        files[currency]['OUT'].append(neutral_row_out)

# Duplicate handling for source fee, with NEUTRAL append
if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
    duplicate_row = base_row.copy()
    duplicate_currency = row['Source fee currency']
    duplicate_row['*Amount'] = -abs(row['Source fee amount'])
    duplicate_row['Description'] += '_BANK_FEES'
    if direction == 'NEUTRAL':  # Specifically append "NEUTRAL" for duplicates from NEUTRAL direction
        duplicate_row['Description'] += " NEUTRAL"
    if duplicate_currency not in files:
        files[duplicate_currency] = {'IN': [], 'OUT': []}
    files[duplicate_currency]['OUT'].append(duplicate_row)

Key Changes:

Description Update for NEUTRAL Items: The script now appends “NEUTRAL” to the “Description” for every item that has a NEUTRAL direction, ensuring that this tag follows the currency conversion information or any other prior content in the “Description”.
Handling for Duplicates and Regular Entries: This adjustment is applied to both regular entries and duplicates derived from NEUTRAL items, making sure that the NEUTRAL designation is clearly indicated for clarity and tracking purposes.

This modification ensures that all entries from a NEUTRAL direction, whether they are regular entries or duplicates, include the “NEUTRAL” tag in their description, helping to maintain clear and informative data tracking. Make sure to thoroughly test the updated script with various scenarios to confirm that all data is processed and tagged as expected.

Sure! Below is the complete Python script that integrates all your specified functionalities, including handling special cases for items from the wise_reports.xlsx based on their “Direction” and “Currency”, and appending relevant information to the “Description”. This script also incorporates a basic GUI for selecting the input files and running the process.

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def get_conversion_rate(date, currency):
    conversion_rates = {
        ('GBP', '2024-03-31'): 1.0,
        ('EUR', '2024-03-31'): 1.18,
        ('USD', '2024-03-31'): 1.31,
        ('CAD', '2024-03-31'): 1.74,
    }
    return conversion_rates.get((currency, str(date)[:10]), 1.0)

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Status' is 'CANCELLED'
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Adjusting amounts based on currency and direction
        if (direction == 'OUT' or direction == 'NEUTRAL') and row['Source currency'] != row['Target currency']:
            # Append currency conversion information to the Description
            base_row['Description'] += f" {row['Source currency']} to {row['Target currency']}"

            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Source amount (after fees)'])
                currency = row['Source currency']
            elif direction == 'NEUTRAL':
                base_row['Description'] += " NEUTRAL"
                # For NEUTRAL, handle IN and OUT separately
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
                files[currency]['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
                source_currency = row['Source currency']
                if source_currency not in files:
                    files[source_currency] = {'IN': [], 'OUT': []}
                files[source_currency]['OUT'].append(neutral_row_out)
                continue
        else:
            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Target amount (after fees)'])
            elif direction == 'IN':
                base_row['*Amount'] = abs(row['Target amount (after fees)'])
            if direction == 'NEUTRAL':
                base_row['Description'] += " NEUTRAL"
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
                files[currency]['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(row['Target amount (after fees)'])
                files[currency]['OUT'].append(neutral_row_out)

        # Ensure currency keys exist in the files dictionary
        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}
        files[currency][direction].append(base_row.copy())

        # Duplicate handling for source fee, with NEUTRAL append
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if direction == 'NEUTRAL':
                duplicate_row['Description'] += " NEUTRAL"
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name='Wise Report')
    mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
    xero_df = pd.read_excel(xero_report_path)

    # Map data and process files
    mapped_data = pd.DataFrame(columns=xero_df.columns)
    for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
        if xero_col in mapped_data.columns:
            mapped_data[xero_col] = wise_df[wise_col]

    files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

    # Save files
    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}'
    os.makedirs(output_directory, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
                df_to_save.to_excel(file_name, index=False)

    return f"Files have been successfully created in {output_directory}."

def browse_file(path_label):
    filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
    path_label.set(filename)

def on_run_pressed(root, wise_report_path, xero_report_path):
    print(load_and_process_files(wise_report_path, xero_report_path))
    root.destroy()  # Closes the Tkinter window after processing

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")

    # Set the window size and disable resizing
    root.geometry("600x200")  # Set initial size of the window
    root.resizable(False, False)  # Disable resizing

    style = ttk.Style(root)
    style.theme_use('default')  # Modern theme

    # Setup variable to store file paths
    wise_path_label = tk.StringVar(root)
    xero_path_label = tk.StringVar(root)

    # Configure grid to expand buttons
    root.grid_columnconfigure(0, weight=1)
    root.grid_rowconfigure(0, weight=1)
    root.grid_rowconfigure(1, weight=1)
    root.grid_rowconfigure(2, weight=1)

    # Create and place buttons
    browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label))
    browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

    browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label))
    browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    run_button = ttk.Button(root, text="Run", command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
    run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    root.mainloop()

if __name__ == "__main__":
    main()

This full script now incorporates all your required functionalities, with the final changes ensuring that “NEUTRAL” items are handled as specified, with additional description updates to reflect currency conversions and “NEUTRAL” status where applicable. The script includes a simple GUI for user interaction to select files and initiate processing. Make sure to test the complete script in your environment to confirm that everything works as expected.

Let’s correct the issue with handling duplicates for items with “Direction” as “NEUTRAL”. Specifically, we need to ensure that when duplicates are created based on “Source fee amount” and “Source fee currency” for items with “NEUTRAL” direction, these duplicates are correctly added to the OUT files based on the “Source fee currency”. Here’s an adjustment to the script, focusing on this particular section:

# Duplicate handling for source fee
if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
    duplicate_row = base_row.copy()
    duplicate_currency = row['Source fee currency']
    duplicate_row['*Amount'] = -abs(row['Source fee amount'])
    duplicate_row['Description'] += '_BANK_FEES'
    if direction == 'NEUTRAL':
        duplicate_row['Description'] += " NEUTRAL"
    # Make sure the duplicate currency keys exist in the files dictionary
    if duplicate_currency not in files:
        files[duplicate_currency] = {'IN': [], 'OUT': []}
    # Always append to OUT for duplicates, especially for NEUTRAL direction
    files[duplicate_currency]['OUT'].append(duplicate_row)

Explanation:

This snippet checks for a non-null and positive “Source fee amount”.
It creates a duplicate entry, copying the base row and setting the “*Amount” to the negative of the “Source fee amount”, ensuring it represents an outflow.
The “Description” is appended with “_BANK_FEES” to indicate the fee aspect, and if the direction is “NEUTRAL”, it adds ” NEUTRAL” to signify its origin.
It then ensures that the duplicate uses the “Source fee currency”. The script checks if the “Source fee currency” has been initialized in the files dictionary and if not, initializes it.
Importantly, the duplicate entry is always added to the OUT files, adhering to the specific requirement for NEUTRAL direction duplicates.

Make sure this block of code replaces or is correctly integrated with your current handling of duplicates in the prepare_files_with_duplicates function. This adjustment guarantees that duplicates derived from NEUTRAL direction items are appropriately categorized under the OUT files of their respective “Source fee currency”, thereby addressing the issue of these duplicates not appearing as expected.

Here’s how the relevant portion of the prepare_files_with_duplicates function should look in context, ensuring that the entire logic around this handling is consistent:

# Ensure currency keys exist in the files dictionary
if currency not in files:
    files[currency] = {'IN': [], 'OUT': []}
if direction == 'OUT':
    base_row['*Amount'] = -abs(row['Target amount (after fees)'])
    files[currency]['OUT'].append(base_row.copy())
elif direction == 'IN':
    base_row['*Amount'] = abs(row['Target amount (after fees)'])
    files[currency]['IN'].append(base_row.copy())
elif direction == 'NEUTRAL':
    # Handle NEUTRAL as both IN and OUT
    neutral_row_in = base_row.copy()
    neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
    files[currency]['IN'].append(neutral_row_in)

    neutral_row_out = base_row.copy()
    neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
    files[currency]['OUT'].append(neutral_row_out)

Make sure to test this updated script to confirm that it handles all specified scenarios correctly, particularly ensuring that duplicates from NEUTRAL direction items appear in the OUT files as intended.

Here’s the refined prepare_files_with_duplicates function from your Python script, ensuring it correctly processes duplicates, particularly for items with “Direction” NEUTRAL. This function is designed to make sure duplicates using “Source fee amount” and “Source fee currency” are handled appropriately and that they appear in the correct OUT files based on the “Source fee currency”.

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Status' is 'CANCELLED'
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Adjusting amounts and descriptions based on currency and direction
        if (direction == 'OUT' or direction == 'NEUTRAL') and row['Source currency'] != row['Target currency']:
            base_row['Description'] += f" {row['Source currency']} to {row['Target currency']}"
            if direction == 'NEUTRAL':
                base_row['Description'] += " NEUTRAL"

            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Source amount (after fees)'])
                currency = row['Source currency']
            elif direction == 'NEUTRAL':
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
                files[currency]['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
                source_currency = row['Source currency']
                if source_currency not in files:
                    files[source_currency] = {'IN': [], 'OUT': []}
                files[source_currency]['OUT'].append(neutral_row_out)
                continue
        else:
            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Target amount (after fees)'])
            elif direction == 'IN':
                base_row['*Amount'] = abs(row['Target amount (after fees)'])
            if direction == 'NEUTRAL':
                base_row['Description'] += " NEUTRAL"
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
                files[currency]['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(row['Target amount (after fees)'])
                files[currency]['OUT'].append(neutral_row_out)

        # Duplicate handling for source fee, with NEUTRAL append
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_currency = row['Source fee currency']
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            if direction == 'NEUTRAL':
                duplicate_row['Description'] += " NEUTRAL"
            if duplicate_currency not in files:
                files[duplicate_currency] = {'IN': [], 'OUT': []}
            files[duplicate_currency]['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

This function now:

Correctly handles special cases for NEUTRAL direction, including the separate processing for IN and OUT files with appropriate currency and amount adjustments.
Includes currency conversion information in the description for transactions where “Source currency” and “Target currency” differ.
Ensures that duplicates for NEUTRAL direction items are only added to the OUT files of their “Source fee currency”, with the description appended with “_BANK_FEES” and “NEUTRAL” when applicable.

This updated function should now comprehensively handle all the cases as described in your requirements. Make sure to integrate this function into your existing script and test thoroughly to ensure it performs as expected.

if the user does not browse for files and presses the run, just use the defaults. in the same dir as the py script there will be a wise_report.xlsx and a xero_report.xlsx file. you need to chenge this section probably?

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("600x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons
browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

run_button = ttk.Button(root, text="Run", command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

root.mainloop()

To ensure the application uses default file paths if the user does not browse for files, you can modify the main() function and the on_run_pressed() function to check if the file paths have been set. If not, it should default to the files expected to be in the same directory as the Python script. Here’s how you can implement these changes:

Step 1: Import the `os` module

First, import the os module at the beginning of your script if it’s not already imported, to use it for building the default file paths.

Step 2: Modify the `on_run_pressed()` function

Adjust the function to check if the file paths are empty and set them to the defaults if necessary.

Updated Script Sections:

import os  # Make sure this is included at the top

def on_run_pressed(root, wise_report_path, xero_report_path):
    # Check if paths are empty and set default paths
    if not wise_report_path:
        wise_report_path = os.path.join(os.getcwd(), 'wise_report.xlsx')
    if not xero_report_path:
        xero_report_path = os.path.join(os.getcwd(), 'xero_report.xlsx')

    result = load_and_process_files(wise_report_path, xero_report_path)
    print(result)
    root.destroy()  # Closes the Tkinter window after processing

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")

    # Set the window size and disable resizing
    root.geometry("600x200")  # Set initial size of the window
    root.resizable(False, False)  # Disable resizing

    style = ttk.Style(root)
    style.theme_use('default')  # Modern theme

    # Setup variable to store file paths
    wise_path_label = tk.StringVar(root)
    xero_path_label = tk.StringVar(root)

    # Configure grid to expand buttons
    root.grid_columnconfigure(0, weight=1)
    root.grid_rowconfigure(0, weight=1)
    root.grid_rowconfigure(1, weight=1)
    root.grid_rowconfigure(2, weight=1)

    # Create and place buttons
    browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label))
    browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

    browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label))
    browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    run_button = ttk.Button(root, text="Run", command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
    run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    root.mainloop()

if __name__ == "__main__":
    main()

Explanation:

Default Paths: The script now sets default paths to wise_report.xlsx and xero_report.xlsx located in the same directory as the script if the user has not selected any files.
Path Check: Before processing, it checks if the paths have been set (non-empty). If they are empty, it defaults to the specified files in the script’s directory.
Improved Usability: The user is no longer forced to select files if they are content with using the default expected files.

This modification ensures that the script is more user-friendly and robust, capable of running with minimal user interaction when default conditions are met. Make sure to test the script in the environment where the files are expected to ensure smooth operation.

this is my final working code

import pandas as pd import os from datetime import datetime import tkinter as tk from tkinter import filedialog, ttk

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)
            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

# Map data and process files
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save files
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

return f"Files have been successfully created in {output_directory}."

def browse_file(path_label): filename = filedialog.askopenfilename(filetypes=[(“Excel files”, “*.xlsx *.xls”)]) path_label.set(filename)

def on_run_pressed(root, wise_report_path, xero_report_path): # Check if paths are empty and set default paths if not wise_report_path: wise_report_path = os.path.join(os.getcwd(), ‘wise_report.xlsx’) if not xero_report_path: xero_report_path = os.path.join(os.getcwd(), ‘xero_report.xlsx’)

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("600x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons
browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

run_button = ttk.Button(root, text="Run", command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

root.mainloop()

if name == “main”: main()

but still the duplicates we create in this if statement:

Duplicate handling for source fee

    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:

if they are NEUTRAL Direction while they show up in the print function they do not get appended to the corresponding OUT excel file . why is this happening?

Given the behavior you’re describing, where duplicates created for “Direction” NEUTRAL and having a “Source fee amount” greater than 0 are not appearing in the corresponding OUT Excel files despite being printed as processed, we need to examine how these duplicates are handled and added to the files dictionary.

Possible Issue

One potential issue could be related to how and when these entries are added to your files dictionary, particularly for NEUTRAL direction. If there’s a specific condition under which they are either not added to the dictionary or overwritten, they might not be saved correctly.

Troubleshooting Approach

Ensure that the currency and file type directories ('IN' or 'OUT') are initialized correctly before attempting to append entries.
Confirm that you’re not overwriting entries by mistake, particularly when handling NEUTRAL direction entries.
Check the logic that appends these entries to ensure it correctly addresses the files dictionary structure.

Updated Code

Let’s review and refine the part of your script that handles duplicates for “Direction” NEUTRAL, ensuring it correctly appends duplicates to the OUT files:

# Duplicate handling for source fee
if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0:
    duplicate_row = base_row.copy()
    duplicate_currency = row['Source fee currency']
    duplicate_row['*Amount'] = -abs(row['Source fee amount'])
    duplicate_row['Description'] += '_BANK_FEES'
    if direction == 'NEUTRAL':
        duplicate_row['Description'] += " NEUTRAL"

    # Initialize currency key if not present
    if duplicate_currency not in files:
        files[duplicate_currency] = {'IN': [], 'OUT': []}

    # Append to OUT only for duplicates, ensuring NEUTRAL duplicates are handled
    files[duplicate_currency]['OUT'].append(duplicate_row)

Key Points

Initialization: Ensure that each possible currency is initialized in the files dictionary before trying to append to it. This prevents any KeyError from happening when attempting to append to a non-initialized list.
Appending to ‘OUT’: As intended, duplicates, especially those with NEUTRAL direction, should only be appended to the ‘OUT’ list of their respective currency. This code makes sure of that.
Description Addition: The description modification for NEUTRAL duplicates is added after copying the base row to avoid modifying the original row inadvertently.

Final Thoughts

Ensure all parts of your code align with this logic. Double-check the logic flow and ensure that no other parts of your code are inadvertently removing these entries or preventing them from being added. Also, confirm that the final saving logic iterates correctly through each currency and direction in the files dictionary to create the Excel files.

If the issue persists even after implementing these checks, consider adding more print statements around the appending operation to confirm that the function attempts to append to the correct part of the dictionary. This debugging step will help isolate whether the issue is with the dictionary handling or earlier in the data handling logic.

this is the final working code :

import pandas as pd import os from datetime import datetime import tkinter as tk from tkinter import filedialog, ttk def prepare_files_with_duplicates(df, wise_df): files = {} # Initialize the files dictionary for index, row in wise_df.iterrows(): # Skip rows where ‘Status’ is ‘CANCELLED’ if row[‘Status’] == ‘CANCELLED’: continue

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            neutral_row_fee_out = base_row.copy()
            neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
            neutral_row_fee_out['Description'] += '_BANK_FEES'
            source_fee_currency = row['Source fee currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)

            if source_fee_currency not in files:
                files[source_fee_currency] = {'IN': [], 'OUT': []}
            files[source_fee_currency]['OUT'].append(neutral_row_fee_out)

            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

# Map data and process files
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save files
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

return f"Files have been successfully created in {output_directory}."

def browse_file(path_label): filename = filedialog.askopenfilename(filetypes=[(“Excel files”, “*.xlsx *.xls”)]) path_label.set(filename)

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("600x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons
browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

run_button = ttk.Button(root, text="Run", command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

root.mainloop()

if name == “main”: main()

i want to change the UI so that if the user has not used any of the 2 file browsers therefore hasnt selected any excel files then the run button should be greyed out and inactive. If you havent selected files the script cannot run

To disable the “Run” button in your GUI until both files have been selected, you can update the button’s state based on whether file paths are set in the corresponding StringVar objects for the file paths. You’ll need to add a function to check the state of these variables and update the button accordingly.

Here’s how you can modify your existing code to include this functionality:

Add a Function to Update Button State: This function checks whether both file paths are set and updates the “Run” button state accordingly.
Bind this Function to Changes in the StringVar Objects: Whenever the file path variables change, this function will be called.

Updated Script with Disabled Run Button Until Files are Selected:

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def update_run_button_state(run_button, wise_path, xero_path):
    """Enable or disable the run button based on whether file paths are set."""
    if wise_path.get() and xero_path.get():
        run_button['state'] = 'normal'  # Enable the button if both paths are set
    else:
        run_button['state'] = 'disabled'  # Disable the button if any path is not set

def browse_file(path_label, run_button, wise_path_label, xero_path_label):
    """Open a file dialog to set a file path and update the run button state."""
    filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
    if filename:
        path_label.set(filename)
        update_run_button_state(run_button, wise_path_label, xero_path_label)

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")

    # Set the window size and disable resizing
    root.geometry("600x200")  # Set initial size of the window
    root.resizable(False, False)  # Disable resizing

    style = ttk.Style(root)
    style.theme_use('default')  # Modern theme

    # Setup variable to store file paths
    wise_path_label = tk.StringVar(root)
    xero_path_label = tk.StringVar(root)

    # Configure grid to expand buttons
    root.grid_columnconfigure(0, weight=1)
    root.grid_rowconfigure(0, weight=1)
    root.grid_rowconfigure(1, weight=1)
    root.grid_rowconfigure(2, weight=1)

    # Create and place buttons for file selection
    browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label))
    browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

    browse_xero_button = ttk.Button(root, text="Select Xero Report", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label))
    browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    # Create and place the Run button, initially disabled
    run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
    run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    # Update the run button state whenever file paths change
    wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))
    xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))

    root.mainloop()

if __name__ == "__main__":
    main()

Key Changes:

File Browsing Function Update: The browse_file function now also receives the run_button and path labels as arguments to check and update the button’s state after a file is selected.
Trace on Path Variables: The StringVar objects for the file paths now have a trace set on them. This trace calls update_run_button_state whenever the file path variables change, enabling or disabling the “Run” button as appropriate.
Initial Button State: The “Run” button starts as disabled and only becomes enabled once both file paths are set.

This modification ensures that your application is user-friendly and prevents running the processing without necessary inputs, enhancing usability and robustness.

change the code so that we have 2 excel files as input one will be the mappings and the other the wise report. so we dont need the second sheet in wise report and also we dont need the xero report because we can use the mappings straight from the new excel file. Also dont select a sheet by name use the sheet id . change the ui accordingly so that there are 2 buttons one for wise report and one for mapping and a 3rd button to run as it is now. This is the final code to modify :

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            neutral_row_fee_out = base_row.copy()
            neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
            neutral_row_fee_out['Description'] += '_BANK_FEES'
            source_fee_currency = row['Source fee currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)

            if source_fee_currency not in files:
                files[source_fee_currency] = {'IN': [], 'OUT': []}
            files[source_fee_currency]['OUT'].append(neutral_row_fee_out)

            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

# Map data and process files
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save files
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

return f"Files have been successfully created in {output_directory}."

def update_run_button_state(run_button, wise_path, xero_path): """Enable or disable the run button based on whether file paths are set.""" if wise_path.get() and xero_path.get(): run_button[‘state’] = ‘normal’ # Enable the button if both paths are set else: run_button[‘state’] = ‘disabled’ # Disable the button if any path is not set

def browse_file(path_label, run_button, wise_path_label, xero_path_label): """Open a file dialog to set a file path and update the run button state.""" filename = filedialog.askopenfilename(filetypes=[(“Excel files”, “*.xlsx *.xls”)]) if filename: path_label.set(filename) update_run_button_state(run_button, wise_path_label, xero_path_label) def on_run_pressed(root, wise_report_path, xero_report_path):

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("300x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons for file selection
browse_wise_button = ttk.Button(root, text="Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Xero Template", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

# Create and place the Run button, initially disabled
run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

# Update the run button state whenever file paths change
wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))
xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))

root.mainloop()

if name == “main”: main()

Here’s your modified script to handle two separate Excel files—one for the Wise report and the other for mappings. The UI has been updated to include two file selection buttons and a run button that activates only when both files have been selected. This script simplifies the handling by separating the functionality clearly between selecting the files and processing them.

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def prepare_files_with_duplicates(mappings_df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Status' is 'CANCELLED'
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = {mappings_df.iloc[i, 1]: row[mappings_df.iloc[i, 0]] for i in range(len(mappings_df))}

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(base_row['Description']) or base_row['Description'].strip() == "" else base_row['Description']
        base_row['Payee'] = "none" if pd.isna(base_row['Payee']) or base_row['Payee'].strip() == "" else base_row['Payee']

        # Adjusting amounts based on currency and direction
        if base_row['Source currency'] != base_row['Target currency']:
            # Append currency conversion information to the Description
            base_row['Description'] = f"{base_row['Direction']}_{base_row['Description']}_{base_row['Source currency']} to {base_row['Target currency']}"

            if direction == 'OUT':
                base_row['*Amount'] = -abs(base_row['Source amount (after fees)'])
                currency = base_row['Source currency']
            elif direction == 'NEUTRAL':
                # For NEUTRAL, handle IN and OUT separately
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(base_row['Target amount (after fees)'])
                files.setdefault(currency, {'IN': [], 'OUT': []})['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(base_row['Source amount (after fees)'])
                source_currency = base_row['Source currency']
                files.setdefault(source_currency, {'IN': [], 'OUT': []})['OUT'].append(neutral_row_out)
                continue
        else:
            base_row['*Amount'] = -abs(base_row['Target amount (after fees)']) if direction == 'OUT' else abs(base_row['Target amount (after fees)'])
            files.setdefault(currency, {'IN': [], 'OUT': []})[direction].append(base_row)

        # Duplicate handling for source fee
        if pd.notna(base_row['Source fee amount']) and base_row['Source fee amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_row['*Amount'] = -abs(base_row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            files.setdefault(base_row['Source fee currency'], {'IN': [], 'OUT': []})['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

def load_and_process_files(mappings_path, wise_report_path):
    mappings_df = pd.read_excel(mappings_path, sheet_name=0)
    wise_df = pd.read_excel(wise_report_path, sheet_name=0)

    files_dict = prepare_files_with_duplicates(mappings_df, wise_df)

    # Save files
    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}'
    os.makedirs(output_directory, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
                df_to_save.to_excel(file_name, index=False)

    return f"Files have been successfully created in {output_directory}."

def browse_file(path_label, run_button, *args):
    filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
    if filename:
        path_label.set(filename)
        # Check if both files are selected
        if all(arg.get() for arg in args):
            run_button['state'] = 'normal'
        else:
            run_button['state'] = 'disabled'

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")
    root.geometry("300x200")  # Set initial size of the window
    root.resizable(False, False)  # Disable resizing

    # Variables to store file paths
    mappings_path_label = tk.StringVar(root)
    wise_path_label = tk.StringVar(root)

    # Configure grid to expand buttons
    root.grid_columnconfigure(0, weight=1)
    root.grid_rowconfigure(0, weight=1)
    root.grid_rowconfigure(1, weight=1)
    root.grid_rowconfigure(2, weight=1)

    # Create and place buttons for file selection
    browse_mappings_button = ttk.Button(root, text="Select Mappings", command=lambda: browse_file(mappings_path_label, run_button, mappings_path_label, wise_path_label))
    browse_mappings_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)
    browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label, run_button, mappings_path_label, wise_path_label))
    browse_wise_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    # Run button, initially disabled
    run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, mappings_path_label.get(), wise_path_label.get()))
    run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    root.mainloop()

if __name__ == "__main__":
    main()

This version of your script:

Uses two separate files for mappings and Wise reports, removing the need for multiple sheets or an extra Xero report.
Initializes the ‘OUT’ and ‘IN’ dictionaries only when necessary.
Has a user interface that only allows the script to run when both Excel files have been selected, ensuring all necessary data is available before processing starts.

The NameError you’re encountering indicates that the on_run_pressed function was mentioned in the code but wasn’t actually defined. This function is supposed to handle the event when the “Run” button is pressed, orchestrating the file loading and processing steps. We’ll need to define this function to fix the error.

Here’s the appropriate definition for the on_run_pressed function, which you can add to your script to handle the file processing when the “Run” button is clicked:

def on_run_pressed(root, mappings_path, wise_report_path):
    if mappings_path and wise_report_path:
        # Process files if paths are provided
        result = load_and_process_files(mappings_path, wise_report_path)
        print(result)
    else:
        # Handle the case where one or both paths are missing
        print("Please select both the mappings file and the Wise report before running.")
    root.destroy()  # Optionally close the GUI after processing

This function:

Takes the root widget, mappings_path, and wise_report_path as parameters.
Checks if both file paths are provided.
Calls load_and_process_files to process the data.
Prints results or prompts for file selection based on file path validity.
Optionally closes the GUI window after processing, which you can omit if you want the window to stay open after running.

Let’s integrate this function into the complete main function and ensure the entire script is coherent:

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")
    root.geometry("300x200")  # Set initial size of the window
    root.resizable(False, False)  # Disable resizing

    # Variables to store file paths
    mappings_path_label = tk.StringVar(root)
    wise_path_label = tk.StringVar(root)

    # Create and place buttons for file selection
    browse_mappings_button = ttk.Button(root, text="Select Mappings", command=lambda: browse_file(mappings_path_label, run_button, mappings_path_label, wise_path_label))
    browse_mappings_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

    browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label, run_button, mappings_path_label, wise_path_label))
    browse_wise_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    # Run button, initially disabled
    run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, mappings_path_label.get(), wise_path_label.get()))
    run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    root.mainloop()

if __name__ == "__main__":
    main()

Make sure all referenced functions are defined, and file paths are correctly handled. This setup ensures that the GUI behaves as expected, enabling the “Run” button only when both files are selected and performing the data processing appropriately.

Exception in Tkinter callback Traceback (most recent call last): File “C:\Users\User_\AppData\Local\Programs\Python\Python39\lib\tkinter_init_.py”, line 1892, in call return self.func(*args) File “F:_Github_ULC\automation\src\wise_to_xero\main2.py”, line 111, in run_button = ttk.Button(root, text=“Run”, state=‘disabled’, command=lambda: on_run_pressed(root, mappings_path_label.get(), wise_path_label.get())) File “F:_Github_ULC\automation\src\wise_to_xero\main2.py”, line 57, in on_run_pressed result = load_and_process_files(mappings_path, wise_report_path) File “F:_Github_ULC\automation\src\wise_to_xero\main2.py”, line 67, in load_and_process_files files_dict = prepare_files_with_duplicates(mappings_df, wise_df) File “F:_Github_ULC\automation\src\wise_to_xero\main2.py”, line 23, in prepare_files_with_duplicates if base_row[‘Source currency’] != base_row[‘Target currency’]: KeyError: ‘Source currency’

The KeyError: 'Source currency' indicates that the key 'Source currency' is not present in the base_row dictionary, which is constructed from your DataFrame. This could be happening because the column names expected in the wise_df DataFrame do not match those being used in your script or the mappings are incorrect.

Here’s how you can troubleshoot and fix this issue:

Step 1: Verify Column Names in DataFrames

Ensure that the columns 'Source currency' and 'Target currency' actually exist in the wise_df DataFrame as they are named. You can add a print statement or inspect the DataFrame to verify this:

print(wise_df.columns)  # This will print all column names in the wise_df DataFrame

Step 2: Adjust Mappings DataFrame Usage

Your script attempts to use a mappings_df to map column names from the Wise report to some expected column names. Make sure that the mappings are being correctly applied. If the mappings are incorrect or if they’re not being applied as intended, it would cause the KeyError.

Step 3: Modify `prepare_files_with_duplicates` Function

Modify your prepare_files_with_duplicates function to ensure that the mappings are applied correctly and that all keys are verified before usage. Here’s a potential way to adjust your function:

def prepare_files_with_duplicates(mappings_df, wise_df):
    files = {}
    mappings = dict(zip(mappings_df['wise report'], mappings_df['xero report']))  # Adjust column mapping as needed

    for index, row in wise_df.iterrows():
        if row['Status'] == 'CANCELLED':
            continue

        # Map and validate row data based on mappings
        base_row = {}
        for wise_col, xero_col in mappings.items():
            base_row[xero_col] = row.get(wise_col, None)  # Safely get value or None if not present

        # Check if required keys are in base_row
        if 'Source currency' not in base_row or 'Target currency' not in base_row:
            print(f"Missing currency columns in row {index}")
            continue

        # Use base_row for further processing...
        # Your existing logic follows here...

Step 4: Correct Function Calls

Ensure your functions are called correctly with the correct parameters and the mappings and wise data are loaded properly:

def load_and_process_files(mappings_path, wise_report_path):
    mappings_df = pd.read_excel(mappings_path, sheet_name=0)  # Load the first sheet for mappings
    wise_df = pd.read_excel(wise_report_path, sheet_name=0)  # Load the first sheet for wise report

    files_dict = prepare_files_with_duplicates(mappings_df, wise_df)

    # Further processing and saving logic follows...

This adjustment ensures that your script is robust against missing columns and incorrect data. It also allows for better debugging by clearly identifying missing columns when processing rows. Make sure that your Excel files are formatted as expected, and that the mapping DataFrame corresponds accurately to the column names in your Wise report.

Below is the revised and complete code, which includes modifications to handle two separate Excel files for the Wise report and mappings. This setup ensures you have a robust interface that only activates processing when both files are loaded. This script also handles possible discrepancies in column names and ensures that the process runs smoothly once both files are selected.

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def prepare_files_with_duplicates(mappings_df, wise_df):
    files = {}  # Initialize the files dictionary
    # Create a dictionary from mappings DataFrame
    mappings = {row['Wise Column']: row['Mapped Column'] for index, row in mappings_df.iterrows()}

    for index, row in wise_df.iterrows():
        if row.get('Status', '') == 'CANCELLED':
            continue

        # Apply mappings to the row data
        base_row = {mapped_col: row[wise_col] if wise_col in row else None for wise_col, mapped_col in mappings.items()}

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(base_row.get('Description', '')) or base_row['Description'].strip() == "" else base_row['Description']
        base_row['Payee'] = "none" if pd.isna(base_row.get('Payee', '')) or base_row['Payee'].strip() == "" else base_row['Payee']

        # Adjusting amounts based on currency and direction
        if base_row.get('Source Currency') != base_row.get('Target Currency'):
            base_row['Description'] += f" {base_row.get('Direction')}_{base_row['Source Currency']} to {base_row['Target Currency']}"
            if base_row.get('Direction') == 'OUT':
                base_row['*Amount'] = -abs(base_row.get('Source Amount (after fees)', 0))
            elif base_row.get('Direction') == 'NEUTRAL':
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(base_row.get('Target Amount (after fees)', 0))
                files.setdefault(base_row.get('Target Currency'), {'IN': [], 'OUT': []})['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(base_row.get('Source Amount (after fees)', 0))
                files.setdefault(base_row.get('Source Currency'), {'IN': [], 'OUT': []})['OUT'].append(neutral_row_out)
        else:
            amount = abs(base_row.get('Target Amount (after fees)', 0))
            base_row['*Amount'] = -amount if base_row.get('Direction') == 'OUT' else amount
            files.setdefault(base_row.get('Target Currency'), {'IN': [], 'OUT': []})[base_row.get('Direction')].append(base_row)

        # Duplicate handling for source fee
        if pd.notna(base_row.get('Source Fee Amount')) and base_row['Source Fee Amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_row['*Amount'] = -abs(base_row['Source Fee Amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            files.setdefault(base_row.get('Source Fee Currency'), {'IN': [], 'OUT': []})['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

def load_and_process_files(mappings_path, wise_report_path):
    mappings_df = pd.read_excel(mappings_path, sheet_name=0)
    wise_df = pd.read_excel(wise_report_path, sheet_name=0)

    files_dict = prepare_files_with_duplicates(mappings_df, wise_df)

    # Save files
    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}'
    os.makedirs(output_directory, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
                df_to_save.to_excel(file_name, index=False)

    return f"Files have been successfully created in {output_directory}."

def browse_file(path_label, run_button, *args):
    filename = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
    path_label.set(filename)
    # Check if both files are selected
    if all(arg.get() for arg in args):
        run_button['state'] = 'normal'
    else:
        run_button['state'] = 'disabled'

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")
    root.geometry("300x200")
    root.resizable(False, False)

    mappings_path_label = tk.StringVar(root)
    wise_path_label = tk.StringVar(root)

    browse_mappings_button = ttk.Button(root, text="Select Mappings", command=lambda: browse_file(mappings_path_label, run_button, mappings_path_label, wise_path_label))
    browse_mappings_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

    browse_wise_button = ttk.Button(root, text="Select Wise Report", command=lambda: browse_file(wise_path_label, run_button, mappings_path_label, wise_path_label))
    browse_wise_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: load_and_process_files(mappings_path_label.get(), wise_path_label.get()))
    run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    root.mainloop()

if __name__ == "__main__":
    main()

This script ensures that both the mappings file and the Wise report are loaded correctly before processing starts. It also includes a responsive UI that only enables processing once all necessary files are selected, and ensures that errors related to missing columns or keys are avoided by using a dictionary comprehension to map and verify columns based on the mappings DataFrame.

To adjust the mapping setup in your code to use positional indexing of columns instead of named indexing, you can modify the mappings dictionary creation to fetch the first column for the Wise report and the second column for the mappings directly by their positions.

Here’s how you can adjust the prepare_files_with_duplicates function to use positional column indexing from the mappings DataFrame:

def prepare_files_with_duplicates(mappings_df, wise_df):
    files = {}  # Initialize the files dictionary
    # Create a dictionary from mappings DataFrame using positional column access
    mappings = {mappings_df.iloc[i, 0]: mappings_df.iloc[i, 1] for i in range(len(mappings_df))}

    for index, row in wise_df.iterrows():
        if row.get('Status', '') == 'CANCELLED':
            continue

        # Apply mappings to the row data
        base_row = {mapped_col: row[wise_col] if wise_col in row else None for wise_col, mapped_col in mappings.items()}

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(base_row.get('Description', '')) or base_row['Description'].strip() == "" else base_row['Description']
        base_row['Payee'] = "none" if pd.isna(base_row.get('Payee', '')) or base_row['Payee'].strip() == "" else base_row['Payee']

        # Adjusting amounts based on currency and direction
        if base_row.get('Source Currency') != base_row.get('Target Currency'):
            base_row['Description'] += f" {base_row.get('Direction')}_{base_row['Source Currency']} to {base_row['Target Currency']}"
            if base_row.get('Direction') == 'OUT':
                base_row['*Amount'] = -abs(base_row.get('Source Amount (after fees)', 0))
            elif base_row.get('Direction') == 'NEUTRAL':
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(base_row.get('Target Amount (after fees)', 0))
                files.setdefault(base_row.get('Target Currency'), {'IN': [], 'OUT': []})['IN'].append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(base_row.get('Source Amount (after fees)', 0))
                files.setdefault(base_row.get('Source Currency'), {'IN': [], 'OUT': []})['OUT'].append(neutral_row_out)
        else:
            amount = abs(base_row.get('Target Amount (after fees)', 0))
            base_row['*Amount'] = -amount if base_row.get('Direction') == 'OUT' else amount
            files.setdefault(base_row.get('Target Currency'), {'IN': [], 'OUT': []})[base_row.get('Direction')].append(base_row)

        # Duplicate handling for source fee
        if pd.notna(base_row.get('Source Fee Amount')) and base_row['Source Fee Amount'] > 0:
            duplicate_row = base_row.copy()
            duplicate_row['*Amount'] = -abs(base_row['Source Fee Amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            files.setdefault(base_row.get('Source Fee Currency'), {'IN': [], 'OUT': []})['OUT'].append(duplicate_row)

    return files  # Make sure to return files from the function

This modification in your script will fetch column names directly by index, making it less error-prone if the column names change but their positions remain consistent. This assumes the first column in the mappings DataFrame corresponds to the Wise report columns and the second column to the mapped target columns. Ensure that the mappings DataFrame is formatted correctly with this expectation.

i removed the headers from the mappings sheet so there are no headers like wise report and xero report can you modify this part of the code to accommodate for this?

Map data and process files

mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

and is part of this code which works :

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            neutral_row_fee_out = base_row.copy()
            neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
            neutral_row_fee_out['Description'] += '_BANK_FEES'
            source_fee_currency = row['Source fee currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)

            if source_fee_currency not in files:
                files[source_fee_currency] = {'IN': [], 'OUT': []}
            files[source_fee_currency]['OUT'].append(neutral_row_fee_out)

            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

def load_and_process_files(wise_report_path, xero_report_path): wise_df = pd.read_excel(wise_report_path, sheet_name=‘Sheet1’) mappings_df = pd.read_excel(wise_report_path, sheet_name=‘mappings’) xero_df = pd.read_excel(xero_report_path)

# Map data and process files
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['wise report'], mappings_df['xero report'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save files
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

return f"Files have been successfully created in {output_directory}."

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("300x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons for file selection
browse_wise_button = ttk.Button(root, text="Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Xero Template", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

# Create and place the Run button, initially disabled
run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

# Update the run button state whenever file paths change
wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))
xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))

root.mainloop()

if name == “main”: main()

To accommodate the changes you requested, where the mappings no longer have headers and the second sheet is removed from the Wise report, here’s the updated Python script:

This update also changes the UI to have two buttons for selecting the Wise report and the mapping file, and adjusts the data processing to use positional indexing for the mapping Excel file.

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def prepare_files_with_duplicates(mappings_df, wise_df):
    files = {}
    # Create a dictionary from mappings DataFrame using positional column access
    mappings = {mappings_df.iloc[i, 0]: mappings_df

This is my final working code :

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            neutral_row_fee_out = base_row.copy()
            neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
            neutral_row_fee_out['Description'] += '_BANK_FEES'
            source_fee_currency = row['Source fee currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)

            if source_fee_currency not in files:
                files[source_fee_currency] = {'IN': [], 'OUT': []}
            files[source_fee_currency]['OUT'].append(neutral_row_fee_out)

            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

# Map data and process files
mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Save files
current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
output_directory = f'wise_transactions_{current_time}'
os.makedirs(output_directory, exist_ok=True)

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

return f"Files have been successfully created in {output_directory}."

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("300x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons for file selection
browse_wise_button = ttk.Button(root, text="Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Xero Template", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

# Create and place the Run button, initially disabled
run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

# Update the run button state whenever file paths change
wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))
xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))

root.mainloop()

if name == “main”: main()

can you make sure the *Date column in the final excel files contains the date formatted in year/month/day ? and do not include the time information. do not alter the rest of the code in any way

To ensure that the *Date column in the final Excel files contains dates formatted as year/month/day without including time information, you’ll need to modify how the date is processed and formatted during the DataFrame handling before saving to Excel.

Here is how you can integrate this date formatting requirement into your existing code:

Identify where the *Date column is being used.
Convert the date column to the desired format during the processing phase.

Here’s the specific adjustment to be made to the prepare_files_with_duplicates function (or wherever you manipulate or finalize your DataFrame before saving):

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        # Skip rows where 'Status' is 'CANCELLED'
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Adjust the *Date field if it exists in the DataFrame
        if '*Date' in base_row:
            # Format the date as year/month/day without time information
            base_row['*Date'] = pd.to_datetime(base_row['*Date']).strftime('%Y/%m/%d')

        # Additional processing (existing code remains unchanged)
        # ...

    return files  # Make sure to return files from the function

# Ensure to include this datetime handling where you process the DataFrame to ensure all instances of *Date across various possible DataFrames are formatted.

This code snippet takes the *Date column, converts it to a datetime object (if it isn’t one already), and then reformats it to the string format ‘year/month/day’ ('%Y/%m/%d'). This ensures that when you save the DataFrame to an Excel file, the *Date column will only display the date in the specified format without the time.

Make sure to implement this change wherever the *Date column is managed or before saving the DataFrame to ensure consistency across all outputs. Here’s a quick addition to ensure the final saving respects this formatting:

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            # Ensure *Date is formatted correctly before saving
            if '*Date' in df_to_save.columns:
                df_to_save['*Date'] = pd.to_datetime(df_to_save['*Date']).dt.strftime('%Y/%m/%d')
            file_name = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
            df_to_save.to_excel(file_name, index=False)

This change should be integrated into your script where the final DataFrame is being prepared for saving to Excel. By adjusting the date formatting at this point, you ensure that all saved files reflect your formatting requirements.

To add functionality to your Python script that exports the data to CSV files in addition to the Excel files, you can create a new function or modify the existing code where you handle file saving. This function will save the DataFrames to CSV files with the same naming convention used for the Excel files.

Here’s how to add this functionality to your script without altering the rest of your existing code:

Step 1: Define a Function to Save CSV Files

You can define a function called save_to_csv, which takes the DataFrame, the output directory, and the filename details as parameters:

def save_to_csv(df, output_directory, file_name):
    csv_path = os.path.join(output_directory, f'{file_name}.csv')
    df.to_csv(csv_path, index=False)

Step 2: Integrate CSV Saving with Existing Excel Saving

Incorporate the call to save_to_csv in the part of your script where you are currently saving Excel files:

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            # Ensure *Date is formatted correctly before saving, if necessary
            if '*Date' in df_to_save.columns:
                df_to_save['*Date'] = pd.to_datetime(df_to_save['*Date']).dt.strftime('%Y/%m/%d')
            file_name = f'{currency}_{direction}'
            excel_file_path = os.path.join(output_directory, f'{file_name}.xlsx')
            df_to_save.to_excel(excel_file_path, index=False)
            # Call to save CSV files
            save_to_csv(df_to_save, output_directory, file_name)

Complete Script with CSV Export Added

Below is your script with the CSV export functionality added, seamlessly integrated with the existing code to export both Excel and CSV files:

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Format the date for consistency across formats, if necessary
        if '*Date' in base_row:
            base_row['*Date'] = pd.to_datetime(base_row['*Date']).strftime('%Y/%m/%d')

        # Rest of your existing logic
        # ...

        # Ensure currency keys exist in the files dictionary
        if currency not in files:
            files[currency] = {'IN': [], 'OUT': []}
        files[currency][direction].append(base_row.copy())

        # Handling source fee as before
        # ...

    return files

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name='Sheet1')
    mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
    xero_df = pd.read_excel(xero_report_path)

    # Map data and process files using the existing mappings
    mapped_data = pd.DataFrame(columns=xero_df.columns)
    for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
        if xero_col in mapped_data.columns:
            mapped_data[xero_col] = wise_df[wise_col]

    files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

    # Save files to both Excel and CSV formats
    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}'
    os.makedirs(output_directory, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_name = f'{currency}_{direction}'
                df_to_save.to_excel(os.path.join(output_directory, f'{file_name}.xlsx'), index=False)
                df_to_save.to_csv(os.path.join(output_directory, f'{file_name}.csv'), index=False)

    return f"Files have been successfully created in {output_directory}."

# Your existing Tkinter GUI setup and functions
# ...

if __name__ == "__main__":
    main()

This script will now save both .xlsx and .csv files in the same directory for each data set processed, making your data easily accessible in either format without needing significant changes to your existing code structure.

To store the CSV files in a subfolder named CSV within your main output directory, you will need to adjust the path where the CSV files are saved. Here’s how you can modify the load_and_process_files function to ensure CSV files are placed in a designated subfolder:

Step 1: Modify File Saving Logic

Update the file saving logic in the load_and_process_files function to create a subfolder and use it for saving CSV files:

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name='Sheet1')
    mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')
    xero_df = pd.read_excel(xero_report_path)

    mapped_data = pd.DataFrame(columns=xero_df.columns)
    for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
        if xero_col in mapped_data.columns:
            mapped_data[xero_col] = wise_df[wise_col]

    files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}'
    os.makedirs(output_directory, exist_ok=True)

    csv_subfolder = os.path.join(output_directory, 'CSV')  # Define the subfolder path for CSV files
    os.makedirs(csv_subfolder, exist_ok=True)  # Create the subfolder

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_name = f'{currency}_{direction}'
                excel_path = os.path.join(output_directory, f'{file_name}.xlsx')
                csv_path = os.path.join(csv_subfolder, f'{file_name}.csv')  # Use the CSV subfolder path for CSV files

                df_to_save.to_excel(excel_path, index=False)
                df_to_save.to_csv(csv_path, index=False)  # Save to CSV in the subfolder

    return f"Files have been successfully created in {output_directory}."

Explanation

CSV Subfolder Creation: The script creates a subfolder named CSV within the main output directory. This is done using os.makedirs(), which ensures that the directory is created if it doesn’t exist.
File Paths: Adjusts the path for saving CSV files to use this subfolder.

This modification ensures that while Excel files are saved directly in the main output directory, all CSV files are neatly organized into a CSV subfolder, keeping the output structured and clean.

This approach keeps your data organized and makes it easier to navigate through different types of output files while maintaining a clear separation between Excel and CSV formats. This is particularly useful if you are generating a large number of files or if you need to share or process these files separately.

To append a date range derived from the “Finished on” column in your Wise Excel file to the output directory name, you will need to:

Extract the earliest and latest dates from the “Finished on” column of the Wise report.
Format these dates into a specific string format.
Append this formatted date range to the output directory name.

Here’s how you can modify the load_and_process_files function to achieve this:

Step 1: Extract and Format Date Range

Add the logic to find the minimum and maximum dates within the load_and_process_files function:

import pandas as pd
import os
from datetime import datetime
import tkinter as tk
from tkinter import filedialog, ttk

def prepare_files_with_duplicates(df, wise_df):
    # Your existing preparation logic...
    return files

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name='Sheet1')
    mappings_df = pd.read_excel(wise_report_path, sheet_name='mappings')

    # Extract earliest and latest dates from the 'Finished on' column
    if 'Finished on' in wise_df.columns:
        wise_df['Finished on'] = pd.to_datetime(wise_df['Finished on'])  # Convert to datetime
        min_date = wise_df['Finished on'].min()
        max_date = wise_df['Finished on'].max()
        date_range = f"_{min_date.strftime('%d_%b_%Y')}_to_{max_date.strftime('%d_%b_%Y')}"
    else:
        date_range = ""  # No date range if column is missing

    current_time = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    output_directory = f'wise_transactions_{current_time}{date_range}'
    os.makedirs(output_directory, exist_ok=True)

    files_dict = prepare_files_with_duplicates(mappings_df, wise_df)

    # Save files logic...
    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                excel_path = os.path.join(output_directory, f'{currency}_{direction}.xlsx')
                csv_path = os.path.join(output_directory, 'CSV', f'{currency}_{direction}.csv')  # Adjusted to include subfolder
                df_to_save.to_excel(excel_path, index=False)
                df_to_save.to_csv(csv_path, index=False)

    return f"Files have been successfully created in {output_directory}."

# Your existing Tkinter GUI setup and functions...

Key Adjustments:

Date Conversion and Formatting: Convert the “Finished on” column to datetime and extract the minimum and maximum dates. Format these dates into a string that fits the desired format (e.g., “10_JAN_2024_to_10_JAN_2025”).
Output Directory Naming: Incorporate the formatted date range into the output directory name.

This approach ensures that the output directory’s name reflects the range of dates contained in the initial Wise Excel file, providing more context about the data included in the exports. This is particularly useful for tracking or archival purposes when dealing with periodic reports or data extracts. Make sure the column names and data types in your Excel file align with the assumptions in the script (i.e., that the “Finished on” column exists and contains valid date information).

add one more file browser to the UI for the output directory of our files. This is my final code. do not alter anything that is not needed.

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Adjust the *Date field if it exists in the DataFrame
    if '*Date' in base_row:
        # Format the date as year/month/day without time information
        base_row['*Date'] = pd.to_datetime(base_row['*Date']).strftime('%Y/%m/%d')

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            neutral_row_fee_out = base_row.copy()
            neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
            neutral_row_fee_out['Description'] += '_BANK_FEES'
            source_fee_currency = row['Source fee currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)

            if source_fee_currency not in files:
                files[source_fee_currency] = {'IN': [], 'OUT': []}
            files[source_fee_currency]['OUT'].append(neutral_row_fee_out)

            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

def load_and_process_files(wise_report_path, xero_report_path): wise_df = pd.read_excel(wise_report_path, sheet_name=0) mappings_df = pd.read_excel(wise_report_path, sheet_name=1) xero_df = pd.read_excel(xero_report_path)

mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Extract earliest and latest dates from the 'Finished on' column
if 'Finished on' in wise_df.columns:
    wise_df['Finished on'] = pd.to_datetime(wise_df['Finished on'])  # Convert to datetime
    min_date = wise_df['Finished on'].min()
    max_date = wise_df['Finished on'].max()
    date_range = f"{min_date.strftime('%d_%b_%Y')}_to_{max_date.strftime('%d_%b_%Y')}"
else:
    date_range = ""  # No date range if column is missing

current_time = datetime.now().strftime('%Y%m%d_%H%M%S')
output_directory = f'{current_time}_wise_{date_range}'
os.makedirs(output_directory, exist_ok=True)

csv_subfolder = os.path.join(output_directory, 'CSV')  # Define the subfolder path for CSV files
os.makedirs(csv_subfolder, exist_ok=True)  # Create the subfolder

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}'
            excel_path = os.path.join(output_directory, f'{file_name}.xlsx')
            csv_path = os.path.join(csv_subfolder, f'{file_name}.csv')  # Use the CSV subfolder path for CSV files

            df_to_save.to_excel(excel_path, index=False)
            df_to_save.to_csv(csv_path, index=False)  # Save to CSV in the subfolder

return f"Files have been successfully created in {output_directory}."

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("300x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons for file selection
browse_wise_button = ttk.Button(root, text="Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Xero Template", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

# Create and place the Run button, initially disabled
run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

# Update the run button state whenever file paths change
wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))
xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))

root.mainloop()

if name == “main”: main()

To add an additional file browser in your Tkinter UI for selecting the output directory where the files will be saved, you can add a StringVar for the output directory path and a button to browse for the directory. Below, I’ll show how to integrate these changes into your final script:

Step 1: Add an Output Directory Browser

def browse_directory(path_label):
    """Open a directory dialog to set the output directory path."""
    directory = filedialog.askdirectory()
    if directory:
        path_label.set(directory)

Step 2: Add UI Components for Output Directory Selection

Update the main function to include these components:

def main():
    root = tk.Tk()
    root.title("Wise to Xero Processing Tool")

    # Set the window size and disable resizing
    root.geometry("300x300")  # Adjusted window size to fit new button
    root.resizable(False, False)

    style = ttk.Style(root)
    style.theme_use('default')

    # Setup variables to store file paths
    wise_path_label = tk.StringVar(root)
    xero_path_label = tk.StringVar(root)
    output_path_label = tk.StringVar(root)  # Variable for output directory

    # Configure grid to expand buttons
    root.grid_columnconfigure(0, weight=1)
    root.grid_rowconfigure([0, 1, 2, 3], weight=1)

    # Create and place buttons for file selection
    browse_wise_button = ttk.Button(root, text="Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label, output_path_label))
    browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

    browse_xero_button = ttk.Button(root, text="Xero Template", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label, output_path_label))
    browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

    browse_output_button = ttk.Button(root, text="Output Directory", command=lambda: browse_directory(output_path_label))
    browse_output_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

    # Create and place the Run button, initially disabled
    run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get(), output_path_label.get()))
    run_button.grid(row=3, column=0, sticky="nsew", padx=1, pady=1)

    # Update the run button state whenever file paths change
    wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label, output_path_label))
    xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label, output_path_label))
    output_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label, output_path_label))

    root.mainloop()

Step 3: Update the `update_run_button_state` Function

The function now also checks if an output directory has been selected:

def update_run_button_state(run_button, wise_path, xero_path, output_path):
    """Enable or disable the run button based on whether file paths are set."""
    if wise_path.get() and xero_path.get() and output_path.get():
        run_button['state'] = 'normal'  # Enable the button if all paths are set
    else:
        run_button['state'] = 'disabled'  # Disable the button if any path is not set

Step 4: Modify `on_run_pressed` Function to Use Output Path

Make sure the on_run_pressed function uses the selected output directory for saving files:

def on_run_pressed(root, wise_report_path, xero_report_path, output_directory):
    if not os.path.exists(output_directory):
        os.makedirs(output_directory)
    result = load_and_process_files(wise_report_path, xero_report_path, output_directory)
    print(result)
    root.destroy()

This modification allows the user to select a custom directory for saving the output files, providing more flexibility and control over where the data is stored.

This is my final working script. i want to make one change. For NEUTRAL direction transactions i would like to have separate excel files generated in the end for each currency. So instead of copying these transactions to IN and OUT files as we did before, i want to put them in IN_NEUTRAL and OUT_NEUTRAL files for each currency. The corresponding bank fees, if any, can be treated as before and added to the corresponding currency OUT file. Leave everything else as it was before

    currency = row['Target currency']
    direction = row['Direction']
    base_row = df.iloc[index].copy()

    # Adjust the *Date field if it exists in the DataFrame
    if '*Date' in base_row:
        # Format the date as year/month/day without time information
        base_row['*Date'] = pd.to_datetime(base_row['*Date']).strftime('%Y/%m/%d')

    # Handle NaN or empty 'Target name' and 'Source name'
    base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
    base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

    # Adjusting amounts based on currency and direction
    if row['Source currency'] != row['Target currency']:
        # Append currency conversion information to the Description
        base_row['Description'] = f"{row['Direction']}_{base_row['Description']}_{row['Source currency']} to {row['Target currency']}"

        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Source amount (after fees)'])
            currency = row['Source currency']
        elif direction == 'NEUTRAL':
            print(f"{row['Direction']}_Source fee amount : {row['Source fee amount']}{row['Source fee currency']}")
            # For NEUTRAL, handle IN and OUT separately
            neutral_row_in = base_row.copy()
            neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
            files[currency]['IN'].append(neutral_row_in)

            neutral_row_out = base_row.copy()
            neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
            source_currency = row['Source currency']

            neutral_row_fee_out = base_row.copy()
            neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
            neutral_row_fee_out['Description'] += '_BANK_FEES'
            source_fee_currency = row['Source fee currency']

            if source_currency not in files:
                files[source_currency] = {'IN': [], 'OUT': []}
            files[source_currency]['OUT'].append(neutral_row_out)

            if source_fee_currency not in files:
                files[source_fee_currency] = {'IN': [], 'OUT': []}
            files[source_fee_currency]['OUT'].append(neutral_row_fee_out)

            continue
    else:
        if direction == 'OUT':
            base_row['*Amount'] = -abs(row['Target amount (after fees)'])
        elif direction == 'IN':
            base_row['*Amount'] = abs(row['Target amount (after fees)'])

    # Ensure currency keys exist in the files dictionary
    if currency not in files:
        files[currency] = {'IN': [], 'OUT': []}
    files[currency][direction].append(base_row.copy())

    # Duplicate handling for source fee
    if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:

        duplicate_row = base_row.copy()
        duplicate_currency = row['Source fee currency']
        duplicate_row['*Amount'] = -abs(row['Source fee amount'])
        duplicate_row['Description'] += '_BANK_FEES'

        if duplicate_currency not in files:
            files[duplicate_currency] = {'IN': [], 'OUT': []}
        files[duplicate_currency]['OUT'].append(duplicate_row)

return files  # Make sure to return files from the function

mapped_data = pd.DataFrame(columns=xero_df.columns)
for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
    if xero_col in mapped_data.columns:
        mapped_data[xero_col] = wise_df[wise_col]

files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

# Extract earliest and latest dates from the 'Finished on' column
if 'Finished on' in wise_df.columns:
    wise_df['Finished on'] = pd.to_datetime(wise_df['Finished on'])  # Convert to datetime
    min_date = wise_df['Finished on'].min()
    max_date = wise_df['Finished on'].max()
    date_range = f"{min_date.strftime('%d%b%Y')}_to_{max_date.strftime('%d%b%Y')}"
else:
    date_range = ""  # No date range if column is missing

current_time = datetime.now().strftime('%Y%m%d_%H%M%S')
output_directory = f'{current_time}_wise_{date_range}'
os.makedirs(output_directory, exist_ok=True)

csv_subfolder = os.path.join(output_directory, 'CSV')  # Define the subfolder path for CSV files
os.makedirs(csv_subfolder, exist_ok=True)  # Create the subfolder

for currency, directions in files_dict.items():
    for direction, rows in directions.items():
        if rows:
            df_to_save = pd.DataFrame(rows)
            file_name = f'{currency}_{direction}'
            excel_path = os.path.join(output_directory, f'{file_name}.xlsx')
            csv_path = os.path.join(csv_subfolder, f'{file_name}.csv')  # Use the CSV subfolder path for CSV files

            df_to_save.to_excel(excel_path, index=False)
            df_to_save.to_csv(csv_path, index=False)  # Save to CSV in the subfolder

return f"Files have been successfully created in {output_directory}."

result = load_and_process_files(wise_report_path, xero_report_path)
print(result)
root.destroy()  # Closes the Tkinter window after processing

def main(): root = tk.Tk() root.title(“Wise to Xero Processing Tool”)

# Set the window size and disable resizing
root.geometry("300x200")  # Set initial size of the window
root.resizable(False, False)  # Disable resizing

style = ttk.Style(root)
style.theme_use('default')  # Modern theme

# Setup variable to store file paths
wise_path_label = tk.StringVar(root)
xero_path_label = tk.StringVar(root)

# Configure grid to expand buttons
root.grid_columnconfigure(0, weight=1)
root.grid_rowconfigure(0, weight=1)
root.grid_rowconfigure(1, weight=1)
root.grid_rowconfigure(2, weight=1)

# Create and place buttons for file selection
browse_wise_button = ttk.Button(root, text="Wise Report", command=lambda: browse_file(wise_path_label, run_button, wise_path_label, xero_path_label))
browse_wise_button.grid(row=0, column=0, sticky="nsew", padx=1, pady=1)

browse_xero_button = ttk.Button(root, text="Xero Template", command=lambda: browse_file(xero_path_label, run_button, wise_path_label, xero_path_label))
browse_xero_button.grid(row=1, column=0, sticky="nsew", padx=1, pady=1)

# Create and place the Run button, initially disabled
run_button = ttk.Button(root, text="Run", state='disabled', command=lambda: on_run_pressed(root, wise_path_label.get(), xero_path_label.get()))
run_button.grid(row=2, column=0, sticky="nsew", padx=1, pady=1)

# Update the run button state whenever file paths change
wise_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))
xero_path_label.trace("w", lambda *args: update_run_button_state(run_button, wise_path_label, xero_path_label))

root.mainloop()

if name == “main”: main()

To adjust your script so that transactions with the “NEUTRAL” direction are handled differently and saved into separate Excel files for each currency as IN_NEUTRAL and OUT_NEUTRAL, you will need to modify the prepare_files_with_duplicates function to handle these specific cases separately.

Here’s how you can implement this change in your existing function:

Modifications to `prepare_files_with_duplicates`

def prepare_files_with_duplicates(df, wise_df):
    files = {}  # Initialize the files dictionary
    for index, row in wise_df.iterrows():
        if row['Status'] == 'CANCELLED':
            continue

        currency = row['Target currency']
        direction = row['Direction']
        base_row = df.iloc[index].copy()

        # Adjust the *Date field if it exists in the DataFrame
        if '*Date' in base_row:
            base_row['*Date'] = pd.to_datetime(base_row['*Date']).strftime('%Y/%m/%d')

        # Handle NaN or empty 'Target name' and 'Source name'
        base_row['Description'] = "none" if pd.isna(row['Target name']) or row['Target name'].strip() == "" else row['Target name']
        base_row['Payee'] = "none" if pd.isna(row['Source name']) or row['Source name'].strip() == "" else row['Source name']

        # Define keys for NEUTRAL special handling
        if direction == 'NEUTRAL':
            in_key = f'IN_NEUTRAL'
            out_key = f'OUT_NEUTRAL'
        else:
            in_key = 'IN'
            out_key = 'OUT'

        # Adjusting amounts based on currency and direction
        if row['Source currency'] != row['Target currency']:
            base_row['Description'] += f" {row['Direction']}_{row['Source currency']} to {row['Target currency']}"
            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Source amount (after fees)'])
                currency = row['Source currency']
            elif direction == 'NEUTRAL':
                neutral_row_in = base_row.copy()
                neutral_row_in['*Amount'] = abs(row['Target amount (after fees)'])
                files.setdefault(currency, {}).setdefault(in_key, []).append(neutral_row_in)

                neutral_row_out = base_row.copy()
                neutral_row_out['*Amount'] = -abs(row['Source amount (after fees)'])
                source_currency = row['Source currency']

                neutral_row_fee_out = base_row.copy()
                neutral_row_fee_out['*Amount'] = -abs(row['Source fee amount'])
                neutral_row_fee_out['Description'] += '_BANK_FEES'
                source_fee_currency = row['Source fee currency']

                files.setdefault(source_currency, {}).setdefault(out_key, []).append(neutral_row_out)
                files.setdefault(source_fee_currency, {}).setdefault(out_key, []).append(neutral_row_fee_out)
        else:
            if direction == 'OUT':
                base_row['*Amount'] = -abs(row['Target amount (after fees)'])
            elif direction == 'IN':
                base_row['*Amount'] = abs(row['Target amount (after fees)'])

            if direction == 'NEUTRAL':
                files.setdefault(currency, {}).setdefault(in_key, []).append(base_row.copy())
                files.setdefault(currency, {}).setdefault(out_key, []).append(base_row.copy())
            else:
                files.setdefault(currency, {}).setdefault(direction, []).append(base_row.copy())

        # Duplicate handling for source fee
        if pd.notna(row['Source fee amount']) and row['Source fee amount'] > 0.0:
            duplicate_row = base_row.copy()
            duplicate_row['*Amount'] = -abs(row['Source fee amount'])
            duplicate_row['Description'] += '_BANK_FEES'
            duplicate_currency = row['Source fee currency']
            files.setdefault(duplicate_currency, {}).setdefault('OUT', []).append(duplicate_row)

    return files  # Make sure to return files from the function

Key Changes

NEUTRAL Handling: Transactions with “NEUTRAL” direction are saved into IN_NEUTRAL and OUT_NEUTRAL files instead of the typical IN and OUT.
Files Dictionary Management: Adjusts the keys used for storing transactions in the files dictionary based on the direction, accommodating separate handling for “NEUTRAL”.

This modification ensures that transactions with a “NEUTRAL” direction are managed according to your new requirements, facilitating a clearer and more organized output structure especially designed for easy review and processing of these specific transaction types.

To store the IN_NEUTRAL and OUT_NEUTRAL files in a separate subfolder for better organization, you can modify the load_and_process_files function. This modification involves creating an additional subfolder specifically for neutral transactions and directing the file saving process to this subfolder.

Here’s how you can implement these changes:

Modify the `load_and_process_files` Function

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name=0)
    mappings_df = pd.read_excel(wise_report_path, sheet_name=1)

    mapped_data = pd.DataFrame(columns=xero_df.columns)
    for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
        if xero_col in mapped_data.columns:
            mapped_data[xero_col] = wise_df[wise_col]

    files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

    if 'Finished on' in wise_df.columns:
        wise_df['Finished on'] = pd.to_datetime(wise_df['Finished on'])  # Convert to datetime
        min_date = wise_df['Finished on'].min()
        max_date = wise_df['Finished on'].max()
        date_range = f"{min_date.strftime('%d%b%Y')}_to_{max_date.strftime('%d%b%Y')}"
    else:
        date_range = ""

    current_time = datetime.now().strftime('%Y%m%d_%H%M%S')
    output_directory = f'{current_time}_wise_{date_range}'
    os.makedirs(output_directory, exist_ok=True)

    # Subfolder for CSV files
    csv_subfolder = os.path.join(output_directory, 'CSV')
    os.makedirs(csv_subfolder, exist_ok=True)

    # Subfolder for NEUTRAL files
    neutral_subfolder = os.path.join(output_directory, 'NEUTRAL')
    os.makedirs(neutral_subfolder, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_suffix = 'xlsx'
                file_name = f'{currency}_{direction}'

                # Adjust paths for NEUTRAL files
                if 'NEUTRAL' in direction:
                    excel_path = os.path.join(neutral_subfolder, f'{file_name}.{file_suffix}')
                    csv_path = os.path.join(neutral_subfolder, f'{file_name}.csv')
                else:
                    excel_path = os.path.join(output_directory, f'{file_name}.{file_suffix}')
                    csv_path = os.path.join(csv_subfolder, f'{file_name}.csv')

                df_to_save.to_excel(excel_path, index=False)
                df_to_save.to_csv(csv_path, index=False)  # Save to CSV in the designated subfolder

    return f"Files have been successfully created in {output_directory}."

Explanation

NEUTRAL Subfolder: A new subfolder specifically for IN_NEUTRAL and OUT_NEUTRAL files is created within the main output directory.
File Saving Adjustments: The paths for saving Excel and CSV files are adjusted based on whether the file is a NEUTRAL file or not. NEUTRAL files are directed to the NEUTRAL subfolder.

These changes ensure that files related to transactions marked as NEUTRAL are grouped together in their own subfolder, improving the organization of your output files and making it easier to navigate and manage these specific types of transactions. This setup is especially useful for isolating and reviewing transactions that may require special handling or scrutiny.

you forgot to define xero_df

mapped_data = pd.DataFrame(columns=xero_df.columns)

You’re right, the script requires a consistent definition of xero_df especially since your process relies on mapping column names from the Wise report to an expected structure in the Xero report template. Here’s the corrected version of the load_and_process_files function, ensuring xero_df is properly defined and used:

def load_and_process_files(wise_report_path, xero_report_path):
    wise_df = pd.read_excel(wise_report_path, sheet_name=0)
    mappings_df = pd.read_excel(wise_report_path, sheet_name=1)
    xero_df = pd.read_excel(xero_report_path)

    # Create a DataFrame to map data based on Xero report's expected columns
    mapped_data = pd.DataFrame(columns=xero_df.columns)
    for wise_col, xero_col in dict(zip(mappings_df['WISE'], mappings_df['XERO'])).items():
        if xero_col in mapped_data.columns:
            mapped_data[xero_col] = wise_df[wise_col]

    files_dict = prepare_files_with_duplicates(mapped_data, wise_df)

    if 'Finished on' in wise_df.columns:
        wise_df['Finished on'] = pd.to_datetime(wise_df['Finished on'])  # Convert to datetime
        min_date = wise_df['Finished on'].min()
        max_date = wise_df['Finished on'].max()
        date_range = f"{min_date.strftime('%d%b%Y')}_to_{max_date.strftime('%d%b%Y')}"
    else:
        date_range = ""

    current_time = datetime.now().strftime('%Y%m%d_%H%M%S')
    output_directory = f'{current_time}_wise_{date_range}'
    os.makedirs(output_directory, exist_ok=True)

    # Subfolder for CSV files
    csv_subfolder = os.path.join(output_directory, 'CSV')
    os.makedirs(csv_subfolder, exist_ok=True)

    # Subfolder for NEUTRAL files
    neutral_subfolder = os.path.join(output_directory, 'NEUTRAL')
    os.makedirs(neutral_subfolder, exist_ok=True)

    for currency, directions in files_dict.items():
        for direction, rows in directions.items():
            if rows:
                df_to_save = pd.DataFrame(rows)
                file_suffix = 'xlsx'
                file_name = f'{currency}_{direction}'

                # Adjust paths for NEUTRAL files
                if 'NEUTRAL' in direction:
                    excel_path = os.path.join(neutral_subfolder, f'{file_name}.{file_suffix}')
                    csv_path = os.path.join(neutral_subfolder, f'{file_name}.csv')
                else:
                    excel_path = os.path.join(output_directory, f'{file_name}.{file_suffix}')
                    csv_path = os.path.join(csv_subfolder, f'{file_name}.csv')

                df_to_save.to_excel(excel_path, index=False)
                df_to_save.to_csv(csv_path, index=False)  # Save to CSV in the designated subfolder

    return f"Files have been successfully created in {output_directory}."

This function setup ensures that:

Xero DataFrame (xero_df): It is used to define the structure of mapped_data so that the Wise data can be restructured to fit the Xero format correctly.
File Saving Logic: Adjusts the save paths based on whether the files are standard or belong to the NEUTRAL category, directing them to different subfolders accordingly.

This modification retains your functional requirements and ensures that the file handling is robust and well-structured for ease of data management and review.

To handle the currency conversion and append the converted values to a new column “CONVERT TO GBP” in your Excel files, you’ll need to integrate API calls to retrieve historical exchange rates and apply these rates to the transaction values. Here’s a step-by-step guide on how to modify your existing Python script to include this functionality:

Step 1: Get API Key

You’ll need an API key from Open Exchange Rates. Register on their website and retrieve your API key.

Step 2: Install Required Libraries

You might need to install requests library if you haven’t already:

pip install requests

Step 3: Define a Function to Fetch Exchange Rates

This function will make an API call to get historical rates for a given date:

import requests

def get_historical_rate(date, currency):
    API_KEY = 'YOUR_API_KEY'  # Replace YOUR_API_KEY with your Open Exchange Rates API key
    url = f'https://openexchangerates.org/api/historical/{date}.json?app_id={API_KEY}&symbols=GBP,{currency}'
    response = requests.get(url)
    data = response.json()
    # Get the exchange rate from the currency to GBP
    if currency == 'GBP':
        return 1  # If the currency is already GBP, return 1
    return data['rates']['GBP'] / data['rates'][currency]

Step 4: Modify Data Processing to Include Conversion

Integrate the conversion into the DataFrame processing, creating a new column based on the fetched exchange rates:

def prepare_files_with_duplicates(df, wise_df):
    files = {}
    for index, row in wise_df.iterrows():
        if row['Status'] == 'CANCELLED':
            continue

        # Get historical exchange rate for the transaction date and currency
        if 'Finished on' in row and row['Source currency'] != 'GBP':
            date_str = pd.to_datetime(row['Finished on']).strftime('%Y-%m-%d')
            exchange_rate = get_historical_rate(date_str, row['Source currency'])
            converted_amount = row['Amount'] * exchange_rate
        else:
            converted_amount = row['Amount']  # No conversion needed for GBP

        # Append new column for GBP converted amounts
        df.loc[index, 'CONVERT TO GBP'] = converted_amount

        # Continue with existing processing...

Step 5: Update File Saving to Include New Data

Ensure that when saving files, the new column is included. This should naturally happen if you’re saving the entire DataFrame, but ensure that df_to_save contains the ‘CONVERT TO GBP’ column before saving.

Note:

API Limitations: Free API plans often have limitations, such as the frequency of calls or available historical data. Ensure your use case is compatible with your API plan.
Error Handling: Implement error handling around the API call to manage potential downtime or data unavailability.
Currency Support: Ensure the API supports all currencies you are working with; otherwise, you’ll need to handle unsupported currencies gracefully.

This integration provides a method to dynamically convert transaction values based on historical exchange rates, adding a valuable layer of financial consistency and comparability for transactions across different currencies. Adjust this framework according to your specific API details and error handling needs.