Python and Pandas – Moving Average Crossover

numpypandaspython

There is a Pandas DataFrame object with some stock data. SMAs are moving averages calculated from previous 45/15 days.

Date      Price   SMA_45      SMA_15
20150127  102.75  113         106
20150128  103.05  100         106
20150129  105.10  112         105
20150130  105.35  111         105
20150202  107.15  111         105
20150203  111.95  110         105
20150204  111.90  110         106

I want to find all dates, when SMA_15 and SMA_45 intersect.

Can it be done efficiently using Pandas or Numpy? How?

EDIT:

What I mean by 'intersection':

The data row, when:

  • long SMA(45) value was bigger than short SMA(15) value for longer than short SMA period(15) and it became smaller.
  • long SMA(45) value was smaller than short SMA(15) value for longer than short SMA period(15) and it became bigger.

Best Solution

I'm taking a crossover to mean when the SMA lines -- as functions of time -- intersect, as depicted on this investopedia page.

enter image description here

Since the SMAs represent continuous functions, there is a crossing when, for a given row, (SMA_15 is less than SMA_45) and (the previous SMA_15 is greater than the previous SMA_45) -- or vice versa.

In code, that could be expressed as

previous_15 = df['SMA_15'].shift(1)
previous_45 = df['SMA_45'].shift(1)
crossing = (((df['SMA_15'] <= df['SMA_45']) & (previous_15 >= previous_45))
            | ((df['SMA_15'] >= df['SMA_45']) & (previous_15 <= previous_45)))

If we change your data to

Date      Price   SMA_45      SMA_15
20150127  102.75  113         106
20150128  103.05  100         106
20150129  105.10  112         105
20150130  105.35  111         105
20150202  107.15  111         105
20150203  111.95  110         105
20150204  111.90  110         106

so that there are crossings,

enter image description here

then

import pandas as pd

df = pd.read_table('data', sep='\s+')
previous_15 = df['SMA_15'].shift(1)
previous_45 = df['SMA_45'].shift(1)
crossing = (((df['SMA_15'] <= df['SMA_45']) & (previous_15 >= previous_45))
            | ((df['SMA_15'] >= df['SMA_45']) & (previous_15 <= previous_45)))
crossing_dates = df.loc[crossing, 'Date']
print(crossing_dates)

yields

1    20150128
2    20150129
Name: Date, dtype: int64