CODEWITHSUNDEEP
pythonpandasdataframe
ZacAttack
ZacAttack

Reputation: 1055

How do I get a rolling profit and loss in a Pandas DataFrame column per day?

Overview
I am back testing trading strategies and I want to be able to see profit and loss day by day, based on the strategies performance.

Approach
I am storing stock data with trading signals that say "NoTrade", "Win", and "Loss". NoTrade means that there was not trade taken, Win means trade taken and it was profitable, and loss means trade taken but was unprofitable. I wrote a loop to check if there was a signal or not. if there was a win or loss then my account balance is up-dated to reflect a gain or loss.

DataFrame 

            WinLoss  ProfitAndLossPofChg  AccountBalance
D
2010-12-08  NoTrade             0.000000          2000.0
2010-12-09  NoTrade             0.000000          2000.0
2010-12-10  NoTrade             0.000000          2000.0
2010-12-13  NoTrade             0.000000          2000.0
2010-12-14  NoTrade             0.000000          2000.0
2010-12-15     Loss            -0.030842          2000.0
2010-12-16      Win             0.000539          2000.0
2010-12-17  NoTrade             0.000000          2000.0
2010-12-20  NoTrade             0.000000          2000.0
2010-12-21  NoTrade             0.000000          2000.0
2010-12-22      Win             0.014686          2000.0
2010-12-23  NoTrade             0.000000          2000.0
2010-12-27  NoTrade             0.000000          2000.0
2010-12-28     Loss            -0.006190          2000.0
2010-12-29  NoTrade             0.000000          2000.0
2010-12-30  NoTrade             0.000000          2000.0
2010-12-31  NoTrade             0.000000          2000.0
2011-01-03     Loss            -0.055686          2000.0
2011-01-04     Loss            -0.025471          2000.0
2011-01-05     Loss            -0.051420          2000.0
2011-01-06     Loss            -0.000299          2000.0
2011-01-07  NoTrade             0.000000          2000.0
2011-01-10  NoTrade             0.000000          2000.0
2011-01-11      Win             0.003719          2000.0
2011-01-12  NoTrade             0.000000          2000.0
2011-01-13     Loss            -0.041218          2000.0
2011-01-14      Win             0.033365          2000.0
2011-01-18      Win             0.018628          2000.0
2011-01-19  NoTrade             0.000000          2000.0
2011-01-20     Loss            -0.020820          2000.0

Code

def ProfitAndLoss(DataFrame):

    df = DataFrame

    for i in df.WinLoss:

        if i == "NoTrade":
            df.AccountBalance = df.AccountBalance.shift(-1)

        elif i == "Win":
            df.AccountBalance = df.AccountBalance.shift(-1) *  df.ProfitAndLossPofChg

        elif i == "Loss":
            df.AccountBalance = df.AccountBalance.shift(-1) *  df.ProfitAndLossPofChg

Output 

            WinLoss  ProfitAndLossPofChg  AccountBalance
D
2010-12-08  NoTrade             0.000000             0.0
2010-12-09  NoTrade             0.000000             0.0
2010-12-10  NoTrade             0.000000             0.0
2010-12-13  NoTrade             0.000000             0.0
2010-12-14  NoTrade             0.000000             0.0
2010-12-15     Loss            -0.030842            -0.0
2010-12-16      Win             0.000539             0.0
2010-12-17  NoTrade             0.000000             0.0
2010-12-20  NoTrade             0.000000             0.0
2010-12-21  NoTrade             0.000000             0.0
2010-12-22      Win             0.014686             0.0
2010-12-23  NoTrade             0.000000             0.0
2010-12-27  NoTrade             0.000000             0.0
2010-12-28     Loss            -0.006190            -0.0
2010-12-29  NoTrade             0.000000             0.0
2010-12-30  NoTrade             0.000000             0.0
2010-12-31  NoTrade             0.000000             0.0
2011-01-03     Loss            -0.055686            -0.0
2011-01-04     Loss            -0.025471            -0.0
2011-01-05     Loss            -0.051420            -0.0
2011-01-06     Loss            -0.000299            -0.0
2011-01-07  NoTrade             0.000000             0.0
2011-01-10  NoTrade             0.000000             0.0
2011-01-11      Win             0.003719             0.0
2011-01-12  NoTrade             0.000000             0.0
2011-01-13     Loss            -0.041218            -0.0
2011-01-14      Win             0.033365             0.0
2011-01-18      Win             0.018628             0.0
2011-01-19  NoTrade             0.000000             0.0
2011-01-20     Loss            -0.020820            -0.0

Problem
As you can see my account balance is all zeroes; not showing rolling profit and loss with a starting account balance of $2000.00. In my function I figured that if there was no trade then just take the prior days balance and make it todays balance, or if it was a win or losing trade take the prior days balance and times it by current gain or loss. I figured this would show a running total of profit and loss, but I guess I figured wrong. This is a long way of saying that I don't know.

Upvotes: 1

Views: 2524

Answers (2)

Alex Fung
Alex Fung

Reputation: 2006

def ProfitAndLoss(df):

    df = df.copy()
    df = df.reset_index()
    for index,row in df.iterrows():
        if index == 0:
            continue
        if row['WinLoss'] == "NoTrade":
            df['AccountBalance'][index] = df['AccountBalance'][index-1]

        elif row['WinLoss'] in ["Win", "Loss"]:
            df['AccountBalance'][index] = df['AccountBalance'][index-1] *  (1 + df['ProfitAndLossPofChg'][index])

    return df

print(ProfitAndLoss(df).set_index('D'))

Output:

            WinLoss  ProfitAndLossPofChg  AccountBalance
D
2010-12-08  NoTrade             0.000000     2000.000000
2010-12-09  NoTrade             0.000000     2000.000000
2010-12-10  NoTrade             0.000000     2000.000000
2010-12-13  NoTrade             0.000000     2000.000000
2010-12-14  NoTrade             0.000000     2000.000000
2010-12-15     Loss            -0.030842     1938.316000
2010-12-16      Win             0.000539     1939.360752
2010-12-17  NoTrade             0.000000     1939.360752
2010-12-20  NoTrade             0.000000     1939.360752
2010-12-21  NoTrade             0.000000     1939.360752
2010-12-22      Win             0.014686     1967.842204
2010-12-23  NoTrade             0.000000     1967.842204
2010-12-27  NoTrade             0.000000     1967.842204
2010-12-28     Loss            -0.006190     1955.661261
2010-12-29  NoTrade             0.000000     1955.661261
2010-12-30  NoTrade             0.000000     1955.661261
2010-12-31  NoTrade             0.000000     1955.661261
2011-01-03     Loss            -0.055686     1846.758308
2011-01-04     Loss            -0.025471     1799.719527
2011-01-05     Loss            -0.051420     1707.177949
2011-01-06     Loss            -0.000299     1706.667503
2011-01-07  NoTrade             0.000000     1706.667503
2011-01-10  NoTrade             0.000000     1706.667503
2011-01-11      Win             0.003719     1713.014599
2011-01-12  NoTrade             0.000000     1713.014599
2011-01-13     Loss            -0.041218     1642.407564
2011-01-14      Win             0.033365     1697.206492
2011-01-18      Win             0.018628     1728.822055
2011-01-19  NoTrade             0.000000     1728.822055
2011-01-20     Loss            -0.020820     1692.827979

Upvotes: 2

jagdish
jagdish

Reputation: 259

There is no need to loop over the dataframe. Applying shift to the pd Series 'df.AccountBalance' creates another Series. Try this:

df['AccountBalance'] += df['AccountBalance'].shift(-1) * df['ProfitAndLossPofChg']

Upvotes: 3

Related Questions