How to show a carrying account balance?

Question

For a stocks "strategy", I have a DataFrame (called "concat") that shows the trade profit at position "I". Let's say that I have a variable called "Starting Balance" = 1,000

I want to calculate the available balance in each row given the profit/or loss from the trade based on an initial starting balance.

(It says that I'm not allowed to embed pictures so in the link is the example I want to achieve)

I tried to create a loop that should go in every "I" position a calculate that:

    
    if concat['Sell at'][i] > 0:
        strt_bal = Starting_Balance
        number_of_shares = np.round(strt_bal / concat['Bought at'][i], 0)
        trade_profit = number_of_shares * concat['Win / (loss) $ Long'][i]
        strt_bal = strt_bal + trade_profit
        test.append(strt_bal)

And to see if it was working I checked the values in a list, but the values are wrong because its always using the variable "Starting Balance" to calculate.

How can I achieve the desired result as the Excel file?

Answer 1

First I'll just create a mock data frame based on the Excel data you provided:

import pandas as pd

df = pd.DataFrame([2500]+[None]*9,
                  [0]*6+[-51.2548,0,0,198.8144]).reset_index(drop=False)

df.columns = ['profit','balance']

df

    profit  balance
0   0.0000  2500.0
1   0.0000  NaN
2   0.0000  NaN
3   0.0000  NaN
4   0.0000  NaN
5   0.0000  NaN
6   -51.2548    NaN
7   0.0000  NaN
8   0.0000  NaN
9   198.8144    NaN

We have the profit values and the starting balance, nothing else. We want to fill out the rest of the balance column, replacing the NaN values.

Quick fix:

for i in df.index:
    df.balance[i] = list(df[~df.balance.isnull()].balance)[-1]+df.profit[i]

For each row, we'll find the last non-null value in the balance column and add this with the profit value in the row. This will represent our balance value for that row.

Now the dataframe:

    profit  balance
0   0.0000  2500.0000
1   0.0000  2500.0000
2   0.0000  2500.0000
3   0.0000  2500.0000
4   0.0000  2500.0000
5   0.0000  2500.0000
6   -51.2548    2448.7452
7   0.0000  2448.7452
8   0.0000  2448.7452
9   198.8144    2647.5596