Created
April 17, 2016 03:33
-
-
Save ViennaMike/f953f531d5aaef071da22cdbec248794 to your computer and use it in GitHub Desktop.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -*- 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') |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -*- 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') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment