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_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