using excel to analyze time series data

[eggman2001]

I have two columns of data - column A has dates in chronological order
and column B has corresponding closing prices. I'd like to
programmically (or with a formula), find the first date of each month
and add the corresponding closing prices and then divide by the number
of closing prices that meet the criteria.

Is this something that I can do in excel? Should I learn VBA? Or
should I look elsewhere?

 

[eggman2001]

Just want to also note that by the first date of each month, I meant
the earliest date of each month, which in my data isn't always the 1st.

 

[chia-yao chang]

I have two columns of data - column A has dates in chronological order
and column B has corresponding closing prices. I'd like to
programmically (or with a formula), find the first date of each month
and add the corresponding closing prices and then divide by the number
of closing prices that meet the criteria.

Is this something that I can do in excel? Should I learn VBA? Or
should I look elsewhere?

not sure if my understanding is correct~
you can use column C, say, if your data is from A2:A100, then in C2:
=EOMONTH(A2,-1)+1 and copy cell to the whole series will do.

 

[joeu2004]

I have two columns of data - column A has dates in
chronological order and column B has corresponding
closing prices. I'd like to programmically (or with
a formula), find the first date of each month and add
the corresponding closing prices and then divide by
the number of closing prices that meet the criteria.
Is this something that I can do in excel? Should I
learn VBA?

It might be doable in Excel alone. But it seems easier to do in VBA.
See the function below.

To enter the function, press alt+F11 to open the VBA window. Click
Insert > Module to open an editing pane on the left. Copy and paste
the text of the function below into the VBA editing pane. You can now
close the VBA window.

Usage.... You could write:

=avgPrice(A:A,D)

But it would be more efficient to write:

=avgPrice(A2:A4000,D24000)

Alternatively, changes can be made to the VBA function to make it
somewhat more efficient with ranges like A:A and D. However, such
ranges might be overly inclusive, including data that should not be
included in the average.

-----

'***** start copy here (you can omit this line)
Option Explicit
Function avgPrice(dR As Range, pR As Range) As Double
'calculate arithmetic average of price (pR)
'of first date in each month (dR). assume
'dR and pR are each single columns. assume
'dR and pR are in ascending order of dates
'in dR.
Dim d, p, s As Double, n As Long, i As Long
Dim m As Long, m0 As Long
d = dR
p = pR
s = p(1, 1)
m0 = Month(d(1, 1))
n = 1
For i = 2 To UBound(d, 1)
m = Month(d(i, 1))
If m <> m0 Then
s = s + p(i, 1)
m0 = m
n = n + 1
End If
Next
avgPrice = s / n
End Function
'***** end copy here (you can omit this line)

 

[eggman2001]

not sure if my understanding is correct~
you can use column C, say, if your data is from A2:A100, then in C2:
=EOMONTH(A2,-1)+1 and copy cell to the whole series will do.

I guess I'd like to do this in a way that will give me a single output
value. I assume this should get done programmically. Any thoughts?

 

[joeu2004]

Errata....

Usage....  You could write:
=avgPrice(A:A,D)

As I wrote the function, this form can__not__ be used. No matter: it
is generally not efficient, and it might not be reliable.

m0 = Month(d(1, 1)) [....]
   m = Month(d(i, 1))

This assumes essentially contiguous data -- no major gaps, like sparse
years. The modified function below is more reliable.

The following is an Excel implementation of the same algorithm. I
have not tested to see which is more efficient. Obviously the VBA
function is easier to user.

Assume dates in A2:A4000 and prices in D24000.

=(D2+SUMPRODUCT(--(A2:A3999-DAY(A2:A3999)<>A3:A4000-
DAY(A3:A4000)),D34000))
/(1+SUMPRODUCT(--(A2:A3999-DAY(A2:A3999)<>A3:A4000-DAY(A3:A4000))))


-----

Option Explicit
Function avgPrice(dR As Range, pR As Range) As Double
'calculate arithmetic average of price (pR)
'of first date in each month (dR). assume
'dR and pR are each single columns. assume
'dR and pR are in ascending order of dates
'in dR.
Dim d, p, s As Double, n As Long, i As Long
Dim m As Long, m0 As Long
d = dR
p = pR
s = p(1, 1)
m0 = d(1, 1) - Day(d(1, 1))
n = 1
For i = 2 To UBound(d, 1)
m = d(i, 1) - Day(d(i, 1))
If m <> m0 Then
s = s + p(i, 1)
m0 = m
n = n + 1
End If
Next
avgPrice = s / n
End Function

 

[joeu2004]

 The modified function below is more reliable. [....]
m0 = d(1, 1) - Day(d(1, 1))

I meant to explain....

If A2 is a date, A2-DAY(A2) is the date of the end of the previous
month. If A2-DAY(A2) = A3-DAY(A3), then A2 and A3 are in the same
month.

 

[eggman2001]

 The modified function below is more reliable. [....]
m0 = d(1, 1) - Day(d(1, 1))

I meant to explain....

If A2 is a date, A2-DAY(A2) is the date of the end of the previous
month.  If A2-DAY(A2) = A3-DAY(A3), then A2 and A3 are in the same
month.

I don't know VBA (yet). Seems like it will be useful to learn in order
to complete these types of tasks.

 

[joeu2004]

The following is an Excel implementation of the same
algorithm. I have not tested to see which is more
efficient.  Obviously the VBA function is easier to user.

Apparently, it is also much more efficient. For a large data set
(4570), the SUMPRODUCT formula takes more than 2.75 times as long as
the VBA function on my computer.

(But we are still talking about milliseconds for a single execution on
my computer.)