def numpy_pickle_view(picklepath):
'''(str) -> void
Loads and shows a pickled numpy array on the file system
in excel
'''
try:
arr = numpy.load(picklepath)
xlwings.view(arr)
except Exception as _:
pass
def np_pickled_in_excel(pickle_name):
'''(str, bool) -> void
opens the pickled nd array as a new excel spreadsheet
If silent_save is true, then the file is saved as an excel file
to the same directory (and name) as the pickled nd array
Currently assumes a 1D or 2D array. Unknown behaviour with >2 axis.
'''
arr = _np.load(pickle_name)
try:
import xlwings
xlwings.view(arr)
except Exception as _:
_warn(
'np_pickled_in_excel not supported because of xlwings dependency')
def dumpexcel(selection='all',
props='chain segi resi resn name b q',
state=STATELESS):
'''
DESCRIPTION
Open a new Excel spreadsheet with atom property data from the given
selection.
'''
try:
import xlwings
except ImportError:
raise pymol.CmdException('Requires xlwings (conda install xlwings)')
try:
sheet = xlwings.Book().sheets[0]
except IndexError:
sheet = None
if not isinstance(props, (list, tuple)):
props = props.split()
table = [props]
kwargs = {
'expression': '_append((' + ','.join(props) + '))',
'space': {
'_append': table.append
},
}
if state == STATELESS:
pymol.cmd.iterate(selection, **kwargs)
else:
pymol.cmd.iterate_state(state, selection, **kwargs)
xlwings.view(table, sheet)
def open_in_excel(dataframe):
if sys.platform in ['darwin', 'win32']:
import xlwings as xw
xw.view(dataframe)
self.plot_vol_surface(x, y, contract, put_call)
else:
self.plot_raw_vol(x, y, contract, put_call)
return df
def read_vol_surface(self):
"""Read vol surface from pickle file."""
vol_surface = pd.read_pickle("{0}/{1}".format(ROOT_DIR,
self.SRC_VOL_FILE))
print(vol_surface)
# xlwings.view(vol_surface)
if __name__ == '__main__':
tradeDate = '2020-05-14'
d = Options(tradeDate)
d.options_sym = 'LN'
d.futures_sym = 'NG'
#table = d.parse_cme_url()[0][1]
#table.head()
# opt = d.get_cme_settles()
opt1 = d.vol_surface(put_call='Call', raw_vol=False)
xlwings.view(opt1)
def process():
#%%
import numpy as np
import pandas as pd
import os
import xlwings as xw
# wb=xw.Book.caller()
sh_mapping = xw.sheets('mapping')
sh_db = xw.sheets('Dashboard')
driver_source = sh_db.range('driver_source').value
t = read_as_plain_table(driver_source + '.mat')
t['value'] = np.real(t['value'])
l = t.loc[:, ['DriverID']].drop_duplicates().set_index(['DriverID'])
r = t.pivot(index='DriverID', columns='parameter', values='value')
t = l.merge(r, left_index=True, right_index=True)
tdriver = t.copy()
# series of checks
# all(tmapping['DriverID'].isin(tdriver['DriverID']))
t = sh_mapping.range('a1').options(pd.DataFrame, expand='table').value
t = t.merge(tdriver, how='left', right_index=True, left_on='DriverID')
t = t.reset_index()
def get_curve_type(rf):
if rf in ('FinAA', 'GovBBB', 'IndBBB'):
return 'CS'
elif rf in ('GlobalEquityFactor'):
return 'EQ'
else:
second = rf.split('.')[1]
third = rf.split('.')[2]
if second == 'Yield':
return 'IR'
elif second == 'Exchange':
return 'FX'
elif second in ['EquityIndex', 'Equity']:
return 'EQ'
elif second == 'ParCreditSpread':
return 'CS'
elif second == 'Commodity' or third == 'Com':
return 'Com'
t['CurveType'] = t['RiskFactorID'].apply(lambda x: get_curve_type(x))
t['g1Ord1'] = 0
t['g1Ord2'] = 0
t['g1Transform'] = t['model_id'].apply(
lambda x: 'LogNormShift' if x == 'EV-MR' else 'LogNorm' if x == 'GBM'
else 'Norm' if x == 'CS1' else 'LogNorm' if x == 'CS2' else 'ERROR')
t['g1MeanFunc'] = t['model_id'].apply(
lambda x: 'StandardLogNormMR' if x == 'EV-MR' else 'LogNormDrift'
if x == 'GBM' else 'NormMR' if x == 'CS1' else 'LogNormMR'
if x == 'CS2' else 'ERROR')
def calculate_m(model, a, b):
if model == 'EV-MR':
return np.exp(-b / a)
elif model == 'CS1':
return 9999
else:
return 0
t['g1Param_m'] = t.apply(
lambda x: calculate_m(x['model_id'], x['a'], x['b']), axis=1)
t['g1Param_m'] = t.apply(
lambda x: calculate_m(x['model_id'], x['a'], x['b']), axis=1)
def calculate_d(model, a):
if model == 'EV-MR':
return -a
elif model == 'CS1':
return 9999
else:
return 0
t['g1Param_d'] = t.apply(
lambda x: calculate_m(x['model_id'], x['a'], x['b']), axis=1)
t['g1PerAnnumRF'] = 365
t['g1Param_s2'] = 0
t['g1Param_s3'] = 0
t['g1Floor'] = '-infinity'
t['g1IdxRFs'] = np.cumsum(np.ones_like(t.index))
def calculate_beta(CurveType, beta):
if CurveType in ['CS', 'EQ']:
return beta
elif CurveType in ['IR', 'FX', 'Com']:
return 1
t['g1Betas'] = t.apply(lambda x: calculate_beta(x['CurveType'], x['beta']),
axis=1)
qm_rf = t.copy()
# xw.view(tdriver,xw.sheets('data'))
xw.view(qm_rf, xw.sheets('Qm_risk_factor'))
# 사업자번호
테이블_리스트 = []
for i in range(len(사업자)):
사업자번호 = 사업자.iloc[i]['사업자번호']
# 사업자번호 카피하여 붙이기
elem = driver.find_element_by_xpath('//*[@id="bsno"]')
elem.send_keys(사업자번호)
# 시간 주기
time.sleep(4.5)
driver.find_element_by_xpath('//*[@id="trigger5"]').click()
# 테이블_바디로 이동
테이블_바디 = driver.find_element_by_xpath('//*[@id="grid2_body_tbody"]')
time.sleep(1)
print(테이블_바디.text)
테이블_바디_쪼개기 = 테이블_바디.text.split(" ")
테이블_리스트 = 테이블_리스트 + [[
테이블_바디_쪼개기[0], " ".join(테이블_바디_쪼개기[1:-1]), 테이블_바디_쪼개기[-1]
]]
# 결과를 엑셀로 저장하기 : list of list 는 from_records() 함수를 이용한다.
df = pd.DataFrame.from_records(테이블_리스트, columns=['사업자등록번호', '과세여부', '조회일'])
import xlwings as xw
xw.view(df)
def test_list_sheet(self):
n_books = xw.books.count
xw.view([1, 2, 3], sheet=xw.books[0].sheets[0])
self.assertEqual(xw.books.count, n_books)
self.assertEqual(xw.books[0].sheets[0].range('A1:C1').value,
[1., 2., 3.])
def test_list_new_book(self):
n_books = xw.books.count
xw.view([1, 2, 3])
self.assertEqual(xw.books.count, n_books + 1)
def get_excel_data():
df = pd.read_excel(excel_file)
xw.view(df.head())
def test_list_sheet(self):
n_books = xw.books.count
xw.view([1, 2, 3], sheet=xw.books[0].sheets[0])
self.assertEqual(xw.books.count, n_books)
self.assertEqual(xw.books[0].sheets[0].range('A1:C1').value, [1., 2., 3.])
def test_list_new_book(self):
n_books = xw.books.count
xw.view([1, 2, 3])
self.assertEqual(xw.books.count, n_books + 1)
left_on=['Date'],
right_on=['Date'],
suffixes=('_개별', '_코스피지수'))
# 베타 구하기
공분산_개별_코스피 = df_조인.cov().iloc[0]['Change_코스피지수']
분산_코스피 = df_조인['Change_코스피지수'].var()
베타 = 공분산_개별_코스피 / 분산_코스피
print('종목코드 {} {}의 {}일부터 {}일까지 KOSPI 대비 daily B(베타)는 {:.2f} 입니다.'.format(
개별종목코드, 개별종목명, 시작일자, 종료일자, 베타))
테이블_리스트 = 테이블_리스트 + [[개별종목코드, 개별종목명, 시작일자, 종료일자, 베타]]
df_베타결과 = pd.DataFrame.from_records(
테이블_리스트, columns=['개별종목코드', '개별종목명', '시작일자', '종료일자', '베타'])
# 주가 및 변동 history 테이블 구하기
df_종가변동_조인 = pd.merge(df_개별종목,
df_코스피지수,
how='left',
left_on=['Date'],
right_on=['Date'],
suffixes=('_{}'.format(개별종목명), '_코스피지수'))
# 주가 및 변동 history 테이블에서 필요한 칼럼 선택
df_종가변동_조인 = df_종가변동_조인.iloc[:, [3, 6, 5, 11]]
# 엑셀 출력
xw.view(df_종가변동_조인)
xw.view(df_베타결과)
