def get_file_path(ticker):
"""Get data path for the ticker (Also returns the path for the dummy reference).
:param ticker: str - Customized ticker
:return: str - Path
"""
root = oc.cfg['default']['data']
if ticker == 'Reference':
path = os.path.join(root, 'Reference.txt')
else:
ft = oci.get_future_type(ticker)
path = os.path.join(root, 'Daily', ticker[0:2], str(ft),
'{}.txt'.format(ticker))
return path
def contract_position(symbol, maturity, position):
"""To be used in the positions sheet to find out the outright positions
:param symbol: String symbol - Customized ticker
:param maturity: String maturity (short format: U7) of the outright where we wan to get the position
:param position: Int Position on the symbol
:return: Int Number of lots on the specified outright (or empty string if no position)
"""
# Check if the maturity is in the maturity list
try:
idx = oci.get_maturities(symbol, True).index(maturity)
except ValueError:
return ''
# Maturity is in the list => Return the weight
ft = oci.get_future_type(symbol)
weights = oci.type_definition[ft]['weights']
return weights[idx] * position
def get_market_df(ticker, start_date=None, end_date=None):
"""From a ticker get the data from the data repository and store into a dataframe
:param ticker: Customized ticker
:param start_date: Optional parameter start date
:param end_date: Optional parameter start date
:return: Dataframe containing the data or None if there's nothing in the database
"""
log = logging.getLogger(__name__)
log.debug('Get dataframe for: {}'.format(ticker))
file = get_file_path(ticker)
if os.path.exists(file):
fields = ['Open', 'High', 'Low', 'Close', 'Volume', 'OI']
ver = oc.cfg['mode']['version']
if ver == '1':
# Read Data from csv file
df = pd.read_csv(file,
sep=',',
parse_dates=True,
header=None,
names=fields)
else:
# Read Data from database
it = oci.get_future_type(ticker)
ticker = 'Daily-{}-{}-{}'.format(ticker[0:2], str(it),
format(ticker))
df = get_from_database(ticker)
# Remove week end data
df = df[df.index.dayofweek < 5]
# Check if data needs to be truncated
if start_date is not None:
df = df[df.index >= start_date]
if end_date is not None:
df = df[df.index <= end_date]
# Check if missing data
if df.isnull().values.any():
log.warning(
'Missing data in: {} - Count: {}, please check!'.format(
ticker,
df.isnull().sum().sum()))
return df
else:
return None
def save_market_df(ticker, df):
""" Save a dataframe to the datastore, this is to be used after a dataframe has been downloaded.
:param ticker: String customized ticker
:param df: Dataframe data to be saved to the file
"""
log = logging.getLogger(__name__)
log.debug('Save dataframe for {}'.format(ticker))
it = oci.get_future_type(ticker)
file = os.path.join(oc.cfg['default']['data'], 'Daily', ticker[0:2],
str(it), '{}.txt'.format(ticker))
if os.path.exists(file):
ndf = get_market_df(ticker)
# Filter out all data prior to first date of df
ndf = ndf[ndf.index < df.index[0]]
# Concatenate DataFrames
df = pd.concat([ndf, df])
else:
# Create folders if they don't exist
path_ticker = os.path.join(oc.cfg['default']['data'], 'Daily',
ticker[0:2])
if not os.path.exists(path_ticker):
os.mkdir(path_ticker)
path_type = os.path.join(oc.cfg['default']['data'], 'Daily',
ticker[0:2], str(it))
if not os.path.exists(path_type):
os.mkdir(path_type)
# Change Volume and OI to Integer
df['Volume'] = df['Volume'].astype(int)
df['OI'] = df['OI'].astype(int)
ver = oc.cfg['mode']['version']
if ver == '1':
# Save to file
df.to_csv(file, header=False)
else:
ticker = 'Daily-{}-{}-{}'.format(ticker[0:2], str(it), format(ticker))
# Save to database
save_to_database(ticker, df)
def inspect_files(stems=None, future_type=None):
"""File inspection. Go through all the datafiles in the Daily folder and check for missing values.
:param stems: list - List of Futures to inspect
:param future_type: enum FutureType - Type of Futures to inspect (Outright, Spread, etc...)
:return: dict - Problematic tickers to be checked
"""
log = logging.getLogger(__name__)
inspect = {}
for root, dirs, files in os.walk(op.join(oc.cfg['default']['data'], 'Daily')):
for f in files:
ticker = f.split('.')[0]
if stems is not None and ticker[0:2] not in stems:
continue
if future_type is not None and oci.get_future_type(ticker) != future_type:
continue
df = odu.get_market_df(ticker)
if df is not None:
missing = df.isnull().sum().sum()
if missing > 0:
inspect[ticker] = missing
return inspect
def get_ohlcv_data(ticker, interval, start, end):
"""Download OHLCV data from Reuters, can either be daily or minute data
:param ticker: String customized ticker
:param interval: Interval for the data download (daily or minute)
:param start: String start date
:param end: String end date
:return: Dataframe OHLCV format
"""
log = logging.getLogger(__name__)
ric = oci.convert_ticker(ticker, 'Reuters')
log.info('Download data for: {} - RIC: {}'.format(ticker, ric))
try:
# Directly prints to the console if there's an issue - TODO: how to catch this?
df = ek.get_timeseries(
ric,
start_date=start,
end_date=end,
interval=interval,
fields=['HIGH', 'LOW', 'OPEN', 'CLOSE', 'VOLUME'])
df.index.names = [None]
df.columns = ['High', 'Low', 'Open', 'Close', 'Volume']
df = df[['Open', 'High', 'Low', 'Close', 'Volume']]
# Remove weekend days
df = df[df.index.dayofweek < 5]
# Settlement Fix (old spreads don't have settlements so need to get the close)
close = pd.isnull(df['Close'])
if len(close[close]) > 1 and oci.get_future_type(
ticker) == oci.FutureType.Spread:
df = fix_settlement(ticker, df)
# Fill NAs
df['Open'].fillna(df['Close'], inplace=True)
df['High'].fillna(df['Close'], inplace=True)
df['Low'].fillna(df['Close'], inplace=True)
df['Volume'].fillna(0, inplace=True)
# Add OI: Only for outrights and if the outright has traded
vdf = df[df['Volume'] > 0]
if len(vdf) > 0 and oci.get_future_type(
ticker) == oci.FutureType.Outright:
# Have to do this as the OI would shift it we start early (and there's no OI for some days)
first_trade = str(vdf.index[0])[0:10]
df = add_oi(ticker, df, first_trade,
end) if interval is 'daily' else df
else:
df['OI'] = pd.Series([0 for _ in range(len(df.index))],
index=df.index)
# Check if first line has NAs and discard them
# TODO: Move this in a function?
if oci.get_future_type(
ticker) == oci.FutureType.Outright and len(df) > 20:
drop = []
for i in range(20):
if df.iloc[i].isnull().sum():
drop.append(i)
if drop:
log.warning('Dropping lines: {}'.format(drop))
df = df.drop(df.index[drop])
except (TypeError, IndexError, req.HTTPError, ek.eikonError.EikonError,
json.decoder.JSONDecodeError) as e:
log.error('Problem downloading data for {}, see: {}'.format(ticker, e))
df = None
return df
def test_get_future_type(ticker):
ft = oci.get_future_type(ticker)
print(ft)
assert isinstance(ft, oci.FutureType)