Slide writing with Python

In [2]:
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
In [3]:
import pptx
from pptx import Presentation
In [4]:
prs = Presentation()
In [5]:
for l in prs.slide_layouts:
    print("*",l.name)
    for s in l.placeholders:
        print("  ",s.name)
* Title Slide
   Title 1
   Subtitle 2
   Date Placeholder 3
   Footer Placeholder 4
   Slide Number Placeholder 5
* Title and Content
   Title 1
   Content Placeholder 2
   Date Placeholder 3
   Footer Placeholder 4
   Slide Number Placeholder 5
* Section Header
   Title 1
   Text Placeholder 2
   Date Placeholder 3
   Footer Placeholder 4
   Slide Number Placeholder 5
* Two Content
   Title 1
   Content Placeholder 2
   Content Placeholder 3
   Date Placeholder 4
   Footer Placeholder 5
   Slide Number Placeholder 6
* Comparison
   Title 1
   Text Placeholder 2
   Content Placeholder 3
   Text Placeholder 4
   Content Placeholder 5
   Date Placeholder 6
   Footer Placeholder 7
   Slide Number Placeholder 8
* Title Only
   Title 1
   Date Placeholder 2
   Footer Placeholder 3
   Slide Number Placeholder 4
* Blank
   Date Placeholder 1
   Footer Placeholder 2
   Slide Number Placeholder 3
* Content with Caption
   Title 1
   Content Placeholder 2
   Text Placeholder 3
   Date Placeholder 4
   Footer Placeholder 5
   Slide Number Placeholder 6
* Picture with Caption
   Title 1
   Picture Placeholder 2
   Text Placeholder 3
   Date Placeholder 4
   Footer Placeholder 5
   Slide Number Placeholder 6
* Title and Vertical Text
   Title 1
   Vertical Text Placeholder 2
   Date Placeholder 3
   Footer Placeholder 4
   Slide Number Placeholder 5
* Vertical Title and Text
   Vertical Title 1
   Vertical Text Placeholder 2
   Date Placeholder 3
   Footer Placeholder 4
   Slide Number Placeholder 5

Data preparation

In [6]:
df = pd.read_csv("Commodities.csv")
In [7]:
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)
In [8]:
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

In [9]:
# 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
In [10]:
# Get all symbols
symbols = list(df_meta.index)
In [11]:
# Loop over symbols to prepare data and graphs
data_items = {}
for s in symbols:
    data_items[s] = prepareData_item(s)
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/pandas/plotting/_converter.py:129: FutureWarning: Using an implicitly registered datetime converter for a matplotlib plotting method. The converter was registered by pandas on import. Future versions of pandas will require you to explicitly register matplotlib converters.

To register the converters:
	>>> from pandas.plotting import register_matplotlib_converters
	>>> register_matplotlib_converters()
  warnings.warn(msg, FutureWarning)
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/matplotlib/pyplot.py:514: RuntimeWarning: More than 20 figures have been opened. Figures created through the pyplot interface (`matplotlib.pyplot.figure`) are retained until explicitly closed and may consume too much memory. (To control this warning, see the rcParam `figure.max_open_warning`).
  max_open_warning, RuntimeWarning)
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/ipykernel_launcher.py:33: RuntimeWarning: invalid value encountered in double_scalars
/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/ipykernel_launcher.py:34: RuntimeWarning: divide by zero encountered in double_scalars

Store the data

In [12]:
with open("Commodities_item_analysis.json","w") as f_out:
    json.dump(data_items,f_out)

Write the slides

In [13]:
with open("Commodities_item_analysis.json","r") as f_in:
    data_items = json.load(f_in)
In [14]:
prs = Presentation("Financial_Advisory_template.pptx")
In [15]:
# 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)
* Title page
   Picture Placeholder 7
   Title 1
   Text Placeholder 25
* Executive Summary
   Title 1
   Content Placeholder 2
   Text Placeholder 10
* Sector
   Picture Placeholder 9
   Picture Placeholder 14
   Title 1
   Text Placeholder 11
   Text Placeholder 18
   Text Placeholder 11
   Text Placeholder 10
* Commodity
   Picture Placeholder 14
   Picture Placeholder 9
   Title 1
   Text Placeholder 11
   Text Placeholder 11
   Text Placeholder 18
   Text Placeholder 11
   Text Placeholder 10
   Text Placeholder 35
* Blank
   Text Placeholder 10
* Disclaimer
   Title 1
   Content Placeholder 2
   Text Placeholder 10
* Last page
   Picture Placeholder 11
   Picture Placeholder 7
   Text Placeholder 5
In [16]:
# Defining the slide counter
slidecounter = 1
In [17]:
# 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
In [18]:
# 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
In [19]:
# 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"
In [20]:
# 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
In [21]:
from pptx.util import Cm
from pptx.enum.shapes import MSO_SHAPE
from pptx.dml.color import RGBColor
In [22]:
# Defining colors
c_red = (196,0,30)
c_yellow = (196,160,34)
c_green = (0,78,34)
In [23]:
# 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
In [24]:
# 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
In [25]:
# 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
In [26]:
# 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
In [27]:
prs.save("Report_190629.pptx")