Using Python to Calculate Intraday Average Stock Movement

What are the best months/days/times to day-trade stocks? It helps to look at intraday movement to seek patterns and help you decide what actions to take and when to take them. Here I look at minute-to-minute historical data for SPY (SPDR S&P 500 ETF Trust) and use Python to calculate the average movement during the time of day. I will average the total daily movement, and also break it into monthly and weekday subsets. I am not responsible for you losing any or all of your money. But please donate if you found this post helpful! ?

Data Source

First let’s download the dataset. I got my data from here:
http://firstratedata.com/i/index/SPX

They have intraday data (very valuable information btw) from 12/31/2012 to 12/31/2018. You will have to combine the files (there are some duplicated time periods). I did it manually in Excel, but feel free to try it in Python!

Start Coding!!!

Load the packages

import pandas as pd
import numpy as np
import datetime
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import os

Set the working directory

workdir = 'C:\\Users\\aip\\Documents\\Anthony local\\Python\\Intraday'
os.chdir(workdir)

#Protip: Use this to see your current directory
os.getcwd()

Read in the data

df = pd.read_csv('SPY_data.csv', index_col='Datetime',parse_dates=True,keep_date_col = True)

Create new columns to extract date and time information

df['year'] = df.index.year
df['month'] = df.index.month
df['day'] = df.index.day
df['weekday'] = df.index.weekday
df['time'] = df.index.time

Calculate the minute-to-minute percentage change

df['change'] = df['Close'].pct_change()+1

Let’s start with total daily data. Summarize the data with a pivot table and get the mean change for every minute of the day

all = pd.pivot_table(df,index=["time"],values=["change"], aggfunc=np.mean)

Calculate the cumulative change over the whole day (multiplicative)

all['cumulative'] = np.cumprod(all['change'])

Prepare the time column for plotting by converting it back into datetime format (matplotlib likey datetime. matplotlib no likey nothing else). I just created a new column and generated the range of times based on the length of the index

all['time'] = np.arange(datetime.datetime(2019,1,1,9,30,0),datetime.datetime(2019,1,1,9,30,0)+datetime.timedelta(minutes=1*len(all.index)),datetime.timedelta(minutes=1))

Here’s how you can subset the data for January only and perform the same calculations

jan = pd.pivot_table(df[df.month == 1],index=["time"],values=["change"], aggfunc=np.mean)
jan['cumulative'] = np.cumprod(jan['change'])
jan['time'] = np.arange(datetime.datetime(2019,1,1,9,30,0),datetime.datetime(2019,1,1,9,30,0)+datetime.timedelta(minutes=1*len(jan.index)),datetime.timedelta(minutes=1))

Plot the results! I hate plotting in Python.

hourloc = mdates.HourLocator()
hourfmt = mdates.DateFormatter('%I')
minfmt = mdates.DateFormatter('%M')

fig, ax = plt.subplots()

plt.minorticks_on()
plt.grid(b=True, which='both', color='.65', linestyle='-')
fig.suptitle("Average Movement (All)- SPY")
fig.set_size_inches(12, 5)

ax.xaxis.set_major_locator(hourloc)
ax.xaxis.set_major_formatter(hourfmt)
ax.xaxis.set_minor_formatter(minfmt)
ax.tick_params(axis='x', which='major', labelsize=20)

plt.plot(all['time'], all['cumulative'])

#save to png
plt.savefig('all.png')

Here’s the result:

 

See if you can match my results: