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)
