table to show the average price macro

A problem about excel macro.
If the price of stock price is keep on updateing every business days,( colume A is the date ,colume B is stock price). And i need to tablet a table to show the average price of each month from May 2007 to May 2008, how can i use the excel VB to do that? For the unknow date and share price which are updating automatically, how can i make the excel to show the average table is changed follow by the updated data? if adding a chart to show the average price, how is the chart keep on changing.

I am totally confused and need helps...

Hi

See if the attached gives you some ideas. It would need to be extended depending on how far you want to go....

rylo

Originally Posted by rylo

Hi

See if the attached gives you some ideas. It would need to be extended depending on how far you want to go....

rylo

Thanks.
That is what i really want. However, i don't understand that the code you are using. Can you show the steps to guide me how to do this on my own?

Hi

The dates in E1:U1 are built dd/mm/yy using the 1st of the month. This date is used in the SUMPRODUCT functions that work out the average.

Instead of using the AVERAGE function, I've calculated to total price for the month, and divided by the count of instances of the month.

The count is worked out by
SUMPRODUCT(--($A$2:$A$1000>=E1),--($A$2:$A$1000<F1))
So, if the date is greater than or equal to the date in row 1, but less than the date in the next column, then the above will genearate a count. The -- are used to turn the true false into positive numbers.

The sum of the prices is worked out by
=SUMPRODUCT(--($A$2:$A$1000>=E1),--($A$2:$A$1000<F1),($B$2:$B$1000))
Same deal as above to work out which items fall into the date range and then grab the relevant number.

Easiest thing with something like this is to build a small sample, and pull the formula apart in small chunks to see what each bit does.

HTH

rylo

hi......

I don't know why i failed to use this formula, even i had changed the row number, there was a default number came out. Furthermore, if i only copy and paste, it only shows the copy and paste step in the VB editor. For what i would like to obtain the code which show all the step of how i can get the result.

Struggling with this problem, i attended to use SELECT CASE.. I will appreciate if you feel free to have a look what i had done halfway.


Dim i As Double 'datatype

For i = 1 To r.Rows.Count

' check date is correct

If IsDate(r.Cells(i, 1)) = True Then

If Year(r.Cells(i, 1).Value) = 2007 Then

Select Case Month(r.Cells(i, 1).Value)

Case 1 ' January 07 data
Dim n1 As Double, count1 As Integer
n1 = n1 + r.Cells(i, 1).Offset(0, 1).Value
count1 = Count + 1
Worksheets("Sample").Cells(3, n).Value = n1 / count1

Case 2 'February 07 data
Dim n2 As Double, count2 As Integer
n2 = n2 + r.Cells(i, 1).Offset(0, 1).Value
count2 = count2 + 1
Worksheets("Sample").Cells(4, n).Value = n2 / count2

Case 3 ' March 07 data
Dim n3 As Double, count3 As Integer
n3 = n3 + r.Cells(i, 1).Offset(0, 1).Value
count3 = count3 + 1
Worksheets("Sample").Cells(5, n).Value = n3 / count3
........
.......
End Select


End If

End If

Next i

End Sub

Does it works?
If your method do in VB, what will the coding look like?

Thanks for all the effects you had put in to help me slove this problem, i am also a self-study beginner of do this programming. Really thanks alot

Hi

1 ) can you please edit your post and wrap your code.

2) I didn't use any code to create the output. It is all done with functions.

I'm not really sure what you are asking as I didn't use any code.

rylo