perform monthly calculations on daily data

jturn00

Board Regular
Joined
Jul 21, 2004
Messages
80
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,147,622
Messages
5,742,197
Members
423,711
Latest member
luisfreitas

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top