Setting up a moving average alert system

Getting Setup

Quandl provides a financial data API for access to pricing, economic data, company valuation data, and alternative financial data. It's a great resource for anyone looking to do quant work at a reasonable cost, in fact, most of my strategy backtest work is built on Quandl data. The ETL process is extremely simple when using the Quandl Python library and the returned data is by default a Pandas dataframe object. Pandas is an excellent Python library for working with time series data. If you don't have the Quandl or Pandas libraries and want to follow along, install them by following these instructions: Install Quandl and Install Pandas. For accessing certain datasets, or for the ability to make more than 50 API calls a day, you'll also need to setup an account with Quandl.

Importing Pricing Data

First, we want to import some pricing data from Quandl. For this script, I'm going to import daily gold pricing from the "Wiki commodity prices database", located here. 2 lines of code will get us the data in dataframe format, but to be more efficient, we're only going to pull data for the most recent year with an additional 2 lines of code, by dynamically calculating the date one year ago and setting that as the start date in the Quandl data pull function.

import quandl
import datetime

dateMinusOneYear = datetime.datetime.now() - datetime.timedelta(days=365)

goldPricing = quandl.get('COM/AU_LAM', start_date = dateMinusOneYear)

Let's see what this get's us by running the .head() command. (Keep in mind that Pandas dataframe objects are chainable, meaning that the returned object from a method can also have a method, essentially allowing indefinite data manipulation. For example: dataframe.groupby(column).sum().groupby(column).mean()...):

goldPricing.head()
Gold Python Head Function Returned DataFigure 1: Gold Python Head Function Returned Data

Adding the rolling mean column

Adding a rolling function in Pandas is easy, as shown below. Using this syntax, we're appending a column with the rolling mean. The new column, labeled "valueMa20", gives us the average price over the last 20 days, where at least 20 days of history exists. If we didn't specify min_period, the first 20 days would include previous days data only. For example, on day 2, it would only include the average of day 1 and day 2. Setting this criteria ensures we only include data when we have the appropriate number of data points.

goldPricing['valueMa20'] = goldPricing.rolling(20, min_periods = 20).mean()

Visualize

Now that we have our pricing and rolling average, let's take a look at the data by visualizing the time series using matplotlib's pyplot library.

import matplotlib.pyplot as plt
import matplotlib.dates as mdates

plt.figure(figsize=(10, 7))
plt.ylabel('Price', fontsize=16)
plt.xlabel('Date', fontsize=16)
plt.plot(goldPricing.index.date, goldPricing['value'])
plt.plot(goldPricing.index.date, goldPricing['valueMa20'])
plt.suptitle('Gold Moving Average vs Price', fontsize=32)
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m/%Y'))
plt.gca().xaxis.set_major_locator(mdates.AutoDateLocator(minticks=5,maxticks=10))
plt.gcf().autofmt_xdate()
plt.show()
Gold moving average vs priceFigure 2: Gold moving average vs price

Pinpoint crossovers

Pinpointing crossover dates can be done using an apply function in Pandas. An apply function accepts a defined function as an argument and actually passes the dataframe as a function argument. The function returns a series which we can then append to our pricing dataframe. We could of used a loop to iterate through all rows, but using an apply function is more efficient, as the function is applied using a vectorized approach, as opposed to a loop. Below, we're looking for instances where either the price was below the moving average one day and above the next or vice versa.

def crossoverCheck(goldPricing):
    return -1 if (goldPricing.value < goldPricing.valueMa20) & (goldPricing.yesterdayPrice > goldPricing.yesterdayMa20) else 1 if (goldPricing.value > goldPricing.valueMa20) & (goldPricing.yesterdayPrice < goldPricing.yesterdayMa20) else 0

goldPricing['yesterdayPrice'] = goldPricing.shift(1).value
goldPricing['yesterdayMa20'] = goldPricing.shift(1).valueMa20
goldPricing['crossover'] = goldPricing.apply(crossoverCheck, axis = 1)

goldPricing = goldPricing.drop(['yesterdayPrice', 'yesterdayMa20'], axis = 1)

Trigger an alert when there is a current day crossover

Finally, now that we have crossover data available to us, we can check the most current day to see if a crossover trade is warranted. We can do this using "iloc[-1]", which will give us the value for the very last row in a pandas dataframe. To automate this alert, we could then add an emailer function within the if statement, and schedule this task to run automatically using the task scheduler on Windows or Cron on Linux.

if goldPricing.crossover.iloc[-1] == 1:
    print 'Crossover long trade alert'
elif goldPricing.crossover.iloc[-1] == -1:
    print 'Crossover short trade alert'

The full script

import quandl
import datetime

dateMinusOneYear = datetime.datetime.now() - datetime.timedelta(days=365)

goldPricing = quandl.get('COM/AU_LAM', start_date = dateMinusOneYear)

goldPricing.head()

goldPricing['valueMa20'] = goldPricing.rolling(20, min_periods = 20).mean()

import matplotlib.pyplot as plt
import matplotlib.dates as mdates

plt.figure(figsize=(10, 7))
plt.ylabel('Price', fontsize=16)
plt.xlabel('Date', fontsize=16)
plt.plot(goldPricing.index.date, goldPricing['value'])
plt.plot(goldPricing.index.date, goldPricing['valueMa20'])
plt.suptitle('Gold Moving Average vs Price', fontsize=32)
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m/%Y'))
plt.gca().xaxis.set_major_locator(mdates.AutoDateLocator(minticks=5,maxticks=10))
plt.gcf().autofmt_xdate()
plt.show()

def crossoverCheck(goldPricing):
    return -1 if (goldPricing.value < goldPricing.valueMa20) & (goldPricing.yesterdayPrice > goldPricing.yesterdayMa20) else 1 if (goldPricing.value > goldPricing.valueMa20) & (goldPricing.yesterdayPrice < goldPricing.yesterdayMa20) else 0

goldPricing['yesterdayPrice'] = goldPricing.shift(1).value
goldPricing['yesterdayMa20'] = goldPricing.shift(1).valueMa20
goldPricing['crossover'] = goldPricing.apply(crossoverCheck, axis = 1)

goldPricing = goldPricing.drop(['yesterdayPrice', 'yesterdayMa20'], axis = 1)

if goldPricing.crossover.iloc[-1] == 1:
    print 'Crossover long trade alert'
elif goldPricing.crossover.iloc[-1] == -1:
    print 'Crossover short trade alert'