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')
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'])
