How to format a negativ percent in red, and postive percent in green?

Question

I have a code that collects finance data using API, and transform it into a ecxel file. In on of the columns I have data considering the change in the stocks price the last day, and i want python to write negative changes in red, and positve changes in green. I have an ide of how i could du it, using and if/else statment, but the problem is which variable i should call for in the if statment.

Here is tha part when I append the data from the API using a request.get function.

data = requests.get(batch_apu_call_url).json()
for symbol in symbol_string.split(','):

final_dataframe = final_dataframe.append(
        pd.Series(
        [
            data[symbol]['quote']['companyName'],
            symbol,
            data[symbol]['quote']['latestPrice'],
            data[symbol]['quote']['changePercent'], #This is the line thats collects the changes in the stock prices 
            data[symbol]['quote']['marketCap'],
            'N/A',
            data[symbol]['quote']['latestTime']
        ],
        index=my_columns),
        ignore_index=True,

    )

Her is the part when i want to format the special column

change_format = writer.book.add_format(
{
    'num_format': '0.00%',
    'font_color': 'EA1009' if [] < 0 else '2AEA09',   #insted of the square brackets I want to put the variabel 
    'bg_color': background_color,
    'border': 1
}

)

This is an example of part of the outcome in python:

                          Name Ticker  Stock Price     +/-%     Market Cap
0    Agilent Technologies Inc.      A      127.120  0.01096    38026751371
1  American Airlines Group Inc    AAL       26.301  0.02625    16894821033
2       Advance Auto Parts Inc    AAP      184.060  0.01200    12369889031
3                    Apple Inc   AAPL      125.884 -0.02000  2114224151643
4                   Abbvie Inc   ABBV      109.250 -0.00430   191633606290

Process finished with exit code 0

And the same outcome in ecxel. The ecxel file isn't updatet today, so the data may be different than the python data. https://i.sstatic.net/i9PIz.png

And this is hove I want it to loock: https://i.sstatic.net/G8xA5.png

Answer 1

I found the solution, and the best way to do it is:

change_format = writer.book.add_format(
    {
        'num_format': '0.00%[Green];-0.00%[Red]',
        'font_color': '',
        'bg_color': background_color,
        'border': 1
    }
)