can this be done faster?

[frank]

Hi my question is simple and I do know a very slow and tedious way of doing
it. Anybody can offer me any advice to do it in a faster way? Here is my data
series.

I have an index daily closing price for 19 years.

Date Price
1/1/1998 20.30
1/2/1998 20.80
1//3/1998 20.45
****** ****
1/31/1998 23.98
2/1/1998 26.00
****** ****
9/31/2006 120.60

I want to compute monthly volatility (standard deviation) using daily
return. So for each month, I will use stdev function and select the
corresponding daily return (ranges from 20-25 data points depending on how
many trading days in that specific month). I need to do stdev functions for
200+ times to complete this exercise. Any one know any better way to do this?

 

[Ron Rosenfeld]

Hi my question is simple and I do know a very slow and tedious way of doing
it. Anybody can offer me any advice to do it in a faster way? Here is my data
series.

I have an index daily closing price for 19 years.

Date Price
1/1/1998 20.30
1/2/1998 20.80
1//3/1998 20.45
****** ****
1/31/1998 23.98
2/1/1998 26.00
****** ****
9/31/2006 120.60

I want to compute monthly volatility (standard deviation) using daily
return. So for each month, I will use stdev function and select the
corresponding daily return (ranges from 20-25 data points depending on how
many trading days in that specific month). I need to do stdev functions for
200+ times to complete this exercise. Any one know any better way to do this?

Set up a Pivot Table.

1. Make sure your dates are Excel dates and not Text (e.g.
=ISTEXT(date_reference) should return FALSE.

2. Data/Pivot Table and Chart/Finish

3. Drag "DATE" to the row area
4. Drag "PRICE" to the Data area.

5. Select some cell in the ROWS area (which will be a date)
6. Right Cllick
Group and Show Detail
Group
Select Years and Months

7. Select a cell in the DATA area
8. Right click
Field Setttings
Select STDEV


There are ways of automating the process using VBA so that once you have the
Pivot Table set up, you can "refresh" it with new data. But the above should
get you started on an easier process than what you are now doing.

Good luck!

Another method, of course, would be to use a charting program like Metastock --
but that would be a lot more expensive.


--ron

 

[Bernard Liengme]

I will (for the example) assume data is in A2:B1000
In F1:F19 put the years (1998, 1999 ...)
In E2:E13 put month numbers (1,2,3 ... 12)
In F2 enter
=STDEV(IF((MONTH(A2:A1000)=$E2)*(YEAR(A2:A1000)=F$1),($B$2:$B$1000))) and
complete it with CTRL+SHIFT+ENTER not just ENTER because it is an array
formula - Excel will enclose the formula in curly braces {} Pay close
attention to the location of the $ symbols - to make absolute references to
rows and columns.
Copy the formula down and across the table
best wishes

 

[Bernard Liengme]

Step 5: I found one had to right click the DATE label, not a cell with a
date value, in order to get the menu that permitted month & year group.
But, thanks, I learnt something more about PTs.
best wishes

 

[frank]

Hi Bernard, using this approach, I found it works for Jan 1988 and it does
work from Feb 1988. The number I got is different from mannually computed.
Thanks for your input.

 

[frank]

problem solved by modify the cell calculation to:

=STDEV(IF((MONTH(A$2:A$1000)=$E2)*(YEAR(A$2:A$1000)=F$1),($B$2:$B$1000)))

 

[Bernard Liengme]

Yes, this was a silly error of mine! Sorry

 

[ExcelEmailer]

Hi All,
This is a great way to learn hints, and in reading about Pivot Tables below,
I thought someone may know the solution to my problem:
- I have 2 columns of data, A is the month/year and B is the PERCENT RETURN
for that month
- I have 17 months of data, and was asked to ANNUALIZE the ENTIRE PERIOD?
- Is this possible ? I tried using the (1 + (RETURN ^(17/12) * (1 +
(RETURN ^(17/12), which is time consuming and somehow got a ridiculous
number????
- HOW DO I ANNUALIZE PERCENT RETURNS more efficiently? Arrays? Pivot
tables? I am so overwhelmed and forget all that I learned LONG ago...ANY
HELP WILL COME BACK TO YOU IN GOOD VIBES! Keep up the great posts!