def test_one_frame(self): df = pd.DataFrame([[1., 2.], [3., 4.]], columns=['c1', 'c2'], index=['r1', 'r2']) wb = create_report('template_one_frame.xlsx', 'output.xlsx', df=df, title='MyTitle') for i in range(2): sheet = wb.sheets[i] self.assertEqual(sheet['A1'].value, 'MyTitle') self.assertEqual(sheet['A3'].value, 'PART ONE') self.assertEqual(sheet['A8'].value, 'PART TWO') if i == 0: assert_frame_equal( sheet['A4'].options(pd.DataFrame, expand='table').value, df) assert_frame_equal( sheet['A9'].options(pd.DataFrame, expand='table').value, df) elif i == 1: df_table1 = sheet['A4'].options(pd.DataFrame, expand='table').value df_table1.index.name = None df_table2 = sheet['A9'].options(pd.DataFrame, expand='table').value df_table2.index.name = None assert_frame_equal(df_table1, df) assert_frame_equal(df_table2, df) self.assertEqual(sheet['A3'].color, (0, 176, 240)) self.assertEqual(sheet['A8'].color, (0, 176, 240))
def test_update_links_true(self): wb = create_report('template_with_links.xlsx', 'output.xlsx', book_settings={'update_links': True}, **data) self.assertEqual(wb.sheets[0]['M1'].value, 'Updated Text for update_links')
def main(): template = xw.Book.caller() template_path = template.fullname report_path = os.path.join(os.path.dirname(template.fullname), 'report.xlsx') # Matplotlib fig = Figure(figsize=(4, 3)) ax = fig.add_subplot(111) ax.plot([1, 2, 3, 4, 5]) # Pandas DataFrame perf_data = pd.DataFrame(index=['r1', 'r1'], columns=['c0', 'c1'], data=[[1., 2.], [3., 4.]]) # Picture logo = Image.open( os.path.join(os.path.dirname(template.fullname), 'xlwings.jpg')) app = template.app app.screen_updating = False wb = create_report(template_path, report_path, app=app, perf=0.12 * 100, perf_data=perf_data, logo=logo, fig=fig) wb.sheets.active['A1'].select() app.screen_updating = True
def test_two_frames(self): df1 = pd.DataFrame([[1., 2., 3.], [4., 5., 6.], [7., 8., 9.]], columns=['c1', 'c2', 'c3'], index=['r1', 'r2', 'r3']) df1.index.name = 'df1' df3 = pd.DataFrame([[1., 2., 3.], [4., 5., 6.], [7., 8., 9.], [10., 11., 12.], [13., 14., 15.]], columns=['c1', 'c2', 'c3'], index=['r1', 'r2', 'r3', 'r4', 'r5']) df3.index.name = 'df3' text = 'abcd' pic = Image.open(os.path.abspath('xlwings.jpg')) data = dict(df1=df1, df2='df2 dummy', df3=df3, text=text, pic=pic) wb = create_report('template_two_frames.xlsx', 'output.xlsx', **data) sheet = wb.sheets[0] # values assert_frame_equal(sheet['A1'].options(pd.DataFrame, expand='table').value, df3) self.assertEqual(sheet['A8'].value, 'df2 dummy') self.assertEqual(sheet['C10'].value, 'abcd') assert_frame_equal(sheet['A12'].options(pd.DataFrame, expand='table').value, df1) assert_frame_equal(sheet['A17'].options(pd.DataFrame, expand='table').value, df3) assert_frame_equal(sheet['A24'].options(pd.DataFrame, expand='table').value, df3) assert_frame_equal(sheet['A31'].options(pd.DataFrame, expand='table').value, df3) assert_frame_equal(sheet['F1'].options(pd.DataFrame, expand='table').value, df1) self.assertEqual(sheet['G6'].value, 'abcd') assert_frame_equal(sheet['F8'].options(pd.DataFrame, expand='table').value, df3) assert_frame_equal(sheet['F15'].options(pd.DataFrame, expand='table').value, df1) assert_frame_equal(sheet['F27'].options(pd.DataFrame, expand='table').value, df1) self.assertEqual(sheet['F32'].value, 'df2 dummy') assert_frame_equal(sheet['F34'].options(pd.DataFrame, expand='table').value, df3) # colors self.assertEqual(sheet['A2:D6'].color, (221, 235, 247)) self.assertEqual(sheet['A13:D15'].color, (221, 235, 247)) self.assertEqual(sheet['A18:D22'].color, (221, 235, 247)) self.assertEqual(sheet['A25:D29'].color, (221, 235, 247)) self.assertEqual(sheet['A32:D36'].color, (221, 235, 247)) self.assertEqual(sheet['F2:I4'].color, (221, 235, 247)) self.assertEqual(sheet['F9:I13'].color, (221, 235, 247)) self.assertEqual(sheet['F16:I18'].color, (221, 235, 247)) self.assertEqual(sheet['F28:I30'].color, (221, 235, 247)) self.assertEqual(sheet['F35:I39'].color, (221, 235, 247)) # borders # TODO: pending Border implementation in xlwings CE if sys.platform.startswith('darwin'): from appscript import k as kw for cell in ['A4', 'A14', 'D20', 'A28', 'D36', 'F4', 'H10', 'G17', 'G28', 'I36']: self.assertEqual(sheet[cell].api.get_border(which_border=kw.edge_top).properties().get(kw.line_style), kw.continuous) self.assertEqual(sheet[cell].api.get_border(which_border=kw.edge_bottom).properties().get(kw.line_style), kw.continuous) else: pass
def test_app_instance(self): app = xw.App() wb = create_report('template_with_links.xlsx', 'output.xlsx', app=app, book_settings={'update_links': False}, **data) self.assertEqual(wb.sheets[0]['M1'].value, 'Text for update_links') wb.app.quit()
def main(): template = xw.Book.caller() template_path = template.fullname report_path = os.path.join(os.path.dirname(template.fullname), 'report.xlsx') # Matplotlib fig = Figure(figsize=(4, 3)) ax = fig.add_subplot(111) ax.plot([1, 2, 3, 4, 5]) # Pandas DataFrame perf_data = pd.DataFrame(index=['r1', 'r1'], columns=['c0', 'c1'], data=[[1., 2.], [3., 4.]]) # Picture logo = Image(os.path.join(os.path.dirname(template.fullname), 'xlwings.jpg')) # Float perf = 0.12 # Markdown mytext = dedent("""\ # Q1 2021 Results The perfomance was {{ perf }}. This was due to the following points: * More sales * Cost cuts # Sales were strong *Automation was the most important driver*. More info on request. """) style = MarkdownStyle() style.h1.font.color = (21, 164, 58) style.h1.font.size = 14 app = template.app app.screen_updating = False wb = create_report(template_path, report_path, perf_data=perf_data, logo=logo, perf=perf, fig=fig, summary=Markdown(mytext, style) ) wb.sheets.active['A1'].select() app.screen_updating = True
def main(): template = xw.Book.caller() # Config date = template.sheets['Config']['date'].value.date() currency = template.sheets['Config']['currency'].value nb_weak = int(template.sheets['Config']['nb_weak'].value) nb_strong = int(template.sheets['Config']['nb_strong'].value) nb_cheap = int(template.sheets['Config']['nb_cheap'].value) nb_expensive = int(template.sheets['Config']['nb_expensive'].value) # Get Big Mac index data from GitHub # url = 'https://raw.githubusercontent.com/TheEconomist/big-mac-data/master/output-data/big-mac-raw-index.csv' url = os.path.join(os.path.dirname(template.fullname), 'big-mac-raw-index.csv') raw = pd.read_csv(url, index_col=0, parse_dates=True) # Data wrangling summary = raw.loc[date, ['name', 'currency_code', 'dollar_price', 'USD']] summary = summary.set_index('name') summary.index.name = 'Country' summary = summary.rename(columns={"currency_code": "Currency", "dollar_price": "Big Mac Price (USD)", "USD": "Over/undervalued"}) valuation = summary.drop(columns=['Big Mac Price (USD)'], index=['United States']) valuation = valuation.sort_values(by=['Over/undervalued'], ascending=False) strong_valuation = valuation.head(nb_strong) weak_valuation = valuation.tail(nb_weak) price = summary.drop(columns=['Over/undervalued']) price = price.sort_values(by=['Big Mac Price (USD)'], ascending=False) expensive = price.head(nb_expensive) cheap = price.tail(nb_cheap) valuation_history = raw.reset_index().set_index('currency_code') valuation_history = valuation_history.loc[currency, ['date', 'USD']] valuation_history = valuation_history.set_index('date').sort_index() # Create Report template_path = template.fullname report_path = os.path.join(os.path.dirname(template.fullname), f'report_{date}.xlsx') app = template.app app.screen_updating = False data = dict(date=date.strftime('%b %e, %Y'), chart_currency=currency, strong_valuation=strong_valuation, weak_valuation=weak_valuation, nb_strong=nb_strong, nb_weak=nb_weak, valuation_history=valuation_history, expensive=expensive, nb_expensive=nb_expensive, cheap=cheap, nb_cheap=nb_cheap) wb = create_report(template_path, report_path, app=app, **data) app.screen_updating = True wb.sheets.active['A1'].select()
def test_one_frame(self): df = pd.DataFrame([[1., 2.], [3., 4.]], columns=['c1', 'c2'], index=['r1', 'r2']) wb = create_report('template_one_frame.xlsx', 'output.xlsx', df=df, title='MyTitle') sheet = wb.sheets[0] self.assertEqual(sheet['A1'].value, 'MyTitle') self.assertEqual(sheet['A3'].value, 'PART ONE') self.assertEqual(sheet['A8'].value, 'PART TWO') assert_frame_equal( sheet['A4'].options(pd.DataFrame, expand='table').value, df) assert_frame_equal( sheet['A9'].options(pd.DataFrame, expand='table').value, df) self.assertEqual(sheet['A3'].color, (0, 176, 240)) self.assertEqual(sheet['A8'].color, (0, 176, 240))
def main(): # Files template = xw.Book.caller() template_path = template.fullname report_path = os.path.join(os.path.dirname(template.fullname), 'fund_report.xlsx') # Get your data via SQL, APIs, text files, Excel files etc. with open(os.path.join(os.path.dirname(__file__), 'data.pickle'), 'rb') as f: historical_perf, asset_allocation, top_ten_holdings, calendar_year_tot_ret, tot_ret = pickle.load(f) # Data wrangling (with pandas, obviously) historical_perf = historical_perf.resample('M').last() # Configuration (optional) date_format = template.sheets['Config']['date_format'].value if date_format == 'UK': fmt = '%e %b %Y' elif date_format == 'US': fmt = '%b %e, %Y' else: fmt = '%e %b %Y' # Collect all data data = dict(perf_start_date=dt.datetime(2009, 1, 1).strftime(fmt), perf_end_date=dt.date.today().strftime(fmt), reference_date=dt.date.today().strftime(fmt), total_net_assets=123, historical_perf=historical_perf, asset_allocation=asset_allocation, top_ten_holdings=top_ten_holdings, tot_ret=tot_ret, calendar_year_tot_ret=calendar_year_tot_ret, fund_name='xlwings Fund' ) app = template.app app.screen_updating = False # Create the Excel report wb = create_report(template_path, report_path, app=app, **data) wb.sheets.active['A1'].select() app.screen_updating = True
def main(): # These parameters could also come from a config sheet in the template instrument = '.DJI' start_date = '2020-01-01' end_date = '2020-01-31' # Eikon queries df = ek.get_timeseries(instrument, fields='*', start_date=start_date, end_date=end_date) summary, err = ek.get_data(instrument, ['TR.IndexName', 'TR.IndexCalculationCurrency']) # Populate the Excel template with the data template = xw.Book.caller().fullname wb = create_report( template=template, output=os.path.join(os.path.dirname(template), 'factsheet.xlsx'), title= f"{summary.loc[0, 'Index Name']} ({summary.loc[0, 'Calculation Currency']})", df=df)
def main(): # Files template = xw.Book.caller() report_path = Path(template.fullname).resolve().parent / 'report.xlsx' # Eikon setup ek.set_app_key(os.getenv('EIKON_APP_KEY')) # Configuration date_format = template.sheets['Config']['date_format'].value if date_format == 'UK': fmt = '%e %b %Y' elif date_format == 'US': fmt = '%b %e, %Y' else: fmt = '%e %b %Y' instrument = template.sheets['Config']['instrument'].value start_date = dt.datetime(dt.datetime.now().year - 4, 1, 1) # Prices prices = ek.get_timeseries(instrument, fields=['close'], start_date=start_date, end_date=dt.datetime.now(), interval='weekly') end_date = prices.index[-1] # Summary summary, err = ek.get_data(instrument, ['TR.PriceClose', 'TR.Volume', 'TR.PriceLow', 'TR.PriceHigh', 'TR.IndexName', 'TR.IndexCalculationCurrency']) # Constituents constituents, err = ek.get_data(f'0#{instrument}', fields=['TR.CommonName', 'TR.PriceClose', 'TR.TotalReturnYTD']) constituents = constituents.set_index('Company Common Name') # Add empty columns so it goes into the desired Excel cells for i in range(6): constituents.insert(loc=i, column='merged' + str(i), value=np.nan) constituents = constituents.drop(['Instrument'], axis=1) constituents = constituents.rename(columns={"YTD Total Return": "YTD %"}) constituents = constituents.sort_values("YTD %", ascending=False) # Collect data data = dict( perf_start_date=start_date.strftime(fmt), perf_end_date=end_date.strftime(fmt), index_name=summary.loc[0, 'Index Name'], currency=summary.loc[0, 'Calculation Currency'], reference_date=dt.date.today().strftime(fmt), historical_perf=prices, constituents=constituents, price_close=float(summary['Price Close']), volume=float(summary['Volume']), price_low=float(summary['Price Low']), price_high=float(summary['Price High']) ) # Create the Excel report app = template.app app.screen_updating = False wb = create_report(template.fullname, report_path, app=app, **data) app.screen_updating = True wb.sheets.active["A1"].select()
def setUpClass(cls): cls.wb = create_report('template1.xlsx', 'output.xlsx', **data)