Calculating statistical measures based upon a given start and end date for time-series data.

[Chanko]

Hi there,

I have investment returns for stocks that I would like to calculate the mean for based upon a certain date range i.e. 1st May 2010 to 14th May 2010 (daily date to daily date) or January to March (month to month).

The spreadsheet is set up in that Sheet2 has the daily dates in “column A”, “column B” has Stock_A daily returns, “column C” has Stock_B daily returns, and “column D” has Stock_C returns.

Sheet1 has user inputs i.e. Stock, StartDate, EndDate, and DataFrequency.

I would like to create a dynamic procedure that would allow the user to pick the stock, enter the start date in question, enter the end date in question, the data frequency in question, and the mean value for that period would appear in Sheet3 in cell A1 for example.

The below is me just thinking about it.

Dim Stock As String
Dim StartDate As Date
Dim EndDate As Date
Dim DataFreQ As String

‘ User inputs
Fund = Worksheets(“Sheet1”).Range("A1")
StartDate = Worksheets(“Sheet1”).Range("A2")
EndDate = Worksheets(“Sheet1”).Range("A3")
DataFrequency = Worksheets(“Sheet1”).Range("A4")

I’m currently studying up on John Walkenbach’s excel reference guide but it’s taking time.

I’m still learning and definitely not a VBA master yet. Hence, my main question is how do I create procedure that calculates the mean for a certain start date and end date range.

Any assistance would be much appreciated.

Thanks.

 

[p45cal]

Could you put a sample workbook on the web somewhere (mediafire, box.net, 2shared.com, skydrive, wahtever)?

I'm think along the lines of a pivot table plus a little vba.

 

[Chanko]

Hi there p45cal, apologies but I'm not familar with the below mechanisms for uploading a file onto the web.

However, please see below for what I do have from my end. It's very straightforward. Let me know if this does not suffice and I'll look into uploading a sample workbook onto the web.

Thanks.

***

Sheet1
B2 = “Stock”<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
B3 = “StartDate”<o></o>
B4 = “EndDate”<o></o>
<o></o>
C2 = [Empty cell for the user to put in a stock e.g. “Stock_A”]<o></o>
C3 = [Empty cell for the user to put in a startdate e.g. dd/mm/yyyy]<o></o>
C4 = [Empty cell for the user to put in a enddate e.g. dd/mm/yyyy]<o></o>
<o></o>
<o></o>
Sheet2
A1 = “Dates”<o></o>
B1 = “Stock_A”<o></o>
C1 = “Stock_B”<o></o>
D1 = “Stock_C”<o></o>
<o></o>
A2:A11 = Dates from 01/07/2012 to 10/07/2012<o></o>
B2:D11 = Stock returns (I just used RAND to generate random numbers]<o></o>
<o></o>
<o></o>
Sheet3
A1 = [Mean value]

 

[p45cal]

Let me know if this does not suffice and I'll look into uploading a sample workbook onto the web.

If you would.
Help us help you.
Why have several people each reproducing your scenario when you can do it once (and accurately)? It'll be useful for the future for you too.

 

[Chanko]

Done deal p45cal - appreciate your patience with me.

Here's the file from 2shared: http://www.2shared.com/file/2_pZ5YcU/Test_Statiscal_Measure.html

Thanks.

 

[p45cal]

There's a link to a file here, where I've put in various formulae for your result.
There are essentially just 2 formulae, one using named ranges, one not. Both appear on Sheet3 and both on Sheet1 (to make it easier for me to develop).
All formulae are to be ARRAY-ENTERED, meaning you commit the formula to the sheet with Ctrl+Alt+Enter, not just Enter.
There are some named ranges in the file:

Excel Workbook
	=Sheet1!$C$2
Start	=Sheet1!$C$3
End	=Sheet1!$C$4
Dates	=Sheet2!$A$2:$A$11
Stocks	=Sheet2!$B$1:$D$1
Sheet

I'm using xl2003 which doesn't have the AverageIf function of later versions, which would probably be neater.

At the moment, the Dates and Stocks named ranges are hard-coded, but they can be made dynamic, meaning they'll change to reflect how much data there is.
Delete all but the one formula you want to use.
There's stuff in Sheet3 cells C5:D9 which should be deleted.

I may still look at pivot tables/vba.

 

[Ali Kırksekiz]

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">0,6327%</TD></TR></TBODY></TABLE>

Sheet3​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">{=AVERAGE(IF((Sheet2!$A$2:$A$30>=Sheet1!C10)*(Sheet2!$A$2:$A$30<=Sheet1!C11),INDIRECT("Sheet2!"&ADDRESS(2,MATCH(Sheet1!C9,Sheet2!B1:D1,0)+1)&":"&ADDRESS(30,MATCH(Sheet1!C9,Sheet2!B1:D1,0)+1))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

 

[Chanko]

p45cal: Works great - going to play around with it and will advise if I need further assistance as going forward, I'll including other statistical measures. I love learning so I'll be keen to know how to do this on VBA. Once again, thanks for swiftness of your replies.

Ali Kırksekiz: Will also try this out. Cheers.