Trend Analysis, Comparing Two Metrics with Web Intelligence

[Former Member]

Introduction

This is a quick how to for comparing two time based metrics in order to determine the relative differences in their trends. A typical example of this is comparing two stock prices where you are looking to determine which has performed better over a given period.

The technique deployed here is to chart the two metrics where we set a base value of 100 for both metrics at the start of our period. An example of such a chart is shown below,

Figure 1 Example Trend Analysis Chart

We also see this sort of analysis used quite often in articles by the Economist such as this articlecomparing the difference in price of cereals. Another example is seen in Yahoo Finance when we use their stock chart to compare two stock prices.

To calculate these charts we divide each value by the initial value and then multiply by a hundred to express as a percentage,

Trend Value = ( actual value / initial value ) * 100

Web Intelligence

We'll now look at how to generate this using Web Intelligence and in this example we're using Web Intelligence XI 3.1.

Here we going to build a chart comparing two stock prices of two competitors - IBM and Accenture, the query is returning the following objects,

     
• Stock - the symbol for the company whose stock price we're trending
     
• Closing Price - the closing stock price value
     
• Date - the date of the closing stock price

The data is weekly and the closing price is the price of the stock at the end of the week. The data covers the period Jan 2010 to Nov 2010. The screen shot below displays a chart in Web Intelligence of the two stock prices and we can see that the price of IBM is higher than that of Accenture but the question is: which has performed better over the time period?

Figure 2 - Stock Prices for IBM and Accenture

First we need to calculate our trend value. To do this we create a new variable that returns the initial value for each stock and for this we use the First() function which returns the first value in a data set. However we need this to reset for each change in Stock object and so we define our variable as,

Initial value =First([Closing Price]) In ([Stock])

Once we've created this variable we can then create a new variable for Trend Value and this is simply,

Trend Value =([Closing Price] / [Initial Value])*100

Both of these are of type measure. From the templates tab we then add a Vertical Mixed line chart to our report. We then add our Trend Value variable to the measures area, Stock to the dimension area and Date to the x-axis dimension area. We now see the relative change in stock price between the two stocks as displayed in the screenshot below.

Figure 3 - Trend Analysis of stock prices showing relative change in stock price

Here it is apparent that the change in price is quite close with IBM just ahead of Accenture by end of the time period. If we look back at our original chart (figure 2 above) it should now be obvious that using this trend analysis it is much easier to establish which is the better performing stock.

The simplest way to interpret these charts is to consider investing $100 in each stock at the beginning of the period. Since the chart is base lined at 100 at the beginning of the period the chart then shows the return on this $100 investment over the period.

So we can see that for most of 2010 we would have lost money as our trend line falls below the $100 mark and its only in September that we would have made a profit. At the end of the period we see IBM edging ahead of Accenture with a value of about 111 while Accenture is at 104. So if we invested $100 in both stocks we would get back $111 from our IBM investment and $104 from Accenture.