from datetime import datetime
import pandas as pd
import pandas.io.data
import numpy as np
import scipy as sp
import matplotlib.pyplot as plt
pd.set_option('max_columns', 50)
%matplotlib inline

run_console = False
if run_console:
    %qtconsole

update_from_web = False
if update_from_web:
    today = datetime.now()
    # http://research.stlouisfed.org/fred2/series/DJIA/downloaddata
    # http://measuringworth.com/DJA/
    # http://www.eia.gov/dnav/pet/hist/LeafHandler.ashx?n=PET&s=RWTC&f=D
    # http://www.eia.gov/dnav/pet/pet_pri_spt_s1_d.htm

# From http://measuringworth.com/DJA/
dow = pd.read_csv('DJA.csv', skiprows=3, index_col='Date', parse_dates=True, usecols=['Date','DJIA'])
# From  http://research.stlouisfed.org/fred2/series/DJIA/downloaddata
# dow1 = pd.read_excel('DJIA.xls', 'DJIA', skiprows=23, index_col='DATE', parse_dates=True, usecols=['DATE','VALUE'])

# dow['5/4/1970':].plot(figsize=(10, 10))

dow[np.isnan(dow['DJIA'])]

dow.dropna(inplace=True)

dow.tail()

wti = pd.read_csv('WTI.csv', skiprows=6, index_col='Day', parse_dates=True, usecols=['Day','Cushing OK WTI Spot Price FOB $/bbl'])
# From http://www.eia.gov/dnav/pet/pet_pri_spt_s1_d.htm
# wti1 = pd.read_excel('PET_PRI_SPT_S1_D.xls', 'Data 1', skiprows=2, index_col='Date', parse_dates=True, usecols=['Date','Cushing, OK WTI Spot Price FOB (Dollars per Barrel)'])

wti.columns = ['WTI']
wti.index.names = ['Date']

df = pd.DataFrame()
df = df.combineAdd(dow)
df = df.combineAdd(wti)

df.info()

df.plot(subplots=True, figsize=(10, 10), sharex=True)

df['19870102':].plot(subplots=True, figsize=(10, 10), sharex=True)

# df.diff().plot(xlim=datetime(2006,01,02), figsize=(10, 10))

# df.pct_change().plot(subplots=True, xlim=datetime(2006,01,02), figsize=(10, 10), sharex=True)

# df.pct_change().plot(subplots=True, figsize=(10, 10), xlim=datetime(1986,01,02), sharex=True)

# pd.rolling_max(df,100).plot(subplots=True, figsize=(10, 10), xlim=datetime(1999,01,02), sharex=True)

# pd.rolling_max(df['WTI'],100).plot(figsize=(10, 10), xlim=datetime(1999,01,02))

# pd.rolling_min(df['DJIA'],100).plot(figsize=(10, 10), xlim=datetime(1999,01,02))

df['DOW_WTI30'] = df.DJIA[df.WTI>30]
df['DOW_WTI70'] = df.DJIA[df.WTI>70]
df['DOW_WTI100'] = df.DJIA[df.WTI>100]

df['1987':][df.columns.drop('WTI')].plot(figsize=(10, 10))

df['2007':][['DJIA','DOW_WTI100']].plot(color=('b','r'), figsize=(10, 10))

df1 = df.drop(['DOW_WTI30', 'DOW_WTI70', 'DOW_WTI100'],axis=1)

plt.figure()
df1['1995':].plot(secondary_y=['WTI'], figsize=(10, 10))