Hi, let me explain...

I have a link from the web which is a share price. I would like to record
the last 3 price changes and then average of all three. ( price changes can
happen in seconds or minutes)

I believe I am looking to copy the content initially as a static
value(cell1), when it changes to copy it to another static cell (cell3),
when it changes again copy this to another static cell (cell3)... I hope
this is clear enough

I can work out the average...lol

The next stage would be average of another change, cell 2 + 3 + 4 but lets
get the initial problem first... :-)

TIA

Hi Maxibo,
Does this give you any ideas?

If you paste the following into the relevant worksheet module, then
when ever a value is entered into A1 it is copied into A2. Next time A1
has a value entered A2 is copied to A3 and A1 is copied to A2. Next
time A1 has a value entered A3 is copied to A4, A2 is copied to A3 and
A1 is copied to A2. The non-zero value in A4 indicates that three new
values have been entered into A1 so the average of A2:A4 is entered
into B1 and A2:A4 is cleared in preparation for the next three values
entered into A1.

Adapt this code to detect the changes in your linked cell.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
If Range("A4") < "" Then
Let Range("B1") = Application.Average(Range("A2:A4"))
Range("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Many thanks for the assistance, now the embarresing bit..

How do I get this into excel, sorry thought I knew excel... ;-)


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Hi Maxibo,
1. Copy the code.
2. Right click the tab of the worksheet that contains the Stock link
then select View Code
3. Paste the code into the blank module
4. If you see any red lines that means that a line break has broken a
single line of code and you will have to edit it back to a single line.

See how that goes. Let me know

Ken Johnson

Im trying...lol

Do I get this VB thing up in Tools, Macro and paste it in there ?

If so it looks cool and all this is intriging me. However unsure if it
matters but the target.offset row is all red in the code.

Once again thank you for assistance which I am sure you have a busy
schedule.


"Maxibo" wrote in message
...
Many thanks for the assistance, now the embarresing bit..

How do I get this into excel, sorry thought I knew excel... ;-)


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

WOW Ken, you are the man

I have used Excel for a few years but nothing to this degree.

Seeing your solution WORKED ! and now has given me ideas to use excel
better.


"Maxibo" wrote in message
...
Im trying...lol

Do I get this VB thing up in Tools, Macro and paste it in there ?

If so it looks cool and all this is intriging me. However unsure if it
matters but the target.offset row is all red in the code.

Once again thank you for assistance which I am sure you have a busy
schedule.


"Maxibo" wrote in message
...
Many thanks for the assistance, now the embarresing bit..

How do I get this into excel, sorry thought I knew excel... ;-)


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,
If you don't want to lose the average every time a new one is
calculated then try the following. The time that each average was
calculated is kept in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then
Exit Sub
End If
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset - 1,
0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
End Sub

Ken Johnson

Hi Maxibo,

This code has to go into a worksheet code module, and the quickest way
there is to rightclick the sheet tab of the sheet that has the link.
When you do the right click you should see a little popup, and on the
bottom of that popup you should see "View code".
After selecting "View code" you will be in the VBA editor, and you
should see a blank (white) area where you can paste the code. This is
the code module for the relevant sheet.

Edit any red lines to get rid of the line break that has been produced
by the Groups editor.

Try that and let me know, I'm not really busy, but I'm sure you are.

Ken Johnson

Hi Maxibo,
Well done. Glad to help.
PS I noticed we are in the same time zone. Shouldn't you be asleep. I
know I should.
Ken Johnson

LOL....

Im in UK it is only 14.30...


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,

This code has to go into a worksheet code module, and the quickest way
there is to rightclick the sheet tab of the sheet that has the link.
When you do the right click you should see a little popup, and on the
bottom of that popup you should see "View code".
After selecting "View code" you will be in the VBA editor, and you
should see a blank (white) area where you can paste the code. This is
the code module for the relevant sheet.

Edit any red lines to get rid of the line break that has been produced
by the Groups editor.

Try that and let me know, I'm not really busy, but I'm sure you are.

Ken Johnson

Hi Maxibo,
Call me a pillock. I guess I don't know how to interpret the times they
attach to the Groups posts. I'm in Sydney, Australia and it is now 1:50
am. I guess it's to do with Greewich Mean Time since your's is -0000.
I'd better stick to Excel

Ken Johnson

Hi Ken

Entering figures in manually to A1 works fine.

However, in the live environment I put a link to the share price in A1 and
nothing happened.

I put the website link somewhere else on the worksheet and put in A1 ' =cell
where share price is ' in this instance i21.

The first changed was entered in the next line but when the price changed
nothing happened.

It seems the A1 cell will only move on if the ' return / enter ' key is
pressed...lol

Wonder who will fix first..

Cheers

p.s assuming you are still awake.


"Maxibo" wrote in message
...
LOL....

Im in UK it is only 14.30...


"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,

This code has to go into a worksheet code module, and the quickest way
there is to rightclick the sheet tab of the sheet that has the link.
When you do the right click you should see a little popup, and on the
bottom of that popup you should see "View code".
After selecting "View code" you will be in the VBA editor, and you
should see a blank (white) area where you can paste the code. This is
the code module for the relevant sheet.

Edit any red lines to get rid of the line break that has been produced
by the Groups editor.

Try that and let me know, I'm not really busy, but I'm sure you are.

Ken Johnson

Hi Maxibo,
That's the worst news I've had all day!
I'll look into it. The code is in a worksheet_change module so should
be triggered when any cell changes.

Ken Johnson

Hi Maxibo,
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$I$21" Then
Exit Sub
End If
Application.EnableEvents = False
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").Offset(iRowOffset
- 1, 0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
Application.EnableEvents = True
End Sub

Let me know who won!

Hi Maxibo,

Call me a pillock!
The fourth last line: Range ("A1").Clear is deleting the link to I21.
Just get rid of that line in the above code. I hope that does it.
Did I win?

You should goto sleep...lol

Nothing works...lol, try putting a value into i21

I was looking at same concept though $i$21..lol


"Ken Johnson" wrote in message
ups.com...
Hi Maxibo,
try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$I$21" Then
Exit Sub
End If
Application.EnableEvents = False
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").Offset(iRowOffset
- 1, 0).Value
Next iRowOffset
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("A2:A4").Clear
Range("A1").Clear
End If
Application.EnableEvents = True
End Sub

Let me know who won!

Hi Maxibo,
Did you paste in the new code where I changed
"Target.Offset(iRowOffset, 0).Value = Target.Offset(iRowOffset -
1,0).Value" to " Range("A1").Offset(iRowOffset, 0).Value =
Range("A1").Offset(iRowOffset - 1,0).Value" and get rid of the fourth
last line that was deleting the link to I21 in A1?
It has to work.It works for me when I change the value in I21
Unfortunately I know nothing about linking up Excel with the web, so I
can't try out anything that way. I'll see my son after we've both had a
sleep. He might know something.
Ken Johnson

Hi Maxibo,
It's not looking good down here. I threw in another link in I21 linking
it with a cell on sheet3. I then arranged windows vertically to watch
what happened when I changed the cell on sheet3. Sheet1! I21 changed as
did Sheet1!A1 but the code was not triggered. So the sheet's changing
but it's not triggering the Worksheet_Change code.
I'm definitely going to bed now.
Ken Johnson

Hi Maxibo,
I may have hit on something!


Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").Offset(iRowOffset
- 1, 0).Value
Next iRowOffset
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("A2:A4").Clear
'Range("A1").Clear
End If
Application.EnableEvents = True
End Sub


This resides in the same module but is triggered everytime the sheet
does a calculation.
Delete or comment out the other Sub and paste the above code in place.


If you look at the top of the module on the right side you should see a
popup list that contains all of the events that a worksheet can use as
a code trigger. Calculate is the event we should have been using (I
think). Try this out with your pesky little web link.
There's also a FolowHyperlink Event which might be relevant. I'm not
sure what that means!
I still might win!

Ken Johnson

you r meant to be asleep...lol


"Ken Johnson" wrote in message
ups.com...
Hi Maxibo,
I may have hit on something!


Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim iRowOffset As Integer
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").Offset(iRowOffset
- 1, 0).Value
Next iRowOffset
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("A2:A4").Clear
'Range("A1").Clear
End If
Application.EnableEvents = True
End Sub


This resides in the same module but is triggered everytime the sheet
does a calculation.
Delete or comment out the other Sub and paste the above code in place.


If you look at the top of the module on the right side you should see a
popup list that contains all of the events that a worksheet can use as
a code trigger. Calculate is the event we should have been using (I
think). Try this out with your pesky little web link.
There's also a FolowHyperlink Event which might be relevant. I'm not
sure what that means!
I still might win!

Ken Johnson

Hi Maxibo,

A new problem!
When I was triggering the Private Sub Worksheet_Calculate() code from
sheet3 using a link to Sheet1!I21 I happened to notice it failed if I
re-entered the same value into into the cell on Sheet3. However, I only
recently learnt a trick that forces the sheet to re-calculate using the
RAND() worksheet function...

Type the following into cell A1 on Sheet1:

=RAND()*0 + $I$21


and the Private Sub Worksheet_Calculate() code will be triggered even
when the same value is entered into $I$21.


As you know, I can't fully test this myself because I don't know how to
link Excel with the web. If it's not that difficult could you give me
some clues as to how it is done?

Let me know how this all works out, I'm very curious (could end up in
the stock market myself)

Ken Johnson

Hi Ken, (btw I am Bill...lol)

This RAND seems cool and is working at home.

I would love to have the link to the web too. Unfortunately it is on the
boss pc. It is an installed program with some sort of ODBC link to excel so
I cant generate it at home.

I had to send the code to his email, remote connect to his pc and configure
his excel. He thinks I am cool putting in all this code... thanks for that
but have I dug a hole for my self?

You beat me to it but I got sidetracked as the boss pointed out that the
presently calculation is every 3 changes, he asked if it could be average of
1,2,3 then average of 2,3,4 then 3,4,5 and so on...lol

I was stuck for 2 hours trying to understand
For iRowOffset = 3 To 1 Step -1
Range("A1").Offset(iRowOffset, 0).Value = Range("A1").Offset(iRowOffset - 1,
0).Value
Next iRowOffset

I could follow irowoffset stepping 3 down to 1 but then first step of the
For (loop is the terminolgy I believe) would be (3,0) is content of (2,0),
does the step -1 reverse everything i.e:-

(3,0) is value of (2,0)
(2,0) is value of (1,0)
1,0 is value of 0,0

by putting step-1 it reverses it so:-

1,0 is value of 0,0
2,0 is value of 1,0
3,0 is value of 2,0 . which is what happens.

Sorry if its confusing

Bill



"Ken Johnson" wrote in message
oups.com...
Hi Maxibo,

A new problem!
When I was triggering the Private Sub Worksheet_Calculate() code from
sheet3 using a link to Sheet1!I21 I happened to notice it failed if I
re-entered the same value into into the cell on Sheet3. However, I only
recently learnt a trick that forces the sheet to re-calculate using the
RAND() worksheet function...

Type the following into cell A1 on Sheet1:

=RAND()*0 + $I$21


and the Private Sub Worksheet_Calculate() code will be triggered even
when the same value is entered into $I$21.


As you know, I can't fully test this myself because I don't know how to
link Excel with the web. If it's not that difficult could you give me
some clues as to how it is done?

Let me know how this all works out, I'm very curious (could end up in
the stock market myself)

Ken Johnson

Hi Bill,
So as soon as a price comes down you want the average of the new price
and the previous two. Now let me have a think about it.
I am glad to hear the code is being triggered.
I'll get back to you.
Ken Johnson

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