Excel Correlation – Plotting time series data graphs in Excel
Excel Correlation – A quick introduction.
We take a quick look at plotting data and price series in Excel for correlation analysis and presentation. The plot lesson is different from the actual correlation calculation lesson which is covered separately in another post.
Looking at a graphical representation of data over time may give us important insights into how a particular variable reacted to changing market conditions. However, comparing two sets of seemingly related data can provide us with additional insight. For example, in order to assess the price of crude oil we may look at what the numbers of excess capacity were like during the selected period of analysis.
In order to construct such a graph we need to first obtain the historical time series data for the variables, in this case the WTI spot price data time series and the excess capacity of crude (million barrels per day) time series. To ensure the quality of the analysis it is important that data used in the study is obtained from reliable sources- always remember the old computer adage “Garbage in, garbage out”.
Next using EXCEL’s chart function we construct line graphs from the two time series obtained.
Note that as the scale for these two time series differ considerably (excess capacity is expressed in million barrels per day whereas WTI spot prices are expressed as USD per barrel) it becomes necessary to use a separate vertical axis for each time series to ensure a more suitable comparative perspective. This is achieved by selecting a line graph (in this case WTI prices), choosing to “Format Data Series” and selecting to plot the series on the “Secondary Axis”:
The resulting graph is our first graph (after making additional minor formatting adjustments- legend, labeling, etc). This graph is reproduced below once again with the addition of some circled off areas:
To assess the relationship between excess capacity and the WTI spot price we eyeball the trends that the line graphs are showing in relation to each other.
For example in the period between September 2007 – August 2009 (purple circle) there appears to be significant negative correlation between excess capacity and WTI spot prices, i.e. when excess capacity was declining, WTI prices were rising. By contrast the period between November 2004-September 2006 (orange circle) we see that both graphs have an upward trend, i.e. as excess capacity was rising so were WTI prices.
This changing relationship between variables can be misleading. Take the relationship witnessed between West Texas Crude Oil and the S&P 500 index in the period January 2008 to August 2009.
We see that the two data series track each other closely over this period-i.e. a strong positive correlation, meaning that as the Crude prices rose (fell) it was highly likely that we would see the S&P 500 rising (falling) too. This may lead to false assurance that the trend in West Texas Crude is a very good proxy to the trend in the S&P 500. However if we look at the trend over a wider window length, say from January 2001 onwards we can see how reliable or feasible such an assumption would be:
Over a longer historical time horizon we see that the correlation between these two data time series has not been as stable or as positively correlated as the shorter time horizon suggests. In fact there are periods (circled) when WTI and S&P 500 have experienced marked negative correlation.
We do not have to limit our analysis to raw data time series such as price and indices data, and fundamentals such as demand and supply data or excess capacity data. We can extend our correlation analysis of market variables to derived data such as volatilities, returns, etc. For example, the graph below plots trend lines for volatility experienced in Telecommunication stock prices in Pakistan.
The methodology for calculation of trend line volatility will be discussed in an upcoming course on Portfolio Risk Metrics, but for now it is sufficient to know that an upward sloping trend line indicates that average volatility, and hence riskiness of the stock as assessed by stock price movements, is increasing; a horizontal line shows stable volatility levels, whereas a down ward sloping trend line shows declining volatilities and riskiness. A comparison of these 5 telecommunication stocks show that in general all stock volatilities have followed a similar trend over the study period however with different intensities. TRG, TELE and WTL track each other closely whereas their relationship with PTC and NETSOL volatility is less strong.