Auto refresh stock value copied to next row

[pucha]

I have the stock value which gets auto refresh every minute. I want to copy the stock value changed every minute to next row automatically, i.e. every time stock value changes it will go to next row along with the time in another cell, so that I can make a chart out of it. Is there any formula to do so.


Regards

 

[Luke M]

Can't do that using just formulas, but here's the macro that should do it for you. Right click on sheet tab, and paste this in. Currently setup so that stock price is in B2, and data is recorded directly below the stock price with the time stamp in previous column (col A) to aid in creating the chart.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

'Where is the stock price?
Set MyRange = Range("B2")
If Intersect(Target, MyRange) Is Nothing Then Exit Sub
Application.EnableEvents = False

Dim LastRow As Integer
LastRow = Cells(65536, MyRange.Column).End(xlUp).Row

Cells(LastRow + 1, MyRange.Column) = MyRange.Value
Cells(LastRow + 1, MyRange.Column - 1) = Now

Application.EnableEvents = True
End Sub

[/pre]

 

[pucha]

Brilliant!


Thank you Luke M.

 

[Luke M]

You're welcome, pucha. =)

 

[pucha]

Hello Luke M, Request for one more help from you.


I import the data(Stock)in this manner, (A1 to A4) & (B1 to B4). I am using Excel 2003.

Feb 24, 10:35 Low

28889.00 243.00 (0.85%) High

Volume 28723

40181 28944


Firstly, I would like these data to be in a single row.

Date & Time High Low Volume Stock Price Point Change % Change

Feb 24, 10.35 28944 28723 40181 28889 243 0.85


Secondly, breaking the data imported in a single cell i.e (28889.00 243.00 (0.85%)) into (Stock Price, Point Change & % Change) and finally placing them in a row as above.


Thirdly, after auto refresh every minute, I want to copy the stock value row changed every minute to next row automatically, like the above case.


Thanks in advance.

With Regards.

 

[Luke M]

We'll use some formula to translate the data from A1:B4 into a row below, say A6 (with the headers you've given in row 5).


A6:

=A1

B6:

=B4

C6:

=B3

D6:

=A4

E6:

=VALUE(LEFT(A2,FIND(" ",A2)-1))

F6:

=VALUE(MID(A2,LEN(TEXT(E6,"#.00"))+2,FIND(" ",A2,LEN(TEXT(E6,"#.00"))+2)-LEN(TEXT(E6,"#.00"))-2))

G6:

=VALUE(MID(A2,FIND("(",A2)+1,FIND(")",A2)-1-FIND("(",A2)))

You may choose to format the numbers/columns as you see fit. Then, to adapt the macro from above:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

'Where is the stock price?
Set MyRange = Range("A2")
If Intersect(Target, MyRange) Is Nothing Then Exit Sub
Application.EnableEvents = False

Dim LastRow As Integer
LastRow = Cells(65536, MyRange.Column).End(xlUp).Row

'Copy the row with formulas
Range("A6:G6").Copy
'Paste as values only
Cells(LastRow + 1, "A").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Application.EnableEvents = True
End Sub

[/pre]

 

[pucha]

Hi Luke M


Sorry for late reply.

Thank you once again for solving this problem. I am really great full to you. It works like the way I needed.


With regards