Avoid looping to calculate simple moving average crossing-derived signals

Question

I would like to calculate buy and sell signals for stocks based on simple moving average (SMA) crossing. A buy signal should be given as soon as the SMA_short is higher than the SMA_long (i.e., SMA_difference > 0). In order to avoid that the position is sold too quickly, I would like to have a sell signal only once the SMA_short has moved beyond the cross considerably (i.e., SMA_difference < -1), and, importantly, even if this would be for longer than one day.

I managed, by this help to implement it (see below):

Buy and sell signals are indicated by in and out.
Column Position takes first the buy_limit into account.
In Position_extended an in is then set for all the cases where the SMA_short just crossed through the SMA_long (SMA_short < SMA_long) but SMA_short > -1. For this it is taking the Position extended of i-1 into account in case the crossing was more than one day ago but SMA_short remained: 0 > SMA_short > -1.

Python code

import pandas as pd
import numpy as np

index = pd.date_range('20180101', periods=6)
df = pd.DataFrame(index=index)
df["SMA_short"] = [9,10,11,10,10,9]
df["SMA_long"] = 10
df["SMA_difference"] = df["SMA_short"] - df["SMA_long"]
buy_limit = 0
sell_limit = -1

df["Position"] = np.where((df["SMA_difference"] > buy_limit),"in","out")
df["Position_extended"] = df["Position"]
for i in range(1,len(df)):
    df.loc[index[i],"Position_extended"] =  \
    np.where((df.loc[index[i], "SMA_difference"] > sell_limit) \
              & (df.loc[index[i-1],"Position_extended"] == "in") \
              ,"in",df.loc[index[i],'Position'])
print df

The result is:

              SMA_short  SMA_long  SMA_difference Position Position_extended
2018-01-01          9        10              -1      out               out
2018-01-02         10        10               0      out               out
2018-01-03         11        10               1       in                in
2018-01-04         10        10               0      out                in
2018-01-05         10        10               0      out                in
2018-01-06          9        10              -1      out               out

The code works, however, it makes use of a for loop, which slows down the script considerably and becomes inapplicable in the larger context of this analysis. As SMA crossing is such a highly used tool, I was wondering whether somebody could see a more elegant and faster solution for this.

Troy D · Answer 1 · 2018-05-11T19:11:02.017

0

If row T requires as input a value calculated in row T-1, then you'll probably want to do an iterative calculation. Typically backtesting is done by iterating through price data in sequence. You can calculate some signals just based on the state of the market, but you won't know the portfolio value, the pnl, or the portfolio positions unless you start at the beginning and work your way forward in time. That's why if you look at a site like Quantopian, the backtests always run from from start date to end date.

edited May 11 '18 at 19:11

answered May 11 '18 at 16:02

Troy D

2,093
1
14
28

1

`DataFrame.shift()` allows you to compare every element with every other shifted element. – ALollz May 11 '18 at 17:12
My apologies for the downvote. Finding whether one SMA crosses another SMA, can be done via two `rolling` calculations. Finding the *first* time it happens in each period can be done with operations like `diff` and `cumsum`. – Ami Tavory May 11 '18 at 17:13
I know you can calculate SMA crossing points, but I'm not following how you're keeping track of position to determine whether he's looking to enter or exit the position, @AmiTavory. What's your solution? – Troy D May 11 '18 at 19:02
Completely refuting your answer would require answering the OP's question, which I don't want to do (at least now). On the other hand, answering in the negative that it's impossible puts the onus on you, not me, to substantiate it. If the downvote bothers you, LMK and I'll reverse it (even though I disagree with you). – Ami Tavory May 11 '18 at 19:05
So I reversed the downvote, but I disagree with you, FWIW. Anyway, all the best. – Ami Tavory May 11 '18 at 19:11
Ok, I shouldn't say it's impossible. All I'm saying is that the questioner shouldn't be afraid of the iterative solution for backtesting, as that's the industry standard like you'd see on websites like Quantopian. Otherwise keeping track of positions and PNL is going to be pretty gnarly and error prone challenge, in my opinion. – Troy D May 11 '18 at 19:55

plxc · Accepted Answer · 2018-05-15T22:02:51.697

Essentially you are trying to get rid of the ambivalent zero entries by propagating the last non-zero value. Similar to a zero-order hold. You can do so my first replacing the zero values by NaNs and then interpolating over the latter using ffill.

import pandas as pd
import numpy as np

index = pd.date_range('20180101', periods=6)
df = pd.DataFrame(index=index)
df["SMA_short"] = [9,10,11,10,10,9]
df["SMA_long"] = 10
df["SMA_difference"] = df["SMA_short"] - df["SMA_long"]
buy_limit = 0
sell_limit = -1

df["ZOH"] = df["SMA_difference"].replace(0,np.nan).ffill()
df["Position"] = np.where((df["ZOH"] > buy_limit),"in","out")
print df

results in:

            SMA_short  SMA_long  SMA_difference  ZOH Position
2018-01-01          9        10              -1 -1.0      out
2018-01-02         10        10               0 -1.0      out
2018-01-03         11        10               1  1.0       in
2018-01-04         10        10               0  1.0       in
2018-01-05         10        10               0  1.0       in
2018-01-06          9        10              -1 -1.0      out

This is perfect; exactly what I was looking for. I am very sorry that my limited reputation does not allow me to up-vote your answer but glory to you. — Manuel T, May 16 '18 at 14:31

Avoid looping to calculate simple moving average crossing-derived signals

2 Answers2