vba beginner question: manipulating array data

[eggman2001]

I'm just beginning to learn VBA (have experience with Ruby and PHP) and I'm wondering how you would go about doing the following:

I have paired data in 2 columns - daily historical stock data with the date in column A and the closing value in column B, and assume I have 10 years worth of data. I'd like to figure out
a) what is the average close price for the earliest day of each month over a given date range. The earliest day of each month obviously isn't necessarily the 1st or the 2nd or 3rd of the month since there would be no data for days that are weekends or national holidays.
b) what is the average close price for the the earliest day of each month and the earliest day after the 15th of each month (collectively and not separately) given a date range.

I assume that this is something that is the kind of thing that Excel and VBA can do fairly easily, as that's why I'm learning it, but haven't quite gotten that far yet in the book I'm reading.

I appreciate any help with this. Also, if there is some good documentation for doing these kinds of operations, I'd be much obliged.

 

[jim may]

How is the average close price determined? I'd like to see your data in a spreadsheet.
Could you go to the trouble of installing one of the Sheet copying tools listed in my signature? That would be great..

Jim

 

[eggman2001]

I downloaded the date and the corresponding closing prices from an online source. I also want to mention that the this is time series data that goes back many years.

Excel Workbook
	A	B
1	date	close
2	4/29/2011	21.78
3	4/28/2011	21.74
4	4/27/2011	21.63
5	4/26/2011	21.48
6	4/25/2011	21.35
7	4/21/2011	21.37
8	4/20/2011	21.31
9	4/19/2011	21.17
10	4/18/2011	21.12
11	4/15/2011	21.27
12	4/14/2011	21.2
13	4/13/2011	21.22
14	4/12/2011	21.2
15	4/11/2011	21.27
16	4/8/2011	21.27
17	4/7/2011	21.32
18	4/6/2011	21.35
19	4/5/2011	21.33
20	4/4/2011	21.35
Sheet1

Excel 2007

 

[Ralajer]

The following code will display the combined average price for the mid and beginning of the months in the date range. The first day in the range can't be guaranteed to be the closest to the 1st of the month or the 15th. include if the day is 1-5 or 16-20.

Sub averagePriceBeginMidMonth()

    Dim rng As Range
    Dim i As Integer
    Dim count As Integer
    Dim priceSum As Double
    Dim averagePrice As Double
    
    '// Set range containing dates and prices
    Set rng = Sheets("Sheet1").Range("A2:B2500")
    
    '// Code Works on the comparison of current day to last to determine
    '//  where if falls in the month. For the first day in the range such
    '//  a comparson cannot be made. How to handle it is a choice.
    '// Assume a max 4 day weekend.
    If Day(rng(1, 1).Value) <= 5 Or _
        (16 >= Day(rng(1, 1).Value) And Day(rng(1, 1).Value) <= 20) Then
        priceSum = rng(1, 2).Value
        count = 1
    End If
    
    '// Start at second cell as comparison will not work for first row
    '// Looping to the end of data comparing the current to last
    For i = 2 To rng.Rows.count
        '// Month has change from current to last date
        '//  It is the first recored day of the current month
        If Month(rng(i, 1).Value) <> Month(rng(i - 1, 1).Value) Then
            priceSum = priceSum + rng(i, 2).Value
            count = count + 1
        '// Are the same month from above if
        '// if the current day is greater than 15 and the previous is not
        '//  The current date is the first day after the 15th
        ElseIf Day(rng(i, 1).Value) > 15 And Day(rng(i - 1, 1).Value) <= 15 Then
            priceSum = priceSum + rng(i, 2).Value
            count = count + 1
        End If
    Next i
    
    averagePrice = priceSum / count
    MsgBox "The Average Price is: " & Format(averagePrice, "0.00")
    Sheets("Sheet1").Range("C2").Value = averagePrice 
  
End Sub

For the beginning of the month average it is essentially the same as the above without a few lines.

Sub averagePriceBeginMonth()

    Dim rng As Range
    Dim i As Integer
    Dim count As Integer
    Dim priceSum As Double
    Dim averagePrice As Double
    
    '// Set range containing dates and prices
    Set rng = Sheets("Sheet1").Range("A2:B2500")
    
    '// Code Works on the comparison of current day to last to determine
    '//  where if falls in the month. For the first day in the range such
    '//  a comparson cannot be made. How to handle it is a choice.
    '// Assume a max 4 day weekend.
    If Day(rng(1, 1).Value) <= 5 Then
        priceSum = rng(1, 2).Value
        count = 1
    End If
    
    '// Start at second cell as comparison will not work for first row
    '// Looping to the end of data comparing the current to last
    For i = 2 To rng.Rows.count
        '// Month has change from current to last date
        '//  It is the first recored day of the current month
        If Month(rng(i, 1).Value) <> Month(rng(i - 1, 1).Value) Then
            priceSum = priceSum + rng(i, 2).Value
            count = count + 1
        End If
    Next i
    
    averagePrice = priceSum / count
    MsgBox "The Average Price is: " & Format(averagePrice, "0.00")
  
End Sub

 

[jim may]

Why not show us in an area of the spreadsheet "the expected output (results)" of the procedure. That would greatly help.

 

[eggman2001]

The expected output would be a single value - the average of closing values whose dates meet the criteria (earliest day of the month in one case and earliest day of the month or earliest day of the month after the 15th in the other case).

I was hoping that there was a "group by" like method where I could group by year and month, then get the minimum of each group to get the earliest day of the month.

 

[jim may]

Here's a quick way to get your "First of Month" average prices.. Maybe you can do something with this to develope "End of month" also..
Jim

In a standard module post in:

Option Base 1

Sub GetFirstOfMonthData()
Dim arr1()
Dim arr2()
Dim Rng As Range
Dim FDate As Date
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range(Cells(2, 1), Cells(LR, 2))
arr1 = Rng
ReDim Preserve arr2(2, LR)
arr2(1, 1) = Cells(2, 2)
arr2(2, 1) = Cells(2, 1)
NumMonths = 1
FDate = arr1(1, 1)
    For i = 3 To LR
        If Month(Cells(i, 1)) <> Month(FDate) Then
        NumMonths = NumMonths + 1
        arr2(1, NumMonths) = Cells(i, 2)
        arr2(2, NumMonths) = Cells(i, 1)
        FDate = DateSerial(Year(FDate), Month(FDate) + 1, 1)
        End If
    Next i
    For j = 1 To NumMonths
    MsgBox "On " & arr2(2, j) & " the avg price was " & arr2(1, j)
    Next j
End Sub