Spaces:
Sleeping
Sleeping
File size: 5,739 Bytes
86fa8c7 e55c584 86fa8c7 e55c584 86fa8c7 acdf71a 86fa8c7 acdf71a 86fa8c7 acdf71a 86fa8c7 835ace6 86fa8c7 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
import pandas as pd
import pandas_datareader as pdr
import yfinance as yf
import datetime
# from datasets import load_dataset
from sqlalchemy import create_engine
import os
from getDailyData import data_start_date
# from dotenv import load_dotenv
# Load environment variables from the .env file
# load_dotenv()
def get_intra(periods_30m = 1):
'''
Method to get historical 30 minute data and append live data to it, if exists.
'''
engine = create_engine(
f"mysql+mysqldb://{os.getenv('DATABASE_USERNAME')}:" \
f"{os.getenv('DATABASE_PASSWORD')}@{os.getenv('DATABASE_HOST')}/" \
f"{os.getenv('DATABASE')}?ssl_ca=ca-certificates.crt&ssl_mode=VERIFY_IDENTITY"
)
query = f'''SELECT
spx30.Datetime AS Datetime,
spx30.Open AS Open30,
spx30.High AS High30,
spx30.Low AS Low30,
spx30.Close AS Close30,
vix30.Open AS Open_VIX30,
vix30.High AS High_VIX30,
vix30.Low AS Low_VIX30,
vix30.Close AS Close_VIX30,
vvix30.Open AS Open_VVIX30,
vvix30.High AS High_VVIX30,
vvix30.Low AS Low_VVIX30,
vvix30.Close AS Close_VVIX30
FROM
SPX_full_30min AS spx30
LEFT JOIN
VIX_full_30min AS vix30 ON spx30.Datetime = vix30.Datetime AND vix30.Datetime > '{data_start_date}'
LEFT JOIN
VVIX_full_30min AS vvix30 ON spx30.Datetime = vvix30.Datetime AND vvix30.Datetime > '{data_start_date}'
WHERE
spx30.Datetime > '{data_start_date}'
'''
# spx30 = pd.read_sql_query(f'SELECT * FROM SPX_full_30min WHERE Datetime > {data_start_date}', con=engine)
# vix30 = pd.read_sql_query(f'SELECT * FROM VIX_full_30min WHERE Datetime > {data_start_date}', con=engine)
# vvix30 = pd.read_sql_query(f'SELECT * FROM VVIX_full_30min WHERE Datetime > {data_start_date}', con=engine)
# dfs = []
df_30m = pd.read_sql_query(sql=query, con=engine.connect())
df_30m['Datetime'] = df_30m['Datetime'].dt.tz_localize('America/New_York')
df_30m = df_30m.set_index('Datetime',drop=True)
# for fr in [spx30, vix30, vvix30]:
# # fr['Datetime'] = fr['Datetime'].apply(lambda x: datetime.datetime.strptime(x[:-6], dt_format))
# fr['Datetime'] = fr['Datetime'].dt.tz_localize('America/New_York')
# fr = fr.set_index('Datetime')
# fr['Open'] = pd.to_numeric(fr['Open'])
# fr['High'] = pd.to_numeric(fr['High'])
# fr['Low'] = pd.to_numeric(fr['Low'])
# fr['Close'] = pd.to_numeric(fr['Close'])
# dfs.append(fr[['Open','High','Low','Close']])
# df_30m = pd.concat(dfs, axis=1)
# df_30m.columns = [
# 'Open30',
# 'High30',
# 'Low30',
# 'Close30',
# 'Open_VIX30',
# 'High_VIX30',
# 'Low_VIX30',
# 'Close_VIX30',
# 'Open_VVIX30',
# 'High_VVIX30',
# 'Low_VVIX30',
# 'Close_VVIX30'
# ]
# Get incremental date
last_date = df_30m.index.date[-1]
last_date = last_date + datetime.timedelta(days=1)
# Get incremental data for each index
spx1 = yf.Ticker('^GSPC')
vix1 = yf.Ticker('^VIX')
vvix1 = yf.Ticker('^VVIX')
yfp = spx1.history(start=last_date, interval='30m')
yf_vix = vix1.history(start=last_date, interval='30m')
yf_vvix = vvix1.history(start=last_date, interval='30m')
if len(yfp) > 0:
# Convert indexes to EST if not already
for _df in [yfp, yf_vix, yf_vvix]:
if (_df.index.tz.zone != 'America/New_York') or (type(_df.index) != pd.DatetimeIndex):
_df['Datetime'] = pd.to_datetime(_df.index)
_df['Datetime'] = _df['Datetime'].dt.tz_convert('America/New_York')
_df.set_index('Datetime', inplace=True)
# Concat them
df_inc = pd.concat([
yfp[['Open','High','Low','Close']],
yf_vix[['Open','High','Low','Close']],
yf_vvix[['Open','High','Low','Close']]
], axis=1)
df_inc.columns = df_30m.columns
df_inc = df_inc.loc[
(df_inc.index.time >= datetime.time(9,30)) & (df_inc.index.time < datetime.time(16,00))
]
df_30m = pd.concat([df_30m, df_inc])
else:
df_30m = df_30m.copy()
df_30m = df_30m.loc[
(df_30m.index.time >= datetime.time(9,30)) & (df_30m.index.time < datetime.time(16,00))
]
df_30m['dt'] = df_30m.index.date
df_30m = df_30m.groupby('dt').head(periods_30m)
df_30m = df_30m.set_index('dt',drop=True)
df_30m.index.name = 'Datetime'
df_30m['SPX30IntraPerf'] = (df_30m['Close30'] / df_30m['Close30'].shift(1)) - 1
df_30m['VIX30IntraPerf'] = (df_30m['Close_VIX30'] / df_30m['Close_VIX30'].shift(1)) - 1
df_30m['VVIX30IntraPerf'] = (df_30m['Close_VVIX30'] / df_30m['Close_VVIX30'].shift(1)) - 1
opens_intra = df_30m.groupby('Datetime')[[c for c in df_30m.columns if 'Open' in c]].head(1)
highs_intra = df_30m.groupby('Datetime')[[c for c in df_30m.columns if 'High' in c]].max()
lows_intra = df_30m.groupby('Datetime')[[c for c in df_30m.columns if 'Low' in c]].min()
closes_intra = df_30m.groupby('Datetime')[[c for c in df_30m.columns if 'Close' in c]].tail(1)
spx_intra = df_30m.groupby('Datetime')['SPX30IntraPerf'].tail(1)
vix_intra = df_30m.groupby('Datetime')['VIX30IntraPerf'].tail(1)
vvix_intra = df_30m.groupby('Datetime')['VVIX30IntraPerf'].tail(1)
df_intra = pd.concat([opens_intra, highs_intra, lows_intra, closes_intra, spx_intra, vix_intra, vvix_intra], axis=1)
return df_intra
|