import requests
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from matplotlib import style
style.use('seaborn')
import datetime as dt
import urllib.request as req
import time
import pyodbc
import csv
import warnings
warnings.filterwarnings('ignore')

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=;'
                      'Database=;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()

t2 = pd.read_sql("select s.[sDate] as Date, s.Sym, s.[Close] as [Close Price], s.Volume, t.[Call volume], [Total Spent on Calls] from Stockdata2018 s left outer join (select count(f.sym)as [call volume], sym, sum(spent) as [Total Spent on Calls],date from FlowTotal f where date between '2017-11-29' and '2018-11-29' and sym = 'spy'and Sentiment = 'Long Call' group by date, sym )t on t.date = s.sDate and t.sym = s.sym where s.sym = 'SPY' and s.sdate between '2017-11-29' and '2018-11-29'", conn)
df2 = pd.DataFrame(t2)
df2 = df2.set_index(df2['Date'])
df2.tail()

	Date	Sym	Close Price	Volume	Call volume	Total Spent on Calls
Date
2018-11-23	2018-11-23	SPY	263.25	42807878	4.0	1161000.0
2018-11-26	2018-11-26	SPY	267.50	79981362	6.0	506000.0
2018-11-27	2018-11-27	SPY	268.40	75502441	3.0	1808000.0
2018-11-28	2018-11-28	SPY	274.58	127629633	16.0	5454000.0
2018-11-29	2018-11-29	SPY	273.98	82346420	NaN	NaN

df2[["Close Price", "Volume", "Call volume"]].plot(label = 'SPY',figsize=(12,8),\
      subplots = True, title = 'SPY Closing Price ($), Volume (Mil), & Call Volume (11/29/2017 - 11/29/2018)')

#plt.savefig("PrVoCa.png")
plt.show()

df2[["Close Price", "Total Spent on Calls", "Call volume"]].plot(label = 'SPY',figsize=(12,8), subplots = True, title = 'SPY Close Price, Total Spent on Calls($) & Call Volume')
plt.legend()
#plt.savefig("PrTsCa.png")
plt.show()

df2['Log Return'] = np.log(df2['Close Price'] / df2['Close Price'].shift(1))
df2.head()

	Date	Sym	Close Price	Volume	Call volume	Total Spent on Calls	Log Return
Date
2017-11-29	2017-11-29	SPY	262.71	77512102	5.0	2230000.0	NaN
2017-11-30	2017-11-30	SPY	265.01	127894389	26.0	2756000.0	0.008717
2017-12-01	2017-12-01	SPY	264.46	164390902	15.0	1752000.0	-0.002078
2017-12-04	2017-12-04	SPY	264.14	94040560	1.0	194000.0	-0.001211
2017-12-05	2017-12-05	SPY	263.19	77994544	NaN	NaN	-0.003603

df2[['Close Price', 'Log Return']].plot(figsize=(12,8), subplots = True, title = 'Close Price vs. Log Returns')
plt.legend()
#plt.savefig("CpLog.png")
plt.show()

df2[['Log Return', 'Total Spent on Calls', 'Call volume']].plot(figsize=(12,8), subplots = True, title = 'Log Returns, Total Spent on Calls & Call Volume')
#plt.savefig("LrTsCv.png")
plt.show()

df3 = df2[['Close Price']] 
df3['Simple Moving Average 10'] = df3['Close Price'].rolling(window = 10).mean()
df3['Simple Moving Average 25'] = df3['Close Price'].rolling(window = 25).mean()
df3.plot(figsize=(15,8), title = "SPY Simple Moving Averages 10 Dat & 25 Day")
#plt.savefig("sma1025.png")
plt.show()

df3['SMA 10'] = df3['Close Price'].rolling(window = 10).mean()
df3['SMA 25'] = df3['Close Price'].rolling(window = 25).mean()
df3.head()
df3.dropna(inplace = True)

df3['Position'] = np.where(df3['SMA 10'] > df3['SMA 25'], 1, -1)
ax = df3[['Close Price','SMA 10','SMA 25', 'Position']].plot(figsize=(15,8),secondary_y = 'Position', title = "SPY SMA 10/25 Cross-Over")
ax.get_legend().set_bbox_to_anchor((.12, .95))
#plt.savefig("smaxo.png")
plt.show()

df2[['Close Price', 'Volume']].plot(figsize=(12,8), subplots = True, title = 'SPY Closing Price & Volume')
#plt.savefig("CpVo.png")
plt.show()