3
$\begingroup$

If I have data on a time series with the following structure

 Day   Value, p
  1     1.2
  2     2.1 
  3     4.0
  4     3.2

How do I calculate the first order serial covariance of the value $p$ on day $t$ with the value from the previous day, i.e. $\text{Cov}(p_t,p_{t-1})$? (I am using Excel)

$\endgroup$
1

3 Answers 3

6
$\begingroup$

Excel has some little-known but powerful functions for operating on arrays. Although they are clunky, they work; and knowing how to use them reduces the chance of making errors (and likely improves computational efficiency). In particular, using these functions helps one to avoid copying data. (Having copies of data in various places creates opportunities for corruption and inconsistency, as well as resulting in a confusing document that is more difficult to check.)

A lagged covariance requires functions to shift (that is, "lag") a vector and to compute covariances. The shift function is called OFFSET and the covariance function is COVAR.

I recommend naming arrays rather than using direct references to them. In this example, let's suppose the range of four cells in which the "Value" data are contained has been named Values. Then an expression to lag this (vertical) array by a number of rows $j$ is

OFFSET(Values,j,0,4-j,1)

The first occurrence of $j$ shifts Values down by $j$ rows. The second occurrence, in the form $4-j$, cuts off the shifted array at the end so it can match the first $4-j$ entries in Values. Those, too, should be cut using OFFSET. Thence the full formula is

=COVAR(OFFSET(Values,0,0,4-j,1), OFFSET(Values,j,0,4-j,1))

For the lag-one covariance, just replace "$j$" by "$1$" all three places it occurs.


An easy flexible way to study the serial covariances more generally is to create a column of values of $j$, to which this formula may refer. Type it in once and copy it down its column. That leads to the following solution in which values $j=0,1,2,3$ (the maximum possible lag is one less than the length of the array) are placed into column D and the corresponding lagged covariances are computed with this formula in neighboring column E:

Results

The lag-1 covariance is $0.363,$ shown in cell E3. Here is the full spreadsheet:

Formulas


Please note that in time series analysis one usually computes an autocorrelation function (ACF) using a slightly different formula: refer to Understanding this acf output for details.

$\endgroup$
0
2
$\begingroup$
Day(t)  p(t)  p(t-1)
2       2.1   1.2
3       4     2.1
4       3.2   4

$$\text{Cov}(p_t,p_{t-1}) = \text{E}[(p_t - \text{E}[p_t]) \times (p_{t-1} - \text{E}[p_{t-1}])]$$

Go to: Data ---> Data Analysis ---> Covariance

enter image description here

$\text{Cov}(p_t,p_{t-1}) = 0.3633$

$\endgroup$
0
$\begingroup$

for lag of 1 in col D: Covariance.s($b$2:$b$4,c3:c4) (B's are absolute - not displaying correctly here. Drag down for different lags Autocovariance both refs in col B

$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.