Dynamic array using xlwings causing formula to be wrapped with {}

I am trying to populate a cell with a dynamic array formula using xlwings but each time I try the formula either ends up with @ after the = or the formula is being put inbetween {} and I can't seem to fix this. This is the code for the function in question:

def process_files():
    # Disable button and change image
    button_1.configure(state=tk.DISABLED, image=button_image_1_processing)
    window.update_idletasks()  # Force GUI update

    try:
        update_entry_2('Process Started...')
        directory = entry_1.get()
        if not directory:
            update_entry_2("Directory not selected.")
            return

        start_date_str = entry_6.get()
        end_date_str = entry_5.get()
        forward_date_str = entry_4.get()

        if not validate_date(start_date_str):
            update_entry_2('Start Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
            return

        if not validate_date(end_date_str):
            update_entry_2('End Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
            return

        if not validate_date(forward_date_str):
            update_entry_2('Forward Date is not in the correct format. Please enter a date in the format "DD-MM-YYYY".')
            return

        # Convert dates from strings to datetime objects
        start_date = datetime.strptime(start_date_str, "%d/%m/%Y")
        end_date = datetime.strptime(end_date_str, "%d/%m/%Y")
        forward_date = datetime.strptime(forward_date_str, "%d/%m/%Y")

        # Check if end date is earlier than start date
        if end_date < start_date:
            update_entry_2("End date cannot be earlier than start date.")
            return

        # Check if forward date is earlier than start date
        if forward_date < start_date:
            update_entry_2("Forward date cannot be earlier than start date.")
            return

        # Check if end date is earlier than forward date
        if end_date < forward_date:
            update_entry_2("End date cannot be earlier than forward date.")
            return

        if not validate_currency(entry_3.get()):
            update_entry_2('Account balance is not correct, this must be a number.')
            return


        xml_files = [f for f in os.listdir(directory) if f.endswith('.xml')]
        paired_files = [(f, f.replace('.forward', '')) for f in xml_files if
                        f.endswith('.forward.xml') and f.replace('.forward', '') in xml_files]

        for fwd_file, back_file in paired_files:
            update_entry_2(f"{fwd_file} Found, Converting to XLSX")

            fwd_file_path = os.path.join(directory, fwd_file)
            back_file_path = os.path.join(directory, back_file)
            template_path = os.path.join(directory, 'SetFinderTemplate.xlsx')
            fwd_xlsx_path = os.path.join(directory, fwd_file.replace('.xml', '.xlsx'))
            back_xlsx_path = os.path.join(directory, back_file.replace('.xml', '.xlsx'))
            output_file_path = os.path.join(directory, fwd_file.replace('.forward.xml', '_Filtered.xlsx'))

            # Convert XML to XLSX
            convert_xml_to_xlsx(fwd_file_path, fwd_xlsx_path)
            convert_xml_to_xlsx(back_file_path, back_xlsx_path)
            update_entry_2('Files converted, importing data into Jake’s Template')

            # Load the excel template
            template_wb = load_workbook(template_path)

            # Load dataframes
            fwd_df = pd.read_excel(fwd_xlsx_path)
            back_df = pd.read_excel(back_xlsx_path)

            # Make sure the columns are in the correct order
            fwd_columns = ['Pass', 'Forward Result', 'Back Result', 'Profit', 'Expected Payoff', 'Profit Factor',
                        'Recovery Factor', 'Sharpe Ratio', 'Custom', 'Equity DD %', 'Trades']
            back_columns = ['Pass', 'Result', 'Profit', 'Expected Payoff', 'Profit Factor', 'Recovery Factor',
                        'Sharpe Ratio', 'Custom', 'Equity DD %', 'Trades']

            fwd_df = fwd_df[fwd_columns]
            back_df = back_df[back_columns]

            # Get the sheets
            fwd_sheet = template_wb['Fwd Data']
            back_sheet = template_wb['Back Data']

            # Write dataframes to the sheets
            for i, row in enumerate(fwd_df.values, start=3):
                for j, value in enumerate(row, start=6):  # Start from column F (6)
                    fwd_sheet.cell(row=i, column=j, value=value)

            for i, row in enumerate(back_df.values, start=3):
                for j, value in enumerate(row, start=1):
                    back_sheet.cell(row=i, column=j, value=value)

            instructions_notes_sheet = template_wb['INSTRUCTIONSNOTES']
            instructions_notes_sheet['B15'] = entry_6.get()  # Start Date
            instructions_notes_sheet['B17'] = entry_5.get()  # End Date
            instructions_notes_sheet['B16'] = entry_4.get()  # Forward Date
            instructions_notes_sheet['B18'] = entry_3.get()  # Account balance

            # Save workbook
            template_wb.save(output_file_path)
            update_entry_2('Template populated and Filtered file saved')

            # Load workbook
            wb = xw.Book(output_file_path)

            # Get the sheet
            sheet = wb.sheets['RESULTS-ExcelVersion']

            # Assign the formula to cell A9
            sheet.range(
                'A9').formula_array = '=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")'

            # Save and close the workbook
            wb.save()
            wb.close()

I am not super experienced with code and I have tried reading xlwings documentation along with openpyxl and also XlsxWriter to try and solve this problem but just keep hitting brick wall after brick wall. The formula I want to have in cell A9 is;

=IFERROR(SORT(FILTER('Fwd Data'!B:P,'Fwd Data'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")

I have also tried putting the formula in the cell without the = and just getting python to add the = for me after the file has been created but this also resulted in an error. I have Office 365 and it is fully up to date so I know the formula is supported and when putting it in manually I get the result I wanted.

I have read through the forums and seen This post but no solutions I have seen have given results as yet. Also a lot of the posts are a year plus old so I was hoping that there may be a solution to this by now.

Any help anyone can offer is appreciated. Thank you

The question concerns these lines in your code:

# Assign the formula to cell A9
sheet.range(
    'A9').formula_array = '=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")'

Consider the following three variants of returning a formula to an Excel cell with xlwings:

formula = """=IFERROR(SORT(FILTER(\'Fwd Data\'!B:P,\'Fwd Data\'!A:A=TRUE),2,-1),"NO RESULTS FOUND WITHIN THESE THRESHOLDS")"""

# 1: .formula_array  '={...}'
sheet.range("A9").formula_array = formula
# 2: .formula  '=@'
sheet.range("A9").formula = formula
# 3: .formula2  '='
sheet.range("A9").formula2 = formula

You will find that the output for option 1 contains {}, option 2 has =@, and option 3 is just =.

Option 1 - .formula_array

This returns an array of formula, which can also be recreated by highlighting multiple cells in Excel, typing a formula in one and pressing Ctrl + Shift + Enter.

This Microsoft webpage provides a good explanation on when this might be used.

Options 2 and 3 - .formula and .formula2

As mentioned in your post, this answer provides a good explanation on the differences between the two.

.formula2 is explained here by Microsoft, which also contains a link to a very detailed comparison of the two. This variation allows for spilling of outputs from the formula.

Summary

So, if you do not want either {} or @ in the returned formula, you should use .formula2 in your code.

Also note that I have used triple quotes """ around of the Excel formula, because the formula includes both ' and ".