Tool in Data Science

Professor S Anand
CEO and Co-Founder, Gramener
Indian Institute of Technology, Madras
Excel Forecasting Visualization
Data visualization can be a great way of exploring data. And Excel is a pretty fantastic tool to be
able to do that. What I will be showing you in this session is how you can create simple
visualizations in Excel to explore time series data and use that to find meaningful insights about
the data itself.

(Refer Side Time: 00:31)

Let us start with this data. What we have is data about a series of securities that includes
currencies like the Australian Dollar, and the Canadian Dollar, the Swiss franc, and so on, as
well as some indices like the FTSE and the S and P and the NASDAQ. This apart, we also have
some commodities, like the price of silver, or the price of gold or the price of platinum.

This information, that is the values for each of these securities, we have for a period ranging
from August, that is somewhere around 8th of August, all the way down to the 6th of September,
which is a one quarter 90 day period.

Well, one thing we might be curious about is, what is the trend, how is the Australian Dollar
going, how is NASDAQ trending and so on? We may also want to see whether the value
tomorrow is likely to be higher or lower. Which of these has a higher value today which of these
fluctuates more?

These are examples of questions that we may want answered. And the way in which we might
answer them is potentially through the visualization that might look something like this. Let us
zoom in. We have for each of these securities. Let us say the FTSE the GSPC, et cetera, a sense
of what the trend looks like. So, the FTSE looks like a dipped, grows, dipped again and grows
even further, which is almost exactly the same trend as the S and P and the NASDAQ.

And it is very similar to the trend exhibited by the Australian Dollar as well, is slightly different
from the Brazilian Riyal and certainly different from something like the Hong Kong Dollar,
which seems to have a much more pronounced discrete trend, or the Israeli kroner, which has a
very different pattern as well.

So, from this, we can see that the indices seem to be going fairly similarly, but the currencies are
though somewhat close, still fairly different from each other, and we can infer what the pattern is
from those, we can also see that the Israeli kroner from the one of the very few that is dipping,
rather than rising compared to the others.

We can also see what the average values of these indices or the exchange rates are, we can see
how much they are likely to grow the next day, effectively, a forecast from this we can quickly
infer that the Brazilian Riyal is what is forecasted to grow the most tomorrow. And also, from
their variance as a percentage, we can see that the Brazilian Riyal also has had the highest
percentage fluctuation.

The Indian rupee has also had a fairly high fluctuation but is not expected to grow as much
tomorrow. The range tells us another measure of fluctuation, which is the spread between the
highest and the lowest values as a percentage. And we can see that the Brazilian Riyal has had a
6% spread. The NASDAQ has fluctuated by as much as 5%. But the Hong Kong Dollar is barely
fluctuating at 0.04%. How does one create a visual like this?
(Refer Slide Time: 03:38)

We will start by creating a pivot table. So, let us select this data range, click on Insert Pivot Table
from this table or range. And for the selection that we have, it says I can create it in a new
worksheet or an existing one. Let us create it in a new worksheet.

(Refer Slide Time: 03:53)

Now, this has condensed the dates into months by adding a month column and under that dates, I
do not really want to collapse it I just want to see all the dates at one shot.

And now I can get the rates for the individual dates. Now, this gives me for each security for
each date, what the value is. This allows me to start putting in a variety of trends. So, let us start
by adding a new column here. We will call this column security. And we will put in the value
same as let us say cell C5, that is FTSE and we will extend the selection from here, all the way
down to the bottom.

Let us get rid of the grand totals. Right click currency. Now, let us add a column for the trend.
And we will call that column trend.
The way to insert this is using the insert menu, and the Sparklines called line, where we can pick
any data range, such as from 8th of August for the FTSE all the way to the 6th of November.
And that automatically inserts a Sparkline, you can expand the cell and the Sparkline fills the
entire range. And this can now be extended all the way down to the last row, which fills all of
these trend lines automatically.

And you can make inferences about how these are moving. So, you can see that platinum, for
example, seems to have had a slightly bigger dip than silver has had, we can also look at the
average value. So, that is simply equals average of the data over this entire range. So, we start
with, let us say, this is cell G5, all the way down to the end, which would be CS5,

Now, that we have this, it can again be extended all the way down to the bottom. And we will
have the average values for each of these, we can decide how exactly we want to format these
cells, depending on the actual numbers. So, we will put in a few more decimals. For the
currencies, we can also predict what the value is going to be.

So, let us call that a forecast for which we can use a formula called growth. I will start with the
growth formula. For Australian Dollar this takes three things, the growth formula requires a set
of known y values such as these values, and a set of known x values, which are kind of like the
dates, and the new value for which we want to predict.

So, let us fill those in. What I am going to do is take these dates, values and convert them into
numbers. So, this can be formatted as a number that is the number of days since the first of
January 1980. And you will see that it increases by one for every successive date. So, we can go
all the way to the end, and then take this data, and then say for the next available date that is 7th
of November, that is simply this value, plus 1.

And we want to predict for this as an x value. So, let us do that equals growth of the known y
values, which is simply the currency values against the known x values, which is simply these
values. And I am going to press F4 to add Dollar against the formula so that when we copy paste
this down, these numbers do not change.

And that leaves us with the value that we want to predict, which is this particular value. So, let us
say that was CT Dollar. So, that is predicting that the Australian Dollar is going to be 0.96. But
how much higher is that than the previous day. So, let us put in the value of the last day, which is
CS8- 1 to look at the growth. So, that is going to be a bit over 1% say 1.5% higher than the last
date’s value. That is the forecast for the next date.

Let us take this all the way down, and we can see which of these securities is going to increase or
decrease. We unfortunately are not able to do this for missing values. And since the FTSE, S and
P and the NASDAQ have weekends that do not have values, we cannot use the growth formula.
But for the rest, we can do this and apply a conditional formatting. That gives us a color scale
that tells us which of these values in this case, the Brazilian Riyal as going to be the one with the
highest increase. We can then similarly add one for what is the variations. So, let us take the
standard deviation of the same data starting from here all the way down to the end.

And that gives us what is the standard deviation. But rather than have this as an absolute number,
it says it is 6553 plus or minus 106. At one standard deviation, it is probably better to divide this
by the mean. So, that we get the variance as a percentage of the mean. And that is slightly more
informative. It says this varies plus or minus a certain percentage.

And again, with a certain amount of conditional formatting, what we will be able to do is see
where the variation is higher. If you are not comfortable with standard deviation, then you could
just use min and max instead, and see how much the spread of the way the value is. But if there
are outliers, if there are very high and very low values, then you are probably better off with
something, which does a little bit of outlier removal. But what we have right now is a quick
sense of how each of these securities are performing. And you can think of that as the first level
of exploratory visualization of this data.
(Refer Slide Time: 10:50)
Now, that we have this another exploration you may want to do is how do these securities move
with each other? So, for instance, it looks like when the, let us say, Australian Dollar is moving
up, the Brazilian Riyal is also kind of moving up. But how close are they? And it does not look
like the Canadian Dollar, is that close?

So, can we get a quantitative measure, or an intuitive feel for how close these moves together?
Well, that is the next level of visualization where we could take for example, the correlation
between the Australian Dollar and the Brazilian Riyal and see how they tend to move together,
and you find that the patterns very close.
So, in general, when the Brazilian Riyal moves up, which is the vertical axis, the Australian
Dollar also tends to move up and vice versa. In fact, we see that about 82% or 83%, almost of the
variation in the Brazilian Riyal is explainable by the Australian Dollars movement, that is pretty
strong.

If I take the Canadian Dollar, so now we are taking the x-axis to be the Australian Dollar. How
much of the Australian Dollars movement can explain the Canadian Dollars movement? The
answer is only about 26%. Not so strong. Now, this is not exactly a perfect pattern. What about
the Swiss franc 60% slightly closer.

So, I can quantitatively say that the Australian Dollar is slightly closer to the Swiss franc than to
the, let us say, Canadian Dollar. And we can keep moving these and figuring out so at 85%, the
Chinese Yuan is even closer, closely determined by the Australian Dollar. And we can look at
any pair of currencies to see how well they are related.

So, how do we create this kind of visualization? How do we also use this to spot the outliers?
What are the specific points when we had an unusually high or an unusually low value? Well, all
it takes is pick a column let us say the Australian Dollar in this case, we want to find the
influence of the Australian Dollar on pick another column, let us say the Swiss franc.

Now, go to insert and click a Scatterplot. That gives you the scatterplot right away, and it tells
you that it is the CHF that you are predicting, but let us ignore that for now. The next thing that
you can do is right click and add a Trendline. A trendline can be linear, logarithmic, exponential,
and so on. In this particular case, we will assume that we want the linear trendline, the variations
are pretty small. And you can also add an equation and the R square to this, which you can move
around and format any way you like. So, I am going to move it here, put it in bold, make the
whole thing a little bit bigger, and reduce the chart area, small amounts so that this can go all the
way to the top. And there we are.

So, now we can do exactly the same thing that we did before, which is change any of the values
and see how well one particular currency is able to predict the value of another currency. Now,
this gives us a good feel, therefore, for which of these currencies have a strong correlation, or
regression, that is, what is the strength of the impact of one on the other. But it would be good if
I could just get the number without having to make this movement, which takes us to the next
level of abstraction, where if I could just put on for example, the slope or even the correlation
coefficient, let us just say I want the correlation coefficient of, the Brazilian Riyal against the
Australian Dollar.

So, that is equals correlation of take all the values of the Brazilian Riyal against the Australian
Dollar. So, that says 91% correlate. Great. What if I wanted to do this for the Canadian Dollar?
That is 54%. That is a lot less 27%. This is Brazilian Riyal and against the Canadian Dollar. I
want column E to stay the same. And this is 51% Canadian Dollar against the Australian Dollar.
And this is 78% Swiss franc against the Australian Dollar, and so on. And I can quickly see that
so far.

In fact, let us add a conditional formatting around this to see which is the most correlated with
the Australian Dollar. So, if I take it all the way exclude silver, go up to the Taiwanese Dollar. It
looks like at 92%, the Singaporean Dollar is closest to the New Zealand Dollar. The New
Zealand Dollar is the closest to the Australian Dollar with a 95% correlation. And I can see that
by moving this all the way up to 95% here we go. And I can see, it is pretty tight. And the
deviation from the line is pretty small for the New Zealand Dollar. Now, we got these values,
what if we could get these values for any pair of currencies, I just have these for the Australian
Dollar. What if we could do this for any pair of currencies?

(Refer Slide Time: 16:18)

Well, that you can do using a correlation matrix. And that is what this shows. So, if I take for
example, the Canadian Dollar, versus let us say the Brazilian Riyal, that is a 54% correlation. If I
take the Hong Kong Dollar, versus let us say, the Chinese Yuan that is 57% correlation.

From this, I can with the color coding see a few things wherever it is red, the correlations are low
or even negative. So, the Pakistani rupee, for example, seems to be anti correlated with most
securities, but moderately positively correlated with gold, silver, and platinum. We also see that
the Israeli kroner is not very strongly positively correlated with most securities, though it is fairly
strongly positively correlated with the Pakistani rupee here.
So, using a table like this, what we are able to do is figure out which securities move well with
each other, I can see that the Canadian Dollar is also fairly neutrally correlated. But among the
strongest correlations, let us see this something higher than 95%. But we did see the New
Zealand Dollar, and the Australian Dollar at 95%

Is there something higher than 95%? Well, one way of doing that is again, using conditional
formatting, but we can just in this case, eyeball it, and find that the Malaysian ringgit versus the
Singapore Dollar. That is 97%. And you will find this pattern interesting. It looks like both the
Malaysian ringgit and Singapore Dollar scenario is for neighboring countries, just like we saw it
for the New Zealand Dollar and the Australian Dollar.

And maybe there is something to the neighboring country effect, which is a hypothesis that you
might be able to test with this data. But first, what I will do is show you how you can create a
table of this kind, what we do is take the underlying data, which is this kind of pivot table. And
this pivot table has the dates and the securities just flipped from the pivot table that we had
earlier.

It is just instead of the rows and the columns being what they were, they are flip flopped. Now
against this, I can create a correlation matrix. To apply correlations, you need the data analysis
ToolPak, which you will find in the data menu out here under analysis called data analysis.

If you do not find it, then you may want to go to the file menu options and look for Add-ins. And
in the Add-ins section, make sure that you have the analysis ToolPak, Excel add in inserted. If
that is not available, not possible, or you are using a version of Excel that just does not have it
not a problem, you can still apply the correlation formula manually the way we just did here and
do it for all of the securities.

But if you have the analysis ToolPak, what I am about to show you becomes a whole lot easier.
So, you click on data analysis, and choose correlation. There are several other kinds of analysis
that you can do, but you choose correlation, click OK. And the input range that we select will be
effectively the same data that we have here. Each of the values as columns, and then we say
labels are present in the first row because they are present in the first row. And the data is
grouped as columns, not rows. That is how we have structured the data. And the output is going
to go into a new worksheet, click OK.
(Refer Slide Time: 19:37)

And that gives us exactly the same data that we looked at earlier. What we will do is change
these values rather than numbers to percentages.

(Refer Slide Time: 19:49)

And that makes it is little easier to see, we will also apply a conditional formatting to this and
that can be a red Amber green kind of structure. And we have exactly the same visual that we
had before, if you do not have the data analysis ToolPak, you will have to do fair bit of things
like equal to correl. And pick the right values and fill these in that can be slightly more time
consuming. But if you had data analysis ToolPak, you would be able to create this kind of data
visualization much more easily.

(Refer Slide Time: 20:24)

To summarize. what we did was took the underlying raw data, we then converted it to a set of
exploratory visuals at the next level, which helped us see what the trends were and what the
value likely to be. We then started looking for patterns within these to see whether one particular
security was more correlated with another.

And we looked at which were securities that are most correlated, which were some of the outliers
using scatter plots. And then we took it to the next level and started looking at whether there are
patterns of correlations themselves using a correlation matrix to see whether there are some
securities that are consistently anti correlated with others. Like the Pakistani rupee, or the Israeli
kroner, and whether there are some securities that are very highly correlated with each other, like
the Singapore Dollar and the Malaysian ringgit, or the Australian Dollar and the New Zealand
Dollar, both of which happened to be neighbors in this particular case. Where we want to take
this is that excel in itself is a very powerful tool for exploratory analysis and exploratory
visualization. And you can take data sets like this time series data, and create some fairly
powerful insights just from that.