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 a 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.
Python for Finance: Unlock Financial Freedom and Build Your Dream Life
Discover the key to financial freedom and secure your dream life with Python for Finance!
Say goodbye to financial anxiety and embrace a future filled with confidence and success. If you’re tired of struggling to pay bills and longing for a life of leisure, it’s time to take action.
Imagine breaking free from that dead-end job and opening doors to endless opportunities. With Python for Finance, you can acquire the invaluable skill of financial analysis that will revolutionize your life.
Make informed investment decisions, unlock the secrets of business financial performance, and maximize your money like never before. Gain the knowledge sought after by companies worldwide and become an indispensable asset in today’s competitive market.
Don’t let your dreams slip away. Master Python for Finance and pave your way to a profitable and fulfilling career. Start building the future you deserve today!
Python for Finance a 21 hours course that teaches investing with Python.
Learn pandas, NumPy, Matplotlib for Financial Analysis & learn how to Automate Value Investing.
“Excellent course for anyone trying to learn coding and investing.” – Lorenzo B.
