How do I normalize a time series so the series starts at 100.
As per some of my clients its a common procedure to do in Bloomberg to
normalize a data series.
He says it divides by the starting level * 100, so the series starts
at 100. It would be great if some one can provide me a formula for
this.. As a example , I have historical 5 year treasury yields as
follows. I would like to know the formula to normalize this series so
it starts from 100. Appreciate your help.

10/30/2007 4.052
10/29/2007 4.045
10/26/2007 4.036
10/25/2007 3.998
10/24/2007 3.967
10/23/2007 4.055
10/22/2007 4.057
10/19/2007 4.041
10/18/2007 4.153
10/17/2007 4.211
10/16/2007 4.342
10/15/2007 4.389
10/12/2007 4.412
10/11/2007 4.371
10/10/2007 4.369
10/9/2007 4.366
10/5/2007 4.335
10/4/2007 4.207

First we need to decide if we are going to normalize to 10/4 or 10/30. Say
we pick 10/4.

In C18 we enter 100
in C17 we enter:
=100*B17/$B$18
and copy upwards. This yields:

10/30/2007 4.052 96.316
10/29/2007 4.045 96.149
10/26/2007 4.036 95.935
10/25/2007 3.998 95.032
10/24/2007 3.967 94.295
10/23/2007 4.055 96.387
10/22/2007 4.057 96.435
10/19/2007 4.041 96.054
10/18/2007 4.153 98.716
10/17/2007 4.211 100.095
10/16/2007 4.342 103.209
10/15/2007 4.389 104.326
10/12/2007 4.412 104.873
10/11/2007 4.371 103.898
10/10/2007 4.369 103.851
10/09/2007 4.366 103.779
10/05/2007 4.335 103.043
10/04/2007 4.207 100.000


Does this look reasonable??
--
Gary''s Student - gsnu2007a


"wowfed" wrote:

How do I normalize a time series so the series starts at 100.
As per some of my clients its a common procedure to do in Bloomberg to
normalize a data series.
He says it divides by the starting level * 100, so the series starts
at 100. It would be great if some one can provide me a formula for
this.. As a example , I have historical 5 year treasury yields as
follows. I would like to know the formula to normalize this series so
it starts from 100. Appreciate your help.

10/30/2007 4.052
10/29/2007 4.045
10/26/2007 4.036
10/25/2007 3.998
10/24/2007 3.967
10/23/2007 4.055
10/22/2007 4.057
10/19/2007 4.041
10/18/2007 4.153
10/17/2007 4.211
10/16/2007 4.342
10/15/2007 4.389
10/12/2007 4.412
10/11/2007 4.371
10/10/2007 4.369
10/9/2007 4.366
10/5/2007 4.335
10/4/2007 4.207

Assuming data in A1:B18, and cell B18 holds the "base" value,

=ROUND(B1/$B$18*100,3)

and copy down.

On Wed, 31 Oct 2007 17:03:18 -0700, wowfed wrote:

How do I normalize a time series so the series starts at 100.
As per some of my clients its a common procedure to do in Bloomberg to
normalize a data series.
He says it divides by the starting level * 100, so the series starts
at 100. It would be great if some one can provide me a formula for
this.. As a example , I have historical 5 year treasury yields as
follows. I would like to know the formula to normalize this series so
it starts from 100. Appreciate your help.

10/30/2007 4.052
10/29/2007 4.045
10/26/2007 4.036
10/25/2007 3.998
10/24/2007 3.967
10/23/2007 4.055
10/22/2007 4.057
10/19/2007 4.041
10/18/2007 4.153
10/17/2007 4.211
10/16/2007 4.342
10/15/2007 4.389
10/12/2007 4.412
10/11/2007 4.371
10/10/2007 4.369
10/9/2007 4.366
10/5/2007 4.335
10/4/2007 4.207