def histDataReq(ticker, field, startTime, endTime, freq='DAILY', overrides=[]):
data = {
"ticker": ticker,
"field": field,
"startDate": startTime,
"endDate": endTime,
"periodicitySelection": freq,
"overrides": overrides
}
for each in data["ticker"]:
temp_df = LocalTerminal.get_historical(each,
data["field"][0],
start=data["startDate"],
end=data["endDate"],
period='DAILY')
temp_df = temp_df.as_frame()
temp_df = temp_df.swaplevel(0, 1, axis=1)
temp_df = temp_df.stack()
temp_df.reset_index(inplace=True)
temp_df = temp_df[['date', data['field'][0]]]
temp_df.columns = ['date', 'mkt_value']
return temp_df
def get_security_hist(tickers,
st_date,
ed_date,
adj_mode=AdjMode.PX_LAST,
additional_fields=None,
periodicity=Periodicity.MONTHLY):
"""
This function is a wrapper of the LocalTerminal.get_historical function inside tia.
:param tickers: The given security list
:param st_date: the starting point of the period
:param ed_date: the end point of the period
:param adj_mode: dvd adjusted mode. If PX_LAST is supplied, whatever Bloomberg setting will be applied. Check DPDF {<GO>} for more details
:param additional_fields: A list of additional fields that would like to be queried
:param periodicity: Frequency of the data that will be returned
:return: the data got from Bloomberg
"""
if additional_fields is None:
additional_fields = []
if adj_mode == AdjMode.PX_LAST:
field = ["PX_LAST"]
elif adj_mode == AdjMode.NET:
field = ["TOT_RETURN_INDEX_NET_DVDS"]
else:
field = ["TOT_RETURN_INDEX_GROSS_DVDS"]
hist = LocalTerminal.get_historical(tickers,
field + additional_fields,
start=st_date,
end=ed_date,
period=periodicity.name)
return hist.as_frame()
def downloadData(self, tw):
self.tw = tw
self.d = pd.datetools.BDay(-self.tw).apply(pd.datetime.now())
self.m = pd.datetools.BMonthBegin(-2).apply(pd.datetime.now())
self.response = LocalTerminal.get_historical(self.idx, ['px_last'],
start=self.d)
return self.response.as_frame()
def bdh_ohlc(symbol,
start,
end=datetime.date.today(),
addfield=None,
addfieldname=None):
"""
download single security data from bloomberg.
by default downlodas OHLC and volumes, can add additional fields
"""
from tia.bbg import LocalTerminal
import pandas as pd
import sys
fields = ['PX_OPEN', 'PX_HIGH', 'PX_LOW', 'PX_LAST', 'EQY_WEIGHTED_AVG_PX']
fieldnames = ['Open', 'High', 'Low', 'Close', 'VWAP']
if (addfield == None):
if (addfieldname == None):
pass
else:
sys.exit(
"additional fields and additoinal field names don't match!")
else:
field2 = addfield
fieldname2 = addfieldname
fields = fields + field2
fieldnames = fieldnames + fieldname2
data = LocalTerminal.get_historical(symbol, fields, start, end)
data = data.as_frame()
data.columns = fieldnames
#fill the nan
return data
def bbg_load_ts(bbg_tckr, bbg_flds, start='1/1/1955', end='TODAY'):
if end == 'TODAY':
end = datetime.now().strftime("%m/%d/%Y")
def replace_australia(flds):
def rep(f):
return 'fut_norm_px' if f == 'px_last' else f
if bbg_tckr.startswith('XM') or bbg_tckr.startswith('YM'):
return [rep(f) for f in flds]
else:
return flds
def revert_fields(flds):
def replace_norm(f):
return 'px_last' if f == 'fut_norm_px' else f
return [replace_norm(f) for f in flds]
bbg_flds = replace_australia(bbg_flds)
res = LocalTerminal.get_historical(bbg_tckr,
bbg_flds,
start=start,
end=end)
df = res.as_frame()[bbg_tckr]
df.columns = revert_fields(df.columns)
return df
def downloadData(self, tw):
self.tw = tw
self.d = pd.datetools.BDay(-self.tw).apply(pd.datetime.now())
self.m = pd.datetools.BMonthBegin(2).apply(pd.datetime.now())
self.prices = LocalTerminal.get_historical(self.assets, ['PX_LAST'],
start=self.d)
self.names = LocalTerminal.get_reference_data(idx, ['SHORT_NAME'])
return True
def getFXfromBloomberg(self):
self.USDCLP = LocalTerminal.get_historical(
['CLFXDOOB Index'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['CLFXDOOB Index']['PX_LAST'][0]
self.MXNCLP = LocalTerminal.get_historical(
['MXNCLP Curncy'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['MXNCLP Curncy']['PX_LAST'][0]
self.COPCLP = LocalTerminal.get_historical(
['COPCLP Curncy'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['COPCLP Curncy']['PX_LAST'][0]
self.PENCLP = LocalTerminal.get_historical(
['PENCLP Curncy'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['PENCLP Curncy']['PX_LAST'][0]
self.BRLCLP = LocalTerminal.get_historical(
['BRLCLP Curncy'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['BRLCLP Curncy']['PX_LAST'][0]
self.CLFCLP = LocalTerminal.get_historical(
['CLF Curncy'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['CLF Curncy']['PX_LAST'][0]
self.EURCLP = LocalTerminal.get_historical(
['EURCLP Curncy'], ['PX_LAST'],
start=dt.datetime.now() -
dt.timedelta(days=1)).as_frame()['EURCLP Curncy']['PX_LAST'][0]
def bloomberg_prices(tickers, start_dt, frequency, names):
"""
Get Bloomberg price data
"""
df=pd.DataFrame()
rep1 = LocalTerminal.get_historical([tickers],['PX_LAST'], start=start_dt,
period=frequency)
df=rep1.as_frame()
df.columns = names
return df
def downloadData(self,assets,tw):
self.tw = tw
self.idx = assets
self.d = pd.datetools.BDay(-self.tw).apply(pd.datetime.now())
self.m = pd.datetools.BMonthBegin(-2).apply(pd.datetime.now())
self.response = LocalTerminal.get_historical(self.idx, ['px_last'], start=self.d)
data = self.response
symbols = []
for i in assets:
symbols.append(LocalTerminal.get_reference_data( i, 'ID_BB_SEC_NUM_DES').as_frame()['ID_BB_SEC_NUM_DES'][0])
return data, symbols
def _simu_memb_weight(ticker_list, as_of_date):
mkt_cap = LocalTerminal.get_historical(ticker_list,
"CUR_MKT_CAP",
as_of_date,
as_of_date,
EQY_FUND_CRNCY='EUR').as_frame()
mkt_cap = mkt_cap.stack(level=0)
mkt_cap.index = mkt_cap.index.droplevel(0)
mkt_cap['Weight_est'] = mkt_cap['CUR_MKT_CAP'] / mkt_cap[
'CUR_MKT_CAP'].sum() * 100
return mkt_cap
def get_data_blp_historical(self):
'''imports historical data from bbg api, converts to dataframe '''
formattedPair = self.pair #+str(' Curncy')##removed this as what if you want a Comdty or Index?
resp = LocalTerminal.get_historical(formattedPair, self.fields,
self.startDate, self.endDate,
self.period)
df = resp.as_frame()
#use below to start at second row , to get rid of the extra column index ['XYZ Curncy'] title on top
#else df MultiIndex(levels=[['EURUSD Curncy'], ['PX_OPEN', 'PX_LAST', 'PX_LOW', 'PX_HIGH']]
df.columns = df.columns.get_level_values(1)
return df
def download(ticker):
# takes a list
df = LocalTerminal.get_historical(
ticker, fields, start, end, period="DAILY"
).as_frame()
df.columns = df.columns.droplevel()
df = df.rename(
columns={
"OPEN": "open",
"HIGH": "high",
"LOW": "low",
"LAST PRICE": "close",
"VOLUME": "volume",
}
).dropna()
ticker = ticker.replace("/", ".")
df.to_csv(fr"{output_dir}/{ticker}.csv")
import plotly.figure_factory as ff
from fredapi import Fred
import credentials
fred = credentials.fred
#set the script start time
start_time = datetime.now()
date_now = "{:%m_%d_%Y}".format(datetime.now())
start_date = '01/01/1950'
end_date = "{:%m/%d/%Y}".format(datetime.now())
IDs = ['GDP CYOY Index', 'CPI YOY Index'] #'SPXT Index']
fields = ['LAST PRICE']
df = LocalTerminal.get_historical(IDs, fields, start_date,
end_date).as_frame() #period = 'QUARTERLY',
#non_trading_day_fill_option = 'ALL_CALENDAR_DAYS',
#non_trading_day_fill_method = 'PREVIOUS_VALUE').as_frame()
df.columns = df.columns.droplevel(-1)
df = df.resample('Q').mean()
df = df.dropna()
df['gdp_ror'] = df['GDP CYOY Index'].pct_change()
df['cpi_ror'] = df['CPI YOY Index'].pct_change()
df['gdp_dir'] = df.apply(lambda x: 1 if x['gdp_ror'] > 0 else(-1 if \
x['gdp_ror'] < 0 else 0), axis = 1)
df['gdp_dir'] = df['gdp_dir'].replace(to_replace=0, method='ffill')
df['cpi_dir'] = df.apply(lambda x: 1 if x['cpi_ror'] > 0 else(-1 if \
dd2here = prices - max2here
return np.round(dd2here.min(), 3)
# set dates, securities, and fields
start_date = '01/01/2005'
end_date = "{:%m/%d/%Y}".format(datetime.now())
IDs = ['CVALIM 8.25 CORP', 'CVAL IM EQUITY']
price_fields = ['LAST PRICE', 'HIGH', 'LOW']
ref_data = ['ID_ISIN', 'CPN', 'CPN_FREQ', 'CRNCY', 'SECURITY_NAME',
'NXT_CALL_DT', 'ISSUE_DT', 'COMPANY_CORP_TICKER']
df = LocalTerminal.get_historical(IDs, 'LAST PRICE', start_date, end_date,
period = 'DAILY').as_frame()
df.columns = df.columns.droplevel(-1)
df = df.fillna(method = 'ffill')
df = df.dropna()
#for q in IDs:
# name = list(q.values())[1]
# code = list(q.values())[0]
#
# d[name] = LocalTerminal.get_historical(code, price_fields, start_date, end_date, period = 'DAILY').as_frame()
# d[name].columns = d[name].columns.droplevel()
# d[name] = d[name].append(pd.DataFrame(data = {'LAST PRICE':100, 'HIGH':100, 'LOW':100}, index=[(d[name].index[0] + timedelta(days = -1))])).sort_index()
# d[name] = d[name].fillna(method = 'ffill')
#
# m[name] = LocalTerminal.get_reference_data(code, ref_data).as_frame()
banner('ReferenceDataRequest: single security, multi-field (with bulk), frame response')
response = LocalTerminal.get_reference_data('eurusd curncy', ['px_last', 'fwd_curve'])
print (response.as_map())
rframe = response.as_frame()
print (rframe.columns)
# show frame within a frame
print (rframe.ix[0, 'fwd_curve'].tail())
banner('ReferenceDataRequest: multi security, multi-field, bad field')
response = LocalTerminal.get_reference_data(['eurusd curncy', 'msft us equity'], ['px_last', 'fwd_curve'],
ignore_field_error=1)
print (response.as_frame()['fwd_curve']['eurusd curncy'])
banner('HistoricalDataRequest: multi security, multi-field, daily data')
response = LocalTerminal.get_historical(['eurusd curncy', 'msft us equity'], ['px_last', 'px_open'], start=d)
print (response.as_map())
print (response.as_frame().head(5))
banner('HistoricalDataRequest: multi security, multi-field, weekly data')
response = LocalTerminal.get_historical(['eurusd curncy', 'msft us equity'], ['px_last', 'px_open'], start=m,
period='WEEKLY')
print ('--------- AS SINGLE TABLE ----------')
print (response.as_frame().head(5))ba
#
# HOW TO
#
# - Retrieve an fx vol surface: BbgReferenceDataRequest('eurusd curncy', 'DFLT_VOL_SURF_MID')
# - Retrieve a fx forward curve: BbgReferenceDataRequest('eurusd curncy', 'FWD_CURVE')
# - Retrieve dividends: BbgReferenceDataRequest('csco us equity', 'BDVD_PR_EX_DTS_DVD_AMTS_W_ANN')
print(response.as_map())
rframe = response.as_frame()
print(rframe.columns)
# show frame within a frame
print(rframe.ix[0, "fwd_curve"].tail())
banner("ReferenceDataRequest: multi security, multi-field, bad field")
response = LocalTerminal.get_reference_data(
["eurusd curncy", "msft us equity"],
["px_last", "fwd_curve"],
ignore_field_error=1,
)
print(response.as_frame()["fwd_curve"]["eurusd curncy"])
banner("HistoricalDataRequest: multi security, multi-field, daily data")
response = LocalTerminal.get_historical(
["eurusd curncy", "msft us equity"], ["px_last", "px_open"], start=d)
print(response.as_map())
print(response.as_frame().head(5))
banner("HistoricalDataRequest: multi security, multi-field, weekly data")
response = LocalTerminal.get_historical(
["eurusd curncy", "msft us equity"],
["px_last", "px_open"],
start=m,
period="WEEKLY",
)
print("--------- AS SINGLE TABLE ----------")
print(response.as_frame().head(5))
#
# HOW TO
##This uses 'tia' package bloomberg api wrapper and utils from https://github.com/bpsmith/tia
##Installed at C:\Python27\Lib\site-packages
##Imports for simple daily data query
from tia.bbg import LocalTerminal
import pandas as pd
#Other imports for all tia utils
#import datetime
#import matplotlib.pyplot as plt
#Simple bloomberg data download to responce object
resp = LocalTerminal.get_historical('INTC US EQUITY', ['PX_OPEN', 'PX_LAST'], start='1/1/2014', end='3/1/2014')
#View data in terminal
#resp.as_map()
#Data as data frame
resp_frame = resp.as_frame()
#To csv
resp_frame.to_csv('C:\Program Files\StrataStack\Bberg_Python_out\histTest.csv')#, sep='\t')
fx = ['EURUSD Curncy', 'GBPUSD Curncy']
q = {
'open_end': oe,
#'booty': oe
}
fields_hist = ['FUND_TOTAL_ASSETS']
fields_ref = ['FUND_TOTAL_ASSETS_CRNCY']
fields_fx = ['LAST_PRICE']
d_fx = LocalTerminal.get_historical(
fx,
fields_fx,
start_date,
end_date,
period='DAILY',
non_trading_day_fill_option='ALL_CALENDAR_DAYS',
non_trading_day_fill_method='PREVIOUS_VALUE').as_frame()
d_fx.columns = d_fx.columns.droplevel(-1)
d_fx = d_fx.rename(columns={'EURUSD Curncy': 'EUR', 'GBPUSD Curncy': 'GBP'})
d_fx['USD'] = 1.0
d = {} #dict of original dataframes per asset class
m = {} #dict of ref data
b = {} #list of lists for asset diffs
p = {} #list of list for $asset diffs
f = {} #simplified asset dicts
r = {} #daily rate of change dict
u = {} #weekly rate of change
pq = {} #monthly rate of change
def bloomberg(self):
securities = self.secList()
df = self.frame()
rundate = get_rundate(rundate=df)
historical_data = LocalTerminal.get_historical(securities,
['PX_HIGH', 'PX_LOW'],
start=rundate,
end=rundate).as_frame()
historical_bval = LocalTerminal.get_historical(
securities, ['PX_ASK', 'PX_BID'],
start=rundate,
end=rundate,
PRICING_SOURCE='BVAL').as_frame()
historical_bval = historical_bval.transpose().reset_index()
historical_data = historical_data.transpose().reset_index()
frames = [historical_bval, historical_data]
frames = pd.concat(frames)
hd = historical_data
hb = historical_bval
hdcols = ['bond', 'pcs', 'price']
hd.columns = hdcols
high = hd[hd['pcs'] == 'PX_HIGH']
high = high[['bond', 'price']]
high.columns = ['bond', "PX_HIGH"]
low = hd[hd['pcs'] == 'PX_LOW']
low = low[['bond', 'price']]
low.columns = ['bond', "PX_LOW"]
hbcols = ['bond', 'pcs', 'price']
hb.columns = hbcols
bid = hb[hb['pcs'] == 'PX_BID']
bid = bid[['bond', 'price']]
bid.columns = ['bond', "PX_BID"]
ask = hb[hb['pcs'] == 'PX_ASK']
ask = ask[['bond', 'price']]
ask.columns = ['bond', "PX_ASK"]
x = pd.merge(df,
high,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = pd.merge(x,
low,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = pd.merge(x,
bid,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = pd.merge(x,
ask,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = x[[
'As of Date', 'Ticket Number', 'Security Description',
'Trader Name', 'Buy/Sell', 'TRADE FEED TRADE AMOUNT',
'Trade price', 'TBLT Ticket Type', 'Cusip Number',
'Benchmark Cusip or Bloomberg', 'Parskeyeable Description',
'Security Type', 'Trader Login', 'Sales Login', 'Par Amount',
'Issue Date', 'Principal', 'Market Sector Description',
'Identifier', 'Counterparty', 'Master Account Long Name',
'Master Account', 'Benchmark', 'Z-Spread', 'Benchmark Price',
'Factor', 'PX_ASK', 'PX_BID', 'PX_HIGH', 'PX_LOW'
]]
bestEx = x
bestEx['inside'] = np.where(
bestEx['Buy/Sell'] == 'B',
np.where(bestEx['Trade price'] > bestEx['PX_LOW'], 'inside',
'outside'),
np.where(bestEx['Trade price'] < bestEx['PX_HIGH'], 'inside',
'outside'))
bestEx['insideBidAsk'] = np.where(
bestEx['PX_HIGH'].astype(str) ==
'nan', #if this is true look for Buy sell code
np.where(
bestEx['Buy/Sell'] == 'B',
np.where(bestEx['Trade price'] > bestEx['PX_BID'], 'inside',
'outside'),
np.where(bestEx['Trade price'] < bestEx['PX_ASK'], 'inside',
'outside')),
bestEx['inside'])
bestEx['PX_HIGH_LOW_DIFF_%'] = np.where(
bestEx['Buy/Sell'] == 'B',
((bestEx['Trade price'] - bestEx['PX_LOW']) / bestEx['PX_LOW']) *
100,
((bestEx['Trade price'] - bestEx['PX_HIGH']) / bestEx['PX_HIGH']) *
100)
bestEx['PX_BID_ASK_DIFF_%'] = np.where(
bestEx['Buy/Sell'] == 'B',
((bestEx['Trade price'] - bestEx['PX_BID']) / bestEx['PX_BID']) *
100,
((bestEx['Trade price'] - bestEx['PX_ASK']) / bestEx['PX_ASK']) *
100)
return bestEx
return historical_data
def bloomberg(self):
securities = self.secList()
df = self.frame()
rundate = get_rundate(rundate=df)
historical_data = LocalTerminal.get_historical(
securities, ['PX_HIGH', 'PX_LOW'],
start=self.rundate,
end=self.rundate,
ignore_security_error=1).as_frame()
historical_data = historical_data.transpose().reset_index()
historical_bval = LocalTerminal.get_historical(
securities, ['YLD_YTM_MID', 'YLD_CHG_NET_2D_NO_BP'],
start=self.rundate,
end=self.rundate,
PRICING_SOURCE='BVAL',
ignore_security_error=1).as_frame()
historical_bval = historical_bval.transpose().reset_index()
frames = [historical_data, historical_bval]
frames = pd.concat(frames)
hd = historical_data
hb = historical_bval
hdcols = ['bond', 'pcs', 'price']
hd.columns = hdcols
high = hd[hd['pcs'] == 'PX_HIGH']
high = high[['bond', 'price']]
high.columns = ['bond', "PX_HIGH"]
low = hd[hd['pcs'] == 'PX_LOW']
low = low[['bond', 'price']]
low.columns = ['bond', "PX_LOW"]
hbcols = ['bond', 'pcs', 'yield']
hb.columns = hbcols
bid = hb[hb['pcs'] == 'YLD_YTM_MID']
bid = bid[['bond', 'yield']]
bid.columns = ['bond', "YLD_YTM_MID"]
ask = hb[hb['pcs'] == 'YLD_CHG_NET_2D_NO_BP']
ask = ask[['bond', 'yield']]
ask.columns = ['bond', "YLD_CHG_NET_2D_NO_BP"]
x = pd.merge(df,
high,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = pd.merge(x,
low,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = pd.merge(x,
bid,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x = pd.merge(x,
ask,
left_on='Parskeyeable Description',
right_on='bond',
how='inner')
x['inside'] = np.where(
x['Buy/Sell'] == 'B',
np.where(x['Trade price'] > x['PX_LOW'], 'inside', 'outside'),
np.where(x['Trade price'] < x['PX_HIGH'], 'inside', 'outside'))
x['PX_HIGH_LOW_DIFF_BPS'] = np.where(x['Buy/Sell'] == 'B',
x['Trade price'] - x['PX_LOW'],
x['PX_HIGH'] - x['Trade price'])
x['PX_HIGH_LOW_DIFF_%'] = np.where(
x['Buy/Sell'] == 'B',
((x['Trade price'] - x['PX_LOW']) / x['PX_LOW']) * 100,
((x['PX_HIGH'] - x['Trade price']) / x['PX_HIGH']) * 100)
return x[[
'As of Date', 'Ticket Number', 'Security Description',
'Trader Name', 'Buy/Sell', 'TRADE FEED TRADE AMOUNT',
'Trade price', 'TBLT Ticket Type', 'Cusip Number',
'Parskeyeable Description', 'Security Type', 'Trader Login',
'Sales Login', 'Issue Date', 'Maturity Date', 'Principal',
'Counterparty', 'Master Account Long Name', 'Master Account',
'Yield', 'Int at Maturity', 'Days from Settle to Maturity',
'Accrued Number Of Days', 'Coupon', 'Inflation-Linked Indicator',
'Mid Modified Duration', 'tenor', 'Years_until_maturity',
'PX_HIGH', 'PX_LOW', 'YLD_YTM_MID', 'YLD_CHG_NET_2D_NO_BP',
'inside', 'PX_HIGH_LOW_DIFF_BPS', 'PX_HIGH_LOW_DIFF_%'
]]
import pandas as pd
import matplotlib.pyplot as plt
#from pandas.plotting import register_matplotlib_converters
#register_matplotlib_converters()
from pypfopt import EfficientFrontier
from pypfopt import risk_models
from pypfopt import expected_returns
from tia.bbg import LocalTerminal
# Get monthly data from Bloomberg
tickers = ['SPY', 'VLUE', 'QUAL', 'SIZE', 'MTUM', 'USMV']
factor_names = ['Value', 'Quality', 'Size', 'Momentum', 'MinVol']
req2 = LocalTerminal.get_historical(['SPY Equity', 'VLUE Equity', 'QUAL Equity',
'SIZE Equity', 'MTUM Equity', 'USMV Equity'],
['PX_LAST'], start='1/2/1995',
period='DAILY', ignore_security_error=1)
ETF_Prices = req2.as_frame()
ETF_Prices.columns = tickers
ETF_Prices = ETF_Prices.dropna()
ETF_Prices.to_csv('Factor_ETF_Prices.csv')
SPY = ETF_Prices.iloc[:,0]
SPY_returns = SPY.pct_change().dropna()
Factors = ETF_Prices.iloc[:,1:]
Factor_returns = Factors.pct_change().dropna()
# Loop to solve for portfolio weights each period
backtest_wgts = []
market = {
"BBG Barclays US HY": "LF98TRUU Index",
"Crude Oil": "CL1 Comdty",
"2s_10s": "USYC2Y10 Index",
"USD_Index": "DXY Index",
"IHYG": "IHYG LN Equity",
"Oil_Equipment_Services": "XES US Equity",
"Oil_E": "XOP US Equity",
"OIH ETF": "OIH US Equity"
} # BBG Barclays US HY
cfields = ["LAST PRICE"]
df = LocalTerminal.get_historical(list(long_tickers.values()),
cfields,
start_date,
end_date,
period="DAILY").as_frame()
df.columns = df.columns.droplevel(-1)
#%%
for i, j in long_tickers.items():
df = df.rename(columns={j: i})
df_price = df.copy().dropna()
#df = df.pct_change()
'''
selected = ['CNP', 'F', 'WMT', 'GE', 'TSLA', 'SPY', 'QQQ', 'IWM']
select_string = ' '.join(selected)
def download_yf(long_tickers):
df = yf.download(long_tickers=long_tickers,
# Save
#dictionary = {'hello':'world'}
np.save(fr'{output_dir2}/tickers.npy', ticker_dict)
# # Load
# read_dictionary = np.load('my_file.npy',allow_pickle='TRUE').item()
# print(read_dictionary['hello']) # displays "world"
#ticker_dict = np.load(fr'{output_dir2}/tickers.npy', allow_pickle=True).item()
hist_tickers = list(set().union(*ticker_dict.values()))
hist_tickers_bbg = [x + " Equity" for x in hist_tickers]
fields = ["OPEN", "HIGH", "LOW", "LAST PRICE", "VOLUME"]
start = "2000-01-01"
end = "{:%m/%d/%Y}".format(datetime.now())
df = LocalTerminal.get_historical(hist_tickers_bbg, fields, start, end, period = 'DAILY').as_frame()
df = df.reset_index()
#df.to_csv(fr'{output_dir2}/historical_data.csv')
df.to_pickle(fr'{output_dir2}/historical_data.pkl')
#df = pd.read_csv(fr'{output_dir2}/historical_data.csv')
df = pd.read_pickle(fr'{output_dir2}/historical_data.pkl')
#%%
data = {}
date_list = list(ticker_dict.keys())
#fields = ["OPEN", "HIGH", "LOW", "LAST PRICE", "VOLUME"]
#df.set_index('index', inplace=True)
def BackDate(self, event):
i = 0
try:
while i < 1:
wait = wx.BusyCursor() #run busy cursor until end
date = self.dateCtrl.GetValue() #get the date input from the gui
print(date)
adv = self.advThreshold.GetValue() #get the adv string value from gui
price = self.priceThreshold.GetValue() # get the price threshold from gui
backdate = lowPriceSecBackDate(date, price, adv) #craate back date object
backdate.formatDates() # get the dates and file dirs
print(backdate.FILE_DIR, '\n', backdate.cpty_report,'\n', backdate.cpty_stepout)
backDateCptyDf = pd.read_csv(backdate.cpty_report, sep="|")
backDateAllocationDf = pd.read_csv(backdate.cpty_stepout, sep="|")
bkDateReport = executedOrderReport(backdate.FILE_DIR, backdate.SAVE, 3, 10) # use the low price sec class to get symbols dont run the regulat low price report
syms = bkDateReport.getSymbols()
syms = syms.SYMBOL.tolist()
syms = [i + " US EQUITY" for i in syms]
print('sybmols found are: ', syms)
print("date is report will run for is: ", backdate.RUN_DATE)
print('running advs')
advs = LocalTerminal.get_historical(syms, 'PX_VOLUME', backdate.RUN_DATE, backdate.RUN_DATE).as_frame() #uses custom bloomberg api based on TIA_BBG github
adv2 = LocalTerminal.get_reference_data(syms, 'VOLUME_AVG_30D', backdate.RUN_DATE,
backdate.RUN_DATE).as_frame()
advs = advs.transpose().reset_index().set_index('level_0').iloc[:, -1:]
advs.columns = ['PX_VOLUME_1D']
adv2 = adv2.join(advs).reset_index()
adv2.columns = ['SYMBOL', 'VOLUME_AVG_30D', 'PX_VOLUME_1D']
adv2['SYMBOL'] = [i.split(" ", 1)[0] for i in adv2.SYMBOL.tolist()]
exceptionFrame = bkDateReport.getSymbols()
exceptionFrame = exceptionFrame.merge(adv2, on='SYMBOL', how='left')
exceptionFrame['BKCM_TOTAL_VOL'] = exceptionFrame.groupby('SYMBOL')['VOLUME'].transform('sum')
exceptionFrame['BKCM_%_ADV'] = (exceptionFrame['BKCM_TOTAL_VOL'] / exceptionFrame['VOLUME_AVG_30D']) * 100
exceptionFrame['BKCM_%_OF_VOLUME_YESTERDAY'] = (exceptionFrame['BKCM_TOTAL_VOL'] / exceptionFrame['PX_VOLUME_1D']) * 100
exceptionFrame = exceptionFrame[exceptionFrame['BKCM_%_ADV'] > 10]
print('running backdate cpty')
cpty = BackDateCpty(backDateAllocationDf, backDateCptyDf)
cpty.merge()
cpty = cpty.alloc
exceptionFrame = pd.merge(exceptionFrame, cpty, left_on='PARENT_ORDER_ID', right_on='ORDER_ID', how='left')
print("excpetion report found these counter parties :", exceptionFrame.COUNTERPARTY_CODE.tolist())
print('saving')
exception = FormatSaveBackDate(exceptionFrame, backdate.date2)
i = 2
return exception.save()
except Exception as e:
print(e)
i = 2
return
banner("ReferenceDataRequest: single security, multi-field (with bulk), frame response")
response = LocalTerminal.get_reference_data("eurusd curncy", ["px_last", "fwd_curve"])
print response.as_map()
rframe = response.as_frame()
print rframe.columns
# show frame within a frame
print rframe.ix[0, "fwd_curve"].tail()
banner("ReferenceDataRequest: multi security, multi-field, bad field")
response = LocalTerminal.get_reference_data(
["eurusd curncy", "msft us equity"], ["px_last", "fwd_curve"], ignore_field_error=1
)
print response.as_frame()["fwd_curve"]["eurusd curncy"]
banner("HistoricalDataRequest: multi security, multi-field, daily data")
response = LocalTerminal.get_historical(["eurusd curncy", "msft us equity"], ["px_last", "px_open"], start=d)
print response.as_map()
print response.as_frame().head(5)
banner("HistoricalDataRequest: multi security, multi-field, weekly data")
response = LocalTerminal.get_historical(
["eurusd curncy", "msft us equity"], ["px_last", "px_open"], start=m, period="WEEKLY"
)
print "--------- AS SINGLE TABLE ----------"
print response.as_frame().head(5)
#
# HOW TO
#
# - Retrieve an fx vol surface: BbgReferenceDataRequest('eurusd curncy', 'DFLT_VOL_SURF_MID')
# - Retrieve a fx forward curve: BbgReferenceDataRequest('eurusd curncy', 'FWD_CURVE')
"""
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from tia.bbg import LocalTerminal
from matplotlib import rc
rc('mathtext', default='regular')
if __name__ == '__main__':
d = pd.datetools.BDay(-30).apply(pd.datetime.now())
m = pd.datetools.BMonthBegin(-2).apply(pd.datetime.now())
FX = ['eurusd curncy', 'audusd curncy', 'cadusd curncy']
response = LocalTerminal.get_historical(FX, ['px_last'], start=d)
# print response.as_frame()
t = [pd.datetools.BDay(-i).apply(pd.datetime.now()) for i in range(31)]
x = pd.DataFrame(columns=['price', 'returns'])
x['price'] = response.as_frame()['eurusd curncy']['px_last']
x['returns'] = np.log(x.price) - np.log(x.price.shift(1))
# plt.plot(t,x.price,t,x.returns)
# plt.show()
fig, ax1 = plt.subplots()
ax1.plot(t, x.price, 'b-')
for tl in ax1.get_yticklabels():
tl.set_color('b')
# ax2 = ax1.twinx()
# ax2.plot(t,x.returns,'r-')
banner('ReferenceDataRequest: single security, multi-field (with bulk), frame response')
response = LocalTerminal.get_reference_data('eurusd curncy', ['px_last', 'fwd_curve'])
print(response.as_map())
rframe = response.as_frame()
print(rframe.columns)
# show frame within a frame
print(rframe.ix[0, 'fwd_curve'].tail())
banner('ReferenceDataRequest: multi security, multi-field, bad field')
response = LocalTerminal.get_reference_data(['eurusd curncy', 'msft us equity'], ['px_last', 'fwd_curve'],
ignore_field_error=1)
print(response.as_frame()['fwd_curve']['eurusd curncy'])
banner('HistoricalDataRequest: multi security, multi-field, daily data')
response = LocalTerminal.get_historical(['eurusd curncy', 'msft us equity'], ['px_last', 'px_open'], start=d)
print(response.as_map())
print(response.as_frame().head(5))
banner('HistoricalDataRequest: multi security, multi-field, weekly data')
response = LocalTerminal.get_historical(['eurusd curncy', 'msft us equity'], ['px_last', 'px_open'], start=m,
period='WEEKLY')
print('--------- AS SINGLE TABLE ----------')
print(response.as_frame().head(5))
#
# HOW TO
#
# - Retrieve an fx vol surface: BbgReferenceDataRequest('eurusd curncy', 'DFLT_VOL_SURF_MID')
# - Retrieve a fx forward curve: BbgReferenceDataRequest('eurusd curncy', 'FWD_CURVE')
# - Retrieve dividends: BbgReferenceDataRequest('csco us equity', 'BDVD_PR_EX_DTS_DVD_AMTS_W_ANN')
baf = pd.concat(frames, keys=columns, join='outer', axis=1)
baf = baf.fillna(method='ffill')
#reading bloomberg tickers from a csv
start_date = '01/01/1990'
end_date = "{:%m/%d/%Y}".format(datetime.now())
fields = ['LAST PRICE']
path = r'C:\Users\dpsugasa\WorkFiles\Macro_Data\Time_Series\napm_tickers.csv'
df = pd.read_csv(path)
roger = dict(zip(df['Ticker'].values, df['Code'].values))
baf1 = LocalTerminal.get_historical(
roger.keys(),
fields,
start_date,
end_date,
period='DAILY',
non_trading_day_fill_option='ALL_CALENDAR_DAYS',
non_trading_day_fill_method='PREVIOUS_VALUE').as_frame()
baf1.columns = baf1.columns.droplevel(-1)
baf1 = baf1.fillna(method='bfill')
baf1 = baf1.resample('M').last()
baf1 = baf1.fillna(method='ffill').dropna()
q = {} #for bbg dicts
#using BBG data
bbg_dics = {
'USTBTOT Index': 'USTBTOT', #US trade balance
'TBEXTOT Index': 'TBEXTOT', #US Exports Total
'USEXREAL Index': 'USEXREAL', #US Real Exports SA
'TMNOCHNG Index': 'TMNOCHNG', #US Manufacturers NO
# set dates, securities, and fields
start_date = '01/01/2012'
end_date = "{:%m/%d/%Y}".format(datetime.now())
IDs = opt_tickers
fields = ['LAST PRICE']
d = {} #dict of original dataframes per ID
d2 = {} #final dict of a prices
temp = {} #dict of temp dataframes
temp2 = {} #additional dict of temp dataframes
#get initial prices in 'd', create a temp dataframe with entry/exit dates,
# price, and expiry for each ticker
for name in IDs:
d[name] = LocalTerminal.get_historical(name, fields, start_date, end_date,
period = 'DAILY').as_frame()
d[name].columns = d[name].columns.droplevel()
d[name] = d[name].fillna(method = 'ffill')
temp[name] = trade_sheet.loc[trade_sheet.Ticker == name][['Date',
'Amount', 'Expiry', 'Direction','Shares']]
temp[name].index = temp[name].Date
temp[name] = temp[name].drop('Date', axis=1)
#because some of the price info does not extend to maturity, make new pricing
# dataframes that have the full price set, including expiry value = 'd2'
for i in opt_tickers:
temp2[i] = pd.DataFrame(np.nan, columns = ['LAST PRICE_NA'],
index = pd.date_range(start = d[i].index[0],
end = temp[i]['Expiry'][-1],
freq = 'B'))
def value_bond_2(a, s, grid_points, bond):
model = ql.HullWhite(ts_spreaded_handle2, a, s)
engine = ql.TreeCallableFixedRateBondEngine(model, grid_points)
bond.setPricingEngine(engine)
return bond
value_bond_2(0.01, 0.08, 40, bond)
print("Bond price: ", bond.NPV())
altice = LocalTerminal.get_reference_data(
'YCCD1949 Index',
'CURVE_MEMBERS',
).as_frame()
curve = altice.iloc[0].loc['CURVE_MEMBERS']
memb = curve['Curve Members'].tolist()
rates = []
y = LocalTerminal.get_historical('CY188822 Curncy', 'LAST PRICE').as_frame()
for i in memb:
rates.append()
import blp
blp = blp.BLPInterface()
df = blp.bulkRequest('YCCD1949 Index', 'CURVE_TENOR_RATES')
#YCCD1949 Index
# extra_ticks = ets['Ticker'].values
# tl2.append('EK3988418 Corp')
q = set(tl2)
# set dates, securities, and fields
start_date = "01/04/2017"
end_date = "{:%m/%d/%Y}".format(datetime.now())
cfields = ["LAST PRICE"]
window = 90
df = LocalTerminal.get_historical(tl2,
cfields,
start_date,
end_date,
period="DAILY").as_frame()
df.columns = df.columns.droplevel(-1)
#df = df.pct_change()
#df = df.std(axis=1)
#df = df.rolling(window=window).mean()
#df = df.dropna()
month = df.last_valid_index().month
month_full = df.last_valid_index().strftime("%B")
day = df.last_valid_index().day
year = df.last_valid_index().year
output_dir = Path(
fr"D:\OneDrive - Northlight Group\Images\Dispersion\{year}\{month_full}")
from tia.bbg import LocalTerminal
import matplotlib
import matplotlib.pyplot as plt
import tia.analysis.ta as ta
# Single SID, Multiple Valid Fields
resp = LocalTerminal.get_historical(['EURUSD Curncy'], ['PX_LAST'],
start='1/1/2020',
end='10/27/2020')
df = resp.as_frame()
#Add the 30 and 50 day rolling averages to the dataframe
df['eurusd30dma'] = df['EURUSD Curncy']['PX_LAST'].rolling(window=30).mean()
df['eurusd50dma'] = df['EURUSD Curncy']['PX_LAST'].rolling(window=50).mean()
# Add the trading signals when the 30 day crosses the 50 day MA
signal = ta.cross_signal(df['eurusd30dma'], df['eurusd50dma']).dropna()
#only keep the entry/exit signals
entry_signal = signal.copy()
entry_signal[signal.shift(1) == signal] = 0
entry_signal = entry_signal[entry_signal != 0]
#df['entry signal'] = entry_signal
#print(entry_signal.head())
#print(df.head())
#print(df['eurusd30dma']['2020-06-01'])
#matplotlib.style.use('ggplot')
df.plot(kind='line',
fin_IDs = ['EUR001M Index', 'US0001M Index']
price_fields = ['LAST PRICE', 'HIGH', 'LOW']
ref_data = [
'ID_ISIN', 'CPN', 'CPN_FREQ', 'CRNCY', 'SECURITY_NAME', 'NXT_CALL_DT',
'ISSUE_DT', 'COMPANY_CORP_TICKER'
]
d = {} #dict of original dataframes per ID
m = {} #reference data
n = {} #pnl data
z = {} #financing data
for i in fin_IDs:
z[i] = LocalTerminal.get_historical(i,
'LAST PRICE',
start_date,
end_date,
period='DAILY').as_frame()
z[i].columns = z[i].columns.droplevel(-1)
z[i] = z[i].fillna(method='ffill')
for q in IDs:
name = list(q.values())[1]
code = list(q.values())[0]
d[name] = LocalTerminal.get_historical(code,
price_fields,
start_date,
end_date,
period='DAILY').as_frame()
d[name].columns = d[name].columns.droplevel()
resp = LocalTerminal.get_reference_data(['ED1 COMDTY', 'MSFT US EQUITY'], ['PX_LAST', 'GICS_SECTOR_NAME'], ignore_field_error=1)
resp.as_frame()
# Retrieve data without override
LocalTerminal.get_reference_data('SPX INDEX', 'CUST_TRR_RETURN_HOLDING_PER').as_frame()
# Retrieve data with override (1 month total return)
dt = pd.datetools.BDay(-21).apply(pd.datetime.now()).strftime('%Y%m%d')
LocalTerminal.get_reference_data('SPX INDEX', 'CUST_TRR_RETURN_HOLDING_PER', CUST_TRR_START_DT=dt).as_frame()
###################
##Historical Data##
###################
# Single SID, Multiple Valid Fields
resp = LocalTerminal.get_historical('MSFT US EQUITY', ['PX_OPEN', 'PX_LAST'], start='1/1/2014', end='3/1/2014')
resp.as_frame().head()
# Multiple SIDs, Multiple Valid Fields
resp = LocalTerminal.get_historical(['IBM US EQUITY', 'MSFT US EQUITY'], ['PX_OPEN', 'PX_LAST'], start='1/1/2014', end='3/1/2014')
resp.as_frame().head()
# Weekly data
resp = LocalTerminal.get_historical(['IBM US EQUITY', 'MSFT US EQUITY'], ['PX_OPEN', 'PX_LAST'],
start='1/1/2014', end='3/1/2014', period='WEEKLY')
resp.as_frame().head()
# format response as panel
resp.as_panel()
#################################
