Financial and Economic Data Applications

In this final lecture, we will look at a number of useful applications of the techniques discussed in previous weeks. These all come from the worlds of finance and economics, mostly because of the abundance of data. We will also be using Wes McKinney's template .ipynb file as a skeleton for the lectures.

Throughout, we will make use of some technical jargon. Here are frequent terms that you should be familiar with:

• cross-sectional data: data that comprise a fixed point in time (for example, the current closing price of publicly traded companies on April 1 2015).
• panel data: multi-dimensional data, which frequently involve time series measurements but can also be cross-sectional.
• futures contract: a financial instrument in which two parties agree to the sale of a distinct instrument (such as corn, oil, or a stock) sometime into the future, thereby deriving its value from the value of an underlying asset. As such, it is a form of derivative contract.

Data munging topics

In previous lectures we have discussed a number of useful tools that are present within the Python data analysis ecosystem for data munging. Here we will overview a few of these tools in action.

Time series and cross-section alignment

Suppose you are given two different financial datasets on which you hope to perform a reasonable analysis. More likely than not, the series may have indices that don't line up perfectly, or (if the data is stored into a DataFrame) might have columns or row labels that don't match.

This is a terribly frequent and frustrating problem in data analysis, so much so that it has its own name: the data alignment problem. In Pandas, basic arithmetic operations between data sets performs automatic alignment. For example, let us consider the following datasets.

The stock_px.csv dataset can be downloaded here and the volume.csv dataset can be downloaded here.

One useful metric in the financial analysis of stock prices is the volume-weighted average price, or VWAP, of a stock. This metric weights the price of a stock at any given time by the amount of trades are occurring at that time. The idea is that high-volume trades give a better indication of the perceptions of institutional investors, who presumably have expert understanding, of the value of the stock.

Since Pandas aligns the data automatically and excludes missing data in functions like sum, we can express this concisely as:

Because no volume data is given for the SPX exchange-traded fund, the VWAP value for the asset is consequently absent. We can remove it from the above series by a simple call to dropna.

Manual alignment can be achieved by using the align method built-in to DataFrame objects.

You can also build DataFrame objects with Series objects that may potentially have different individual shapes without a hitch, because of Pandas automatic alignment.

Again, NaN is assigned to values for which the original Series do not cover respectively.

You can specify explicitly the index of the result, discarding the rest:

Operations with time series of different frequencies

The Federal Reserve publishes new GDP data every quarter, but only publishes inflation data annually. Publicly listed firms are required to provide income and balance sheet data quarterly, but it need not happen at the same day for every company. These types of problems for data analysts fall under the category of time series frequency problems, and Pandas provides a number of techniques for solving them.

Suppose you have a time series that contains data compiled weekly (on Wednesdays):

In certain circumstances, it might be useful to resample the data to different frequencies. For example, if you need to resample the data so that an entry is provided for every business day in the period, you can employ:

Notice here that because no new information is provided, all of the new dates are simply left NaN. If you want to fill these gaps with prevous data, you can apply various fillers with the fill_method parameter specified.

This remedy is an elegant solution to upsampling from lower frequency data to higher frequency data, but another class of frequency problems involve irregular time series data, in which the above methods will not work as neatly. Consider the following data set containing irregularly sampled data:

If you want to add the "as of" values in ts1 to ts2, one option would be to resample both to a regular frequency and then add, but if you want to maintain the date index in ts2, using reindex is a more precise solution:

Using periods instead of timestamps

Periods, as opposed to timestamps, are another common approach to organizing time series data, which can lead to its own time series frequency problems. Suppose you have the following GDP and inflation data, which are both periodic but at different frequencies:

In Pandas, unlike with timestamps, operations between different-frequency time series that are indexed by periods are not possible without explicit conversions. In this case, if we know that infl values were observed at the end of the year, we can then convert to Q-SEP to get the right periods in that frequency:

Then, we can simply reindex the inflation time series data with a forward-filling method to match the GDP data.

Time of day and "as of" data selection

Suppose you have a long time series containing intraday market data and you want to extract the prices at a particular time of day on each day of the data. What if the data are irregular such that observations do not fall exactly on the desired time? In practice this task can make for error-prone data munging if you are not careful. Here is an example for illustration purposes:

We can index ts with a datetime.time object to extract the values at 10:00 AM throughout the entire time series.

Alternatively, you can specify timestamps between two time intervals, such as 10:00 AM and 10:01 AM (inclusively).

However, it could be the case that no data actually fell exactly at 10:00 AM, but you might want to know the last known value at 10:00. In that case, you might do something like the following:

The asof method performs this functionality for you.

Splicing together data sources

In financial and economic contexts, there are a number of widely occurring use cases of merging related datasets:

• Switching from one data source to another at a specific point in time
• "Patching" missing values in a time series at the beginning, middle, or end using another time series
• Completely replacing the data for a subset of symbols

In the first case, switching from one set of time series to another at a specific instant, it is a matter of splicing together two TimeSeries or DataFrame objects using pandas.concat:

Suppose in a similar example that data1 was missing a time series present in data2:

Using combine_first, you can bring in data from before the splice point to extend the history for d item:

Since there isn't any data for d at 2012-06-12 in data2, the entry receives an NaN.

The update method for DataFrame objects performs in-place updates to the data. To fill in only holes in the data, pass in overwrite=False.

You can also perform data replacement on any subset of symbols. Below, this is used to fill in values for specific columns:

Return indexes and cumulative returns

The return of a financial asset is defined as the cumulative percent change in its price.

Here is some price data for Apple, Inc.:

Apple does not pay dividends, which would otherwise be included into the calculation of net returns. Thus, a quick-and-dirty computation of returns will suffice:

Stocks with dividend payments complicate the computation because you have to factor in the stream of payments over time. The Adj Close attempts to adjust for stock splits and dividends, but in any case, it's quite common to derive a return index, which indicates the value of a dollar's investment into the asset. For Apple, let's compute a simple return index using cumprod.

With a return index, we can manipulate the frequency at which we compute the returns quite easily.

Since no dividends or other adjustments are considered, we could have alternatively computed from the daily percent changed by resampling with a simple aggregation:

Then, to include the dividend payments you can simply add the separate dividend payment data as follows:

returns[dividend_dates] += dividend_pcts

Group transforms and analysis

Let's consider a collection of made-up assets. We first generate a universe of 1000 tickers:

Then, we can create a DataFrame containing three columns representing random portfolios for a given subset of the above tickers:

We can aggregate these random tickers by industry. In this simple example, let's use two industries: financial and technology. We can store the mapping as a Series object.

Using groupby mechanics, we can group industries and carry out group aggregation and transformations:

Of course, remember the handy describe method:

We can transform these portfolios along a particular industry by defining customized transformation functions. For example, standardizing within industry is widely used in equity portfolio research:

You can verify that each industry has mean (very nearly) 0 and standard deviation 1:

Other, built-in kinds of transforms, such as rank, can be used to make the analysis more concise.

In quantitative equity, "rank and standardize" is a common sequence of transformations. You can compose these operations concisely with a well-placed lambda, as follows:

Group factor exposures

Quantitative portfolio management takes heavy advantage of factor analysis. From Wikipedia:

Factor analysis is a statistical method used to describe variability among observed, correlated variables in terms of a potentially lower number of unobserved variables called factors. For example, it is possible that variations in four observed variables mainly reflect the variations in two unobserved variables. Factor analysis searches for such joint variations in response to unobserved latent variables.

Portfolio holdings and performance are decomposed using one or more factors represented as a portfolio of weights. A common example is a stock's beta, which measures co-movement between a stock and a benchmark (like the S&P 500). We can consider a contrived example of a portfolio constructed from three randomly-generated factors (usually called factor loadings) and some weights:

Vector correlations between each factor and the portfolio may not indicate too much:

The standard method to compute factor exposure is by least squares regression. You can do so with a number of Python libraries, from SciPy and NumPy to more advanced libraries such as statsmodels. However, Pandas makes the process particularly easy with its pandas.ols method.

Compare these with the original factor weights that were provided above arbitrarily, and you will see that this regression performed considerably better than the corrwith results; we have almost completely recovered the weights. With groupby you can compute exposures industry by industry. To do so, encapsulate the regression method with a new function, such as:

Then, simply group by industries and apply the function, passing the DataFrame of factor loadings:

Decile and quartile analysis

In many circumstances it is useful to break down data based on sample quantiles. For example, the performance of a stock portfolio could be separated into quartiles based on each stock's price-to-earnings ratio. With Pandas, the method pandas.qcut combined with groupby makes this a straightforward task.

Consider a simple trend following or momentum strategy trading the S&P 500 index via the SPY exchange-traded fund.

We compute the daily returns and a function for transforming the returns into a trend signal formed by a lagged moving sum:

Using this function, we can create and test a trading strategy that trades this momentum signal every Friday:

We can then convert the strategy returns to a return index and plot them:

Caveat: this is a naive strategy!

Suppose that you want to decompose the strategy performance into more and less volatile periods of trading. Trailing one-year annualized standard deviation is a simple measure of volatility, and we can compute Sharpe ratios to assess the reward-to-risk ratio in various volatility regimes:

Now we can divide vol into quartiles with pd.qcut and aggregating with sharpe:

These results show that the strategy performed the best during the period when the volatility was the highest.

More example applications

Future contract rolling

From Python for Data Analysis:

In practice, modeling and trading futures contracts on equities, currencies, commodities, bonds, and other asset classes is complicated by the time-limited nature of each contract. For example, at any given time for a type of future (say silver or copper futures) multiple contracts with different expiration dates may be traded. In many cases, the future contract expiring next (the near contract) will be the most liquid (highest volume and lowest bid-ask spread.

For the purposes of modeling and forecasting, it can be much easier to work with a continuous return index indicating the profit and loss associated with always holding the near contract. Transitioning from an expiring contract to the next (or far) contract is referred to as rolling. Computing a continuous future series from the individual contract data is not necessarily a straightforward exercise and typically requires a deeper understanding of the market and how the instruments are traded. For example, in practice when and how quickly would you trade out of an expiring contract and into the next contract?

We will go into one way to do so. Using scaled prices for the SPY exchange-traded fund as a proxy for the S&P 500, we have:

Now, a little bit of preamble. Let's put a couple of S&P 500 future contracts and expiry dates in a Series object.

Using Yahoo! Finance prices and a random walk with noise, we can simulate the two contracts into the future.

prices then has two time series for each contract that differ by a random amount.

The technique that we will use to splice these two separate time series into a single continuous series is by constructing a weighting matrix. Active constraints have a weight of 1 until the expiry date gets close. At that point, we decide on a roll convention. Here is a function that computes a weighting matrix with linear decay:

The weights look like this around the ESU2 expiry:

Finally, the rolled future returns are just a weighted sum of the contract returns:

Rolling correlation and linear regression

From Python for Data Analysis:

Dynamic models play an important role in financial modeling as they can be used to simulate trading decisions over a historical period. Moving window and exponentially-weighted time series functions are an example of tools that are used for dynamic models.

Correlation is one way to look at the co-movement between the changes in two asset time series. panda's rolling_corr function can be called with two return series to compute the moving window correlation.

First, let's load some stocks from Yahoo! Finance and compute the daily returns.

Then, compute and plot the one-year moving correlation.

To better capture the differences in volatility, we can use least-squares regression. OLS regression can model the dynamic relationship between a variable and one or more other predictor variables.

There are of course more sophisticated techniques than OLS regression, which can be found in the statsmodels project, but this gives a flavor for the kind of analysis that is possible.