# perform monthly calculations on daily data

#### jturn00

##### Board Regular
I have a spreadsheet that contains Dates, stock prices and returns over a 10 year period.
Volatility Estimation Techniques.xls
ABCD
2DatePxLastPn/Pn-1LN(Pn/Pn-1)
31/13/19940.9063#REF!#REF!
41/14/19940.93751.0344260.0338464
51/17/199411.0666670.0645385
61/18/19940.93750.9375-0.064539
71/19/19940.84380.900053-0.105301
81/20/19940.85941.0184880.018319
91/24/19940.84380.981848-0.018319
101/25/19940.85941.0184880.018319
111/26/19940.81250.945427-0.056119
121/27/19940.84381.0385230.0377996
131/28/19940.843810
141/31/19940.81250.962906-0.0378
152/1/19940.84381.0385230.0377996
162/2/19940.750.888836-0.117842
172/3/19940.84381.1250670.1178423
182/4/19940.843810
192/8/19940.82810.981394-0.018782
202/9/19940.84381.0189590.0187816
212/11/19940.8751.0369760.0363084
222/14/19940.95311.0892570.0854959
232/15/19941.03131.0820480.0788556
242/16/19941.06251.0302530.0298045
252/17/19941.062510
Daily - 10 yrs

I want to take this 10 year history and create an average return on a monthly basis column D and calculate the standard deviation on a monthly basis. Any ideas. I would like to put the results on a separate sheet / table. I didn't use a pivot table since grouping put all the JAN months together I need them separated out by year. I also couldn't figure out how to put a standard deviation equation into a pivot table. I will use the results to import into another software application so having the results in a excel sheet (non pivot table) seems easier but considering my experience I could be wrong.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### MrExcel MVP
You can group by month then by year...

#### jturn00

##### Board Regular
Thanks,

Is there any way to do not using a pivot table???

Jeff

Replies
0
Views
58
Replies
11
Views
655
Replies
0
Views
533
Replies
1
Views
204
Replies
0
Views
205

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back