Hi Bill,
I think I've got it!

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim iRowOffset As Integer
Range("A1").Offset(3, 0).Value = Range("A1").Offset(2, 0).Value 'A4
takes on A3 value
Range("A1").Offset(2, 0).Value = Range("A1").Offset(1, 0).Value 'A3
takes on A2 value
Range("A1").Offset(1, 0).Value = Range("A1").Offset(0, 0).Value 'A2
takes on A1 value
If Range("A4") < "" Then
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old averages down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 3).Value = Cells(iRowOffset, 3).Value
Next iRowOffset
Let Range("B1") = Application.Average(Range("A2:A4"))
'Put time when average was calculated into C1
Let Range("C1") = Now
Range("A5").Clear
End If
Application.EnableEvents = True
End Sub

It wouldn't work till I got rid of the first loop, which was pretty
unnecessary and down right confusing.
Now, as soon as a new price comes in it is fed into A2 after all the
values below it are shifted down one row. If A2, A3 and A4 all have
values then the average is calculated and fed into B1, after all older
averages are shifted down one row, and similarly with the time that the
average was calculated.

There could still be a problem though Bill. The code is triggered by
anything that causes automatic calculation to occur, and that's just
about everything, even just typing then entering text on any sheet.
Switching to manual calc won't do cause then nothing happens. So, it
looks like the whole workbook has to be devoted to this one purpose, at
least during the period of time that you are receiving downloaded data.

Ken Johnson

Wow Ken, so simple when I see yours....lol

Using that average for A2:A4 was sweet,

I was trying to store values in variables and getting in a mess... glad you
got rid of that first loop, it was going round my head last night when
trying to sleep.

If you need any help with Small Business Server 2003, Networks, PC's just
ask. Anything else I might know a man who can...lol

It all worked manually, might get a xmas bonus monday AM... code is in boss
excel but the futures markets do not open at the weekend so nothing to test
till monday.

Thanks Again

p.s I see you are using your own email address in your posting?. I was
always advised not to or put something in it to break it up as a forum
spider will pick up your email and you will get loads of spam.


"Ken Johnson" wrote in message
oups.com...
Hi Bill,
I think I've got it!

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim iRowOffset As Integer
Range("A1").Offset(3, 0).Value = Range("A1").Offset(2, 0).Value 'A4
takes on A3 value
Range("A1").Offset(2, 0).Value = Range("A1").Offset(1, 0).Value 'A3
takes on A2 value
Range("A1").Offset(1, 0).Value = Range("A1").Offset(0, 0).Value 'A2
takes on A1 value
If Range("A4") < "" Then
For iRowOffset = Application.Count(Range("B:B")) To 1 Step -1
'Shift old averages down 1 row
Let Cells(iRowOffset + 1, 2).Value = Cells(iRowOffset, 2).Value
'Shift old time values down 1 row
Let Cells(iRowOffset + 1, 3).Value = Cells(iRowOffset, 3).Value
Next iRowOffset
Let Range("B1") = Application.Average(Range("A2:A4"))
'Put time when average was calculated into C1
Let Range("C1") = Now
Range("A5").Clear
End If
Application.EnableEvents = True
End Sub

It wouldn't work till I got rid of the first loop, which was pretty
unnecessary and down right confusing.
Now, as soon as a new price comes in it is fed into A2 after all the
values below it are shifted down one row. If A2, A3 and A4 all have
values then the average is calculated and fed into B1, after all older
averages are shifted down one row, and similarly with the time that the
average was calculated.

There could still be a problem though Bill. The code is triggered by
anything that causes automatic calculation to occur, and that's just
about everything, even just typing then entering text on any sheet.
Switching to manual calc won't do cause then nothing happens. So, it
looks like the whole workbook has to be devoted to this one purpose, at
least during the period of time that you are receiving downloaded data.

Ken Johnson

Hi Bill,
That loop was driving me crazy too.
With Email, I use my Gmail account and yes I do get a lot of spam, but
Gmail stores it separate from legitimate mail and it's really easy to
manage and get rid of.
That'll be nice if you do get a xmas bonus, glad I could help!
I trust and hope that the problem of the code being triggered by just
about any activity in that workbook is manageable. BTW if it ever stops
working the first thing you should check out is - has
ToolsOptionsCalculation been set to Manual? If so then change it back
to Automatic.
Ken Johnson