BMF5324 Final Project

BMF5324 Statistics and Analytics in Finance - Final Project

Introduction

In light of the current market situation, in which both market participants and authorities have been worrying about that the inflation rate might rise due to the quantitative easing. We hope to find leading indicators, which could assist traders or portfolio managers to get ahead of the market and avoid taking the digital risk. With the aim, we took commodity index, housing prices, airline travel volumes, and Walmart revenue as variables and test if these variables could provide us some hint about how the macro economy situation is and further on project an expected inflation before the release.

This model projected three objective, projecting inflation expectation with multi-linear regression method by the variables mentioned above, projecting the possibility that the inflation expectation would be above certain level by logistic regression, and projecting the possibility that the US economy will enter recession base on the variables' price movements also by logistic regression.

From the study below, our model got a 0.69 R-square when projecting the inflation expectation and the study also showed that the commodity price is actually the key drivers of the inflation expectation. Moreover, the model also signal that the marginal effect of 1 unit increase in the commodity index could reflecting a 0.5% and 0.38% higher possibility that the US economy will enter recession and the inflation expectation could risen above 3.2%

#installations !pip install statsmodels !pip install yfinance !pip install xlrd !pip install pendulum

#importing packages import yfinance as yf import statsmodels.api as sm import numpy as np # linear algebra import pandas as pd # data processing import seaborn as sns import matplotlib.pyplot as plt import numpy.ma as ma # deal with NaN import scipy.stats from scipy import stats from datetime import datetime,timedelta import datetime as dt import plotly.express as px import xlrd from functools import reduce from statsmodels.stats.outliers_influence import variance_inflation_factor

#import actual CPI data for the US economy and transform to dataframe "CPI" CPI=pd.read_csv('CPI.csv', header=None) #Setting the headers CPI_new_header = CPI.iloc[0] #grab the first row for the header CPI = CPI[1:] #take the data less the header row CPI.columns = CPI_new_header #making the first row the header #Conversion of date format to datetime CPI['Month']= pd.to_datetime(CPI['DATE']) #converts the time to datetime format #Selection of time period of interest mask = (CPI['Month'] >= '2000-1-1') & (CPI['Month'] <= '2021-10-1') CPI = CPI.loc[mask] #Set Month column as index CPI.set_index(CPI.columns[-1], inplace=True) CPI.reset_index(inplace=True) #rename CPI column CPI.rename(columns={'CPALTT01USM657N': 'CPI'}, inplace=True) #drop unnecessary columns CPI = CPI[['Month','CPI']] #Conversion of numerical values to float CPI['CPI'] = CPI['CPI'].astype(float) CPI.head()

#check for missing data CPI.isna().sum()

Expected Inflation DataFrame

#import inflation expectation data for the US economy and transform to dataframe "InfExp" InfExp=pd.read_csv('InfExp.csv', header=None) #Setting the headers InfExp_new_header = InfExp.iloc[0] #grab the first row for the header InfExp = InfExp[1:] #take the data less the header row InfExp.columns = InfExp_new_header #making the first row the header #Conversion of date format to datetime InfExp['Month']= pd.to_datetime(InfExp['DATE']) #converts the time to datetime format #Selection of time period of interest mask = (InfExp['Month'] >= '2000-1-1') & (InfExp['Month'] <= '2021-10-1') InfExp = InfExp.loc[mask] #Set Month column as index InfExp.set_index(InfExp.columns[-1], inplace=True) InfExp.reset_index(inplace=True) #rename Inflation Expectation column InfExp.rename(columns={'MICH': 'Inflation Expectation'}, inplace=True) #drop unnecessary columns InfExp = InfExp[['Month','Inflation Expectation']] #Conversion of numerical values to float InfExp['Inflation Expectation'] = InfExp['Inflation Expectation'].astype(float) #check dataset InfExp.head() #check for missing data InfExp.isna().sum() InfExp.tail()

#Housing DataFrames HPI=pd.read_excel(r'HPI_PO_monthly_hist.xls') #please be noted that a CSV file is generated to facilitate the read_csv function. DeepNote is recommended to run this project. HPI.to_csv (r'HPI.csv', index = None, header=True) #CSV file created so that pandas would be able to read HPI=pd.read_csv('HPI.csv')

HPI

#Dropping rows with totally unmeaningful NaN entries HPI = HPI.drop([0,1,3]) #Setting the headers new_header = HPI.iloc[0] #grab the first row for the header HPI = HPI[1:] #take the data less the header row HPI.columns = new_header #making the first row the header #Conversion of date format to datetime HPI['Month']= pd.to_datetime(HPI['Month']) #converts the time to datetime format #Selection of time period of interest mask = (HPI['Month'] >= '2000-1-1') & (HPI['Month'] <= '2021-10-1') HPI = HPI.loc[mask] #In this project, we will only keep the "USA (SA)" and "USA (NSA)" columns, where NSA stands for non-seasonally-adjusted and SA stands for seasonally adjusted. HPI = HPI[['Month','USA\n\n(NSA)','USA\n\n(SA)']] #Conversion of numerical values to float HPI[['USA\n\n(NSA)','USA\n\n(SA)']] = HPI[['USA\n\n(NSA)','USA\n\n(SA)']].astype(float) HPI.head()

#US Airline Passenger Enplanements (Domestic Flights, in thousands) AirlinePass = pd.read_csv('USCarrier_Traffic_20211026093711.csv') #Remove the last 3 rows with NaN and unmeaningful data, as it obstructs the to_datetime function AirlinePass = AirlinePass[:-3] #Converting the non-standard time format to datetime, facilitating data merger AirlinePass['Period']= pd.to_datetime(AirlinePass['Period'], format = "%b-%y") # #Selection of time period of interest mask = (AirlinePass['Period'] >= '2000-1-1') & (AirlinePass['Period'] <= '2021-10-1') AirlinePass = AirlinePass.loc[mask] #Changing the column name to match AirlinePass.columns = ['Month', 'No_of_passengers'] #----------------- #Cargo Revenue Aircraft Miles Flown (Revenue-generating miles flown) AirlineCar = pd.read_csv('USCarrier_Traffic_20211026220709_Cargo.csv') AirlineCar = AirlineCar[:-3] AirlineCar['Period']= pd.to_datetime(AirlineCar['Period'], format = "%b-%y") mask = (AirlineCar['Period'] >= '2000-1-1') & (AirlineCar['Period'] <= '2021-10-1') AirlineCar = AirlineCar.loc[mask] AirlineCar.columns = ['Month', 'AMF_cargo'] #Domestic Revenue Aircraft Miles Flown (Revenue-generating miles flown) AirlineDom = pd.read_csv('USCarrier_Traffic_20211026220709_Domestic.csv') AirlineDom = AirlineDom[:-3] AirlineDom['Period']= pd.to_datetime(AirlineDom['Period'], format = "%b-%y") mask = (AirlineDom['Period'] >= '2000-1-1') & (AirlineDom['Period'] <= '2021-10-1') AirlineDom = AirlineDom.loc[mask] AirlineDom.columns = ['Month', 'AMF_domestic'] #Merger of above dataframes Airline = AirlinePass.merge(AirlineCar, on='Month') Airline = Airline.merge(AirlineDom, on = 'Month') Airline.isna().sum().sum() #checking NaN values, returns zero. Airline.head() #Conversion of numerical values to float Airline[['No_of_passengers','AMF_cargo','AMF_domestic']] = Airline[['No_of_passengers','AMF_cargo','AMF_domestic']].astype(float) Airline.head()

#import S&P-GSCI Commodity Futures Index(GD=F) data to dataframe "Commodity_Index" Commodity_Index=pd.read_csv('Commodity_Index.csv') Commodity_Index['Month']= pd.to_datetime(Commodity_Index['Date']) Commodity_Index=Commodity_Index[['Month','Adj Close']] Commodity_Index.head() #This DataFrame does not need time period adjustment as it falls within our taimeframe of interest.

#import Walmart_Quarterly_Revenue data to dataframe "WMT_QRevenue"

#import Walmart_Quarterly_Revenue data to dataframe "WMT_QRevenue" WMT_QRevenue=pd.read_csv('Walmart_Quarterly_Revenue.csv') WMT_QRevenue['Date']= pd.to_datetime(WMT_QRevenue['Date']) WMT_QRevenue=WMT_QRevenue[['Date','Revenue($ Millions)']] WMT_QRevenue.head() #Changing the date to 1 to ensure consistency in data presentations (first day of the quarter) WMT_QRevenue['Month'] = pd.to_datetime(pd.DataFrame({'day': 1, 'month': WMT_QRevenue['Date'].dt.month, 'year': WMT_QRevenue['Date'].dt.year}, index=WMT_QRevenue.index)) WMT_QRevenue = WMT_QRevenue[['Month','Revenue($ Millions)']] WMT_QRevenue

#import NBER based Recession Indicators data Recession=pd.read_csv('Recession.csv', header=None) #Setting the headers Recession_new_header = Recession.iloc[0] #grab the first row for the header Recession = Recession[1:] #take the data less the header row Recession.columns = Recession_new_header #making the first row the header #Conversion of date format to datetime Recession['Month']= pd.to_datetime(Recession['DATE']) #converts the time to datetime format #Selection of time period of interest mask = (Recession['Month'] >= '2000-1-1') & (Recession['Month'] <= '2021-10-1') Recession = Recession.loc[mask] #Set Month column as index Recession.set_index(Recession.columns[-1], inplace=True) Recession.reset_index(inplace=True) #rename variable column Recession.rename(columns={'USRECDM': 'Recession_dummy'}, inplace=True) #drop unnecessary columns Recession = Recession[['Month','Recession_dummy']] #Conversion of numerical values to float Recession['Recession_dummy'] = Recession['Recession_dummy'].astype(float) Recession.head()

#DataFrames to be merged here data_frames = [CPI, InfExp, Commodity_Index, Airline,WMT_QRevenue,HPI,Recession] df_merged = reduce(lambda left,right: pd.merge(left,right,on=['Month'], how='outer'), data_frames) df_merged.head() #CPI, bfill #airline-related: Bfill() #Adj close = zero #Revenue: interpolation

#DataFrame Cleaning df_merged[['CPI','Inflation Expectation','No_of_passengers','AMF_cargo','AMF_domestic','USA\n\n(NSA)','USA\n\n(SA)']] = df_merged[['CPI','Inflation Expectation','No_of_passengers','AMF_cargo','AMF_domestic','USA\n\n(NSA)','USA\n\n(SA)']].bfill() df_merged['Adj Close'] = df_merged['Adj Close'].dropna() df_merged['Recession_dummy'] = df_merged['Recession_dummy'].dropna() df_merged['Revenue($ Millions)'] = pd.Series(df_merged['Revenue($ Millions)']).interpolate() df_merged['Revenue($ Millions)'] = df_merged['Revenue($ Millions)'].dropna() df_merged = df_merged.dropna() df_merged.head()

df_merged.isna().sum()

#Function created to visualise linear relationship with best-fit lines def visualisation(dependent, independent, dataframe,colour_scheme): fig = px.scatter( dataframe[dataframe[independent] != None], x=independent, y=dependent, color=colour_scheme, title="The dependent variable is " + dependent + ", the independent varialbe is " + independent + ".", marginal_y="violin", marginal_x="box", trendline="ols" ) fig.show()

visualisation('Inflation Expectation','Adj Close',df_merged,'Inflation Expectation')

visualisation('Inflation Expectation','Recession_dummy',df_merged,'Inflation Expectation')

# Define function to conveniently generate correlation heatmaps def gen_correlation(dataframe): f, ax = plt.subplots(figsize=(10, 8)) corr = dataframe.corr() mask = np.triu(np.ones_like(corr, dtype=np.bool)) mask = mask[1:, :-1] corr = corr.iloc[1:,:-1].copy() # Plot heatmap heatmap=sns.heatmap(corr, cmap="Blues", square=True, ax=ax,vmin=-1, vmax=1,mask=mask,annot=True) heatmap.set_xticklabels(heatmap.get_xticklabels(), rotation=45) plt.title("Correlation Heatmap") return corr

gen_correlation(df_merged[['CPI','Inflation Expectation','Adj Close','AMF_cargo','Revenue($ Millions)','USA\n\n(SA)']])

#creating a function to lag dataframes by the number of months def dflag(dataframe,columns_to_lag, months): dflag = dataframe.copy() dflag[columns_to_lag] = dflag[columns_to_lag].shift(months) dflag= dflag.dropna() return dflag

#control variables of dataframe lags by 1 month and housing by 6 months df_lag1m = dflag(df_merged,['No_of_passengers','AMF_cargo','AMF_domestic','Adj Close','Revenue($ Millions)','Recession_dummy'], 1) df_lag1m_housing6m = dflag(df_lag1m,['USA\n\n(NSA)','USA\n\n(SA)'],6) df_lag1m_housing6m.head() #control varialbes of dataframe lags by 2 months df_lag2m = dflag(df_merged,['No_of_passengers','AMF_cargo','AMF_domestic','Adj Close','Revenue($ Millions)','USA\n\n(NSA)','USA\n\n(SA)'], 2)

#call function to generate correlation heatmap for 1 month lag gen_correlation(df_lag1m[['CPI','Inflation Expectation','Adj Close','AMF_cargo','Revenue($ Millions)','USA\n\n(SA)','Recession_dummy']])

gen_correlation(df_lag1m_housing6m[['CPI','Inflation Expectation','Adj Close','AMF_cargo','Revenue($ Millions)','USA\n\n(SA)','Recession_dummy']])

#Regression of df_lag1m_housing6m #regression trials X = df_lag1m_housing6m[['Adj Close','AMF_domestic','Revenue($ Millions)','USA\n\n(SA)','Recession_dummy']] y = df_lag1m_housing6m['Inflation Expectation'] # Note the difference in argument order X = sm.add_constant(X) model = sm.OLS(y.astype(float), X.astype(float), missing='drop').fit() predictions = model.predict(X.astype(float)) # make the predictions by the model # Print out the statistics print(model.summary())

sns.regplot(x = 'USA\n\n(SA)', y = "Inflation Expectation", data = df_lag1m_housing6m).set(title="Inflation Expectatin Regressed Against Seasonally-adjusted Housing Price Index \n") plt.show() sns.regplot(x = 'Revenue($ Millions)', y = "Inflation Expectation", data = df_lag1m_housing6m).set(title="Inflation Expectatin Regressed Against Walmart Revenue in $Millions \n") plt.show() sns.regplot(x = 'AMF_domestic', y = "Inflation Expectation", data = df_lag1m_housing6m).set(title="Inflation Expectatin Regressed Against Domestic Revenue Air Mile Flown in 1,000 miles \n") plt.show() sns.regplot(x = 'Adj Close', y = "Inflation Expectation", data = df_lag1m_housing6m).set(title="Inflation Expectatin Regressed Against Commodity Index (Adjusted Close) \n") plt.show() sns.regplot(x = 'Recession_dummy', y = "Inflation Expectation", data = df_lag1m_housing6m).set(title="Inflation Expectatin Regressed Against Recession Dummy Variable \n") plt.show()

# the independent variables set X = df_lag1m_housing6m[['Adj Close','AMF_domestic','Revenue($ Millions)','USA\n\n(SA)','Recession_dummy']] # VIF dataframe vif_data = pd.DataFrame() vif_data["control variable"] = X.columns # calculating VIF for each feature vif_data["VIF"] = [variance_inflation_factor(X.values, i) for i in range(len(X.columns))] print(vif_data)

#VIF for lean model # the independent variables set X = df_lag1m_housing6m[['Adj Close','USA\n\n(SA)','Recession_dummy']] # VIF dataframe vif_data = pd.DataFrame() vif_data["control variable"] = X.columns # calculating VIF for each feature vif_data["VIF"] = [variance_inflation_factor(X.values, i) for i in range(len(X.columns))] print(vif_data)

#Regression for the slimmmed down model #regression trials X = df_lag1m_housing6m[['Adj Close','USA\n\n(SA)','Recession_dummy']] y = df_lag1m_housing6m['Inflation Expectation'] # Note the difference in argument order X = sm.add_constant(X) model = sm.OLS(y.astype(float), X.astype(float), missing='drop').fit() predictions = model.predict(X.astype(float)) # make the predictions by the model # Print out the statistics print(model.summary())

#Data Wrangling for hypothesis 3 (Creation of Dummy Variable) a=[] df_lag1m_housing6m['inflation_dummy'] = df_lag1m_housing6m['Inflation Expectation'] for row in df_lag1m_housing6m['inflation_dummy']: if row >= 3: #this line sets the threshold of inflation rate above 3% a.append(1) else: a.append(0) df_lag1m_housing6m['inflation_dummy'] = a

#hypothesis 3 regression (logistic regression) X = df_lag1m_housing6m[['Adj Close','USA\n\n(SA)','Recession_dummy']] y = df_lag1m_housing6m['inflation_dummy'] # Note the difference in argument order X = sm.add_constant(X) model2 = sm.Logit(y.astype(float), X.astype(float), missing='drop').fit() # Print out the statistics print(model2.summary())

#marginal effects average_marginal_effect = model2.get_margeff(at = "mean", method = "dydx") print(average_marginal_effect.summary()) median_marginal_effect = model2.get_margeff(at = "median", method = "dydx") print(median_marginal_effect.summary()) overall_marginal_effect = model2.get_margeff(at = "overall", method = "dydx") print(overall_marginal_effect.summary())

#3.2% INFLATION EXPECTATION AS THRESHOLD b=[] df_lag1m_housing6m['inflation_dummy_3pt2'] = df_lag1m_housing6m['Inflation Expectation'] for row in df_lag1m_housing6m['inflation_dummy_3pt2']: if row >= 3.2: #this line sets the threshold of inflation rate above 3.2% b.append(1) else: b.append(0) df_lag1m_housing6m['inflation_dummy_3pt2'] = b #hypothesis 3 regression (logistic regression) X = df_lag1m_housing6m[['Adj Close','USA\n\n(SA)','Recession_dummy']] y = df_lag1m_housing6m['inflation_dummy_3pt2'] # Note the difference in argument order X = sm.add_constant(X) model3 = sm.Logit(y.astype(float), X.astype(float), missing='drop').fit() # Print out the statistics print(model3.summary())

#marginal effects average_marginal_effect = model3.get_margeff(at = "mean", method = "dydx") print(average_marginal_effect.summary()) median_marginal_effect = model3.get_margeff(at = "median", method = "dydx") print(median_marginal_effect.summary()) overall_marginal_effect = model3.get_margeff(at = "overall", method = "dydx") print(overall_marginal_effect.summary())

After the study above we conclude that by the variables' price movement we could actually predict the inflation expectation one month before the digital release. In light of this study we also suggest below strategy for portfolio mangers to adjust positions before the data been release.

Since we are able to predict the inflation expectation, we took a dive into how the inflation expectation interact with the UST 5Y yields and NASDAQ. By lagging the inflation expectation we realize that, when lagging the inflation expectation by 6 month, there is an decent negative correlation between inflation expectation and NASDAQ price. Further more, we discovered that the aggregate monthly return of NASDAQ, since 2006, is -20.9%(average -0.51%, 41 sample) when the inflation expectation was above 3.2% six months ago, and would be -51.3%(average -1.97%, 26 samples) if the inflation expectation was above 3.3% six month ago.

correlation_UST_NASDAQ = pd.read_csv('Correlation_Chart.csv', header=None) correlation_UST_NASDAQ=correlation_UST_NASDAQ.rename(columns=correlation_UST_NASDAQ.iloc[0]) correlation_UST_NASDAQ=correlation_UST_NASDAQ.drop(correlation_UST_NASDAQ.index[0]) correlation_UST_NASDAQ=correlation_UST_NASDAQ.astype(float) correlation_UST_NASDAQ.head()

correlation_UST_NASDAQ.plot(x='Lagged_Period',y='UST5Y',title="5-year US Treasury Against Lagged Period") correlation_UST_NASDAQ.plot(x='Lagged_Period',y='NASDAQ',title="NASDAQ Against Lagged Period") plt.show()

Returns = pd.read_csv('returns.csv', header=None) Returns=Returns.rename(columns=Returns.iloc[0]) Returns=Returns.drop(Returns.index[0]) Returns=Returns.astype(float) Returns.head()

Returns.plot(x='Threshold',y='Agg Return',title="Aggregated Return Against Threshold") Returns.plot(x='Threshold',y='Avg Return',title="Average Return Against Threshold") plt.show()