Generate monthly resample data from daily close of S&P and Russell 2000, then compute 12 month rolling forward window and lowest value in window vs. opening

callablenotesanalysis.py

# -*- coding: utf-8 -*-
"""
Opens previously downloaded historical daily closing values for 
S&P 500 and Russell 2000, computes 12 month window lows versus 
opening price for the 12 months. Processes month by month, and also computes
monthly open, close, high, low.

For use in analyzing Callable Yield Notes
Created on Mon Apr 11 23:13:15 2016

@author: Mike McGurrin
"""
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Retrieve the previously downloaded data
# (S&P 500 and Russell 2000 end of day price data)
raw_data = pd.read_pickle('triggersrawhistory.pkl')

# Convert missing data (which is days when the markets were closed)
# to the previous day's closing data
triggers = raw_data.fillna(method='pad')

# Resample to 1 month segments, captuing open, high, low, and close
# This worked out to start last day of month, but if it didn't, would need
# to adjust
trigger_monthly = triggers.resample('1M', how='ohlc')

# Need to rolling 52 week window (12 months) to look forward, which the
# Pandas rolling window function doesn't do, so reverse the order of the
# data
flipped_trigger_monthly = trigger_monthly.iloc[::-1]

# Find and add the 12 month rolling window lows and add to the data frame
flipped_trigger_monthly['SP500','52_low'] = pd.rolling_min(flipped_trigger_monthly['SP500','low'], 12)
flipped_trigger_monthly['RU2000PR','52_low'] = pd.rolling_min(flipped_trigger_monthly['RU2000PR','low'], 12)

# Compute the lowest percentage of opening value hit during the window, both
# for each index and the minimum of the two
flipped_trigger_monthly['Trigger_Value','combo'] = pd.np.fmin(flipped_trigger_monthly['SP500','52_low'] / flipped_trigger_monthly['SP500','open'], 
    flipped_trigger_monthly['RU2000PR','52_low'] / flipped_trigger_monthly['RU2000PR','open'])
flipped_trigger_monthly['Trigger_Value','SP500'] = flipped_trigger_monthly['SP500','52_low'] / flipped_trigger_monthly['SP500','open']
flipped_trigger_monthly['Trigger_Value','RU2000PR'] = flipped_trigger_monthly['RU2000PR','52_low'] / flipped_trigger_monthly['RU2000PR','open']

# Flip the data back around to run from earliest to latest
trigger_monthly = flipped_trigger_monthly.iloc[::-1]

#Plot out the low trigger versus the 70% of value trigger line
plt.figure(); trigger_monthly['Trigger_Value','combo'].plot(color='b'); plt.figure(); trigger_monthly['Trigger_Value','combo'].plot(color='b'); 
plt.axhline(y=0.7, linewidth=3, color='r')
plt.xlabel('months')
plt.ylabel('52 week low / open')
plt.show()

# Save the final results to file to open in Excel
trigger_monthly.to_csv('triggerResults.csv')

getstockdata.py

# -*- coding: utf-8 -*-
""" 
Downloads and saves Russell 2000 and S&P500 historical closing price data
Created on Sat Apr 16 15:45:39 2016

@author: Mike McGurrin
"""

import pandas.io.data as web
import datetime

#Set the date range to collect data from
start = datetime.datetime(1979, 1, 1)
end = datetime.datetime(2016, 4, 1)

# Read the data from the Federal Reserve in St. Louis
triggers = web.DataReader(["RU2000PR", "SP500"], "fred", start, end)

# Save the raw data in both pickled and csv format for further analysis
triggers.to_pickle('triggersrawhistory.pkl')
triggers.to_csv('triggersrawhistory.csv')