Weekly Market Update

by Dylan Berkenfeld and Morgen Lerner

Overview

The purpose of this Python code is to automate the process that is used to create a weekly financial market update. So, as an analyst, you could run this code at the end of every week and create an automatically populated PowerPoint Presentation. For the purpose of this document, we have provided an update on general market data (e.g., S&P 500) as well as an update on the FAANG stocks. However, this focus could easily be changed for various purposes.

A few notes: Please ensure that you pip install all of the required installations below. Additionally, some of the data that is used to create the presentation is not published until Friday evening. As such, we designed the file to be run every Saturday. You will notice that we reference ‘yesterday’ as an end point for some of the series as a result of this. Additionally, it is worth noting that this file will work if run on a day that is not Saturday. However, some of the data may not correctly align to dates as a result of this. The file could be updated to run on any day, but we have chosen Saturday.

Additionally, the file will save a PPT file in the same file that this is stored. Once a file has been created, in order to re-run this you must delete the existing file.”

Section 1: Installations

Step 1: Install required installations

pip install python-pptx

pip install pandas-datareader

pip install fredapi

Section 2: Data

Step 2: import relevant functions

from pptx import Presentation
from datetime import date, timedelta
import pandas as pd 
from fredapi import Fred
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE
from pptx.util import Inches, Pt
import pandas_datareader as dr

Step 3: pull data from FRED API

fred = Fred(api_key='...')

[Note: You’ll have to get your own API key from FRED for this to work.)

first data pull: S&P500

sp500 = fred.get_series('SP500')
sp500_latest = round(sp500[-1],2)
sp500_week = round(sp500[-5],2)
sp500_month = round(sp500[-30],2)

second data pull: NASDAQ

nasdaq = fred.get_series('NASDAQCOM')
nasdaq_latest = round(nasdaq[-1],2)
nasdaq_week = round(nasdaq[-5],2)
nasdaq_month = round(nasdaq[-30],2)

third data pull: Dow Jones

dowjones = fred.get_series('DJIA')
dowjones_latest = round(dowjones[-1],2)
dowjones_week = round(dowjones[-5],2)
dowjones_month = round(dowjones[-30],2)

Step 4: pull data from pandas_datareader’s yahoo! finance add-in

fourth data pull: trailing week’s data on big 5 tech stocks

end_date = date.today() - timedelta(days=1)
start_date = date.today() - timedelta(days=6)
fb_data = dr.get_data_yahoo('fb', start = start_date, end = end_date)
goog_data = dr.get_data_yahoo('goog', start = start_date, end = end_date)
aapl_data = dr.get_data_yahoo('aapl', start = start_date, end = end_date)
nflx_data = dr.get_data_yahoo('nflx', start = start_date, end = end_date)
amzn_data = dr.get_data_yahoo('amzn', start = start_date, end = end_date)

Section 3: Data Analysis and Calculations

today = date.today()
yesterday = today - timedelta(days=1)
week_list = []
for x in range (1, 6):
    week_list.append(today - timedelta(days = x))
sp500_wkly_change = round((sp500_latest/sp500_week-1)*100,2)
nasdaq_wkly_change = round((nasdaq_latest/nasdaq_week-1)*100, 2)
dowjones_wkly_change = round((dowjones_latest/dowjones_week-1)*100,2)
fb_close = round(fb_data['Close'][-1],2)
fb_change = round((fb_close / fb_data['Close'][0] -1)*100,2)
goog_close = round(goog_data['Close'][-1],2)
goog_change = round((goog_close / goog_data['Close'][0] -1)*100,2)
aapl_close = round(aapl_data['Close'][-1],2)
aapl_change = round((aapl_close / aapl_data['Close'][0] -1)*100,2)
nflx_close = round(nflx_data['Close'][-1],2)
nflx_change = round((nflx_close / nflx_data['Close'][0] -1)*100,2)
amzn_close = round(amzn_data['Close'][-1],2)
amzn_change = round((amzn_close / amzn_data['Close'][0] -1)*100,2)

Section 4: Add Slides

Step 5: define ppt variables

prs = Presentation()
title_slide_layout = prs.slide_layouts[0]
bullet_slide_layout = prs.slide_layouts[1]
chart_slide_layout = prs.slide_layouts[5]

Step 6: insert slides

title slide

cover = prs.slides.add_slide(title_slide_layout)
title = cover.shapes.title
subtitle = cover.placeholders[1]
title.text = "Weekly Financial Market Update"
subtitle.text = f'Week Ending {yesterday} \nPrepared {today}'

exec summary

execsum = prs.slides.add_slide(bullet_slide_layout)
shapes = execsum.shapes
title_shape = shapes.title
body_shape = shapes.placeholders[1]
title_shape.text = 'Executive Summary'
body = body_shape.text_frame
body.text = f'The S&P500 closed the week at {sp500_latest}'
bullet = body.add_paragraph()
if sp500_wkly_change > 0:
    bullet.text = f'This represents a {sp500_wkly_change}% increase since COB Monday'
else:
    bullet.text = f'This represents a {sp500_wkly_change}% decrease since COB Monday'
bullet.level = 1
bullet = body.add_paragraph()
bullet.text = f'The Dow Jones closed the week at {dowjones_latest}'
bullet = body.add_paragraph()
if dowjones_wkly_change > 0:
    bullet.text = f'This represents a {dowjones_wkly_change}% increase since COB Monday'
else:
    bullet.text = f'This represents a {dowjones_wkly_change}% decrease since COB Monday'
bullet.level = 1
bullet = body.add_paragraph()
bullet.text = f'The Nasdaq closed the week at {nasdaq_latest}'
bullet = body.add_paragraph()
if nasdaq_wkly_change > 0:
    bullet.text = f'This represents a {nasdaq_wkly_change}% increase since COB Monday'
else:
    bullet.text = f'This represents a {nasdaq_wkly_change}% decrease since COB Monday'
bullet.level = 1

sp500 line chart

line_chart_weekly = prs.slides.add_slide(chart_slide_layout)
title_shape = line_chart_weekly.shapes.title
title_shape.text = 'Weekly SP500 Performance'
chart_data = CategoryChartData()
chart_data.categories = week_list
chart_data.add_series('SP500',(sp500[-1],sp500[-2],sp500[-3],sp500[-4],sp500[-5]))
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
chart = line_chart_weekly.shapes.add_chart(
    XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
).chart
chart.has_legend = True
chart.legend.include_in_layout = False
chart.series[0].smooth = True

dow jones line chart

line_chart_weekly = prs.slides.add_slide(chart_slide_layout)
title_shape = line_chart_weekly.shapes.title
title_shape.text = 'Weekly Dow Jones Performance'
chart_data = CategoryChartData()
chart_data.categories = week_list
chart_data.add_series('Dow Jones',(dowjones[-1],dowjones[-2],dowjones[-3],dowjones[-4],dowjones[-5]))
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
chart = line_chart_weekly.shapes.add_chart(
    XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
).chart
chart.has_legend = True
chart.legend.include_in_layout = False
chart.series[0].smooth = True

nasdaq line chart

line_chart_weekly = prs.slides.add_slide(chart_slide_layout)
title_shape = line_chart_weekly.shapes.title
title_shape.text = 'Weekly Nasdaq Performance'
chart_data = CategoryChartData()
chart_data.categories = week_list
chart_data.add_series('Nasdaq',(nasdaq[-1],nasdaq[-2],nasdaq[-3],nasdaq[-4],nasdaq[-5]))
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
chart = line_chart_weekly.shapes.add_chart(
    XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
).chart
chart.has_legend = True
chart.legend.include_in_layout = False
chart.series[0].smooth = True

FAANG stock update

slide = prs.slides.add_slide(chart_slide_layout)
title_shape = slide.shapes.title
title_shape.text = 'FAANG Stock Weekly Update'
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(2)
shape = slide.shapes.add_table(6, 3, x, y, cx, cy)
table = shape.table
cell = table.cell(0, 0)
cell.text = 'Stock'
cell = table.cell(1, 0)
cell.text = 'Facebook'
cell = table.cell(2, 0)
cell.text = 'Amazon'
cell = table.cell(3, 0)
cell.text = 'Apple'
cell = table.cell(4, 0)
cell.text = 'Netflix'
cell = table.cell(5, 0)
cell.text = 'Google'
cell = table.cell(0, 1)
cell.text = 'Close ($)'
cell = table.cell(1, 1)
cell.text = str(fb_close)
cell = table.cell(2, 1)
cell.text = str(amzn_close)
cell = table.cell(3, 1)
cell.text = str(aapl_close)
cell = table.cell(4, 1)
cell.text = str(nflx_close)
cell = table.cell(5, 1)
cell.text = str(goog_close)
cell = table.cell(0, 2)
cell.text = 'Weekly Percent Change'
cell = table.cell(1, 2)
cell.text = str(fb_change)
cell = table.cell(2, 2)
cell.text = str(amzn_change)
cell = table.cell(3, 2)
cell.text = str(aapl_change)
cell = table.cell(4, 2)
cell.text = str(nflx_change)
cell = table.cell(5, 2)
cell.text = str(goog_change)

Section 5: Save File

prs.save(f'Market Update {today}.pptx')

Original files available here:

• Weekly Market Update — Dylan Berkenfeld Morgen Lerner.ipynb
• Market Update 2019–10–30.pptx