This is a series of articles to demonstrate use of Microsoft
Excel for financial data analysis; it will cover a pretty wide range of
functions such as volatility forecasting, backtesting, cross-product analysis. I
want to share some of the things I’ve found useful, hopefully for the readers
too.

Excel formulas (press Ctrl + ` to make formulas show in excel):

This post deals with an overview of historical data for
NASDAQ listed CSCO, Cisco Systems

**Getting the historical data**

Historical daily prices are available at Yahoo Finance.
I got the data going from Mar. 26 1990 to Nov. 13 2012, about 5,700 days (rows)
of data. It is usually a good idea to save the data in .xlsx format (different
if you don’t use office 2007) instead of the default .csv so that the later
applied formulas would be retained.

**Sorting data vertically**

The default spreadsheet from yahoo has the latest values at
the top and the earliest bottom. I like my data the opposite, feel free to skip
to next part unless you also want your data going from top to bottom. To do
this, we highlight all the data EXCEPT the column headers (top row), and click
on Sort.

**Overview of quartile statistics**

To keep this simple, I’ve kept only the Adjusted
Closing values for the sample period.

I usually insert a few rows at the top so that we could have
room to see the quartile and average values for each column of data. In this
case I highlighted rows 2 – 10 for the insert, and hit Freeze Pane so that these
rows would always show when we scroll down the spreadsheet.

*Why Quartiles and not Standard Deviations? Because financial data sets are usually not explained by normal/Gaussian distributions.*The excel quartile formula set allows us to see each data column’s 25% ranges going from minimum, median, to maximum. It provides a much more practical overview of the data from a risk management perspective. From the above screenshot, we can see that since inception, CSCO has spent about 50% of the time above $16.6/share, with the average price at $15.86/share.

Excel formulas (press Ctrl + ` to make formulas show in excel):

*Note that I've made the ranges reach 11,000+, it is so I wouldn't have to update the formulas until the spreadsheet exceeds that many rows.*

**dX (change in price)**

The change in price, here I use 1Day per time step, is
needed for lots of more complex analysis. Sticking with mathematical finance, I
use the logged return formula ln[X(t)/X(t-1)] where

X = last adjusted closing price

t = time step units, in this
case the day where X is observed, and X(t-1) would be the price from the
previous day.

Excel formulas (press Ctrl + ` to make formulas show in
excel):

From the above we can immediately notice a few things about CSCO. It has
grown significantly since inception, with the average day to day return at 0.09%
before transaction costs and etc. From the quartile analysis, we can see that
50% of the time it gets quite volatile, where the single day moves have
exceeded 20%.

Most importantly however, you must keep in mind that the state of the
company today is in no way identical to where it was in March of 1990.
Therefore, this 0.09%/day return is highly unlikely to persist into the future.

I will finish this post at this point. Feel free to contact me for any
help with excel data analysis.

## 0 Reflections:

Post a Comment