Stock price analysis and visualization with Pandas

Leave a reply
Spread the love

Introduction

I recently did some Pandas-based analysis of Uber trip data for trip prediction purpose. During the process, I found Panads is really helpful in data processing and visualization, and thus this post.

However, due to obvious reasons, I am not supposed to publish internal business data online. And hence here I stripped down the original codes and changed the data source to stock prices.

Unfortunately, Uber trip data and stock price data are fundamentally different. For example, Uber trip data exhibit strong weekly pattern while stock prices do not. As the side effect, some of the analysis in this post is not practically useful, and some of it might not even make sense. But I still consider it valuable for practicing Pandas.

Pandas is really a great tool for data transformation, analyzing and visualization, as long as the data set can fit in memory.

To better understand Pandas, one needs first to have a grasp of the basic concepts. I found the best starting point was to read through Introduction to Data Structures. Once you understand the basic data structures, it should be easier to understand the others.

For those need to deal with time series, you should also read through Time Series / Date functionality.

Preparation

As with any data science work, we start with data. Here we need to get historical stock data. We used to have Yahoo Finance and Google Finance python packages at our disposal. The problem is that Yahoo seems to be a sinking ship now. As of May 2017, the Yahoo Finance API for historical data is broken while Google Finance provides realtime data only.

So let me roll up my sleeves, take a detour and provide an API for query historical data from the Google Finance web site.

In [1]:
from datetime import date
from dateutil.relativedelta import relativedelta
import urllib.request

class GoogleFinanceAPI():
    def __init__(self, symbol):
        self._symbol = symbol.upper()
        
    # Warning: if you call this method for too much data, Google finance truncates it and only gives you partial
    def get_simple_historical(self, start_date, end_date=date.today()):
        url_string = "http://www.google.com/finance/historical?q={0}&startdate={1}&enddate={2}&output=csv".format(
            self._symbol, str(start_date), str(end_date))
        with urllib.request.urlopen(url_string) as f:
            csv = f.read()
        return csv;
    
    # Split the whole query into 10 years each to avoid truncating
    def get_full_historical(self, start_date, end_date=date.today()):
        results = []
        while start_date < end_date:
            query_start_date = end_date + relativedelta(years = -10)
            if query_start_date < start_date:
                query_start_date = start_date
            partial_historical_data = self.get_simple_historical(query_start_date, end_date)
            results.append(partial_historical_data)
            end_date = query_start_date
        return results

Go get the data

In [2]:
msft = GoogleFinanceAPI('msft')
# Google Finance data indicates MSFT stock price started from 3/13/1986
# Is that Microsoft's IPO day? I am not old enought to know, and feeling lazy not to search at this moment
msft_csvs = msft.get_full_historical(date(1986, 3, 13))

Meet the Pandas

Pandas enjoys CSV data. In the following codes, we see

  1. read_csv() for creating pandas data frame from csv data. Please note the three named parmeters passed.
    1. The dtype parameter is to inform Pandas about the date field data type
    2. The encoding tells Pandas that the string data contains BOM mark ‘\xef\xbb\xbf’. Without using the ‘utf-8-sig’ encoding parameter, you would run into key error problem when accessing the ‘Date’ column. For there are hidden characters before the string ‘Date’ in the column name.
    3. The parse_dates indicate to Pandas to parse the first column and generate date object. Without this option, you won’t be able to use many of the Pandas’s nice time series manipulation capabilities.
  2. concat() to concatenate multiple homgeneous data frames together
  3. head() and tail() for quick inspection of the data, they are for dumping the first and last few rows of the data table respectively
  4. set_index() call is used to make the date field as the indexing field.
In [3]:
import numpy as np
import pandas as pd
import io

msft_dfs = []
for msft_csv in msft_csvs:
    msft_dfs.append(pd.read_csv(io.BytesIO(msft_csv), encoding='utf-8-sig', parse_dates=[0], dtype={'Date': date}))
msft_df = pd.concat(msft_dfs)
msft_df = msft_df.set_index(['Date'])
print(msft_df.head())
print(msft_df.tail())

             Open   High    Low  Close    Volume
Date                                            
2017-07-03  69.33  69.60  68.02  68.17  16165538
2017-06-30  68.78  69.38  68.74  68.93  24161068
2017-06-29  69.38  69.49  68.09  68.49  28918715
2017-06-28  69.21  69.84  68.79  69.80  25806200
2017-06-27  70.11  70.18  69.18  69.21  25215128
            Open  High   Low  Close    Volume
Date                                         
1986-03-19   0.0   0.1  0.10    0.1  47846304
1986-03-18   0.0   0.1  0.10    0.1  66403296
1986-03-17   0.0   0.1  0.10    0.1  33036768
1986-03-14   0.0   0.1  0.10    0.1   7852896
1986-03-13   0.0   0.1  0.09    0.1  30758400

Without further ado, let’s get some visualization. A few points:

  1. If for any reason, you would like to reverse the order of the x-axis, you can use the invert_xaxis() call after plot().
  2. title() and set_size_inches() are self-explanatory
  3. We only plotted the closing price. If you want to plot multiple columns at the same time, you can use [‘Open’, ‘Close’] in place of ‘Close’. You can put arbitrary number of entries in the list. I did not do it because all these numbers are so close, without zooming in, practically only one series of data is visible even if you show all.
  4. You don’t want to plot every thing, i.e. msft_plot.plot(), because the high numerical values of volume data will obscure everything else.
  5. Note when you pass a list as the parameter, you should have double brackets, i.e. [[‘Open’, ‘Close’]].
In [4]:
import matplotlib.pyplot as plt
%matplotlib inline

msft_df['Close'].plot()
plt.title('Microsoft stock closing prices - All')
plt.gcf().set_size_inches(15, 8)

Extracting and plotting data for a single year is almost one liner:

In [5]:
msft_df['2017']['Close'].plot()
plt.title('Microsoft stock closing prices - 2017')
plt.gcf().set_size_inches(15, 8)

Some advanced techniques

Let’s check if the stock exibits any movement patterns within a week, i.e. does MSFT usually close at a higher price than Tuesday? We evaluate this by dividing the day’s price by the sum of the corresponding five weekdays’ closing prices. There are obviously exceptions, i.e. during holiday time. Please excuse me for not dealing with this complexity. For the whole thing at this point is hypothetical, and it is only for coding illustrations.

Pandas resample

  1. resample() function call reduces the number points and allows aggregation.
  2. asfreq() is the inverse of resample. It expands the data into finer granularity, and the “bfill” method means copy data points backwards.
  3. The spikes in the data is due to the fact that the code did not incorporate holidays. So weeks with holidays will have higher contribution values for each weekday of that week.
  4. As we can see from the plot, the distribution seems random.
In [6]:
from pandas.tseries.offsets import *

weekly_sum = msft_df.resample('W-FRI').sum()
weekly_sum_daily = weekly_sum.asfreq(BDay(), method = 'bfill')
weekly_contrib = (msft_df / weekly_sum_daily)
weekly_contrib['2016']['Close'].plot()
plt.title('Microsoft stock ratio of every weekday price to the weekly price total - 2016')
plt.gcf().set_size_inches(15, 8)

For the closing price, let’s create a table (DataFrame) with columns as Monday, Tuesday, Wednesday, Thursday, Friday, and each row represents a week. From this table, we can easily extract Monday’s value of the weekly distribution.

In the Monday plot below, the gaps should be because that Monday is a holiday. The spikes are due to holidays in the week (other than Monday).

In [7]:
# With the following line of code, each row is for a week, and the one column is
#   the list of values of weekday distributions
weekly_contrib_resample = weekly_contrib['Close'].resample('W-FRI').apply(lambda x: x.tolist()[0:5])

# Seperate the list of weekday values into five columns
weekly_contrib_rect = weekly_contrib_resample.apply(pd.Series)

# Rename the columns
column_names = ['Mon', 'Tue', 'Wed', 'Thur', 'Fri']
weekly_contrib_rect = weekly_contrib_rect.rename(columns=lambda x: column_names[x])

weekly_contrib_rect['2016'].plot()
plt.title('Microsoft stock ratio of weekday price to the week price total - 2016')
plt.gcf().set_size_inches(15, 8)
plt.figure()
weekly_contrib_rect['2016']['Mon'].plot()
plt.title('Microsoft stock weekly ratio of monday price to the week price total - 2016')
plt.gcf().set_size_inches(15, 8)

Histograms

Histograms are good approximation of distributions.

In [8]:
stacked = False
weekly_contrib_rect['Mon'].plot.hist(stacked = stacked, bins=80)
plt.title('weekly contributiona - All')
plt.gcf().set_size_inches(15,8)
Post Views: 8,447

Leave a Reply