Create Excel Sheet with Stock Prices and Moving Average with Chart all from Python

What will we cover in this tutorial?

In this tutorial we will read a historic stock prices, calculate the moving average and export that to an Excel sheet and insert a chart with prices and moving average. And all will be done from Python using Pandas and Pandas-datareader.

Step 1: Get the historic stock prices

A great place to get historic stock prices is using Pandas-datareader. It has a great interface to various places to get the data. In this tutorial we will use Yahoo API through the Pandas-datareader.

It doesn’t require any registration to use the API. It works straight out of the box.

To get stock prices in time series you need to find the ticker of your favorite stock. In this tutorial we will use Apple, which has ticker AAPL.

import pandas_datareader as pdr
import datetime as dt

start = dt.datetime(2020, 1, 1)
ticker = pdr.get_data_yahoo("AAPL", start)

print(ticker.head())

You also need to set the date to set how far back you want historic stock prices. By default, you will get up to the most current date. Also, you will data for each day open. You can use more arguments to configure what if you want weekly or monthly prices. Also, you can set a end-date, if you like.

The above code should give output similar to the following.

                 High        Low       Open      Close       Volume  Adj Close
Date                                                                          
2020-01-02  75.150002  73.797501  74.059998  75.087502  135480400.0  73.840042
2020-01-03  75.144997  74.125000  74.287498  74.357498  146322800.0  73.122154
2020-01-06  74.989998  73.187500  73.447502  74.949997  118387200.0  73.704819
2020-01-07  75.224998  74.370003  74.959999  74.597504  108872000.0  73.358185
2020-01-08  76.110001  74.290001  74.290001  75.797501  132079200.0  74.538239

Step 2: Calculate the Moving Average

To calculate the moving average (also called the simple moving average), we can use the rolling method on DataFrame.

The rolling method takes one argument, which is the window size. This indicates how big a window we want to apply a function on. In this case we want to apply the mean function on a window of size 50.

import pandas_datareader as pdr
import pandas as pd
import datetime as dt

start = dt.datetime(2020, 1, 1)
ticker = pdr.get_data_yahoo("AAPL", start)

ticker['MA'] = ticker['Close'].rolling(50).mean()

print(ticker[50:].head())

This calculates the simple moving average of window size 50. This will give the same result as the moving average with the default window of 50 will give in Yahoo! Finance.

The output will be as follows.

                 High        Low       Open  ...       Volume  Adj Close        MA
Date                                         ...                                  
2020-03-16  64.769997  60.000000  60.487499  ...  322423600.0  59.687832  76.16100
2020-03-17  64.402496  59.599998  61.877499  ...  324056000.0  62.312309  75.93815
2020-03-18  62.500000  59.279999  59.942501  ...  300233600.0  60.786911  75.67250
2020-03-19  63.209999  60.652500  61.847500  ...  271857200.0  60.321156  75.40445
2020-03-20  62.957500  57.000000  61.794998  ...  401693200.0  56.491634  75.03470

Notice we removed the first 50 rows (actually 51, as we index from 0). This is because the MA (moving average) column will not have numbers before we reach here.

Step 3: Export data to Excel and create a chart with close prices and moving average

Now this is where it all get’s a bit more complicated. It takes some reading in the manual to figure all this out.

The code is commented to explain what happens.

import pandas_datareader as pdr
import pandas as pd
import datetime as dt

# Read the stock prices from Yahoo! Finance
start = dt.datetime(2020, 1, 1)
ticker = pdr.get_data_yahoo("AAPL", start)

# Calculate the moving average with window size 50
ticker['MA'] = ticker['Close'].rolling(50).mean()

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('apple.xlsx', engine='xlsxwriter')

# Name the sheet
sheet_name = "Apple"

# We convert the index from datetime to date
# This makes the data in Excel only have the date and
# not the date with time: 00:00:00:0000
ticker.index = ticker.index.date

# Skip the first 51 rows (to be pedantic, 49 is the correct number)
ticker = ticker[50:]

# Convert the dataframe to an XlsxWriter Excel object.
ticker.to_excel(writer, sheet_name=sheet_name)

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets[sheet_name]

# Widen the first column to display the dates.
worksheet.set_column('A:A', 12)

# Get the number of rows and column index
max_row = len(ticker)
col_ma = ticker.columns.get_loc('MA') + 1
col_close = ticker.columns.get_loc('Close') + 1

# Create a chart object of type line
chart = workbook.add_chart({'type': 'line'})

# Insert the first dataset into chart
chart.add_series({
    'name': "MA",
    'categories': [sheet_name, 1, 0, max_row, 0],
    'values': [sheet_name, 1, col_ma, max_row, col_ma],
})
# Insert the second dataset in the same chart
chart.add_series({
    'name': "Close",
    'values': [sheet_name, 1, col_close, max_row, col_close],
})

# Configure axis
chart.set_x_axis({
    'name': 'Date',
    'date_axis': True,
})
chart.set_y_axis({
    'name': '$',
    'major_gridlines': {'visible': False}
})

# Insert the chart into the worksheet.
worksheet.insert_chart('I2', chart)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

The above code will create an Excel sheet looking like this.

Result

Rune