def cointegration(Y, X, sym1, sym2): """Stationarity test for X and Y.""" wk = Workbook.active() dates = [dt.datetime.strptime(d, '%Y-%m-%d') for d in X.index] X.index = dates Y.index = dates X = sm.add_constant(X) model = sm.OLS(Y, X) results = model.fit() print(results.summary()) print('Parameters: ', results.params) print('Standard errors: ', results.bse) #print('Predicted values: ', results.predict()) print('P Value_F: ', results.f_pvalue) print('P Value_T: ', results.pvalues) print('RSQ: ', results.rsquared) print('RSQ_Adj: ', results.rsquared_adj) z = results.resid stdev = np.std(z) up_std = 1.5 * stdev down_std = -1.5 * stdev mn = z.mean() ''' fig = plt.figure() plt.title('%s vs. %s' % (sym1, sym2)) plt.plot(X.index, z ) plt.axhline(up_std, color='r') plt.axhline(down_std, color='g') plt.axhline(y= mn, color='g') plt.show() ''' sym = sym1 + ' vs. ' + sym2 stat_arb = pd.DataFrame(z, index=X.index, columns=[sym]) stat_arb['up_std'] = up_std stat_arb['down_std'] = down_std stat_arb['mean'] = mn Range('parameters', 'graph_data').clear_contents() Range('parameters', 'stat').value = stat_arb.tail(500) #------------------------------------------ #http://statsmodels.sourceforge.net/devel/generated/statsmodels.tsa.stattools.adfuller.html ##http://www.quantstart.com/articles/Basics-of-Statistical-Mean-Reversion-Testing x = z result = ts.adfuller(x, 1) # maxlag is now set to 1 print(result)
if __name__ == "__main__": # Résoudre le problème d'humidité et séchage h_all_all = resSec(L, J, T, N, erreur_max, h_all_0, h_all_1_0, condLim, condLim_0_all, condLim_L_all, temp, p_l, n_l, Pvsat, M_l, R, phi, Sr, k_l, m, D_0, A, B) # Enregistrer la solution savetxt('solution.txt', h_all_all, fmt = '%10.4E', delimiter = '\t', newline = os.linesep) # Début : Plot des solutions plt.figure() x = linspace(0, L, J + 1) # Coordonnées de l'axe x for i in range(1, int(shape(h_all_all)[1])): # pour chaque pas du temps plt.plot(x, h_all_all[:,i], linewidth = 0.5) # Enregistrer la solution en Solution wb = Workbook.active() Range(codenames_sheets['Solution'], "C3").value = h_all_all # title, legends, etc ... plt.rc('text', usetex=True) plt.rc('font', family='serif') plt.xlabel(r'x \text{[m]}') plt.xlim(0,L) plt.ylabel(r'h \text{[-]}') plt.title(u"Evolution de l'humidite relative") plt.grid(True) plt.show() # Fin : Plot des solutions
def stat_arb(): I = 10000 CI = .95 hold_prd = 1 wk = Workbook.active() Range('parameters', 'run').value = "Running stat Arb..." interval = Range('parameters', 'var_window').value interval = int(interval) prices, dates, covar, corr, pos_, rtns, abs_returns, vols = read_data( interval) var_df = [] mc_var = [] for tradeDate in dates: pos = position_prices(pos_, tradeDate, prices) pos = position_weights(pos, tradeDate) Q = spectral_decomp_VaR(tradeDate, pos, covar, rtns, I, CI, hold_prd) mc_var.append(Q) para = calc_VaR(pos, covar, CI, hold_prd, tradeDate) var_df.append(para) df = pd.DataFrame({"MC_VaR": mc_var, "Para_VaR": var_df}, index=dates) Range('vols', 'A1').value = vols Range('VaR', 'A1').value = df Range('VaR', 'E2').value = pos Range('Covariance', 'A1').value = covar.loc[tradeDate, :, :] Range('Correlations', 'A1').value = corr.loc[tradeDate, :, :] # plot graphs for data #plot_graphs(interval) sym1 = Range('parameters', 'product1').value sym2 = Range('parameters', 'product2').value # cointegration of corn vs. ethanol #sym1 = 'CUA2' # " sym1 Sell high, buy low sym2 #sym2 = 'SB1' #y = np.log(prices.loc['2014-12-01':, sym1 ]/prices.loc['2014-12-01', sym1 ]) # corn #x = np.log(prices.loc['2014-12-01':, sym2 ]/prices.loc['2014-12-01', sym2 ]) # ethanol y = np.log(prices.loc['2014-12-01':, sym1] * pos.loc[sym1, 'contractSize']) # corn x = np.log(prices.loc['2014-12-01':, sym2] * pos.loc[sym2, 'contractSize']) # ethanol #y = np.log(prices.loc['2014-12-01':, sym1 ]) # corn #x = np.log(prices.loc['2014-12-01':, sym2 ]) # ethanol cointegration(y, x, sym1, sym2) # run cointegration on spred ''' spread = y - x plt.figure() spread.plot(lw=2) plt.title('%s vs. %s' % (sym1, sym2)) plt.show() ''' Range('parameters', 'run').value = "Run Completed!" wk.save()
def test_active_workbook(self): # TODO: add test over multiple Excel instances on Windows Range('A1').value = 'active_workbook' wb_active = Workbook.active(app_target=APP_TARGET) assert_equal(Range('A1', wkb=wb_active).value, 'active_workbook')
import xlwings as xw from xlwings import Workbook, Sheet, Range, Chart import time import datetime from subprocess import call import matplotlib.pyplot as plt # connect to the active workbook #wb = Workbook('run.xlsm') wb = Workbook.active() # log status Range('B8').value = 'running upload ...' file = 'Output/exposure_trade_Swap_20y.csv' # load data into arrays and cells x = [] y = [] z = [] line = 2 import csv with open(file) as csvfile: reader = csv.DictReader(csvfile, delimiter=',') Range('H1').value = 'Time' Range('I1').value = 'EPE' Range('J1').value = 'ENE' for row in reader: x.append(float(row['Time'])) y.append(float(row['EPE'])) z.append(float(row['ENE'])) Range('H' + str(line)).value = float(row['Time'])