# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

##### 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
3
Views
1K
Replies
3
Views
398
Replies
3
Views
266
Replies
14
Views
3K
Replies
7
Views
105

1,181,746
Messages
5,931,787
Members
436,803
Latest member
Strycure12

### 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