import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from matplotlib import rcParams
from scipy import stats
import json
import os
import pptx
from pptx import Presentation
prs = Presentation()
for l in prs.slide_layouts:
print("*",l.name)
for s in l.placeholders:
print(" ",s.name)
df = pd.read_csv("Commodities.csv")
df_meta = pd.DataFrame(df[:6].set_index(["Commodity"]).T)
df_price = pd.DataFrame(df[6:])
df_price.set_index(["Commodity"],inplace=True)
df_price.index = df_price.index.map(lambda x:pd.datetime.strptime(x,"%YM%m"))
df_price.index.name = "Month"
df_price = df_price.dropna(how="all")
for c in df_price.columns:
df_price[c] = np.array(df_price[c],dtype=np.float)
def getActionTitles(data):
action_titles = []
# Long-term average
lt_avg = (data["Spot"]-data["Lt avg"])/data["Lt avg"]
action_titles.append({"title":"current price {0:.1f}% {1:s} 10-year average".format(np.abs(lt_avg*100),lt_avg>0 and "above" or "below"),"score":np.abs(lt_avg)})
# Compare to last year
lst_y_c = data["Last Year Change"]
action_titles.append({"title":"{0:s} of {1:.1f}% since last year".format((lst_y_c<0 and "drop" or "jump"),np.abs(lst_y_c*100)),"score":np.abs(lst_y_c)})
# Outlook
if data["ST outlook"] == data["LT outlook"]:
if data["ST outlook"] == 1:
action_titles.append({"title":"positive outlook both in short and long term","score":0.05})
elif data["ST outlook"] == -1:
action_titles.append({"title":"negative outlook both in short and long term","score":0.05})
else:
action_titles.append({"title":"constant outlook both in short and long term","score":0.01})
elif data["ST outlook"]*data["LT outlook"]==-1:
action_titles.append({"title":"mixed outlook between long and short term","score":0.02})
else:
if data["ST outlook"]+data["LT outlook"]>0:
action_titles.append({"title":"slightly positive outlook","score":0.02})
elif data["ST outlook"]+data["LT outlook"]>-1:
action_titles.append({"title":"slightly negative outlook","score":0.02})
else:
action_titles.append({"title":"neutral outlook","score":0.00})
# Volatility
action_titles.append({"title":"current volatility of {0:.1f}%".format(data["Vola"]*100),"score":np.abs(data["Vola"]*0.1)})
vola_c = (data["Last Year Vola"]-data["Last 10 Year Vola"])/data["Last 10 Year Vola"]
action_titles.append({"title":"current volatility {0:.1f}% {1:s} 10-year average".format(vola_c*100,vola_c>0 and "above" or "below"),"score":np.abs(vola_c*0.2)})
return action_titles
Creating a function to prepare the data
# Define the time window regarding
n_month_back = 120
n_month_rolling = 12
def prepareData_item(symbol):
meta = df_meta.loc[symbol]
# Variable to store derived data
data = {}
# Extract meta data
data["Symbol"] = symbol
data["Name"] = meta["Name"]
data["Currency"] = meta["Data Type"]
data["Description"] = meta["Description"]
data["Group"] = meta["Type"]
data["Index"] = (meta["Index"]=="Yes")
data["Unit"] = meta["Description"].split(",")[-1]
# Get numbers
dates = df_price[symbol].index
price = np.array(df_price[symbol],dtype=np.float)
rolling_mean_price = np.array(df_price[symbol].rolling(n_month_rolling).mean(),dtype=np.float)
rolling_std_price = np.array(df_price[symbol].rolling(n_month_rolling).std(),dtype=np.float)
vola = rolling_std_price/rolling_mean_price
pct_change = np.array(df_price[symbol].pct_change())
data["Spot"] = price[-1]
data["Lt avg"] = price[-n_month_back:].mean()
data["Last Month Change"] = pct_change[-1]
data["Last Year Change"] = pct_change[-12:].sum()
data["Vola"] = vola[-1]
year_vola = vola[-1:-n_month_back:-12]
data["Last Year Vola"] = year_vola[0]
data["Last 10 Year Vola"] = year_vola.mean()
st_change = pct_change[-3:].mean()
lt_change = pct_change[-24:].mean()
st_rvs = stats.norm(st_change,vola[-1]).rvs()/vola[-1]
lt_rvs = stats.norm(lt_change,vola[-1]).rvs()/vola[-1]
data["ST outlook"] = round(1*(np.abs(st_rvs)>1)*np.sign(st_rvs))
data["LT outlook"] = round(1*(np.abs(lt_rvs)>1)*np.sign(lt_rvs))
# Create plot
fig = plt.figure(symbol,(8,4))
ax_l = plt.subplot()
ax_l.plot(dates[-n_month_back:],price[-n_month_back:],color="#0014a0",label="Spot price")
ax_l.plot(dates[-n_month_back:],rolling_mean_price[-n_month_back:],color="#000000",linewidth=0.75,label="{0:d}-months rolling mean".format(n_month_rolling))
ax_r = ax_l.twinx()
ax_r.plot(dates[-n_month_back:],vola[-n_month_back:]*100,color="#0078ef",linestyle="dashed",label="Volatility (right)")
# Maximum for left scale
max_l = max(np.max(price[-n_month_back:][~pd.isnull(price[-n_month_back:])]),np.max(rolling_mean_price[-n_month_back:][~pd.isnull(rolling_mean_price[-n_month_back:])]))
i = int(np.log10(max_l))
max_l = (int(max_l/10**i)+1)*10**i
# Maximum for right scale
max_r = np.max(vola[-n_month_back:][~pd.isnull(vola[-n_month_back:])]*100)
i = int(np.log10(max_l))
max_l = (int(max_l/10**i)+1)*10**i
ax_l.set_ylim([0,max_l])
ax_r.set_ylim([0,max_r])
ax_l.set_xlim([min(dates[-n_month_back:]),max(dates[-n_month_back:])])
ax_l.set_ylabel(data["Unit"])
ax_r.set_ylabel("Monthly {0:d}-months volatility [%]".format(n_month_rolling))
ax_l.legend(loc='upper left', bbox_to_anchor=(0.0, 1.12), ncol=2,frameon=False)
ax_r.legend(loc='upper right', bbox_to_anchor=(1.0, 1.12), ncol=1,frameon=False)
plt.savefig("figs/{0:s}.png".format(symbol),transparent=True,dpi=300)
# Action titles
action_titles = getActionTitles(data)
data["Action Titles"] = action_titles
return data
# Get all symbols
symbols = list(df_meta.index)
# Loop over symbols to prepare data and graphs
data_items = {}
for s in symbols:
data_items[s] = prepareData_item(s)
Store the data
with open("Commodities_item_analysis.json","w") as f_out:
json.dump(data_items,f_out)
with open("Commodities_item_analysis.json","r") as f_in:
data_items = json.load(f_in)
prs = Presentation("Financial_Advisory_template.pptx")
# Let's what layouts are available
layouts = {}
for i,l in enumerate(prs.slide_layouts):
layouts[l.name] = i
print("*",l.name)
for s in l.placeholders:
print(" ",s.name)
# Defining the slide counter
slidecounter = 1
# Generate title slide
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Title page"]])
for p in slide.placeholders:
if "Title" in p.name:
p.text = "Monthly Commodity Report"
elif "Text" in p.name:
p.text = "PyInvest Inc. | {0:s}".format(pd.datetime.today().strftime("%d %B %Y"))
elif "Picture" in p.name:
img = p.insert_picture('pictures/Title_image.jpg')
slide.shapes._spTree.remove(img._element)
slide.shapes._spTree.insert(2, img._element)
slidecounter += 1
# Enter a blank page
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Blank"]])
for p in slide.placeholders:
p.text = "{0:d}".format(slidecounter)
slidecounter += 1
# Prepare executive summary
exec_items = {}
for key,item in data_items.items():
if item["Group"]=="Index" or type(item["Currency"])==float:
continue
if not item["Group"] in exec_items:
exec_items[item["Group"]] = []
ats = [i for i in reversed(sorted(item["Action Titles"],key=lambda x:x["score"]))][:2]
exec_items[item["Group"]].append({"title":item["Name"]+": "+ats[0]["title"]+", "+ats[1]["title"],"score":ats[0]["score"]+ats[1]["score"]})
exec_text = ""
for key,item in exec_items.items():
exec_text += key+"\n"
sort_item = list(reversed(sorted(item,key=lambda x:x["score"])))
exec_text += "- "+sort_item[0]["title"]+"\n"
exec_text += "- "+sort_item[1]["title"]+"\n\n"
# Enter a summary page
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Disclaimer"]])
for p in slide.placeholders:
if "Content Placeholder 2" in p.name:
p.text = exec_text
elif "Text Placeholder" in p.name:
p.text = "{0:d}".format(slidecounter)
elif "Title" in p.name:
p.text = "Executive Summary"
slidecounter += 1
from pptx.util import Cm
from pptx.enum.shapes import MSO_SHAPE
from pptx.dml.color import RGBColor
# Defining colors
c_red = (196,0,30)
c_yellow = (196,160,34)
c_green = (0,78,34)
# Generate mid-slides
for key,item in data_items.items():
ats = [i["title"] for i in reversed(sorted(item["Action Titles"],key=lambda x:x["score"]))]
if item["Group"]=="Index" or type(item["Currency"])==float:
continue
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Commodity"]])
for p in slide.placeholders:
if "Title" in p.name:
# Text with different parts regarding format needs to be greated via runs
run1 = p.text_frame.paragraphs[0].add_run()
run1.text = item["Name"]
run1.font.bold = True
run2 = p.text_frame.paragraphs[0].add_run()
run2.text = " - "+(', '.join(ats[:2]))
elif "Picture Placeholder 1" in p.name:
img = p.insert_picture('figs/{0:s}.png'.format(key))
elif "Picture Placeholder 2" in p.name:
if '{0:s}.png'.format(key) in os.listdir('pictures'):
img = p.insert_picture('pictures/{0:s}.png'.format(key))
else:
img = p.insert_picture('pictures/Title_image.jpg')
elif "Text Placeholder 4" in p.name:
p.text = "{0:,.0f} {1:s}".format(item["Spot"],item["Currency"])
elif "Text Placeholder 5" in p.name:
p.text = "{0:+.1f}%".format(item["Last Month Change"]*100)
elif "Text Placeholder 6" in p.name:
p.text = "Source: IMF"
elif "Text Placeholder 7" in p.name:
p.text = "{0:+.1f}%".format(item["Last Year Change"]*100)
elif "Text Placeholder 8" in p.name:
p.text = "{0:d}".format(slidecounter)
elif "Text Placeholder 9" in p.name:
p.text = item["Description"]
# Add outlook shapes
shapes = slide.shapes
# Short term outlook
shape = shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE,Cm(16.2),Cm(15.5),Cm(1),Cm(1))
c_st = c_yellow
if item["ST outlook"]>0:
c_st = c_green
elif item["ST outlook"]<0:
c_st = c_red
fill = shape.fill
fill.solid()
fill.fore_color.rgb = RGBColor(*c_st)
# Long term outlook
shape = shapes.add_shape(MSO_SHAPE.ROUNDED_RECTANGLE,Cm(18.6),Cm(15.5),Cm(1),Cm(1))
c_lt = c_yellow
if item["LT outlook"]>0:
c_lt = c_green
elif item["LT outlook"]<0:
c_lt = c_red
fill = shape.fill
fill.solid()
fill.fore_color.rgb = RGBColor(*c_lt)
slidecounter += 1
# Enter a blank page to get to an even number of pages
if slidecounter%2==0:
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Blank"]])
for p in slide.placeholders:
p.text = "{0:d}".format(slidecounter)
slidecounter += 1
# Disclaimer slide
with open("disclaimer.txt") as f_i:
disclaimer = f_i.read()
# Enter a blank page
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Disclaimer"]])
for p in slide.placeholders:
if "Content Placeholder 2" in p.name:
p.text = disclaimer
elif "Text Placeholder" in p.name:
p.text = "{0:d}".format(slidecounter)
elif "Title" in p.name:
p.text = "Legal Disclaimer"
slidecounter += 1
# Generate final slide
slide = prs.slides.add_slide(prs.slide_layouts[layouts["Last page"]])
for p in slide.placeholders:
if "Text" in p.name:
run1 = p.text_frame.paragraphs[0].add_run()
run1.font.bold = True
run1.text = "Monty Python\n"
run2 = p.text_frame.paragraphs[0].add_run()
run2.text = "Commodity Analyst\n+41 (0) 89 234 56 78\nm_python@pyinvest.com"
elif "Picture Placeholder 1"==p.name:
img = p.insert_picture("pictures/Monty_Python.png")
elif "Picture Placeholder 2"==p.name:
img = p.insert_picture('pictures/Title_image.jpg')
slide.shapes._spTree.remove(img._element)
slide.shapes._spTree.insert(2, img._element)
# Adding the status
slidecounter += 1
prs.save("Report_190629.pptx")