What will we cover in this tutorial?
We will retrieve the historic stock prices and calculate the moving average. Then we will export the data to Excel and insert a chart, but all done from Python.
See the in depth explanation in the YouTube video. It also gives advice on how to interpret the Simple Moving Averages (SMA).
Step 1: Read historic stock prices
We will use the Pandas-datarader to get the historic prices of NFLX (the ticker for Netflix).
import pandas_datareader as pdr
import datetime as dt
ticker = "NFLX"
start = dt.datetime(2019, 1, 1)
data = pdr.get_data_yahoo(ticker, start)
print(data.head())
And you will get the historic data for Netflix from January 1st, 2019.
High Low Open Close Volume Adj Close
Date
2019-01-02 269.750000 256.579987 259.279999 267.660004 11679500 267.660004
2019-01-03 275.790009 264.429993 270.200012 271.200012 14969600 271.200012
2019-01-04 297.799988 278.540009 281.880005 297.570007 19330100 297.570007
2019-01-07 316.799988 301.649994 302.100006 315.339996 18620100 315.339996
2019-01-08 320.589996 308.010010 319.980011 320.269989 15359200 320.269989
Step 2: Understand Moving Average
We will calculate the Simple Moving Average as defined on Investopedia.
The Simple Moving Average (Now just referred to as Moving Average or MA) is defined by a period of days.
That is, the MA of a period of 10 (MA10) will take the average value of the last 10 close prices. This is done in a rolling way, hence, we will get a MA10 for every trading day in our historic data, except the first 9 days in our dataset.
We can similarly calculate a MA50 and MA200, which is a Moving Average of the last 50 and 200 days, respectively.
Step 3: Calculating the Moving Averages
We can do that by using rolling and mean.
And it is magic.
data['MA10'] = data['Close'].rolling(10).mean()
data['MA50'] = data['Close'].rolling(50).mean()
data['MA200'] = data['Close'].rolling(200).mean()
print(data.tail())
That was easy, right?
High Low Open Close Volume Adj Close MA10 MA50 MA200
Date
2021-01-12 501.089996 485.670013 500.000000 494.250000 5990400 494.250000 515.297998 502.918599 477.08175
2021-01-13 512.349976 493.010010 495.500000 507.790009 5032100 507.790009 512.989999 503.559600 477.76590
2021-01-14 514.500000 499.579987 507.350006 500.859985 4177400 500.859985 510.616995 503.894399 478.39270
2021-01-15 506.320007 495.100006 500.000000 497.980011 5890200 497.980011 506.341998 504.109600 479.06220
2021-01-19 509.250000 493.540009 501.000000 501.769989 11996900 501.769989 504.232999 504.205999 479.72065
Step 4: Visualize it with Matplotlib
We can see the data with Matplotlib.
import matplotlib.pyplot as plt
data[['Close', 'MA10', 'MA50']].loc['2020-01-01':].plot()
plt.show()
Resulting in the following plot.
Where you can see how the MA10 and MA50 move according to the price.
Step 5: Export to Excel
Now we will export the data to Excel.
For this we need to import Pandas and use the XlsxWriter engine, where you can find the details of the code.
The code can be found here.
import pandas as pd
data = data.loc['2020-01-01':]
data = data.iloc[::-1]
writer = pd.ExcelWriter("technical.xlsx",
engine='xlsxwriter',
date_format = 'yyyy-mm-dd',
datetime_format='yyyy-mm-dd')
sheet_name = 'Moving Average'
data[['Close', 'MA10', 'MA50']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]
workbook = writer.book
# Create a format for a green cell
green_cell = workbook.add_format({
'bg_color': '#C6EFCE',
'font_color': '#006100'
})
# Create a format for a red cell
red_cell = workbook.add_format({
'bg_color': '#FFC7CE',
'font_color': '#9C0006'
})
# Set column width of Date
worksheet.set_column(0, 0, 15)
for col in range(1, 4):
# Create a conditional formatted of type formula
worksheet.conditional_format(1, col, len(data), col, {
'type': 'formula',
'criteria': '=C2>=D2',
'format': green_cell
})
# Create a conditional formatted of type formula
worksheet.conditional_format(1, col, len(data), col, {
'type': 'formula',
'criteria': '=C2<D2',
'format': red_cell
})
# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})
# Add a series to the chart.
chart1.add_series({
'name': "MA10",
'categories': [sheet_name, 1, 0, len(data), 0],
'values': [sheet_name, 1, 2, len(data), 2],
})
# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})
# Add a series to the chart.
chart2.add_series({
'name': 'MA50',
'categories': [sheet_name, 1, 0, len(data), 0],
'values': [sheet_name, 1, 3, len(data), 3],
})
# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " " + ticker})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Price'})
# Insert the chart into the worksheet.
worksheet.insert_chart('F2', chart1)
writer.close()
Where the output will be something similar to this.
