def fetch_fred_data(series_list,
start=datetime(2011, 1, 1),
end=datetime.now()):
'''Calls the pandas_datareader API to fetch daily price signals for each
item in series_list (stock tickers and index names in the Saint Louis
Federal Reserve's FRED API).
A good robust cross-section might look like below:
series_list = ['SP500', 'NASDAQCOM', 'DJIA', 'RU2000PR',
'BOGMBASEW', 'DEXJPUS', 'DEXUSEU', 'DEXCHUS', 'DEXUSAL',
'VIXCLS',
'USDONTD156N', 'USD1MTD156N', 'USD3MTD156N', 'USD12MD156N',
'BAMLHYH0A0HYM2TRIV', 'BAMLCC0A1AAATRIV',
'GOLDAMGBD228NLBM',
'DCOILWTICO',
'MHHNGSP', # natural gas
'VXXLECLS'] # cboe energy sector etf volatility
'''
fred_df = pd.DataFrame()
for i, series in enumerate(series_list):
print('Calling FRED API for Series: {}'.format(series))
if i == 0:
fred_df = web.get_data_fred(series, start, end)
else:
_df = web.get_data_fred(series, start, end)
fred_df = fred_df.join(_df, how='outer')
return fred_df
def update_basic_data(dt="2000-01-01"):
"""
Get historical data from FRED and save to local folder 'root/data_temp'
"""
raise "Function update_basic_data is deprecated."
from pandas_datareader import data as pdr
pdr.get_data_fred('DGS3MO', start=dt).dropna()\
.rename(columns={'DGS3MO':'Interest'}).to_csv(data_dir+'\\interest.csv')
print("Interest data loaded and saved as 'interest.csv' from", dt, "to today")
pdr.get_data_yahoo('^GSPC', start=dt).dropna()\
.rename(columns={'Close':'Market'})[['Market']].to_csv(data_dir+'\\market.csv')
print("Market data loaded and saved as 'market.csv' from", dt, "to today")
def get_r():
try:
monthly = float(
str(data.get_data_fred("GS3M").tail(1).values[0][0])[:5]) / 100
daily = float(
str(data.get_data_fred("DGS3MO").tail(1).values[0][0])
[:5]) / 100
except Exception:
return False
return {
"monthly": round(monthly, 4),
"daily": round(daily, 4),
}
def _get_gdp_and_process(cls, macro_data=None, settings=None):
gdp_data = pdata.get_data_fred(settings['gdp_fieldname'], settings['start_date'])
macro_data[settings['gdp_fieldname']] = gdp_data
pop_data = pdata.get_data_fred(settings['pop_fieldname'], settings['start_date'])
macro_data[settings['pop_fieldname']] = pop_data
cols = [settings['gdp_fieldname'], settings['pop_fieldname']]
macro_data[cols] = macro_data[cols].fillna(method='ffill')
macro_data[cols] = macro_data[cols].fillna(method='bfill')
return macro_data
def test_fred_part2(self):
expected = [[576.7],
[962.9],
[684.7],
[848.3],
[933.3]]
result = web.get_data_fred("A09024USA144NNBR", start="1915").ix[:5]
assert_array_equal(result.values, np.array(expected))
def test_fred_part2(self):
expected = [[576.7],
[962.9],
[684.7],
[848.3],
[933.3]]
result = web.get_data_fred("A09024USA144NNBR", start="1915").ix[:5]
assert_array_equal(result.values, np.array(expected))
def test_fred_parts(self): # pragma: no cover
start = datetime(2010, 1, 1)
end = datetime(2013, 1, 27)
df = web.get_data_fred("CPIAUCSL", start, end)
assert df.loc['2010-05-01'][0] == 217.29
t = df.CPIAUCSL.values
assert np.issubdtype(t.dtype, np.floating)
assert t.shape == (37, )
def test_fred_parts(self):
raise nose.SkipTest('buggy as of 2/18/14; maybe a data revision?')
start = datetime(2010, 1, 1)
end = datetime(2013, 1, 27)
df = web.get_data_fred("CPIAUCSL", start, end)
self.assertEqual(df.ix['2010-05-01'][0], 217.23)
t = df.CPIAUCSL.values
assert np.issubdtype(t.dtype, np.floating)
self.assertEqual(t.shape, (37, ))
def test_fred_parts(self):
raise nose.SkipTest('buggy as of 2/18/14; maybe a data revision?')
start = datetime(2010, 1, 1)
end = datetime(2013, 1, 27)
df = web.get_data_fred("CPIAUCSL", start, end)
self.assertEqual(df.ix['2010-05-01'][0], 217.23)
t = df.CPIAUCSL.values
assert np.issubdtype(t.dtype, np.floating)
self.assertEqual(t.shape, (37,))
def get_fred(symbol, components):
symbol = symbol.upper()
try:
df = data.get_data_fred(symbol)
except Exception:
return {"message":Response.fred_notfound(symbol)}
today, dates = current_date(), []
for each in components:
if PATTERNS['valid_date'].match(each):
dates.append(each)
# No dates: get most recent value
if not dates:
df.dropna()
last_value = df.tail(1)[symbol][0]
last_value = ('%.3f' % last_value)
return {"message": Response.basic_fred(symbol, last_value)}
# Clean dates
if len(dates)>2:
return {"message": Response.too_many_dates(symbol)}
for each in dates:
if each > today:
return {"message": Response.invalid_date(each)}
try:
date = datetime.datetime.strptime(each, '%Y-%m-%d')
except ValueError:
return {"message": Response.invalid_date(each)}
# Return price data for one day
if len(dates)==1:
date = dates[0]
ts = pd.DatetimeIndex.asof(df.index, date)
if pd.isnull(ts):
return {"message": Response.fred_date_notfound(symbol, date)}
value = df.loc[ts][symbol]
if pd.isnull(value):
return {"message": Response.fred_date_notfound(symbol, date)}
return {"message": Response.date_fred(symbol, date, value)}
# If 2 dates are entered, returned the range during the given period
else:
dates = sorted(dates)
start, end = dates[0], dates[1]
df = df.loc[start:end]
high = ('%.3f' % df[symbol].max())
low = ('%.3f' % df[symbol].min())
return {"message": Response.fred_data_range(symbol, start, end, high, low)}
def _process_retail_data(cls, macro_data=None, settings=None):
# Filling in retail data: join two series
if 'RSXFS' in macro_data.columns:
retail_series_join_date = dt.datetime(1992, 1, 1)
other_retail_sales = pdata.get_data_fred('RETAIL', settings['start_date'])
levels_ratio = other_retail_sales['RETAIL'] \
[other_retail_sales.index == retail_series_join_date] \
/ macro_data['RSXFS'][macro_data.index == retail_series_join_date][0]
other_retail_sales = other_retail_sales['RETAIL'] / levels_ratio[0]
macro_data['d'] = other_retail_sales
macro_data['RSXFS'][np.isnan(macro_data['RSXFS'])] \
= macro_data['d'][np.isnan(macro_data['RSXFS'])]
del macro_data['d']
return macro_data
def get_ccy_price(start, end):
""" Get currency price with pandas from Fed reserve
"""
from pandas_datareader import data
currency_li = [
"DTWEXBGS",
"DEXUSEU",
"DEXUSUK",
"DEXUSAL",
"DEXCAUS",
"DEXJPUS",
"DEXCHUS",
"DEXHKUS",
"DEXTAUS",
"DEXKOUS",
"DEXMAUS",
"DEXTHUS",
"DEXSIUS",
"DEXINUS",
"DEXBZUS",
"DCOILWTICO",
]
currency_rename = {
"DTWEXBGS": "USD_Index",
"DEXUSEU": "USDEUR",
"DEXUSUK": "USDGBP",
"DEXUSAL": "USDAUD",
"DEXCAUS": "CADUSD",
"DEXCHUS": "CNYUSD",
"DEXJPUS": "JPYUSD",
"DEXTHUS": "THBUSD",
"DEXTAUS": "TWDUSD",
"DEXHKUS": "HKDUSD",
"DEXMAUS": "MYRUSD",
"DEXSIUS": "SGDUSD",
"DEXKOUS": "KRWUSD",
"DEXBZUS": "BRLUSD",
"DEXINUS": "INRUSD",
"DCOILWTICO": "WTI",
}
currency_df = data.get_data_fred(currency_li, start,
end).fillna(method="backfill")
currency_df = currency_df.rename(columns=currency_rename)
return currency_df
def _add_claims_data(cls, macro_data=None, settings=None):
# Handle claims data
claims = pdata.get_data_fred('IC4WSA', settings['start_date'])
claims = claims.resample('M').last()
claims.index = claims.index + pd.tseries.offsets.BDay(1)
tmp = [dt.datetime(claims.index[t].year, claims.index[t].month, 1)
for t in range(0, len(claims))]
claims.index = pd.DatetimeIndex(tmp)
macro_data['Claims'] = claims
settings['data_fields'] = settings['data_fields'] + ['Claims']
settings['data_fieldnames'] = settings['data_fieldnames'] + ['Jobless Claims']
settings['use_log'] += [0]
settings['div_gdp'] += [0]
settings['div_pop'] += [1]
settings['div_cpi'] += [0]
settings['ma_length'] += [12]
return macro_data, settings
def retrieve_recession_data(cls, macro_data=None, start_date=None):
recession_data = pdata.get_data_fred('USRECD', start=start_date)
recession_start_dates = []
recession_end_dates = []
for i in range(1, len(recession_data)):
if (recession_data.values[i][0] == 0) & (
recession_data.values[i - 1][0] == 1):
recession_end_dates.append(recession_data.index[i])
elif (recession_data.values[i][0] == 1) & (
recession_data.values[i - 1][0] == 0):
recession_start_dates.append(recession_data.index[i])
# Assign zeros to recession dates since last data point
tmp_date = recession_data.index[len(recession_data) - 1]
df = pd.DataFrame(macro_data.index[macro_data.index > tmp_date])
df.index = macro_data.index[macro_data.index > tmp_date]
df['USRECD'] = 0
recession_data = pd.DataFrame.append(recession_data, df)
del recession_data['DATE']
return recession_data, recession_start_dates, recession_end_dates
def get_gold(start=datetime(2019, 1, 1), end=datetime.today()):
return DataFrame(
web.get_data_fred('GOLDAMGBD228NLBM', start, end, session=session))
'EXUSUK': {'From': 'USD', 'To': 'GBP', 'Invert': 'N'},
'EXUSEU': {'From': 'USD', 'To': 'EUR', 'Invert': 'N'},
'EXHKUS': {'From': 'USD', 'To': 'HKD', 'Invert': 'Y'},
'EXSIUS': {'From': 'USD', 'To': 'SGD', 'Invert': 'Y'},
'EXSZUS': {'From': 'USD', 'To': 'CHF', 'Invert': 'Y'},
'EXCHUS': {'From': 'USD', 'To': 'RMB', 'Invert': 'Y'},
'EXTAUS': {'From': 'USD', 'To': 'TWD', 'Invert': 'Y'},
'EXINUS': {'From': 'USD', 'To': 'INR', 'Invert': 'Y'},
'EXJPUS': {'From': 'USD', 'To': 'JPY', 'Invert': 'Y'}
}
for currency_ticker, currency_details in currency_pairs.items():
# Load FX Rates from FRED through pandas datareader
start = datetime(year=2016, month=1, day=1)
raw_data = web.get_data_fred(currency_ticker, start)
# Remove NaN
data = raw_data.dropna()
# Create cellset and push it to FX Cube
cellset = collections.OrderedDict()
for tmstp, data in data.iterrows():
date = tmstp.date()
my_year, my_month = date.year, date.month
# Handle Australian Financial Year
if my_month > 6:
my_year += 1
# Handle Inverted FX Rates
if currency_details["Invert"] == 'Y':
value = 1 / data.values[0]
def test_invalid_series(self):
name = "NOT A REAL SERIES"
with pytest.raises(Exception):
web.get_data_fred(name)
def load_fundamental_data(cls, start_date=None):
if start_date is None:
start_date = cls.default_start_date
# Settings
settings = dict()
settings['start_date'] = start_date
settings['config_filename'] = 'qfl/macro/macro_model_cfg.csv'
settings['gdp_fieldname'] = 'GDP'
settings['cpi_fieldname'] = 'CPILFESL'
settings['pop_fieldname'] = 'POP'
# Read configuration
settings['config_table'] = pd.read_csv(settings['config_filename'])
# Active filter
settings['config_table'] = settings['config_table'][
settings['config_table']['ACTIVE'] == 1]
# Fields and field names
settings['data_fields'] = settings['config_table']['FRED_CODE'].tolist()
settings['data_fieldnames'] = settings['config_table']['SERIES_NAME'].tolist()
settings['use_log'] = settings['config_table']['USE_LOG'].tolist()
settings['ma_length'] = settings['config_table']['MA_LENGTH'].tolist()
settings['div_gdp'] = settings['config_table']['DIV_GDP'].tolist()
settings['div_pop'] = settings['config_table']['DIV_POP'].tolist()
settings['div_cpi'] = settings['config_table']['DIV_CPI'].tolist()
# Load data
raw_macro_data = pdata.get_data_fred(settings['data_fields'], start_date)
macro_data = raw_macro_data.copy()
# Get GDP data and fill in divisors
macro_data = cls._get_gdp_and_process(macro_data=macro_data,
settings=settings)
# Process retail data (joining two series)
macro_data = cls._process_retail_data(macro_data=macro_data,
settings=settings)
# Get claims data (weekly)
macro_data, settings = cls._add_claims_data(macro_data=macro_data,
settings=settings)
# Clean data
macro_data = cls._clean_data(macro_data=macro_data,
settings=settings)
# Get series start and end dates
settings['series_start_dates'] = pd.DataFrame(index=settings['data_fields'],
columns=['date'])
settings['series_end_dates'] = pd.DataFrame(index=settings['data_fields'],
columns=['date'])
for data_field in settings['data_fields']:
finite_data = macro_data[data_field][np.isfinite(macro_data[data_field])]
settings['series_start_dates'].loc[data_field] = finite_data.index.min()
settings['series_end_dates'].loc[data_field] = finite_data.index.max()
settings['common_start_date'] = settings['series_start_dates'].values.max()
settings['common_end_date'] = settings['series_end_dates'].values.min()
cls.settings = settings
cls.data = dict()
cls.data['macro_data'] = macro_data
cls.raw_data = raw_macro_data
return macro_data, settings, raw_macro_data
def get_cny(start=datetime(2019, 1, 1), end=datetime.today()):
return DataFrame(web.get_data_fred('DEXCHUS', start, end, session=session))
# -*- coding: utf-8 -*-
import pandas as pd
import datetime
import pandas_datareader.data as web
import matplotlib.pyplot as plt
from matplotlib import style
from pandas_datareader.quandl import QuandlReader
style.use('fivethirtyeight')
START = datetime.datetime(2010, 1, 1)
END = datetime.datetime.now()
ticker = 'XOM'
#df=web.DataReader("XOM","morningstar",start,end)
data = QuandlReader("WIKI/{}".format(ticker), start=START, end=END)
df = data.read()
print(df)
print(df.head())
df['High'].plot()
plt.legend()
plt.show()
print(web.get_data_fred('GS10'))
from IPython.display import set_matplotlib_formats
set_matplotlib_formats('png', 'pdf')
pd.options.mode.chained_assignment = None # default='warn'
pd.set_option('display.max_columns',110)
pd.set_option('display.max_rows',1000)
plt.style.use('seaborn')
mpl.rcParams['font.family'] = 'serif'
start=datetime(2008, 1, 1)
end=datetime(2019, 12, 31)
Security = web.DataReader("SPY", "av-daily-adjusted", start, end, access_key = 'apikeyhere')
Fed_Balance_Sheet = web.get_data_fred('WALCL', start, end)
Fed_Balance_Sheet.columns = ['Fed Balance Sheet']
Fed_Balance_Sheet['Fed Balance Sheet Change'] = Fed_Balance_Sheet.pct_change(1)
data = Fed_Balance_Sheet.join(Security)
data2 = data[['Fed Balance Sheet', 'Fed Balance Sheet Change','adjusted close']]
data2['SPY returns'] = data2['adjusted close'].pct_change()
fed_change = range(1, 30, 1)
spy_change = range(1, 30, 1)
future_spy = range(-30, -1, 1)
corr_results = pd.DataFrame()
for Fed_Change, SPY_change, Future_SPY in product(fed_change,spy_change, future_spy):
data_new = data2[['Fed Balance Sheet','adjusted close']]
data_new['Fed BS Change'] = data_new['Fed Balance Sheet'].pct_change(Fed_Change)
data_new['SPY Change'] = data_new['adjusted close'].pct_change(SPY_change)
import pandas_datareader.data as web
import datetime
import pandas as pd
f = web.get_data_fred('SHA')
print(f)
import pandas_datareader.data as web import datetime as dt ticker = 'DEXJPUS' start = dt.datetime(2017, 1, 1) end = dt.datetime(2020, 1, 12) df = web.get_data_fred(ticker, start, end) print(df.tail(5))
#importing libraries
import pandas_datareader.data as web
import pandas as pd
import matplotlib.pyplot as plt
#creating a list of terms and a list of rates
rates=[]
terms = [1,2,3,5,7,10,20,30]
for i in terms:
tic='GS'+str(i)
data=web.get_data_fred(tic)
r=data[data.columns[-1]][-1]
rates.append(r)
#plotting
fig=plt.figure(figsize=(16,6),facecolor='#07000D')
ax = plt.subplot2grid((12,4),(7,0), rowspan = 5, colspan = 4, facecolor='#07000D')
ax.grid(linestyle=':', color='w')
ax.yaxis.label.set_color('w')
ax.xaxis.label.set_color('w')
ax.spines['bottom'].set_color("y")
ax.spines['top'].set_color("y")
ax.spines['left'].set_color("y")
ax.spines['right'].set_color("y")
ax.tick_params(axis='both', which='major',colors='w', labelsize=20 , width=2)
plt.ylabel('Rate',fontsize=20)
plt.xlabel('Term',fontsize=20)
dias = 600
training_start = datetime(2010, 1, 4)
training_finish = datetime(2018, 6, 15)
testing_start = datetime(2019, 1, 1)
testing_finish = datetime(2018, 6, 15)
#Market Data
market_data = web.get_data_yahoo(
["^GSPC", "^GDAXI", "^FCHI", "^N225", "^HSI", "^BVSP", "^MXX", "XWD.TO"],
start)
market_data = market_data["Close"].fillna(method="ffill")
activos = ["^GSPC", "^GDAXI", "^FCHI", "^N225", "^HSI", "^BVSP", "^MXX"]
#Risk Free
US_rates = web.get_data_fred(
["DTB3", "DGS3MO", "DTB6", "DGS6MO", "DTB1YR", "DGS2", "DGS10"],
start).fillna(method="ffill")
US_rates = (US_rates["DTB1YR"].loc[training_start:training_finish].mean() /
100)
training = market_data.loc[training_start:training_finish,
activos].pct_change(dias).dropna().mean() - US_rates
#FUNCIÓN DE PORTAFOLIO ÓPTIMO
def portafolio_optimo(retornos):
retornos_portafolio = []
volatilidad_portafolio = []
sharpe_ratio = []
pesos_activos = []
numero_activos = len(activos)
def get_fred_data(series_list, start, end):
fred_df = pd.DataFrame()
for i, series in enumerate(series_list):
print('Calling FRED API for Series: {}'.format(series))
if i == 0:
fred_df = web.get_data_fred(series, start, end)
else:
_df = web.get_data_fred(series, start, end)
fred_df = fred_df.join(_df, how='outer')
return fred_df
