Data Preparation

	Attachment	Size
	Data Preparation.xls

The econometric theory assumes homogeneous (equally spaced) observations throughout time, so hourly, daily, weekly, or monthly observations can be used for that purpose. Please note, although "daily" in a financial time series references a working day (as opposed to a calendar day), the time series is still treated as equally spaced.

TIP: NumXL does not requires the date component of the series, assuming that observations are already homogeneous. If this is not the case, you can use one of the DATE Series functions in CalendarXL to generate the date component, then the INTERPOLATE function in NumXL to interpolate the corresponding values.

Usually, a data set is stored in Excel as a single column in an ascending or descending manner.

Recommendations: It is recommended that you copy the date components and the associated observation used in this example.

Example 1:

Using the Yahoo Finance website (finance.yahoo.com), run a query for the historical prices for Microsoft Corp between January 3rd, 2000 and May 1st, 2009 and download the data as an MS Excel file (i.e. use the hyperlink shown immediately after the result table).

Next, load the data into Excel as follows:

Raw Data

Please note:

We have 5 prices: Open, High, Low, Close and Adjusted Close.
Observations are sorted in ascending chronicle order; the date increases as we scroll down the sheet. Depending on the format of the data that Yahoo provides, your observations may instead be sorted in descending order. If so, please make a note of it for future reference.

Next, we'll plot the adjusted closing price over time.

Price Plot

Save the spreadsheet as an MS Excel workbook.

Strategy's Returns

The price time series does not look like a random signal, and in many cases one can detect a trend over time. The econometric theory assumes a weak stationary process (variance and covariance constant over time). To overcome this issue, we compute our strategy returns and examine this time series instead.

Strategy: In this example, we construct a hypothetical strategy that buys MSFT at market open and closes the position at market close.

$R_t=\frac{S_t^C}{S_t^O}$

$r_t=ln(\frac{S_t^C}{S_t^O})=ln(S_t^C)-ln(S_t^O)$

Where:

$R_t$ : Daily gross return

$S_t^C$ : Daily price at market close

$S_t^O$ : Daily price at market open

$r_t$ : Daily log return

Next, we construct a daily log return series as a new column (e.g. column H) in the worksheet.

MSFT daily log returns

The graph above shows the daily log returns, which look like random noise. We will use this series for our analysis.

TIP: Create a defined name (e.g. RETURNS) in Excel for the daily log returns cell range. (See Appendix A)