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())
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_베타결과)