Issue with Pandas DataFrame

Question

There is a flaw in this code

The code below reads the price of the symbol of a stock and then returns a set of optimal trades that can be made in the timeframe.

The trades dataframe is a single column dataframe where the column is the trades taken on that date and the index is the date.

The only positions which are allowable are 1000 shares long, selling 1000 shares short, or 0 shares.

The only trades which are allowed are 1000 shares long, 2000 shares long, 1000 shares short, 1000 shares short, and 0 shares.

Below is the code:

import pandas as pd

from util import get_data

import pandas as pd

def testPolicy(symbol, sd, ed, sv):

"""

Returns a trades DataFrame based on a trading policy.

:param symbol: The stock symbol to act on

:type symbol: str

:param sd: A DateTime object that represents the start date

:type sd: datetime.datetime

:param ed: A DateTime object that represents the end date

:type ed: datetime.datetime

:param sv: Start value of the portfolio

:type sv: float

:return: A single column DataFrame representing trades for each trading day

:rtype: pandas.DataFrame

"""

dates = pd.date_range(sd, ed)

prices = get_data([symbol], dates)

prices = prices.reindex(dates)

prices.fillna(method='ffill', inplace=True)

prices.fillna(method='bfill', inplace=True)

# Create a full date range DataFrame

dates = pd.date_range(sd, ed)

trades = pd.DataFrame(0, index=dates, columns=[symbol])

holdings = 0 # Current holdings

for i in range(len(dates) - 1):

curr_date = dates[i]

next_date = dates[i + 1]

if curr_date in prices.index and next_date in prices.index:

curr_price = prices.loc[curr_date, symbol]

next_price = prices.loc[next_date, symbol]

if curr_price < next_price:

if holdings <= -1000 and sv >= next_price * 2000:

trades.loc[curr_date] = 2000 # Buy 2000 shares

holdings += 2000

sv -= next_price * 2000

elif holdings == -1000 and sv >= next_price * 1000:

trades.loc[curr_date] = 1000 # Buy 1000 shares

holdings += 1000

sv -= next_price * 1000

elif holdings == 0 and sv >= next_price * 1000:

trades.loc[curr_date] = 1000 # Buy 1000 shares

holdings += 1000

sv -= next_price * 1000

elif curr_price > next_price:

if holdings >= 1000 and sv >= next_price * 2000:

trades.loc[curr_date] = -2000 # Sell 2000 shares

holdings -= 2000

sv += next_price * 2000

elif holdings == 1000 and sv >= next_price * 1000:

trades.loc[curr_date] = -1000 # Sell 1000 shares

holdings -= 1000

sv += next_price * 1000

elif holdings == 0 and sv >= next_price * 1000:

trades.loc[curr_date] = -1000 # Sell 1000 shares

holdings -= 1000

sv += next_price * 1000

return trades

The issue is that the last entries of the trades dataframe show:

2008-10-01 0

2008-10-02 0

2008-10-03 0

2008-10-04 0

2008-10-05 0

2008-10-06 0

2008-10-07 0

2008-10-08 0

2008-10-09 0

2008-10-10 0

2008-10-11 0

2008-10-12 0

2008-10-13 0

2008-10-14 0

2008-10-15 0

2008-10-16 0

2008-10-17 0

2008-10-18 0

2008-10-19 0

2008-10-20 0

2008-10-21 0

2008-10-22 0

2008-10-23 0

2008-10-24 0

2008-10-25 0

All those zeroes show that no trades were made

However if we look at the price history we can see that the price does change.

Below are the last rows prices dataframe:

2009-10-01 39.370

2009-10-02 39.880

2009-10-03 39.880

2009-10-04 39.880

2009-10-05 41.730

2009-10-06 42.790

2009-10-07 43.540

2009-10-08 43.160

2009-10-09 43.680

2009-10-10 43.680

2009-10-11 43.680

2009-10-12 43.900

2009-10-13 43.500

2009-10-14 44.930

2009-10-15 44.930

2009-10-16 43.880

2009-10-17 43.880

2009-10-18 43.880

2009-10-19 43.810

2009-10-20 43.850

2009-10-21 42.540

2009-10-22 43.550

2009-10-23 43.090

2009-10-24 43.090

2009-10-25 43.090

So there are price changes which means that trades should happen.

Answer 1

Hi Ahsan!

This is an interesting problem! I believe the reason that you are not seeing any trades happening is because of the conditionals used to determine when to buy and sell based on the starting value. For example: The only times you make a trade are when holdings are 0, holdings are 1000, or holdings are -1000. Holdings start with 0, so let's look at the conditionals to trade when holdings are 0:

#....

elif holdings == 0 and sv >= next_price * 1000:

#...

elif holdings == 0 and sv >= next_price * 1000:

The key thing here is that, in order to make any initial trades the sv must be greater than or equal to the next_price*1000. This means that for the prices you gave (starting at 39.370, but the first next_price is 39.88) the starting value of the portfolio needs to be greater than or equal to 39,880 in order to make an initial trade. I suspect that you are calling the method with an sv value less than 39,880. I ran with several different starting values and observed that when the SV was less than 39,880 that no trades were made, and once I made the SV greater than or equal to 39,880 it began to make trades.

If you would like to see how I tested / came to this conclusion: I went ahead and made my own simple get_data util to match the data you were seeing.

def get_data(symbol, dates):

data = {symbol[0]: [

39.370, 39.880, 39.880, 39.880, 41.730, 42.790, 43.540, 43.160, 43.680,

43.680, 43.680, 43.900, 43.500, 44.930, 44.930, 43.880, 43.880, 43.880,

43.810, 43.850, 42.540, 43.550, 43.090, 43.090, 43.090

]}

return pd.DataFrame(data, index=dates)

And then I ran the testPolicy with multiple starting values as seen below

if __name__ == '__main__':

dates = pd.date_range(start='2009-10-01', end='2009-10-25')

starting_value = 0

trades = testPolicy('AAPL', dates[0], dates[-1], starting_value)

print("Starting with " + str(starting_value))

print(trades)

print("-----------------------------------")

starting_value = 39880

trades = testPolicy('AAPL', dates[0], dates[-1], starting_value)

print("Starting with " + str(starting_value))

print(trades)

print("-----------------------------------")

starting_value = 100000

trades = testPolicy('AAPL', dates[0], dates[-1], starting_value)

print("Starting with " + str(starting_value))

print(trades)

print("-----------------------------------")

starting_value = 1000000

trades = testPolicy('AAPL', dates[0], dates[-1], starting_value)

print("Starting with " + str(starting_value))

print(trades)

print("-----------------------------------")