Analyzing Stock Price Data with Sho, Part I

One fun arena for doing data analysis and visualization is the stock market.  Fortunately, a lot of stock market data is now available publicly,  and Sho makes it easy and fun to do various kinds of analysis on it. We’ll look at data from the stocks in the S&P 500 index, since that data is readily available – you can get the last year of daily prices from various locations, including

Start by downloading the data – pick the “Historical” option and click “Get Full Set;” this will give you a zip file which will contain a single file, sp500hst.txt.  Save that to some handy directory.  If we open up that file in a text editor, we’ll see a bunch of lines like the below:


In other words, the date, followed by the symbol, followed by open, high, low, close, and the volume for that day.  We’re interested in grabbing the closing value for each day for a given stock so that we can plot it, compute running averages, make predictions, etc.  Let’s write a function that will extract this from the file:

def getseries(symbol, filename):
    data = csvreadArray(filename)
    dates = []
    prices = []
    for line in data.Rows:
        if line[1] == symbol:
            dates.Add(line[0]) # date
            prices.Add(float(line[5])) # closing price

This will get us the dates and closing price for a given symbol.  Note that we convert the list of prices to a DoubleArray for ease of later manipulations. Let’s retrieve the prices for symbol “CBS” and plot the result:

>>> dates, prices = getseries("CBS", "c:/mypath/sp500hst.txt")
>>> plot(prices)


That gives us a first look at the raw data.  Note that the x axis labels are just the index number of each element; we’d rather see the appropriate date labels for the x axis, but since they’re strings and not numeric values, we can’t just pass them in via plot(x,y).  We can instead modify the x-axis labels with the xlabels command – we just need to tell it which locations to label and what the respective values would be, so we’ll create a series of numbers spaced by 10 and then pick out the appropriate labels with a list comprehension:

>>> sd = range(0,len(dates),10)
>>> xlabels(sd, [dates[ind] for ind in sd], orientation=90) # create date labels on x axis and turn them 90 degrees


Now we’d like to see the moving averages over different windows for the stock in addition to its daily value.  We can use the signal processing tools in Sho to easily compute a moving average by convolving the time series with a box filter, which we can represent via ones(N,1)/N where N is the length of the filter.  We divide by N to normalize the sum of the filter to 1.  We’ll compute a 10-day and a 30-day moving average.

>>> smooth10 = SignalProc.conv(DoubleArray.From(prices).T, ones(10,1)/10.0, CompMode.Auto, BoundaryMode.Reflect)
>>> smooth30 = SignalProc.conv(DoubleArray.From(prices).T, ones(30,1)/30.0, CompMode.Auto, BoundaryMode.Reflect)

We’ll now plot these smoothed signals on top of the original prices by using the hold() command to hold the previous plot.  Finally, we’ll add a legend to label the separate time series; we’ll also add a title for good measure.

>>> hold()
>>> plot(smooth10)
>>> plot(smooth30)
>>> legend(‘daily prices’,’10-day average’,’30-day average’)
>>> plottitle(‘Daily Prices for CBS’)

That gets us to the plot below:


In the next post, we’ll show how build a simple autoregressive model for predicting future prices (though not with particularly high accuracy).

Comments (3)

  1. chasingbeta says:

    Keep up the great work on your blog.

  2. Ivan says:

    Thank you, this helped me get started. When is the next blog entry

    "In the next post, we’ll show how build a simple autoregressive model for predicting future prices (though not with particularly high accuracy."

    due out?

  3. Tom says:

    Are you using Microsoft Excel,  Access to write these formulas???